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

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