Re: BUG #18934: Even with WITH ADMIN OPTION, I Cannot Manage Role Memberships - Mailing list pgsql-bugs
| From | Luis Couto |
|---|---|
| Subject | Re: BUG #18934: Even with WITH ADMIN OPTION, I Cannot Manage Role Memberships |
| Date | |
| Msg-id | CAF8bRTgffn8XJkn=bQOO1rkenOQfwigu4jSPVX7s00M15g=9Gg@mail.gmail.com Whole thread Raw |
| In response to | BUG #18934: Even with WITH ADMIN OPTION, I Cannot Manage Role Memberships (PG Bug reporting form <noreply@postgresql.org>) |
| List | pgsql-bugs |
Scenario:
user_managergranteduser_grouptotest.postgreshasADMIN OPTIONonuser_group.postgresis not a member ofuser_manager.Result:
postgrescannot revokeuser_groupfromtest.
Explanation:
In PostgreSQL, when revoking role memberships, the following rules apply:
Grantor Restriction: Only the role that granted the membership (the grantor) or a role with
ADMIN OPTIONthat is part of the grantor's administrative chain can revoke the membership.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
CASCADEoption 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+3Source: PostgreSQL REVOKE Documentation
Implications:
Given these rules, even though postgres has ADMIN OPTION on user_group, it cannot revoke the membership from test because:
postgresis not the original grantor (user_manager).postgresis not part ofuser_manager's administrative chain.
Therefore, unless user_manager revokes the membership or grants postgres the necessary administrative privileges, postgres cannot perform the revocation.
to clarify:Is this expected?
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 membershipLe 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 :Luis CoutoRegardsThank you Laurenz Albe!I need to change the approach in order to grant and revoke users from groups.This is why does not work for me:So even as postgres I cannot REVOKE I think this is from postgresql 16.
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"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
pgsql-bugs by date: