On Thu, 2011-08-18 at 12:48 +0530, Siva Palanisamy wrote:
> Hi John,
>
> I logged into the same database. I can say the commands, and you can correct me if I'm wrong.
>
> Ordinary User: psql -h localhost -d db -U ordinaryusername
> Select * from contacts
> Now, I can access the tables. I also do have the .SQL file where it states the table schema as follows:
> CREATE USER sa;
> GRANT ALL ON DATABASE db TO sa;
> \c db sa
> CREATE SCHEMA AUTHORIZATION sa;
> ALTER USER sa SET search_path TO sa,public;
This statement changed the search_path of user sa.
> CREATE TABLE sa.contacts (
> contact_id SERIAL PRIMARY KEY,
> contact_type INTEGER DEFAULT 0,
> display_name TEXT NOT NULL DEFAULT '',
> UNIQUE(display_name)
> ) WITHOUT OIDS;
>
Here you created the table contacts in the schema sa.
> I logged into the database as a super-user: psql -h localhost -d db -U postgres
> Select * from contacts;
> ERROR: relation "contacts" does not exist.
>
SELECT * FROM sa.contacts
would work.
Or
SET search_patch TO sa, public;
SELECT * FROM contacts
would work too.
And, please, don't top-post.
--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com
