Waiting for 8.5 – GUC per user and database

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.

One thought on “Waiting for 8.5 – GUC per user and database”

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

Comments are closed.