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

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

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&gt;

Source: PostgreSQL REVOKE Documentation
<https://www.postgresql.org/docs/current/sql-revoke.html&gt;

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

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