Feedback-ID: i76984098:Fastmail
Message-ID: <41f55cd4-9e30-404d-b360-2f1ffeee1126@aklaver.com>
Date: Thu, 21 Aug 2025 10:43:02 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Q: GRANT ... WITH ADMIN on PG 17
To: Dominique Devienne <ddevienne@gmail.com>,
 Karsten Hilbert <Karsten.Hilbert@gmx.net>
Cc: pgsql-general@lists.postgresql.org
References: <aKc855Ez-iHiJ6ww@hermes.hilbert.loc>
 <3662a9b9-3e58-4977-8bd1-e1ed0e25b2a8@aklaver.com>
 <aKdCfim8i_TNw16S@hermes.hilbert.loc>
 <CAFCRh-8O-zBaruts-9jpQXsZr5sGo46UNdW+tftoVVzBxkzxEQ@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAFCRh-8O-zBaruts-9jpQXsZr5sGo46UNdW+tftoVVzBxkzxEQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/41f55cd4-9e30-404d-b360-2f1ffeee1126%40aklaver.com>
Precedence: bulk

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