[Solved] postgresql schema search path

[eric235u]

we had a problem the other day where i was able to view other people's tables when i used the cpanel create database link. this was escalated and appeared to be resolved. see: http://www.helionet.org/index/index.php?sh...ic=5419&hl=

 

now it appears there may be an issue with the default schema. i can still create a database using cpanel but i can't use it. i'm unable to interact with it without creating and specifying a schema. this isn't a big deal and i can do it but i just wanted to verify this is how we should be doing it on the server.

 

i think the way it should work is that when you create a database in cpanel it automatically creates a schema the same as the users name. then when the user connects they would automatically use their own private schema and would not have to specify a schema in their sql. please see http://www.postgresql.org/docs/8.4/interac...CHEMAS-PATTERNS

 

create schema prod_schema;

 

SET search_path TO prod_schema,public;

 

show search_path; -- note that it doesnt show my new schema

-- $user,public -- i think that the schema $user, which should be my user name, is never created

 

CREATE TABLE errors (

"when" time without time zone,

what character varying(100),

who integer

); -- note that this does now work with the following error.

-- ERROR: no schema has been selected to create in

 

CREATE TABLE prod_schema.errors (

"when" time without time zone,

what character varying(100),

who integer

); -- note that this does work as i specified a schema.

[Wizard]

This support request is being escalated to our root admin.

[Ashoat]

I'm not experienced with PostgreSQL, so this might be a stupid question... but can't you create a schema and associate the database with it, so that you won't have to specify the schema in every query?

[eric235u]

i finally got it working.

 

i created a database in cpanel. then i created a schema in phppgadmin with the same name as my username. since $user is in search_path my user will now get access to the schema. so my php code can now use the db. :-) i still haven't been able to get this for other users created via cpanel as postgresql keeps complaining about the new user not being a member of the role of my default user name. my default user does not have the permission to alter roles. so it appears if somebody wants that functionality they would have to request it from the admin. i could be wrong. if there's anybody else using postrgresql please let me know if i misunderstand.

 

anyway all is well. many thanks for your time guys.