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 🙂