Обсуждение: Q: GRANT ... WITH ADMIN on PG 17

Dear all,

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 ?

Thanks,
Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

On 8/21/25 08:36, Karsten Hilbert wrote:
> Dear all,
> 
> 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.

I would start by reading this:

https://rhaas.blogspot.com/2023/01/surviving-without-superuser-coming-to.html


> 
> Thanks,
> Karsten


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Am Thu, Aug 21, 2025 at 08:46:00AM -0700 schrieb Adrian Klaver:

> >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.
>
> I would start by reading this:
>
> https://rhaas.blogspot.com/2023/01/surviving-without-superuser-coming-to.html

Thanks, I did, but did not find the answer to: Is there a
way for a role that can manage membership in a group role to
not itself be a member of that group role ?

Best regards,
Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

Am Thu, Aug 21, 2025 at 08:46:00AM -0700 schrieb Adrian Klaver:

> >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.
>
> I would start by reading this:
>
> https://rhaas.blogspot.com/2023/01/surviving-without-superuser-coming-to.html

Thanks, I did, but did not find the answer to: Is there a
way for a role that can manage membership in a group role to
not itself be a member of that group role ?

Am Thu, Aug 21, 2025 at 09:11:57AM -0700 schrieb David G. Johnston:

> > Thanks, I did, but did not find the answer to: Is there a
> > way for a role that can manage membership in a group role to
> > not itself be a member of that group role ?
>
> A superuser can do this.  Otherwise, no.  In order to be made admin of a
> role on must be a member of said role

Thanks, fine, that makes it clear to me.

Regards,
Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

On Thu, Aug 21, 2025 at 6:00 PM Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:
> Am Thu, Aug 21, 2025 at 08:46:00AM -0700 schrieb Adrian Klaver:
> > https://rhaas.blogspot.com/2023/01/surviving-without-superuser-coming-to.html
>
> Thanks, I did, but did not find the answer to: Is there a
> way for a role that can manage membership in a group role to
> not itself be a member of that group role ?

Yes and no. Depends what you mean by MEMBER...

You can be ADMIN of a ROLE, yet not have SET or INHERIT on that ROLE.
As ADMIN you can grant yourself those SET and INHERIT, sure.
But still, with only ADMIN, you can't SET ROLE. So are you a MEMBER?
In the pg_has_role(), you are. In reality, not so much.

In fact, I argued (after the fact it broke my architecture, introducing cycles)
that have just ADMIN shouldn't have considered it a MEMBER of the role.
But that ship had sailed already. 1 year later, we're finally catching up.
I truly hope v18 won't reserve us similar backward-compatibility breaks.

Read the docs for pg_auth_members. pg_has_role(). create role.
If you have CREATEROLE, and do a CREATE ROLE foo, you'll
have ADMIN on foo, but not SET or INHERIT (but you can grant them to yourself).
Also look at pg_auth_members.grantor::regrole::text and you'll see that the
postgres SUPERUSER itself gave you that ADMIN grant. But if you grant yourself
the role, it's a separate pg_auth_members row, and you're now the grantor.

So I didn't spend time studying your specific use case. That's your job :).
But given my painful experience of the past year, I'd answer yes to your
question, on logical grounds. If you see what I mean. --DD

Am Thu, Aug 21, 2025 at 06:29:36PM +0200 schrieb Dominique Devienne:

> > Thanks, I did, but did not find the answer to: Is there a
> > way for a role that can manage membership in a group role to
> > not itself be a member of that group role ?
>
> Yes and no. Depends what you mean by MEMBER...
...
> So I didn't spend time studying your specific use case. That's your job :).
> But given my painful experience of the past year, I'd answer yes to your
> question, on logical grounds. If you see what I mean. --DD

I followed your posts back then when you worked out your use
case so I did have _some_ idea where to look ;-)

I just wanted to confirm my understanding in relation to my
current usage. David kindly provided the needed affirmation.

Regards,
Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

