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…