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

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

From

PG Bug reporting form

Date:

19 May, 11:44:51

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

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

From

Laurenz Albe

Date:

20 May, 09:40:15

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

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

From

Luis Couto

Date:

26 May, 12:18:22

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:

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.
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
Source: PostgreSQL REVOKE Documentation

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