MIME-Version: 1.0
References: <CAFCRh-8JNEy+dV4SXFOrWca50u+d=--TO4cq=+ac1oBtfJy4AA@mail.gmail.com>
 <42cf78d42a376bf05ca9f5776ad29edc30e784d5.camel@cybertec.at> <CA+TgmobvCGAHPZxX1rNgUb1cGcmD5e8ESGVSL=OyVqDYCAV3EQ@mail.gmail.com>
In-Reply-To: <CA+TgmobvCGAHPZxX1rNgUb1cGcmD5e8ESGVSL=OyVqDYCAV3EQ@mail.gmail.com>
From: Dominique Devienne <ddevienne@gmail.com>
Date: Mon, 23 Sep 2024 14:54:03 +0200
Message-ID: <CAFCRh-9XX5W1Q3EtuowFqvgo-Hjb8P+RQE1MUTiPej8aR-Bg1Q@mail.gmail.com>
Subject: Re: Why no pg_has_role(..., 'ADMIN')?
To: Robert Haas <robertmhaas@gmail.com>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>, pgsql-general@postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAFCRh-9XX5W1Q3EtuowFqvgo-Hjb8P%2BRQE1MUTiPej8aR-Bg1Q%40mail.gmail.com>
Precedence: bulk

On Fri, Sep 20, 2024 at 6:51=E2=80=AFPM Robert Haas <robertmhaas@gmail.com>=
 wrote:
> On Fri, Sep 20, 2024 at 12:37=E2=80=AFPM Laurenz Albe <laurenz.albe@cyber=
tec.at> wrote:
> > That would be a useful addition, yes.
>
> I think this already exists. The full list of modes supported by
> pg_has_role() is listed in convert_role_priv_string(). You can do
> something like pg_has_role('alice', 'USAGE WITH ADMIN OPTION'). This
> is not new: it worked in older releases too, but AFAIK it's never been
> mentioned in the documentation.

Thanks. Now that you mention it, and with Tom's message,
I now recall seeing it before indeed. Just not close enough
to pg_has_role() "immediate" doc, to notice it.

> However, the precise rule for DROP ROLE in v16+ is not just that you
> need to have ADMIN OPTION on the role. The rule is:

> 1. You must have ADMIN OPTION on the target role.

Easy now, thanks to your reminder.

> 2. You must also have CREATEROLE.

That's easy to check, and I already do, in fact.

> 3. If the target role is SUPERUSER, you must be SUPERUSER.

Doesn't apply in my case, most of the time,
but also easy to check, and I already do in fact.

> If I'm not wrong, pg_has_role(..., 'USAGE WITH ADMIN OPTION') will
> test #1 for you, but not #2 or #3.

It's perfect for what I want to do. Thanks again, --DD

PS: I'm found [an old thread][1] from you around pg_has_role() and
  'WITH ADMIN OPTION', but I'm not sure there was any resolution on that.
  Was the weirdness fixed?

[1]: https://www.postgresql.org/message-id/flat/CA%2BTgmoYg6_j1brUcYWXwF4fR=
%3DTOWpED%3DXj1QMSgKCi0%2Bh1dgjA%40mail.gmail.com