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
