From: Tom Lane <tgl@sss.pgh.pa.us>
To: Luis Couto <snaperling@gmail.com>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18934: Even with WITH ADMIN OPTION, I Cannot Manage Role Memberships
Date: Mon, 26 May 2025 11:07:18 -0400
Message-ID: <634052.1748272038@sss.pgh.pa.us> (raw)
In-Reply-To: <CAF8bRTgffn8XJkn=bQOO1rkenOQfwigu4jSPVX7s00M15g=9Gg@mail.gmail.com>
References: <18934-67d2e90068801d2b@postgresql.org>
<382be150738e46811e2bd0b6dcda1dbffe096272.camel@cybertec.at>
<CAF8bRTifOPzuP2Ebku-Vx8GOLKytsSy7EOy5ZYqs_=eGAkiTiQ@mail.gmail.com>
<CAF8bRTh-RXhcEjV_BPnnjNA_RpXux5qoMUa_Fg4KVFtoks-umw@mail.gmail.com>
<CAF8bRTguQp15HZUn84vT_F7B7Xra=FKogSsk1nMSs8FM_RpSeQ@mail.gmail.com>
<CAF8bRTgffn8XJkn=bQOO1rkenOQfwigu4jSPVX7s00M15g=9Gg@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, snaperling@gmail.com, laurenz.albe@cybertec.at, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18934: Even with WITH ADMIN OPTION, I Cannot Manage Role Memberships
In-Reply-To: <634052.1748272038@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox