public inbox for [email protected]  

help / color / mirror / Atom feed

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

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

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

view thread (3+ messages)  latest in thread

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: <CAFCRh-8O-zBaruts-9jpQXsZr5sGo46UNdW+tftoVVzBxkzxEQ@mail.gmail.com>

* 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