Content-Type: multipart/alternative;
 boundary="------------4zrnTvi5PNEjfyaarTFBCiFz"
Message-ID: <b4b4c4ea-b9b0-4435-a84c-d172e01db8cb@postgrespro.ru>
Date: Mon, 25 Aug 2025 15:21:47 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Q: GRANT ... WITH ADMIN on PG 17
To: Laurenz Albe <laurenz.albe@cybertec.at>,
 Karsten Hilbert <Karsten.Hilbert@gmx.net>, pgsql-general@lists.postgresql.org
References: <aKc855Ez-iHiJ6ww@hermes.hilbert.loc>
 <03be331512b0a52423b86e70018b39b5d2966f52.camel@cybertec.at>
Content-Language: en-US
From: Pavel Luzanov <p.luzanov@postgrespro.ru>
In-Reply-To: <03be331512b0a52423b86e70018b39b5d2966f52.camel@cybertec.at>
Archived-At: <https://www.postgresql.org/message-id/b4b4c4ea-b9b0-4435-a84c-d172e01db8cb%40postgrespro.ru>
Precedence: bulk

This is a multi-part message in MIME format.
--------------4zrnTvi5PNEjfyaarTFBCiFz
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

On 22.08.2025 11:40, Laurenz Albe wrote:
>> - gm-dbo: user role for a DBA admin (not! superuser)
>> - gm-bones: user role for a LLAP doctor
>> - gm-doctors: group role for doctors, upon which are resting
>>    access permissions for clinical data
>> - gm-bones is to be a member of gm-doctors in order to access clinical data
>> - gm-dbo is intended to manage membership of gm-bones in gm-doctors
>> - however, gm-dbo need not itself be a member of gm-doctors
>>
>> Is that possible within the current (as of PG 17) framework ?
> Yes, that should work as follows:
>
>    test=# CREATE ROLE "gm-dbo" LOGIN;
>    CREATE ROLE
>    test=# CREATE ROLE "gm-bones";
>    CREATE ROLE
>    test=# CREATE ROLE "gm-doctors";
>    CREATE ROLE
>    test=# GRANT "gm-doctors" to "gm-dbo" WITH ADMIN TRUE, INHERIT FALSE, SET FALSE;
>    GRANT ROLE
>    test=# SET SESSION AUTHORIZATION "gm-dbo";
>    SET
>    test=> GRANT "gm-doctors" TO "gm-bones";
>    GRANT ROLE
>    test=> SET ROLE "gm-doctors";
>    ERROR:  permission denied to set role "gm-doctors"
>
> "gm-dbo" can manage membership in "gm-doctors" (ADMIN TRUE), but does not inherit
> the role's privileges, nor can "gm-dbo" assume the identity of "gm-doctors".

Such a scheme will protect against accidental (unintended) use of the gm-dbo
role of its capabilities. But gm-dbo can grant itself SET and INHERIT 
options
in gm-doctors:

GRANT "gm-doctors" to "gm-dbo" WITH INHERIT TRUE, SET TRUE;

A safer option is to use security definer function to grant membership
in the gm-doctors group. Something like this:

\connect - postgres
CREATE ROLE dbo LOGIN;
CREATE ROLE bones LOGIN;

CREATE ROLE doctors;

