tgl@sss.pgh.pa.us wrote:
ronljohnsonjr@gmail.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
«
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!