Horton

Thread

Commits

  1. Doc: explain how to test ADMIN privilege with pg_has_role().

  1. Why no pg_has_role(..., 'ADMIN')?

    Dominique Devienne <ddevienne@gmail.com> — 2024-09-20T15:26:10Z

    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?

Thanks for any insights. --DD

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

    Laurenz Albe <laurenz.albe@cybertec.at> — 2024-09-20T16:37:49Z

    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

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

    Robert Haas <robertmhaas@gmail.com> — 2024-09-20T16:51:01Z

    On Fri, Sep 20, 2024 at 12:37 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> > 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.

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

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-09-20T18:16:38Z

    Robert Haas <robertmhaas@gmail.com> writes:
> 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.

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

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-09-20T18:34:27Z

    I wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> 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.

> 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

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

    Robert Haas <robertmhaas@gmail.com> — 2024-09-20T18:49:13Z

    On Fri, Sep 20, 2024 at 2:34 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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).

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

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

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-09-20T19:20:35Z

    Robert Haas <robertmhaas@gmail.com> writes:
> On Fri, Sep 20, 2024 at 2:34 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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).

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

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

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

    Dominique Devienne <ddevienne@gmail.com> — 2024-09-23T12:54:03Z

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

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

    Dominique Devienne <ddevienne@gmail.com> — 2024-09-23T12:56:33Z

    On Fri, Sep 20, 2024 at 8:49 PM Robert Haas <robertmhaas@gmail.com> wrote:
> On Fri, Sep 20, 2024 at 2:34 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > I'm now inclined to add wording within the pg_has_role entry
> I don't have an opinion about the details, but +1 for documenting it

+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