Re: NULL pg_database.datacl

On Mon, Mar 20, 2023 at 1:18 PM Erik Wienhold <ewie(at)ewie(dot)name> wrote:

> > On 20/03/2023 11:52 CET Dominique Devienne <ddevienne(at)gmail(dot)com> wrote:
> > What does a NULL AclItem[] mean exactly?
>
> It means that the object has default privileges (before any GRANT or REVOKE
> is executed). For databases this means full privileges for the database
> owner
> and the CONNECT and TEMPORARY privileges for PUBLIC. So any user can
> connect
> if allowed by pg_hba.conf.
>
> https://www.postgresql.org/docs/current/ddl-priv.html

Thanks Erik. But then, how come aclexplode() is not showing these
default/implicit privileges?

Is there a SQL function returning those per-type default provileges?
That I could then coalesce() datacl with?

Or some other means to list actual / effective privileges, even those
implicit ones?

Thanks, --DD

ddb=> select datname, grantor::regrole::text, grantee::regrole::text,
privilege_type, is_grantable
ddb-> from pg_database
ddb-> left join lateral aclexplode(datacl) on true
ddb-> where datacl is null;
datname | grantor | grantee | privilege_type | is_grantable
---------+---------+---------+----------------+--------------
qadb | | | |
(1 row)