> [email protected] wrote:
>
>> [email protected] 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!
