PostgreSQL search_path change not working as advertised

I am using PostgreSQL 9.0.3 on RedHat. The database contains two schemas, public and wh. I cr开发者_如何学编程eated 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?

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 ...

I just tested it on (just releases) 9.1 on Windows 64-bit and it worked as specified.

Excerpt from the ALTER ROLE manpage:

The remaining variants change a role's session default for a configuration variable, either for all databases or, when the IN DATABASE clause is specified, only for sessions in the named database. Whenever the role subsequently starts a new session, the specified value becomes the session default, overriding whatever setting is present in postgresql.conf or has been received from the postgres command line. This only happens at login time; executing SET ROLE or SET SESSION AUTHORIZATION does not cause new configuration values to be set.

(emphasis mine)

That might be a limitation of the \dt command.

To verify that the search_path is working properly, try to run SELECT * FROM some_table where some_table is one that is located in the wh schema.

For PostgreSQL , if a user connect a database and look for objects like a table , first it will looks for the schema just as the same name of user name ,if not found ,it will look for public schema, In your case, if you connect the database via django user, it will default looks for the schema django , but you want to the current schema is wh, so make the schema name and the role name the same, and than login the database as the role will sove your problem ,without typing the prefix, just have a try!

For me the problem was I was trying to set the search path in pgAdmin. For some reason it wasn't applying the changes to the search_path. (It kept setting the parameters on the database?)
I logged in via psql and ran the exact same commands and it worked. Maybe I did something wrong but this may help others if they are doing something wrong too :)