From: Bryn Llewellyn <bryn@yugabyte.com>
To: Tom Lane PostgreSQL <tgl@sss.pgh.pa.us>
Cc: Ron <ronljohnsonjr@gmail.com>
Cc: pgsql-general list <pgsql-general@lists.postgresql.org>
Subject: Re: Test if a database has any privilege granted to public
Date: Thu, 15 Dec 2022 11:51:07 -0800
Message-ID: <C6F2AD66-67A7-4089-9A41-65187DA5BAC3@yugabyte.com> (raw)
In-Reply-To: <3300816.1671084655@sss.pgh.pa.us>
References: <0BFC452B-0AC8-46B3-9E65-B685544ED110@yugabyte.com>
<d6410959-50a7-b0bd-ce0f-99dca388b815@gmail.com>
<3300816.1671084655@sss.pgh.pa.us>
> 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
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!
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: bryn@yugabyte.com, tgl@sss.pgh.pa.us, ronljohnsonjr@gmail.com
Subject: Re: Test if a database has any privilege granted to public
In-Reply-To: <C6F2AD66-67A7-4089-9A41-65187DA5BAC3@yugabyte.com>
References: <0BFC452B-0AC8-46B3-9E65-B685544ED110@yugabyte.com> <d6410959-50a7-b0bd-ce0f-99dca388b815@gmail.com> <3300816.1671084655@sss.pgh.pa.us> <C6F2AD66-67A7-4089-9A41-65187DA5BAC3@yugabyte.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox