Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p5hKw-0003cl-PP for pgsql-general@arkaria.postgresql.org; Thu, 15 Dec 2022 06:03:18 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p5hKv-0000ao-3z for pgsql-general@arkaria.postgresql.org; Thu, 15 Dec 2022 06:03:17 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p5hKu-0000af-MV for pgsql-general@lists.postgresql.org; Thu, 15 Dec 2022 06:03:16 +0000 Received: from mail-oa1-x29.google.com ([2001:4860:4864:20::29]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1p5hKp-0001cp-QB for pgsql-general@lists.postgresql.org; Thu, 15 Dec 2022 06:03:16 +0000 Received: by mail-oa1-x29.google.com with SMTP id 586e51a60fabf-14449b7814bso19703072fac.3 for ; Wed, 14 Dec 2022 22:03:11 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=xBfAd3kFB7RCvzpQTAS6DJiyJop3RGx8roR3qpFbeok=; b=mAhar4Mj18RhaNkbaMzwRtyJXAB6v3XMtD9mS8jcDpnaHQdNyStbx41ia42EJRg6OG HIX8cjTUl5H6izTn3jFptOdkGEUVybgnOXTE8Pc2alZwv3JVYyPSXJEmKm7CJ7BXfCUE vIB5ZzrfiN96r+T/GXwdmbVoxbMr1fjQbMiJfnfKuyep3RhXcOxrWz7Va1tQ6EyOaDBR XVeiqVD+x/RNzSpUbKxRQ6WAAEKjkgKUfZvBr3696WJbGxqeN/0cSWhqsGMB/npFEMcj qVEE9Q22WTt3hAJac9idWnrNmeqAJ5eiP+3BMKUEeod9oxIHPvspfOnmqXmPUY2YW48k Urjw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=xBfAd3kFB7RCvzpQTAS6DJiyJop3RGx8roR3qpFbeok=; b=uKzyKlL+KJiq9Dazx5kkbLfqI0Yp/XlJuJ8f+Ty65LB7F7VdaLr7dZxl56XdMBx+Tb WNAETmBwczswW4oyk5uf2MmjxMLaGxnbZxTMgGBGtVM32pgK4tE2/x8FJI2EgXC5yFd0 CUOWJVBPBbzrfJQP66IjNq0U7lVxSHzo8pEluNWjVo0IDDZnoaph8ioRkzI7Zbl2k1lO aMeo3tb5as/UMGmHRlISpJ31v0ghAAqOz8jMdbEjV0pel7/xbupTIHOipXiTpVmd9CMt fpcDJ02KLaVZKFCyUn6B9Wm8U0+FxMqna2hYhIwVizofHlOCJt1vUqmopBSjuNv4Qbc0 NP2A== X-Gm-Message-State: ANoB5plRFVvvW7sBY09guc6UfrXxonpTI5d3ngnhrBrCwv43lZejdsjG 6H56Ogg76egxWfdnOutgyreLF7IbvSs= X-Google-Smtp-Source: AA0mqf5ULO06l7zcZ2x7K0frkR+gZ46R5UErxn6YT0G4wiJwCT4mJFD/Fgq/FnHcOzR3hpJ6hdGjuw== X-Received: by 2002:a05:6870:d699:b0:13d:6424:d740 with SMTP id z25-20020a056870d69900b0013d6424d740mr13638994oap.42.1671084189663; Wed, 14 Dec 2022 22:03:09 -0800 (PST) Received: from [192.168.1.66] (108-250-206-84.lightspeed.nworla.sbcglobal.net. [108.250.206.84]) by smtp.googlemail.com with ESMTPSA id k22-20020a05680808d600b003546fada8f6sm719529oij.12.2022.12.14.22.03.08 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 14 Dec 2022 22:03:09 -0800 (PST) Content-Type: multipart/alternative; boundary="------------j5TgN0R9xTyOAyFF3pEtNDp0" Message-ID: Date: Thu, 15 Dec 2022 00:03:08 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.2.2 Subject: Re: Test if a database has any privilege granted to public Content-Language: en-US To: pgsql-general@lists.postgresql.org References: <0BFC452B-0AC8-46B3-9E65-B685544ED110@yugabyte.com> From: Ron In-Reply-To: <0BFC452B-0AC8-46B3-9E65-B685544ED110@yugabyte.com> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------j5TgN0R9xTyOAyFF3pEtNDp0 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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. --------------j5TgN0R9xTyOAyFF3pEtNDp0 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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:

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.
--------------j5TgN0R9xTyOAyFF3pEtNDp0--