Re: Q: GRANT ... WITH ADMIN on PG 17

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