public inbox for [email protected]  

help / color / mirror / Atom feed

From: Pavel Luzanov <[email protected]>
To: Laurenz Albe <[email protected]>
To: Karsten Hilbert <[email protected]>
To: [email protected]
Subject: Re: Q: GRANT ... WITH ADMIN on PG 17
Date: Mon, 25 Aug 2025 15:21:47 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>

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

view thread (4+ messages)  latest in thread

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 using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Q: GRANT ... WITH ADMIN on PG 17
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox