Re: How to access tables using a superuser

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