Viswamitra's blog

Sometimes we set parameters in PostgreSQL for user and database specific. So the parameters will take effect only for that user when he connected to that database. To do that we can use below command

--- set statement timeout for a user to 1 min when he connect to postgres database
ALTER USER viswamitra IN DATABASE postgres SET statement_timeout TO 60000;

We can see this value only when we connect to this database with user and execute “show <parameter>”. To see all such configuration parameters use the below command.

SELECT coalesce(role.rolname, 'database wide') as role, 
       coalesce(db.datname, 'cluster wide') as database, 
       setconfig as what_changed
FROM pg_db_role_setting role_setting
LEFT JOIN pg_roles role ON role.oid = role_setting.setrole
LEFT JOIN pg_database db ON db.oid = role_setting.setdatabase;

Example

postgres=# ALTER USER viswamitra IN DATABASE postgres SET statement_timeout TO 60000;
ALTER ROLE
postgres=#
postgres=# SELECT coalesce(role.rolname, 'database wide') as role,
postgres-#        coalesce(db.datname, 'cluster wide') as database,
postgres-#        setconfig as what_changed
postgres-# FROM pg_db_role_setting role_setting
postgres-# LEFT JOIN pg_roles role ON role.oid = role_setting.setrole
postgres-# LEFT JOIN pg_database db ON db.oid = role_setting.setdatabase;
    role    | database |       what_changed
------------+----------+---------------------------
 viswamitra | postgres | {statement_timeout=60000}
(1 row)