From: | Ron <ronljohnsonjr(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Test if a database has any privilege granted to public |
Date: | 2022-12-15 06:03:08 |
Message-ID: | d6410959-50a7-b0bd-ce0f-99dca388b815@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2022-12-15 06:10:55 | Re: Test if a database has any privilege granted to public |
Previous Message | Bryn Llewellyn | 2022-12-15 05:44:24 | Test if a database has any privilege granted to public |