Test if a database has any privilege granted to public

From: Bryn Llewellyn <bryn(at)yugabyte(dot)com>
To: pgsql-general list <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Test if a database has any privilege granted to public
Date: 2022-12-15 05:44:24
Message-ID: 0BFC452B-0AC8-46B3-9E65-B685544ED110@yugabyte.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I want to adopt a rule that no database in my cluster has any privilege granted to public. It suits me best to encapsulate the test as a boolean function thus:

function mgr.db_has_priv_granted_to_public(db in name)

where "mgr" is a convenient schema for various admin utilities. I have implemented the function. And preliminary tests haven't shown that it doesn't work.

I created two databases like this, using a session that I authorized as a non-bootstrap superuser called "yugabyte":

create database d1;
create database d2;

grant all on database d1 to yugabyte;
revoke all on database d2 from yugabyte;

grant all on database d1 to public;
revoke all on database d2 from public;

And I tested the function like this:

select
datname,
mgr.db_has_priv_granted_to_public(datname)::text as "bad?"
from pg_database
where datname in ('d1'::name, 'd2'::name)
order by 1;

It produced this result:

datname | bad?
---------+-------
d1 | true
d2 | false

Here's the function's implementation:

create type mgr.acl_t as (grantor oid, grantee oid, privilege_type text, is_grantable boolean);

create function mgr.db_has_priv_granted_to_public(db in name)
returns boolean
set search_path = pg_catalog, pg_temp
language sql
as $body$
with
c1 as
(
select (aclexplode(datacl)::text)::mgr.acl_t as v1 from pg_database where datname = db
),
c2 as
(
select (c1.v1).grantee as grantee from c1
)
select exists(select 1 from c2 where grantee = 0::oid);
$body$;

The design of the user-defined type was inspired by "\df aclexplode". And the typecast to "text" and thence to my "acl_t" works around the error "cannot cast type record to acl_t". I want only to access the "grantee" field of the "aclitem" value.

My code feels very obscure and verbose. Having said this, searching the PG doc, and doing general Internet searches didn't bring any inspiration. Rather, all that I found was this:

https://pgxn.org/dist/pg_acl/
The default ACL type in Postgres (aclitem) produces rather cryptic output that is hard to understand.

And indeed, "pg_database.datacl::text" produces output like this:

{=CTc/yugabyte,yugabyte=CTc/yugabyte}

Sure enough, the PG doc does say "An empty grantee field in an aclitem stands for PUBLIC." But it would be a pain to parse that text and deduce the presence of the empty field that I can see with my human eyes.

However, I don't want to mess around with a third party extension to meet a goal that is so simply stated.

Am I missing something? Is there a better way to implement my function?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ron 2022-12-15 06:03:08 Re: Test if a database has any privilege granted to public
Previous Message Zheng Li 2022-12-15 05:36:41 Re: Support logical replication of DDLs