PostgreSQL search_path change not working as advertised

Question

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?

Cotter

Answer 1

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

Varied

Hi Erwin, This worked! It is very odd to me that granting superuser to this role did not accomplish this. However, the explicit grant on the schema makes the behavior correct. Thank you very much and thanks to all who contributed. – Cotter

When querying a table without schema qualification such as SELECT id FROM mytable, PG will say relation "mytable" does not exists, which is a bit misleading since you know it does exists, but maybe you just don't have access. Therefore when you query the table with a properly qualified schema name such as SELECT id FROM myschema.mytable; you will instead get the message: permission denied for schema .. which clearly points out a GRANT USAGE ON SCHEMA .. statement is needed. This is just one more reason why using fully qualified schema names in queries is a good idea. – Dvandva

Hey Erwin, Thanks for the solution, you have saved me ! :-) and thanks to talonsensei for initiating the question here :-) – Egarton

Answer 2

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)

Thorny

Thank you Milen. I have done that. With the primary admin/superuser role this works and a \dt will only show tables in the wh schema. However, for a reason I don't understand, when I do this for another user, even when I made them also admin/superuser, a \dt still only shows public tables, not wh, even though the path is changed. – Cotter

If you could reliably reproduce this and could devise a small test case, I believe the developers will want to hear from you. But first you have to ensure you're testing/reproducing this on the latest minor version (9.0.4) of the major version you're using (9.0). – Thorny

Thanks Milen. It is reproducible I believe. I tested on both 9.0.1 (Snow Leopard) and 9.0.3 (Redhat) installs. – Cotter

Answer 3

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.

Oshinski

Thanks for the suggestion. I tried that, but I get an error saying the table does not exist. If I prefix the query with wh, it works. So it doesn't appear to be a \dt limitation – Cotter

Answer 4

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!

Catabasis

Hi Francs, Your suggestion makes perfect sense and I tried it, but the default search space for user wh is still "$user",public and a \dt still only shows public tables. The output of current_schemas(true) is {pg_catalog,public}. So it does not appear to work. – Cotter

now, what's your user name and schema name ? if the database is not a prod db, you can make the both the same name. And also notice the table's owner name. – Catabasis

Answer 5

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 :)

Fontanel