Re: Test if a database has any privilege granted to public

> david(dot)g(dot)johnston(at)gmail(dot)com wrote:
>
>> bryn(at)yugabyte(dot)com wrote:
>>
>> select datname::text
>> from pg_database
>> where 0::oid = any(select (aclexplode(datacl)).grantee)
>> or datacl is null;
>>
>> That's easy if you know that you need to write this. But the need to do so seems to depend on pretty arcane knowledge that, as far as I can see, isn't documented.
>
> The last paragraph of the privileges documentation says this explicitly:
>
> If the “Access privileges” column is empty for a given object, it means the object has default privileges (that is, its privileges entry in the relevant system catalog is null). Default privileges always include all privileges for the owner, and can include some privileges for PUBLIC depending on the object type, as explained above.
>
> https://www.postgresql.org/docs/current/ddl-priv.html
>
> Or, you know, just use the provided functions that have been programmed with knowledge of how the system works.
>
> https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-INFO-ACCESS-TABLE
>
> select has_database_privilege(0,current_database(),'CONNECT');

Ah yes, thanks. I’d missed that at the bottom of the page. I find the "aclitem" base type a bit confusing. I understand that, as a base type, its structure is hidden. However, its text typecast, exemplified by this:

calvin=r*w/hobbes

is conventional and rather informally doc'd. For example, what is a field? You have to guess.

There's no mention on the "Privileges" page of the "has_database_privilege()" function. Nor of "aclexplode()".

Even now, I haven't managed a linear start to finish read of the entire PG docs. And I found "has_database_privilege()" and "aclexplode()" by Internet search rather than x-refs within the PG doc.

The account of "has_database_privilege()" has this:

has_database_privilege ( [ user name or oid, ] database text or oid, privilege text ) → boolean

but that's the only mention of the function on the "System Information Functions and Operators" page. So nothing says what it means to use the (text, text) or (oid, text) overloads.

Moreover, nothing says that "0" denotes "public". (Nor does anything that I've found say that it's the same for "0" in the first field of what "aclexplode()" produces for each element of its "aclitem[]" argumemt. Internet search for "postgres oid of public" gets no useful hits.

But experiment shows that you can use this reserved name (in single quotes) with the same effect as "0".

I suppose that it all boils down to this:

…where
has_database_privilege('public', datname, 'connect') or
has_database_privilege('public', datname, 'create') or
has_database_privilege('public', datname, 'temp');

versus this:

…where 0::oid = any(select (aclexplode(datacl)).grantee) or datacl is null;

Yes, I prefer the version that uses "has_database_privilege()" (even though it's longer) because it says more clearly what it means.

Thanks!