On Thu, Aug 21, 2025 at 6:37 PM Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:
> Am Thu, Aug 21, 2025 at 06:29:36PM +0200 schrieb Dominique Devienne:
> > > Thanks, I did, but did not find the answer to: Is there a
> > > way for a role that can manage membership in a group role to
> > > not itself be a member of that group role ?
> >
> > Yes and no. Depends what you mean by MEMBER...
> ...
> > So I didn't spend time studying your specific use case. That's your job :).
> > But given my painful experience of the past year, I'd answer yes to your
> > question, on logical grounds. If you see what I mean. --DD
>
> I followed your posts back then when you worked out your use
> case so I did have _some_ idea where to look ;-)

Glad it helped someone, maybe.

> I just wanted to confirm my understanding in relation to my
> current usage. David kindly provided the needed affirmation.

Well, I disagree with David that you're a MEMBER with just ADMIN.
pg_has_role(..., 'MEMBER') says yes. But pg_has_role(..., 'SET') says no.
If you can't endorse the role's privileges, you're not a member in my book.
That was kinda my point. --DD

On 8/21/25 09:29, Dominique Devienne wrote:
> On Thu, Aug 21, 2025 at 6:00 PM Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:
>> Am Thu, Aug 21, 2025 at 08:46:00AM -0700 schrieb Adrian Klaver:
>>> https://rhaas.blogspot.com/2023/01/surviving-without-superuser-coming-to.html
>>
>> Thanks, I did, but did not find the answer to: Is there a
>> way for a role that can manage membership in a group role to
>> not itself be a member of that group role ?
> 
> Yes and no. Depends what you mean by MEMBER...

> Read the docs for pg_auth_members. pg_has_role(). create role.
> If you have CREATEROLE, and do a CREATE ROLE foo, you'll
> have ADMIN on foo, but not SET or INHERIT (but you can grant them to yourself).

That is a matter of choice as described here:

https://www.postgresql.org/docs/current/runtime-config-client.html

createrole_self_grant (string)

     If a user who has CREATEROLE but not SUPERUSER creates a role, and 
if this is set to a non-empty value, the newly-created role will be 
granted to the creating user with the options specified. The value must 
be set, inherit, or a comma-separated list of these. The default value 
is an empty string, which disables the feature.

     The purpose of this option is to allow a CREATEROLE user who is not 
a superuser to automatically inherit, or automatically gain the ability 
to SET ROLE to, any created users. Since a CREATEROLE user is always 
implicitly granted ADMIN OPTION on created roles, that user could always 
execute a GRANT statement that would achieve the same effect as this 
setting. However, it can be convenient for usability reasons if the 
grant happens automatically. A superuser automatically inherits the 
privileges of every role and can always SET ROLE to any role, and this 
setting can be used to produce a similar behavior for CREATEROLE users 
for users which they create.



> Also look at pg_auth_members.grantor::regrole::text and you'll see that the
> postgres SUPERUSER itself gave you that ADMIN grant. But if you grant yourself
> the role, it's a separate pg_auth_members row, and you're now the grantor.
> 
> So I didn't spend time studying your specific use case. That's your job :).
> But given my painful experience of the past year, I'd answer yes to your
> question, on logical grounds. If you see what I mean. --DD


-- 
Adrian Klaver
adrian.klaver@aklaver.com

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

- 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".

-- 
Pavel Luzanov
Postgres Professional: https://postgrespro.com

On Mon, Aug 25, 2025 at 2:22 PM Pavel Luzanov <p.luzanov@postgrespro.ru> wrote:
>> On 22.08.2025 11:40, Laurenz Albe wrote:
>> Yes, that should work as follows: [...]

> [...] A safer option is to use security definer function to grant membership

FWIW, it's basically what I did.

My primary "admin" application role lost CREATEROLE,
and instead gained EXECUTE on security-definer procs
from a new lower-level role (with CREATEROLE),
in a new separate schema, which does all create/drop
roles or grant/revoke DDLs.

Which has the added benefits to enforce naming conventions for roles,
to enforce grants are only between our "per-DB" roles,
and made it easy to generate an audit-log for all those DDLs.

So the v16 ROLE changes created a BIG MESS for us,
slowing us down quite a bit, but we ended up with a much
better "v2" architecture, so it was not all a loss... YMMV.

So +1 to Pavel. --DD