On 7th of October Alvaro Herrera committed his own patch, which adds quite interesting possibilty:
Log Message: ----------- Make it possibly to specify GUC params per user and per database. Create a new catalog pg_db_role_setting where they are now stored, and better encapsulate the code that deals with settings into its realm. The old datconfig and rolconfig columns are removed. psql has gained a \drds command to display the settings. Backwards compatibility warning: while the backwards-compatible system views still have the config columns, they no longer completely represent the configuration for a user or database. Catalog version bumped.
So, as you probably know you can specify several GUCs per database or per user.
Let's see that – first we need to create some test users and databases:
# create user test1; # create user test2; # create database db1; # create database db2;
Now, let's make sure that we have some non-standard settings:
# alter user test1 set client_encoding = 'latin2'; # alter DATABASE db1 set search_path = 'xxx, public';
And now, let's see how the settings are visible for users:
\connect db1 test1 # select current_setting('search_path') as search_path, current_setting('client_encoding') as client_encoding; search_path | client_encoding ---------------+----------------- "xxx, public" | latin2 (1 row) \connect db2 test1 # select current_setting('search_path') as search_path, current_setting('client_encoding') as client_encoding; \connect db1 test2 search_path | client_encoding ----------------+----------------- "$user",public | latin2 (1 row) # select current_setting('search_path') as search_path, current_setting('client_encoding') as client_encoding; \connect db2 test2 search_path │ client_encoding ───────────────┼───────────────── "xxx, public" │ UTF8 (1 row) # select current_setting('search_path') as search_path, current_setting('client_encoding') as client_encoding; search_path │ client_encoding ────────────────┼───────────────── "$user",public │ UTF8 (1 row)
Which is all fine, cool, and predictable, but has one very important drawback – it is not possible to set specific parameter for user, but only when he connects to give database.
For example – I might want to set specific search_path for user depesz, but only in database test1 – not in others.
Now, thanks to Alvaro it's actually possible:
# ALTER ROLE test2 IN database db2 SET search_path = ‘something, really, cool';
And it works as expected:
\connect db1 test1 # select current_setting('search_path') as search_path, current_setting('client_encoding') as client_encoding; search_path | client_encoding ---------------+----------------- "xxx, public" | latin2 (1 row) \connect db2 test1 # select current_setting('search_path') as search_path, current_setting('client_encoding') as client_encoding; search_path | client_encoding ----------------+----------------- "$user",public | latin2 (1 row) \connect db1 test2 # select current_setting('search_path') as search_path, current_setting('client_encoding') as client_encoding; search_path │ client_encoding ───────────────┼───────────────── "xxx, public" │ UTF8 (1 row) \connect db2 test2 # select current_setting('search_path') as search_path, current_setting('client_encoding') as client_encoding; search_path │ client_encoding ───────────────────────────┼───────────────── "something, really, cool" │ UTF8 (1 row)
Nice. Helpful.
ALTER ROLE test2 IN database db2 SET search_path = ’something, really, cool’;
I’m waiting for this feature already about 3 years, because many programms does not work correctly with schemas e.g. GIS software etc. I’m using in one database 2 shemas ‘gis’ (PostGIS functions) AND ‘public’ (main database objects).
Thank You for good news 🙂