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

[Bryn Llewellyn <bryn@xxxxxxxxxxxx>]

tgl@xxxxxxxxxxxxx wrote:

ronljohnsonjr@xxxxxxxxx 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!