Обсуждение: BUG #18934: Even with WITH ADMIN OPTION, I Cannot Manage Role Memberships

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

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

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