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.
Best Answer
You can get the role configuration by querying pg_catalog.pg_shadow or pg_catalog.pg_user, as the following:
--Query pg_shadow