public inbox for [email protected]  

help / color / mirror / Atom feed

From: Dominique Devienne <[email protected]>
To: Robert Haas <[email protected]>
Cc: Laurenz Albe <[email protected]>
Cc: [email protected]
Subject: Re: Why no pg_has_role(..., 'ADMIN')?
Date: Mon, 23 Sep 2024 14:54:03 +0200
Message-ID: <CAFCRh-9XX5W1Q3EtuowFqvgo-Hjb8P+RQE1MUTiPej8aR-Bg1Q@mail.gmail.com> (raw)
In-Reply-To: <CA+TgmobvCGAHPZxX1rNgUb1cGcmD5e8ESGVSL=OyVqDYCAV3EQ@mail.gmail.com>
References: <CAFCRh-8JNEy+dV4SXFOrWca50u+d=--TO4cq=+ac1oBtfJy4AA@mail.gmail.com>
	<[email protected]>
	<CA+TgmobvCGAHPZxX1rNgUb1cGcmD5e8ESGVSL=OyVqDYCAV3EQ@mail.gmail.com>

On Fri, Sep 20, 2024 at 6:51 PM Robert Haas <[email protected]> wrote:
> On Fri, Sep 20, 2024 at 12:37 PM Laurenz Albe <[email protected]> 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%2Bh1dg...

view thread (8+ 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]
  Subject: Re: Why no pg_has_role(..., 'ADMIN')?
  In-Reply-To: <CAFCRh-9XX5W1Q3EtuowFqvgo-Hjb8P+RQE1MUTiPej8aR-Bg1Q@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