pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: Test if a database has any privilege granted to public
Date: Thu, 15 Dec 2022 00:03:08 -0600
Message-ID: <d6410959-50a7-b0bd-ce0f-99dca388b815@gmail.com> (raw)
In-Reply-To: <0BFC452B-0AC8-46B3-9E65-B685544ED110@yugabyte.com>
References: <0BFC452B-0AC8-46B3-9E65-B685544ED110@yugabyte.com>

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.

view thread (7+ messages)  latest in thread

Message-ID: <d6410959-50a7-b0bd-ce0f-99dca388b815@gmail.com>
Permalink:  ../d6410959-50a7-b0bd-ce0f-99dca388b815@gmail.com/
Also on:    postgresql.org/message-id/d6410959-50a7-b0bd-ce0f-99dca388b815@gmail.com

 · 

reply

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: ronljohnsonjr@gmail.com
  Subject: Re: Test if a database has any privilege granted to public
  In-Reply-To: <d6410959-50a7-b0bd-ce0f-99dca388b815@gmail.com>
  References: <0BFC452B-0AC8-46B3-9E65-B685544ED110@yugabyte.com> <d6410959-50a7-b0bd-ce0f-99dca388b815@gmail.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