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