As you might know you can set various parameters on the session level in PostgreSQL by using the “set” command:
postgres=# h set Command: SET Description: change a run-time parameter Syntax: SET [ SESSION | LOCAL ] configuration_parameter { TO | = } { value | 'value' | DEFAULT } SET [ SESSION | LOCAL ] TIME ZONE { timezone | LOCAL | DEFAULT }
This allows a session to adjust parameters at runtime and can be a handy way for on the fly configuration when you need special settings. Wouldn’t it be great if we could have a default set of parameters for a role or user? Maybe there is one user who needs a special setting for work_mem and another one who needs a special setting for search_path. Instead of setting that each time after the connect in the session you can also do that on the server side.
Lets create to users, user a and user b:
postgres=# create user a login password 'a'; CREATE ROLE postgres=# create user b login password 'b'; CREATE ROLE postgres=# du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- a | | {} b | | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
When we want a special setting for work_mem every time user a creates a new connection and a special setting for search_path every time user b creates a connection we can do it like this:
postgres=# alter user a set work_mem = '1MB'; ALTER ROLE postgres=# alter user b set search_path='b'; ALTER ROLE postgres=#
When user a connects from now on:
postgres=# c postgres a You are now connected to database "postgres" as user "a". postgres=> show work_mem; work_mem ---------- 1MB (1 row)
When user b connects from now on:
postgres=> c postgres b You are now connected to database "postgres" as user "b". postgres=> show search_path ; search_path ------------- b (1 row)
Notice that this does not prevent a user from overriding the setting:
postgres=> select current_user; current_user -------------- b (1 row) postgres=> set search_path=c; SET postgres=> show search_path ; search_path ------------- c (1 row)
… this is more meant as setting defaults that differ from the main server configuration where it makes sense. And how can you know then which settings are configured for a specific role? Easy, there is pg_roles:
postgres=> select rolname,rolconfig from pg_roles where rolname in ('a','b'); rolname | rolconfig ---------+----------------- a | {work_mem=1MB} b | {search_path=b} (2 rows)
Good to know…