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
