Horton
Menu

Thread

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

    PG Bug reporting form <noreply@postgresql.org> — 2025-05-19T08:44:51Z

    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

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

    Laurenz Albe <laurenz.albe@cybertec.at> — 2025-05-20T06:40:15Z

    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

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

    Luis Couto <snaperling@gmail.com> — 2025-05-26T09:18:22Z

    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
>>>>
>>>

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-05-26T15:07:18Z

    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