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

From: Laurenz Albe <[email-masked]>
Date: Fri, 20 Sep 2024 18:37:49 +0200

On Fri, 2024-09-20 at 17:26 +0200, Dominique Devienne wrote:
> To find out whether a ROLE can DROP another in v16+.
> Prior to v16, just having CREATEROLE was enough,
> so it didn't really seem necessary.
> 
> But knowing whether DROP ROLE will work,
> w/o invalidating the current transaction,
> seems like something quite useful to know now, no?
> 
> I can query pg_auth_members for admin_option,
> but only easily for direct membership. Taking into
> account indirect membership, which I assume applies,
> is exactly why pg_has_role() exists, no?

That would be a useful addition, yes.

Yours,
Laurenz Albe

---

From: Robert Haas <[email-masked]>
Date: Fri, 20 Sep 2024 12:51:01 -0400

> 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.

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.
2. You must also have CREATEROLE.
3. If the target role is SUPERUSER, you must be SUPERUSER.

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

-- 
Robert Haas
EDB: http://www.enterprisedb.com

---

From: Tom Lane <[email-masked]>
Date: Fri, 20 Sep 2024 14:16:38 -0400

> mentioned in the documentation.

Surely that's a bad documentation omission.  Do we want to document
all the variants convert_role_priv_string allows?  They appear
functionally equivalent, so I'd be inclined to document just one.
'USAGE WITH ADMIN OPTION' seems a reasonable choice.

			regards, tom lane

---

From: Tom Lane <[email-masked]>
Date: Fri, 20 Sep 2024 14:34:27 -0400

> Surely that's a bad documentation omission.

Actually, it's not true that it's entirely undocumented, because the
text above the table that describes pg_has_role mentions

    Optionally, WITH GRANT OPTION can be added to a privilege type to
    test whether the privilege is held with grant option.

But I concur that it's not immediately obvious that that applies
to role membership, since we don't use the "grant option" terminology
for roles.

I'm now inclined to add wording within the pg_has_role entry, along
the lines of

    WITH ADMIN OPTION or WITH GRANT OPTION can be added to any of
    these privilege types to test whether ADMIN privilege is held
    (all six spellings test the same thing).

			regards, tom lane

---

From: Robert Haas <[email-masked]>
Date: Fri, 20 Sep 2024 14:49:13 -0400

>     (all six spellings test the same thing).

I don't have an opinion about the details, but +1 for documenting it
somehow. I also think it's weird that we have six spellings that test
the same thing, none of which are $SUBJECT. pg_has_role seems a little
half-baked to me...

---

From: Tom Lane <[email-masked]>
Date: Fri, 20 Sep 2024 15:20:35 -0400

> the same thing, none of which are $SUBJECT. pg_has_role seems a little

Yeah.  I think the original idea was to make it as parallel to
has_table_privilege and friends as we could (but why did we then
stick a pg_ prefix on it?).  So that led to MEMBER WITH GRANT OPTION,
and then the other spellings seem to have come along later.

			regards, tom lane

---

From: Dominique Devienne <[email-masked]>
Date: Mon, 23 Sep 2024 14:54:03 +0200

> 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.

> 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.

> 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

---

From: Dominique Devienne <[email-masked]>
Date: Mon, 23 Sep 2024 14:56:33 +0200

On Fri, Sep 20, 2024 at 8:49 PM Robert Haas <[email-masked]> wrote:
> > I'm now inclined to add wording within the pg_has_role entry

+1 as well. Especially since I now recall, in hindsight,
about reading the *distant* mention about 'WITH ADMIN OPTION'
that could be added, but still forgot about it when reading only the
*immediate* doc on pg_has_role(). My $0.02. --DD