Message-ID: <03be331512b0a52423b86e70018b39b5d2966f52.camel@cybertec.at>
Subject: Re: Q: GRANT ... WITH ADMIN on PG 17
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Karsten Hilbert <Karsten.Hilbert@gmx.net>, 
	pgsql-general@lists.postgresql.org
Date: Fri, 22 Aug 2025 10:40:16 +0200
In-Reply-To: <aKc855Ez-iHiJ6ww@hermes.hilbert.loc>
References: <aKc855Ez-iHiJ6ww@hermes.hilbert.loc>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-1.fc42) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/03be331512b0a52423b86e70018b39b5d2966f52.camel%40cybertec.at>
Precedence: bulk

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.
>=20
> Does this imply that an ADMIN role _must_ itself be a member
> of the group role it is to maintain membership of ?
>=20
> 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.
>=20
> =C2=A0From 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".
>=20
> IOW:
>=20
> - 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
> =C2=A0 access permissions for clinical data
> - gm-bones is to be a member of gm-doctors in order to access clinical da=
ta
> - 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
>=20
> Is that possible within the current (as of PG 17) framework ?

Yes, that should work as follows:

  test=3D# CREATE ROLE "gm-dbo" LOGIN;
  CREATE ROLE
  test=3D# CREATE ROLE "gm-bones";
  CREATE ROLE
  test=3D# CREATE ROLE "gm-doctors";
  CREATE ROLE
  test=3D# GRANT "gm-doctors" to "gm-dbo" WITH ADMIN TRUE, INHERIT FALSE, S=
ET FALSE;
  GRANT ROLE
  test=3D# SET SESSION AUTHORIZATION "gm-dbo";
  SET
  test=3D> GRANT "gm-doctors" TO "gm-bones";
  GRANT ROLE
  test=3D> 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 i=
nherit
the role's privileges, nor can "gm-dbo" assume the identity of "gm-doctors"=
.

Yours,
Laurenz Albe