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

[Ron <ronljohnsonjr@xxxxxxxxx>]

    Off-topic, but you don't need all those text casts.

On 12/14/22 23:44, Bryn Llewellyn wrote:

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?

--
Angular momentum makes the world go 'round.