On Thu, 2025-08-21 at 17:36 +0200, Karsten Hilbert wrote: > PG 17 documentation says that using "WITH ADMIN" allows the > role being added to another group role to grant/revoke > membership in said group to other roles. > > Does this imply that an ADMIN role _must_ itself be a member > of the group role it is to maintain membership of ? > > The question arises from a scenario where a DBA role would > not need to be a member of a clinical group role but would > be intended to maintain membership of clinical user roles > within that group role. > > From a security point of view the question might be moot > because an ADMIN role could always grant itself membership > in the group role -- but it feels wrong for reasons of > theoretical "correctness". > > IOW: > > - 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". Yours, Laurenz Albe
