On Thu, Apr 27, 2023 at 12:34 AM richard coleman <rcoleman.ascentgl@xxxxxxxxx> wrote:
All,This might sound like a silly question, but how would I list the privileges the various roles have on the database objects themselves?There appear to be a million ways to list the privileges on various database objects; tables, views, foreign tables, etc. but for the life of me there doesn't appear to an analogous way to get permissions on the database objects themselves.At the moment all that I've found is:select has_database_privilege('role','db_name', 'CREATE') as can_create;
select has_database_privilege('role','db_name', 'CONNECT') as can_connect;
select has_database_privilege('role','db_name', 'TEMP') as can_temp;Am I missing something?Thanks,rik.
I found this is more right than the aclexplode function.
to make it one query, then you need to construct a values table like CROSS JOIN (values('CREATE'), ('CONNECT'), ('TEMPORARY')) s(priv)
also join view pg_roles.
I don't think aclexplode can handle grants on roles. like grant alice to bob
