Re: how to list privileges on the database object itself via SQL?

On Thu, Apr 27, 2023 at 12:34 AM richard coleman <
rcoleman(dot)ascentgl(at)gmail(dot)com> 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