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

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