On Mon, Mar 20, 2023 at 2:18 PM Erik Wienhold <ewie@xxxxxxxxx> wrote:
> On 20/03/2023 13:50 CET Dominique Devienne <ddevienne@xxxxxxxxx> wrote:
>
> On Mon, Mar 20, 2023 at 1:18 PM Erik Wienhold <ewie@xxxxxxxxx> wrote:
> > > On 20/03/2023 11:52 CET Dominique Devienne <ddevienne@xxxxxxxxx> 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)
count
-------
0
(1 row)