CREATE OR REPLACE PROCEDURE grant_doctors_to (member_role text)
AS $$
BEGIN
     IF member_role != 'dbo' THEN
         EXECUTE format('GRANT doctors TO %I WITH INHERIT TRUE, SET 
TRUE, ADMIN FALSE', member_role);
     END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
REVOKE EXECUTE ON PROCEDURE grant_doctors_to FROM public;
GRANT EXECUTE ON PROCEDURE grant_doctors_to TO dbo;

\connect - dbo
CALL grant_doctors_to('bones');
CALL

GRANT doctors to dbo WITH INHERIT TRUE, SET TRUE;
ERROR:  permission denied to grant role "doctors"
DETAIL:  Only roles with the ADMIN option on role "doctors" may grant 
this role.

-- 
Pavel Luzanov
Postgres Professional:https://postgrespro.com

--------------4zrnTvi5PNEjfyaarTFBCiFz
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <div class="moz-cite-prefix">On 22.08.2025 11:40, Laurenz Albe
      wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:03be331512b0a52423b86e70018b39b5d2966f52.camel@cybertec.at">
      <pre class="moz-quote-pre" wrap=""><blockquote type="cite"
      style="color: #007cff;"><pre wrap="" class="moz-quote-pre">- gm-dbo: user role for a DBA admin (not! superuser)
- gm-bones: user role for a LLAP doctor
- gm-doctors: group role for doctors, upon which are resting
  access permissions for clinical data
- gm-bones is to be a member of gm-doctors in order to access clinical data
- gm-dbo is intended to manage membership of gm-bones in gm-doctors
- however, gm-dbo need not itself be a member of gm-doctors

Is that possible within the current (as of PG 17) framework ?
</pre></blockquote><pre wrap="" class="moz-quote-pre">Yes, that should work as follows:

  test=# CREATE ROLE "gm-dbo" LOGIN;
  CREATE ROLE
  test=# CREATE ROLE "gm-bones";
  CREATE ROLE
  test=# CREATE ROLE "gm-doctors";
  CREATE ROLE
  test=# GRANT "gm-doctors" to "gm-dbo" WITH ADMIN TRUE, INHERIT FALSE, SET FALSE;
  GRANT ROLE
  test=# SET SESSION AUTHORIZATION "gm-dbo";
  SET
  test=&gt; GRANT "gm-doctors" TO "gm-bones";
  GRANT ROLE
  test=&gt; SET ROLE "gm-doctors";
  ERROR:  permission denied to set role "gm-doctors"

"gm-dbo" can manage membership in "gm-doctors" (ADMIN TRUE), but does not inherit
the role's privileges, nor can "gm-dbo" assume the identity of "gm-doctors".</pre></pre>
    </blockquote>
    <br>
    <font face="monospace">Such a scheme will protect against accidental
      (unintended) use of the gm-dbo<br>
      role of its capabilities. But gm-dbo can grant itself SET and
      INHERIT options<br>
      in gm-doctors:<br>
      <br>
      GRANT "gm-doctors" to "gm-dbo" WITH INHERIT TRUE, SET TRUE;<br>
      <br>
      A safer option is to use security definer function to grant
      membership<br>
      in the gm-doctors group. Something like this:<br>
      <br>
      \connect - postgres<br>
      CREATE ROLE dbo LOGIN;<br>
      CREATE ROLE bones LOGIN;<br>
      <br>
      CREATE ROLE doctors;<br>
      <br>
      CREATE OR REPLACE PROCEDURE grant_doctors_to (member_role text)<br>
      AS $$<br>
      BEGIN<br>
          IF member_role != 'dbo' THEN<br>
              EXECUTE format('GRANT doctors TO %I WITH INHERIT TRUE, SET
      TRUE, ADMIN FALSE', member_role);<br>
          END IF;<br>
      END;<br>
      $$ LANGUAGE plpgsql SECURITY DEFINER;<br>
      REVOKE EXECUTE ON PROCEDURE grant_doctors_to FROM public;<br>
      GRANT EXECUTE ON PROCEDURE grant_doctors_to TO dbo;<br>
      <br>
      \connect - dbo<br>
      CALL grant_doctors_to('bones');<br>
      CALL<br>
      <br>
      GRANT doctors to dbo WITH INHERIT TRUE, SET TRUE;<br>
      ERROR:  permission denied to grant role "doctors"<br>
      DETAIL:  Only roles with the ADMIN option on role "doctors" may
      grant this role.</font><br>
    <pre class="moz-signature" cols="72">-- 
Pavel Luzanov
Postgres Professional: <a class="moz-txt-link-freetext" href="https://postgrespro.com">https://postgrespro.com</a></pre>
  </body>
</html>

--------------4zrnTvi5PNEjfyaarTFBCiFz--