public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: Karsten Hilbert <[email protected]>
To: [email protected]
Subject: Re: Q: GRANT ... WITH ADMIN on PG 17
Date: Fri, 22 Aug 2025 10:40:16 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

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

view thread (4+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Q: GRANT ... WITH ADMIN on PG 17
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox