From: Laurenz Albe <laurenz.albe@cybertec.at>
To: snaperling@gmail.com
To: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18934: Even with WITH ADMIN OPTION, I Cannot Manage Role Memberships
Date: Tue, 20 May 2025 08:40:15 +0200
Message-ID: <382be150738e46811e2bd0b6dcda1dbffe096272.camel@cybertec.at> (raw)
In-Reply-To: <18934-67d2e90068801d2b@postgresql.org>
References: <18934-67d2e90068801d2b@postgresql.org>
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
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: laurenz.albe@cybertec.at, snaperling@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18934: Even with WITH ADMIN OPTION, I Cannot Manage Role Memberships
In-Reply-To: <382be150738e46811e2bd0b6dcda1dbffe096272.camel@cybertec.at>
* 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