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

Erik,

Thanks for that. I'll have to look into the aclexplode() function some
more.

When I try running your SQL on a pg11 database I get:

ERROR: role "role" does not exist
LINE 3: WHERE grantee = 'role'::regrole AND privilege_type I...
^
SQL state: 42704
Character: 151

rik.

On Wed, Apr 26, 2023 at 1:17 PM Erik Wienhold <ewie(at)ewie(dot)name> wrote:

> > On 26/04/2023 18:34 CEST richard coleman <rcoleman(dot)ascentgl(at)gmail(dot)com>
> wrote:
> >
> > 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?
>
> If you're interested in specific roles or privileges, then aclexplode is
> your
> friend.
>
> SELECT
> datname,
> grantor::regrole,
> grantee::regrole,
> privilege_type,
> is_grantable
> FROM
> pg_database,
> aclexplode(datacl)
> WHERE
> grantee = 'role'::regrole
> AND privilege_type IN ('CREATE', 'CONNECT', 'TEMPORARY');
>
> --
> Erik
>
>
>