Re: Why no pg_has_role(..., 'ADMIN')?

On Fri, Sep 20, 2024 at 6:51 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Fri, Sep 20, 2024 at 12:37 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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