7

I have to collect some information of PostgreSQL roles. One piece of information is whether the role has different settings than 'usual', usual meaning here default values for only a few parameters (eg. log_statement).

So far I have only one idea: loop over all the users and issue the commands

\c postgres username

SHOW log_statement;

and then process the output.

I am wondering whether there is a similar method like querying the pg_settings view, showing per-user settings? I don't need to know session-specific differences, only those that are persisted.

2 Answers 2

8

You can get the role configuration by querying pg_catalog.pg_shadow or pg_catalog.pg_user, as the following:

--Query pg_shadow

francs=# select usename,useconfig from pg_shadow ;
   usename    |           useconfig           
--------------+-------------------------------
 postgres     | 
 user_a       | 
 user_b       | 
 skytf_select | {"search_path=skytf, public"}
 francs       | 
 dwetl        | 
 skytf        | 
(7 rows)

francs=# alter role francs  set log_statement='all';
ALTER ROLE

francs=# select usename,useconfig from pg_shadow ;
   usename    |           useconfig           
--------------+-------------------------------
 postgres     | 
 user_a       | 
 user_b       | 
 skytf_select | {"search_path=skytf, public"}
 rancs        | {log_statement=all}
 dwetl        | 
 skytf        | 
(7 rows)
2
  • Thanks a lot! I started browsing the source code and came to pg_db_role_setting which contains the data, but of course the views you mentioned are easier to use. Apr 19, 2013 at 9:53
  • @dezso: I find pg_db_role_setting is better later, thanks .
    – francs
    Apr 20, 2013 at 3:10
10

Unfortunately pg_shadow does not show roles with no LOGIN privilege.

I find it more useful to use @dezso hint view in the following query:

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;

As always, just add it to psqlrc file (see doc)

1
  • 5
    Or just type \drds in psql console )
    – Zorg
    Aug 8, 2017 at 19:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.