Subject: Re: Q: GRANT ... WITH ADMIN on PG 17

From: Dominique Devienne <[email-masked]>
Date: Thu, 21 Aug 2025 18:29:36 +0200

On Thu, Aug 21, 2025 at 6:00 PM Karsten Hilbert <[email-masked]> 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

---

From: Karsten Hilbert <[email-masked]>
Date: Thu, 21 Aug 2025 18:37:47 +0200

> Yes and no. Depends what you mean by MEMBER...
...
> 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

---

From: Adrian Klaver <[email-masked]>
Date: Thu, 21 Aug 2025 10:43:02 -0700

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

-- 
Adrian Klaver
[email-masked]