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
