MIME-Version: 1.0
References: <aKc855Ez-iHiJ6ww@hermes.hilbert.loc> <3662a9b9-3e58-4977-8bd1-e1ed0e25b2a8@aklaver.com>
 <aKdCfim8i_TNw16S@hermes.hilbert.loc>
In-Reply-To: <aKdCfim8i_TNw16S@hermes.hilbert.loc>
From: Dominique Devienne <ddevienne@gmail.com>
Date: Thu, 21 Aug 2025 18:29:36 +0200
Message-ID: <CAFCRh-8O-zBaruts-9jpQXsZr5sGo46UNdW+tftoVVzBxkzxEQ@mail.gmail.com>
Subject: Re: Q: GRANT ... WITH ADMIN on PG 17
To: Karsten Hilbert <Karsten.Hilbert@gmx.net>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>, pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAFCRh-8O-zBaruts-9jpQXsZr5sGo46UNdW%2BtftoVVzBxkzxEQ%40mail.gmail.com>
Precedence: bulk

On Thu, Aug 21, 2025 at 6:00=E2=80=AFPM Karsten Hilbert <Karsten.Hilbert@gm=
x.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-t=
o.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 cyc=
les)
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 yourse=
lf).
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 yours=
elf
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