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

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.