Re: NULL pg_database.datacl - Mailing list pgsql-general

From Dominique Devienne
Subject Re: NULL pg_database.datacl
Date
Msg-id CAFCRh--Ozj2z7EV0KXi4-nTC6_Cu6rux8ra_TAZKDzkhanEa3A@mail.gmail.com
Whole thread Raw
In response to Re: NULL pg_database.datacl  (Erik Wienhold <ewie@ewie.name>)
List pgsql-general
On Mon, Mar 20, 2023 at 2:18 PM Erik Wienhold <ewie@ewie.name> wrote:
> On 20/03/2023 13:50 CET Dominique Devienne <ddevienne@gmail.com> wrote:
>
> On Mon, Mar 20, 2023 at 1:18 PM Erik Wienhold <ewie@ewie.name> wrote:
> > > On 20/03/2023 11:52 CET Dominique Devienne <ddevienne@gmail.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)

pgsql-general by date:

Previous
From: Erik Wienhold
Date:
Subject: Re: NULL pg_database.datacl
Next
From: "David G. Johnston"
Date:
Subject: Re: pg_upgrade Only the install user can be defined in the new cluster