Re: BUG #18934: Even with WITH ADMIN OPTION, I Cannot Manage Role Memberships
Luis Couto <snaperling@gmail.com>
From: Luis Couto <snaperling@gmail.com>
To: Laurenz Albe <laurenz.albe@cybertec.at>, pgsql-bugs@lists.postgresql.org
Date: 2025-05-26T09:18:22Z
Lists: pgsql-bugs
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 >>>> >>>