pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18934: Even with WITH ADMIN OPTION, I Cannot Manage Role Memberships
4+ messages / 4 participants
[nested] [flat]
* BUG #18934: Even with WITH ADMIN OPTION, I Cannot Manage Role Memberships
@ 2025-05-19 08:44 PG Bug reporting form <noreply@postgresql.org>
2025-05-20 06:40 ` Re: BUG #18934: Even with WITH ADMIN OPTION, I Cannot Manage Role Memberships Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 4+ messages in thread
From: PG Bug reporting form @ 2025-05-19 08:44 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: snaperling@gmail.com
The following bug has been logged on the website:
Bug reference: 18934
Logged by: Luis Couto
Email address: snaperling@gmail.com
PostgreSQL version: 16.8
Operating system: Windows 10
Description:
In PostgreSQL, I have a role hierarchy involving a user and two group roles:
Roles:
tester1@something — a user role (not superuser)
user_manager — an intermediate group role
user_group — the target group role whose membership I want to manage
| Role | Member Of | `WITH ADMIN
OPTION` |
| `tester1@something` | `user_manager` | YES
|
| `user_manager` | `user_group` | YES
|
In this configuration:
tester1@something should be able to add/remove members from user_group.
But it cannot — GRANT or REVOKE on user_group fails.
Even though tester1@something has full admin rights on user_manager, and
user_manager has admin rights on user_group.
Role Setup (After Manual Fix)
When I run:
REVOKE ADMIN OPTION FOR user_group FROM user_manager;
So that now:
Role Member Of WITH ADMIN OPTION
tester1@something user_manager YES
user_manager user_group NO
Now, unexpectedly:
tester1@something can add and remove members from user_group.
Even though no role in the chain has WITH ADMIN OPTION on user_group.
Summary of the Two Issues
1. Main Issue (Unexpected Restriction):
When user_manager has WITH ADMIN OPTION on user_group, and I (as
tester1@something) have WITH ADMIN OPTION on user_manager, I cannot manage
user_group.
This is confusing — PostgreSQL should allow it, as both admin links are
present. (maybe I'm getting something wrong)
2. Secondary Issue (Unexpected Permission Grant):
If I revoke admin option from user_manager on user_group, suddenly I can
manage user_group from tester1@something.
This contradicts the documented need for WITH ADMIN OPTION and appears to
expose a privilege inconsistency or escalation.
What could be happening?
Thank you in advance,
Luis Couto
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #18934: Even with WITH ADMIN OPTION, I Cannot Manage Role Memberships
2025-05-19 08:44 BUG #18934: Even with WITH ADMIN OPTION, I Cannot Manage Role Memberships PG Bug reporting form <noreply@postgresql.org>
@ 2025-05-20 06:40 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-05-26 09:18 ` Re: BUG #18934: Even with WITH ADMIN OPTION, I Cannot Manage Role Memberships Luis Couto <snaperling@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Laurenz Albe @ 2025-05-20 06:40 UTC (permalink / raw)
To: snaperling@gmail.com; pgsql-bugs@lists.postgresql.org
On Mon, 2025-05-19 at 08:44 +0000, PG Bug reporting form wrote:
> PostgreSQL version: 16.8
> Operating system: Windows 10
>
> In PostgreSQL, I have a role hierarchy involving a user and two group roles:
> Roles:
> tester1@something — a user role (not superuser)
> user_manager — an intermediate group role
> user_group — the target group role whose membership I want to manage
> > Role | Member Of | `WITH ADMIN OPTION` |
> > `tester1@something` | `user_manager` | YES
> > `user_manager` | `user_group` | YES
> >
> In this configuration:
> tester1@something should be able to add/remove members from user_group.
> But it cannot — GRANT or REVOKE on user_group fails.
> Even though tester1@something has full admin rights on user_manager, and
> user_manager has admin rights on user_group.
> Role Setup (After Manual Fix)
> When I run:
> REVOKE ADMIN OPTION FOR user_group FROM user_manager;
> So that now:
> Role Member Of WITH ADMIN OPTION
> tester1@something user_manager YES
> user_manager user_group NO
> Now, unexpectedly:
> tester1@something can add and remove members from user_group.
> Even though no role in the chain has WITH ADMIN OPTION on user_group.
I cannot reproduce that:
\c - postgres
You are now connected to database "postgres" as user "postgres".
CREATE ROLE a LOGIN;
CREATE ROLE b ADMIN a;
CREATE ROLE c ADMIN b;
\drg
List of role grants
Role name │ Member of │ Options │ Grantor
═══════════╪═══════════════════════════╪═════════════════════╪══════════
a │ b │ ADMIN, INHERIT, SET │ postgres
b │ c │ ADMIN, INHERIT, SET │ postgres
[...]
\c - a
You are now connected to database "postgres" as user "a".
GRANT c TO laurenz;
Works without a hitch!
Let's undo the grant and remove the ADMIN option as user "postgres":
REVOKE c FROM laurenz;
\c - postgres
You are now connected to database "postgres" as user "postgres".
GRANT c TO b WITH ADMIN FALSE;
\drg
List of role grants
Role name │ Member of │ Options │ Grantor
═══════════╪═══════════════════════════╪═════════════════════╪══════════
a │ b │ ADMIN, INHERIT, SET │ postgres
b │ c │ INHERIT, SET │ postgres
[...]
Now let's try again as user "a":
\c - a
You are now connected to database "postgres" as user "a".
GRANT c TO laurenz;
ERROR: permission denied to grant role "c"
DETAIL: Only roles with the ADMIN option on role "c" may grant this role.
So please explain in detail what doesn't work for you.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #18934: Even with WITH ADMIN OPTION, I Cannot Manage Role Memberships
2025-05-19 08:44 BUG #18934: Even with WITH ADMIN OPTION, I Cannot Manage Role Memberships PG Bug reporting form <noreply@postgresql.org>
2025-05-20 06:40 ` Re: BUG #18934: Even with WITH ADMIN OPTION, I Cannot Manage Role Memberships Laurenz Albe <laurenz.albe@cybertec.at>
@ 2025-05-26 09:18 ` Luis Couto <snaperling@gmail.com>
2025-05-26 15:07 ` Re: BUG #18934: Even with WITH ADMIN OPTION, I Cannot Manage Role Memberships Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Luis Couto @ 2025-05-26 09:18 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-bugs@lists.postgresql.org
So I fond the behaviour this is expected:
Thank you for the support:
Scenario:
-
user_manager granted user_group to test.
-
postgres has ADMIN OPTION on user_group.
-
postgres is not a member of user_manager.
-
*Result*: postgres cannot revoke user_group from test.
------------------------------
Explanation:
In PostgreSQL, when revoking role memberships, the following rules apply:
1.
*Grantor Restriction*: Only the role that granted the membership (the
grantor) or a role with ADMIN OPTION that is part of the grantor's
administrative chain can revoke the membership.
2.
*Superuser Limitation*: Even superusers cannot revoke role memberships
unless they are the original grantor or have been granted the necessary
administrative privileges by the grantor.
This behavior ensures a strict and secure delegation of administrative
privileges, preventing unauthorized revocation of role memberships.
------------------------------
Supporting Documentation:
-
*PostgreSQL Official Documentation*:
"A user can only revoke privileges that were granted directly by that
user. If, for example, user A has granted a privilege with grant option to
user B, and user B has in turn granted it to user C, then user A cannot
revoke the privilege directly from C. Instead, user A could revoke the
grant option from user B and use the CASCADE option so that the
privilege is in turn revoked from user C. For another example, if both A
and B have granted the same privilege to C, A can revoke their own grant
but not B's grant, so C will still effectively have the privilege.C."
PostgreSQL+3PostgreSQL+3EDB+3
<https://www.postgresql.org/docs/current/sql-revoke.html;
Source: PostgreSQL REVOKE Documentation
<https://www.postgresql.org/docs/current/sql-revoke.html;
------------------------------
Implications:
Given these rules, even though postgres has ADMIN OPTION on user_group, it
cannot revoke the membership from test because:
-
postgres is not the original grantor (user_manager).
-
postgres is not part of user_manager's administrative chain.
Therefore, unless user_manager revokes the membership or grants postgres
the necessary administrative privileges, postgres cannot perform the
revocation.
Le lun. 26 mai 2025 à 09:59, Luis Couto <snaperling@gmail.com> a écrit :
> to clarify:
> user_manager granted user_group to test
>
> postgres has ADMIN OPTION on user_group
>
> BUT: postgres is not a member of user_manager
>
> RESULT:
> postgres cannot revoke test's membership
>
> Is this expected?
>
> Le lun. 26 mai 2025 à 09:56, Luis Couto <snaperling@gmail.com> a écrit :
>
>> Another think that i notice is even if im a user that have with admin on
>> the user_group i cannot remove other users granted by "user_manger" from
>> the user_group this should do not whappen correct?
>>
>> Le lun. 26 mai 2025 à 09:16, Luis Couto <snaperling@gmail.com> a écrit :
>>
>>> This is why does not work for me:
>>> WARNING: role "test" has not been granted membership in role
>>> "user_group" by role "postgres"
>>> NOTICE: role "test" has already been granted membership in role
>>> "user_group" by role "user_manager"
>>>
>>> So even as postgres I cannot REVOKE I think this is from postgresql 16.
>>>
>>> I need to change the approach in order to grant and revoke users from
>>> groups.
>>>
>>> Thank you Laurenz Albe!
>>>
>>> Regards
>>> Luis Couto
>>>
>>> Le mar. 20 mai 2025 à 07:40, Laurenz Albe <laurenz.albe@cybertec.at> a
>>> écrit :
>>>
>>>> On Mon, 2025-05-19 at 08:44 +0000, PG Bug reporting form wrote:
>>>> > PostgreSQL version: 16.8
>>>> > Operating system: Windows 10
>>>> >
>>>> > In PostgreSQL, I have a role hierarchy involving a user and two group
>>>> roles:
>>>> > Roles:
>>>> > tester1@something — a user role (not superuser)
>>>> > user_manager — an intermediate group role
>>>> > user_group — the target group role whose membership I want to
>>>> manage
>>>> > > Role | Member Of | `WITH ADMIN OPTION` |
>>>> > > `tester1@something` | `user_manager` | YES
>>>> > > `user_manager` | `user_group` | YES
>>>> > >
>>>> > In this configuration:
>>>> > tester1@something should be able to add/remove members from
>>>> user_group.
>>>> > But it cannot — GRANT or REVOKE on user_group fails.
>>>> > Even though tester1@something has full admin rights on
>>>> user_manager, and
>>>> > user_manager has admin rights on user_group.
>>>> > Role Setup (After Manual Fix)
>>>> > When I run:
>>>> > REVOKE ADMIN OPTION FOR user_group FROM user_manager;
>>>> > So that now:
>>>> > Role Member Of WITH ADMIN OPTION
>>>> > tester1@something user_manager YES
>>>> > user_manager user_group NO
>>>> > Now, unexpectedly:
>>>> > tester1@something can add and remove members from user_group.
>>>> > Even though no role in the chain has WITH ADMIN OPTION on
>>>> user_group.
>>>>
>>>> I cannot reproduce that:
>>>>
>>>> \c - postgres
>>>> You are now connected to database "postgres" as user "postgres".
>>>>
>>>> CREATE ROLE a LOGIN;
>>>> CREATE ROLE b ADMIN a;
>>>> CREATE ROLE c ADMIN b;
>>>>
>>>> \drg
>>>> List of role grants
>>>> Role name │ Member of │ Options │
>>>> Grantor
>>>>
>>>> ═══════════╪═══════════════════════════╪═════════════════════╪══════════
>>>> a │ b │ ADMIN, INHERIT, SET │
>>>> postgres
>>>> b │ c │ ADMIN, INHERIT, SET │
>>>> postgres
>>>> [...]
>>>>
>>>> \c - a
>>>> You are now connected to database "postgres" as user "a".
>>>>
>>>> GRANT c TO laurenz;
>>>>
>>>> Works without a hitch!
>>>>
>>>> Let's undo the grant and remove the ADMIN option as user "postgres":
>>>>
>>>> REVOKE c FROM laurenz;
>>>>
>>>> \c - postgres
>>>> You are now connected to database "postgres" as user "postgres".
>>>>
>>>> GRANT c TO b WITH ADMIN FALSE;
>>>>
>>>> \drg
>>>> List of role grants
>>>> Role name │ Member of │ Options │
>>>> Grantor
>>>>
>>>> ═══════════╪═══════════════════════════╪═════════════════════╪══════════
>>>> a │ b │ ADMIN, INHERIT, SET │
>>>> postgres
>>>> b │ c │ INHERIT, SET │
>>>> postgres
>>>> [...]
>>>>
>>>> Now let's try again as user "a":
>>>>
>>>> \c - a
>>>> You are now connected to database "postgres" as user "a".
>>>>
>>>> GRANT c TO laurenz;
>>>> ERROR: permission denied to grant role "c"
>>>> DETAIL: Only roles with the ADMIN option on role "c" may grant this
>>>> role.
>>>>
>>>> So please explain in detail what doesn't work for you.
>>>>
>>>> Yours,
>>>> Laurenz Albe
>>>>
>>>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #18934: Even with WITH ADMIN OPTION, I Cannot Manage Role Memberships
2025-05-19 08:44 BUG #18934: Even with WITH ADMIN OPTION, I Cannot Manage Role Memberships PG Bug reporting form <noreply@postgresql.org>
2025-05-20 06:40 ` Re: BUG #18934: Even with WITH ADMIN OPTION, I Cannot Manage Role Memberships Laurenz Albe <laurenz.albe@cybertec.at>
2025-05-26 09:18 ` Re: BUG #18934: Even with WITH ADMIN OPTION, I Cannot Manage Role Memberships Luis Couto <snaperling@gmail.com>
@ 2025-05-26 15:07 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2025-05-26 15:07 UTC (permalink / raw)
To: Luis Couto <snaperling@gmail.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-bugs@lists.postgresql.org
Luis Couto <snaperling@gmail.com> writes:
> Scenario:
> user_manager granted user_group to test.
> postgres has ADMIN OPTION on user_group.
> postgres is not a member of user_manager.
> *Result*: postgres cannot revoke user_group from test.
This was already explained to you, but: the only thing stopping that
is that you are specifying the REVOKE incorrectly. You have to
match the "grantor" property of the grant, and if you are not doing
REVOKE as the same role that made the grant, that requires an explicit
GRANTED BY clause. Example (starting as postgres):
regression=# create role user_group;
CREATE ROLE
regression=# create role user_manager;
CREATE ROLE
regression=# grant user_group to user_manager with admin option;
GRANT ROLE
regression=# create user test;
CREATE ROLE
regression=# set role user_manager;
SET
regression=> grant user_group to test;
GRANT ROLE
regression=> reset role;
RESET
regression=# \drg
List of role grants
Role name | Member of | Options | Grantor
--------------+------------+---------------------+--------------
test | user_group | INHERIT, SET | user_manager
user_manager | user_group | ADMIN, INHERIT, SET | postgres
(2 rows)
regression=# revoke user_group from test;
WARNING: role "test" has not been granted membership in role "user_group" by role "postgres"
REVOKE ROLE
regression=# \drg
List of role grants
Role name | Member of | Options | Grantor
--------------+------------+---------------------+--------------
test | user_group | INHERIT, SET | user_manager
user_manager | user_group | ADMIN, INHERIT, SET | postgres
(2 rows)
regression=# revoke user_group from test granted by user_manager;
REVOKE ROLE
regression=# \drg
List of role grants
Role name | Member of | Options | Grantor
--------------+------------+---------------------+----------
user_manager | user_group | ADMIN, INHERIT, SET | postgres
(1 row)
My first REVOKE failed because it was targeting a grant
that didn't exist, not because postgres would have lacked
the privilege.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-05-26 15:07 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-05-19 08:44 BUG #18934: Even with WITH ADMIN OPTION, I Cannot Manage Role Memberships PG Bug reporting form <noreply@postgresql.org>
2025-05-20 06:40 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-05-26 09:18 ` Luis Couto <snaperling@gmail.com>
2025-05-26 15:07 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox