8

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.

Improve this question

2 Answers 2

Reset to default
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)
Improve this answer
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. Commented Apr 19, 2013 at 9:53
  • @dezso: I find pg_db_role_setting is better later, thanks . Commented Apr 20, 2013 at 3:10
11

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)

Improve this answer
1
  • 6
    Or just type \drds in psql console ) Commented Aug 8, 2017 at 19:32

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.