How to get user-specific settings in PostgreSQL? 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.
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)