public inbox for [email protected]  

help / color / mirror / Atom feed

From: Adrian Klaver <[email protected]>
To: Dominique Devienne <[email protected]>
To: Karsten Hilbert <[email protected]>
Cc: [email protected]
Subject: Re: Q: GRANT ... WITH ADMIN on PG 17
Date: Thu, 21 Aug 2025 10:43:02 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAFCRh-8O-zBaruts-9jpQXsZr5sGo46UNdW+tftoVVzBxkzxEQ@mail.gmail.com>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<CAFCRh-8O-zBaruts-9jpQXsZr5sGo46UNdW+tftoVVzBxkzxEQ@mail.gmail.com>

On 8/21/25 09:29, Dominique Devienne wrote:
> On Thu, Aug 21, 2025 at 6:00 PM Karsten Hilbert <[email protected]> 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
[email protected]

view thread (3+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Q: GRANT ... WITH ADMIN on PG 17
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox