November 16th, 2009 by depesz | Tags: , , , , | 1 comment »
Did it help? If yes - maybe you can help me?

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.

  1. One comment

  2. # xor
    Nov 16, 2009

    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 🙂

Leave a comment