Re: BUG #18934: Even with WITH ADMIN OPTION, I Cannot Manage Role Memberships

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(at)gmail(dot)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(at)gmail(dot)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(at)gmail(dot)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(dot)albe(at)cybertec(dot)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(at)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(at)something` | `user_manager` | YES
>>>> > > `user_manager` | `user_group` | YES
>>>> > >
>>>> > In this configuration:
>>>> > tester1(at)something should be able to add/remove members from
>>>> user_group.
>>>> > But it cannot — GRANT or REVOKE on user_group fails.
>>>> > Even though tester1(at)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(at)something user_manager YES
>>>> > user_manager user_group NO
>>>> > Now, unexpectedly:
>>>> > tester1(at)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
>>>>
>>>