Re: NULL pg_database.datacl

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

> > On 20/03/2023 13:50 CET Dominique Devienne <ddevienne(at)gmail(dot)com> wrote:
> >
> > 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 [...]
> > > https://www.postgresql.org/docs/current/ddl-priv.html
> >
> > Is there a SQL function returning those per-type default provileges?
>
> Use acldefault. Pass in ownerId=0 to get the privileges for PUBLIC.
>
>
> https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-ACLITEM-FN-TABLE

Thanks again. After experimenting a little, sounds like acldefault('d',
datdba) is what I want,
since it seems to give something closer to the AclItem[] actually
instantiated on the first GRANT or REVOKE.

This has been an education for me, once again. I appreciate the help.
Thanks, --DD

ddb=> select datname, coalesce(datacl, acldefault('d', 0)) from pg_database
where datacl is null;
datname | coalesce
---------+----------------
qadb | {=Tc/0,=CTc/0}
(1 row)

ddb=> select datname, coalesce(datacl, acldefault('d', datdba)) from
pg_database where datacl is null;
datname | coalesce
---------+--------------------------------
qadb | {=Tc/qauser,qauser=CTc/qauser}
(1 row)

ddb=# revoke all on database qadb from public;
REVOKE

ddb=# select datname, datacl from pg_database where datname = 'qadb';
datname | datacl
---------+---------------------
qadb | {qauser=CTc/qauser}
(1 row)

ddb=# grant connect, temporary on database qadb to public;
GRANT

ddb=# select datname, datacl from pg_database where datname = 'qadb';
datname | datacl
---------+--------------------------------
qadb | {qauser=CTc/qauser,=Tc/qauser}
(1 row)

ddb=# select count(*) from pg_database where datacl is null;
count
-------
0
(1 row)