public inbox for [email protected]
help / color / mirror / Atom feedRe: Why no pg_has_role(..., 'ADMIN')?
8+ messages / 4 participants
[nested] [flat]
* Re: Why no pg_has_role(..., 'ADMIN')?
@ 2024-09-20 16:37 Laurenz Albe <[email protected]>
2024-09-20 16:51 ` Re: Why no pg_has_role(..., 'ADMIN')? Robert Haas <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Laurenz Albe @ 2024-09-20 16:37 UTC (permalink / raw)
To: Dominique Devienne <[email protected]>; pgsql-general; +Cc: [email protected]
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Why no pg_has_role(..., 'ADMIN')?
2024-09-20 16:37 Re: Why no pg_has_role(..., 'ADMIN')? Laurenz Albe <[email protected]>
@ 2024-09-20 16:51 ` Robert Haas <[email protected]>
2024-09-20 18:16 ` Re: Why no pg_has_role(..., 'ADMIN')? Tom Lane <[email protected]>
2024-09-23 12:54 ` Re: Why no pg_has_role(..., 'ADMIN')? Dominique Devienne <[email protected]>
0 siblings, 2 replies; 8+ messages in thread
From: Robert Haas @ 2024-09-20 16:51 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: Dominique Devienne <[email protected]>; pgsql-general
On Fri, Sep 20, 2024 at 12:37 PM Laurenz Albe <[email protected]> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Why no pg_has_role(..., 'ADMIN')?
2024-09-20 16:37 Re: Why no pg_has_role(..., 'ADMIN')? Laurenz Albe <[email protected]>
2024-09-20 16:51 ` Re: Why no pg_has_role(..., 'ADMIN')? Robert Haas <[email protected]>
@ 2024-09-20 18:16 ` Tom Lane <[email protected]>
2024-09-20 18:34 ` Re: Why no pg_has_role(..., 'ADMIN')? Tom Lane <[email protected]>
1 sibling, 1 reply; 8+ messages in thread
From: Tom Lane @ 2024-09-20 18:16 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: Laurenz Albe <[email protected]>; Dominique Devienne <[email protected]>; pgsql-general
Robert Haas <[email protected]> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Why no pg_has_role(..., 'ADMIN')?
2024-09-20 16:37 Re: Why no pg_has_role(..., 'ADMIN')? Laurenz Albe <[email protected]>
2024-09-20 16:51 ` Re: Why no pg_has_role(..., 'ADMIN')? Robert Haas <[email protected]>
2024-09-20 18:16 ` Re: Why no pg_has_role(..., 'ADMIN')? Tom Lane <[email protected]>
@ 2024-09-20 18:34 ` Tom Lane <[email protected]>
2024-09-20 18:49 ` Re: Why no pg_has_role(..., 'ADMIN')? Robert Haas <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2024-09-20 18:34 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: Laurenz Albe <[email protected]>; Dominique Devienne <[email protected]>; pgsql-general
I wrote:
> Robert Haas <[email protected]> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Why no pg_has_role(..., 'ADMIN')?
2024-09-20 16:37 Re: Why no pg_has_role(..., 'ADMIN')? Laurenz Albe <[email protected]>
2024-09-20 16:51 ` Re: Why no pg_has_role(..., 'ADMIN')? Robert Haas <[email protected]>
2024-09-20 18:16 ` Re: Why no pg_has_role(..., 'ADMIN')? Tom Lane <[email protected]>
2024-09-20 18:34 ` Re: Why no pg_has_role(..., 'ADMIN')? Tom Lane <[email protected]>
@ 2024-09-20 18:49 ` Robert Haas <[email protected]>
2024-09-20 19:20 ` Re: Why no pg_has_role(..., 'ADMIN')? Tom Lane <[email protected]>
2024-09-23 12:56 ` Re: Why no pg_has_role(..., 'ADMIN')? Dominique Devienne <[email protected]>
0 siblings, 2 replies; 8+ messages in thread
From: Robert Haas @ 2024-09-20 18:49 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Laurenz Albe <[email protected]>; Dominique Devienne <[email protected]>; pgsql-general
On Fri, Sep 20, 2024 at 2:34 PM Tom Lane <[email protected]> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Why no pg_has_role(..., 'ADMIN')?
2024-09-20 16:37 Re: Why no pg_has_role(..., 'ADMIN')? Laurenz Albe <[email protected]>
2024-09-20 16:51 ` Re: Why no pg_has_role(..., 'ADMIN')? Robert Haas <[email protected]>
2024-09-20 18:16 ` Re: Why no pg_has_role(..., 'ADMIN')? Tom Lane <[email protected]>
2024-09-20 18:34 ` Re: Why no pg_has_role(..., 'ADMIN')? Tom Lane <[email protected]>
2024-09-20 18:49 ` Re: Why no pg_has_role(..., 'ADMIN')? Robert Haas <[email protected]>
@ 2024-09-20 19:20 ` Tom Lane <[email protected]>
1 sibling, 0 replies; 8+ messages in thread
From: Tom Lane @ 2024-09-20 19:20 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: Laurenz Albe <[email protected]>; Dominique Devienne <[email protected]>; pgsql-general
Robert Haas <[email protected]> writes:
> On Fri, Sep 20, 2024 at 2:34 PM Tom Lane <[email protected]> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Why no pg_has_role(..., 'ADMIN')?
2024-09-20 16:37 Re: Why no pg_has_role(..., 'ADMIN')? Laurenz Albe <[email protected]>
2024-09-20 16:51 ` Re: Why no pg_has_role(..., 'ADMIN')? Robert Haas <[email protected]>
2024-09-20 18:16 ` Re: Why no pg_has_role(..., 'ADMIN')? Tom Lane <[email protected]>
2024-09-20 18:34 ` Re: Why no pg_has_role(..., 'ADMIN')? Tom Lane <[email protected]>
2024-09-20 18:49 ` Re: Why no pg_has_role(..., 'ADMIN')? Robert Haas <[email protected]>
@ 2024-09-23 12:56 ` Dominique Devienne <[email protected]>
1 sibling, 0 replies; 8+ messages in thread
From: Dominique Devienne @ 2024-09-23 12:56 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: Tom Lane <[email protected]>; Laurenz Albe <[email protected]>; pgsql-general
On Fri, Sep 20, 2024 at 8:49 PM Robert Haas <[email protected]> wrote:
> On Fri, Sep 20, 2024 at 2:34 PM Tom Lane <[email protected]> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Why no pg_has_role(..., 'ADMIN')?
2024-09-20 16:37 Re: Why no pg_has_role(..., 'ADMIN')? Laurenz Albe <[email protected]>
2024-09-20 16:51 ` Re: Why no pg_has_role(..., 'ADMIN')? Robert Haas <[email protected]>
@ 2024-09-23 12:54 ` Dominique Devienne <[email protected]>
1 sibling, 0 replies; 8+ messages in thread
From: Dominique Devienne @ 2024-09-23 12:54 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: Laurenz Albe <[email protected]>; pgsql-general
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...
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2024-09-23 12:56 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-09-20 16:37 Re: Why no pg_has_role(..., 'ADMIN')? Laurenz Albe <[email protected]>
2024-09-20 16:51 ` Robert Haas <[email protected]>
2024-09-20 18:16 ` Tom Lane <[email protected]>
2024-09-20 18:34 ` Tom Lane <[email protected]>
2024-09-20 18:49 ` Robert Haas <[email protected]>
2024-09-20 19:20 ` Tom Lane <[email protected]>
2024-09-23 12:56 ` Dominique Devienne <[email protected]>
2024-09-23 12:54 ` Dominique Devienne <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox