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

On Thu, Dec 15, 2022 at 5:17 PM Bryn Llewellyn <bryn(at)yugabyte(dot)com> wrote:

>
> 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.
>
>
Sure, because as a typical user the implementation detail of all this is
unimportant. You interact through the GRANT/REVOKE interface. Or find
tools that present this kind of information graphically. People just
aren't asking the kinds of questions that suggest our level of
documentation is insufficient. That you've found gaps to be possibly
filled in isn't surprising. But it is also less time and effort answering
your questions to help mostly just you than it is to improve the
documentation to help mostly just you.

> 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.
>
>
The paragraph I note below covers all of this. Maybe it's a bit
"wall-of-text"ish but the material is present.

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

Yep, as documented:

Table 9.67 lists functions that allow querying object access privileges
programmatically. (See Section 5.7 for more information about privileges.)
In these functions, the user whose privileges are being inquired about can
be specified by name or by OID (pg_authid.oid), or if the name is given as
public then the privileges of the PUBLIC pseudo-role are checked.

https://www.postgresql.org/docs/current/functions-info.html

I'm not sure where I picked up the comment about 0 working but since
"public" works and is documented that implementation detail need not be
discoverable.

David J.