PostgreSQL search_path change not working as advertised

postgresql privileges search-path
talonsensei picture talonsensei · Sep 13, 2011 · Viewed 15k times · Source

I am using PostgreSQL 9.0.3 on RedHat. The database contains two schemas, public and wh. I created a new role called django. I want this user to use the wh schema as it's default.

Following the manual, I did:

ALTER USER django SET SEARCH_PATH TO wh, public;

This appears to work:

SHOW SEARCH_PATH;
search_path 
-------------
wh, public

However, if I then do a \dt, only tables from the public schema are displayed. In the manual, changing the search path should have an immediate effect, and I should be able to access wh tables without a prefix, but this is not the case. Logging in and out preserves the changes to search_path but does not show any change of behavior.

What am I missing?

Answer

Erwin Brandstetter picture Erwin Brandstetter · Sep 14, 2011

GRANT might solve your problem:

GRANT USAGE ON SCHEMA wh TO django;

(Or GRANT USAGE ... to any role which has django as a (direct or indirect) member.)
(Or GRANT ALL ... if that is what you want.)

Setting the search_path instructs Postgres to look for objects in the listed schemas. It does not grant permission to see what's there. If "django" does not have the necessary privileges, \dt must not (and does not) show that information.

On the other hand, if you have already tried as superuser (as per your comment on the previous suggestion), then this might not be it ...

Related questions

Permission denied for relation

I tried to run simple sql command: select * from site_adzone; and I got this error ERROR: permission denied for relation site_adzone What could be the problem here? I tried also to do select for other tables and got …

Read More
Give all the permissions to a user on a DB

I would like to give an user all the permissions on a database without making it an admin. The reason why I want to do that is that at the moment DEV and PROD are different DBs on the same …

Read More
Grant all on a specific schema in the db to a group role in PostgreSQL

Using PostgreSQL 9.0, I have a group role called "staff" and would like to grant all (or certain) privileges to this role on tables in a particular schema. None of the following work GRANT ALL ON SCHEMA foo TO staff; GRANT …

Read More