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

> tgl(at)sss(dot)pgh(dot)pa(dot)us wrote:
>
>> ronljohnsonjr(at)gmail(dot)com writes:
>>
>> Off-topic, but you don't need all those text casts.
>
> Indeed. Something like this ought to do it:
>
> select datname from pg_database where 0::oid = any(select (aclexplode(datacl)).grantee);
>
> datname
> ------------
> template1
> template0
> regression

Thanks, both, for the lightning-fast replies. Yes, I see it now. (I got myself confused about the requirements for using parentheses.) I should have slept on it before sending to the list.

There's still a little snag though. I created a brand-new cluster (with bootstrap superuser called "postgres"), started a session as "postgres", and did this:

create database d1;
revoke all on database d1 from postgres;
revoke all on database d1 from public;

create database d2;
revoke all on database d2 from postgres;

create database d3;

select
datname::text as name,
case
when datacl is null then '<NULL>'
else datacl::text
end as datacl,
(0::oid = any(select (aclexplode(datacl)).grantee))::text as "public has a priv"
from pg_database
where datname in ('d1', 'd2', 'd3')
order by 1;

It produced this result:

name | datacl | public has a priv
------+----------------+-------------------
d1 | {} | false
d2 | {=Tc/postgres} | true
d3 | <NULL> | false

This seems to imply that this wording from "5.7. Privileges" (https://www.postgresql.org/docs/current/ddl-priv.html) is a little sketchy:

«
For other types of objects, the default privileges granted to PUBLIC are as follows: CONNECT and TEMPORARY (create temporary tables) privileges for databases…
»

The effect of a NULL "datacl" is as if CONNECT and TEMPORARY have been granted to public. But even so, these privileges are not shown to have been actually granted.

In my test, I simply revoked "all" on "d2" from postgres. And this produced a not null "datacl" that did then show the documented default regime.

The following test:

create role r with login password 'p';
\c d1 r
\c d2 r
\c d3 r

Showed that "public has a priv" (as I coded it) doesn't tell the whole story because "\c d3 r" (as well as "\c d2 r") succeeds. Of course, "\c d1 r" fails.

I do see that, in a strict "legal sense", the doc that I quoted is not (quite) wrong. But to implement the test that I want robustly, I need to extend the logic thus:

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.

Anyway, my immediate requirement is solved. Thanks again!