On Fri, Sep 20, 2024 at 6:51 PM Robert Haas <robertmhaas@xxxxxxxxx> wrote:
> On Fri, Sep 20, 2024 at 12:37 PM Laurenz Albe <laurenz.albe@xxxxxxxxxxx> 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
