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

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?

Ron <ronljohnsonjr@gmail.com> writes:
> Off-topic, but you don't need all those text casts.

Indeed.  Something like this ought to do it:

=# select datname from pg_database where 0::oid = any(select (aclexplode(datacl)).grantee);
  datname
------------
 template1
 template0
 regression
(3 rows)

            regards, tom lane

tgl@sss.pgh.pa.us wrote:

ronljohnsonjr@gmail.com writes:

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

Indeed.  Something like this ought to do it:

select datname from pg_database where 0::oid = any(select (aclexplode(datacl)).grantee);

 datname   
------------
template1
template0
regression

> david.g.johnston@gmail.com wrote:
>
>> bryn@yugabyte.com wrote:
>>
>> select datname::text
>> from pg_database
>> where 0::oid = any(select (aclexplode(datacl)).grantee)
>> or datacl is null;
>>
>> That's easy if you know that you need to write this. But the need to do so seems to depend on pretty arcane
knowledgethat, as far as I can see, isn't documented. 
>
> The last paragraph of the privileges documentation says this explicitly:
>
> If the “Access privileges” column is empty for a given object, it means the object has default privileges (that is,
itsprivileges entry in the relevant system catalog is null). Default privileges always include all privileges for the
owner,and can include some privileges for PUBLIC depending on the object type, as explained above. 
>
> https://www.postgresql.org/docs/current/ddl-priv.html
>
> Or, you know, just use the provided functions that have been programmed with knowledge of how the system works.
>
> https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-INFO-ACCESS-TABLE
>
> select has_database_privilege(0,current_database(),'CONNECT');

Ah yes, thanks. I’d missed that at the bottom of the page. I find the "aclitem" base type a bit confusing. I understand
that,as a base type, its structure is hidden. However, its text typecast, exemplified by this: 

calvin=r*w/hobbes

is conventional and rather informally doc'd. For example, what is a field? You have to guess.

There's no mention on the "Privileges" page of the "has_database_privilege()" function. Nor of "aclexplode()".

Even now, I haven't managed a linear start to finish read of the entire PG docs. And I found "has_database_privilege()"
and"aclexplode()" by Internet search rather than x-refs within the PG doc. 

The account of "has_database_privilege()" has this:

has_database_privilege ( [ user name or oid, ] database text or oid, privilege text ) → boolean

but that's the only mention of the function on the "System Information Functions and Operators" page. So nothing says
whatit means to use the (text, text) or (oid, text) overloads. 

Moreover, nothing says that "0" denotes "public". (Nor does anything that I've found say that it's the same for "0" in
thefirst field of what "aclexplode()" produces for each element of its "aclitem[]" argumemt. Internet search for
"postgresoid of public" gets no useful hits. 

But experiment shows that you can use this reserved name (in single quotes) with the same effect as "0".

I suppose that it all boils down to this:

…where
  has_database_privilege('public', datname, 'connect') or
  has_database_privilege('public', datname, 'create') or
  has_database_privilege('public', datname, 'temp');

versus this:

…where 0::oid = any(select (aclexplode(datacl)).grantee) or datacl is null;

Yes, I prefer the version that uses "has_database_privilege()" (even though it's longer) because it says more clearly
whatit means. 

Thanks!