Message-ID: <382be150738e46811e2bd0b6dcda1dbffe096272.camel@cybertec.at>
Subject: Re: BUG #18934: Even with WITH ADMIN OPTION, I Cannot Manage Role
 Memberships
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: snaperling@gmail.com, pgsql-bugs@lists.postgresql.org
Date: Tue, 20 May 2025 08:40:15 +0200
In-Reply-To: <18934-67d2e90068801d2b@postgresql.org>
References: <18934-67d2e90068801d2b@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.1 (3.56.1-1.fc42) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/382be150738e46811e2bd0b6dcda1dbffe096272.camel%40cybertec.at>
Precedence: bulk

On Mon, 2025-05-19 at 08:44 +0000, PG Bug reporting form wrote:
> PostgreSQL version: 16.8
> Operating system:   Windows 10
>=20
> In PostgreSQL, I have a role hierarchy involving a user and two group rol=
es:
> Roles:
>     tester1@something =E2=80=94 a user role (not superuser)
>     user_manager =E2=80=94 an intermediate group role
>     user_group =E2=80=94 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
> >=20
> In this configuration:
>     tester1@something should be able to add/remove members from user_grou=
p.
>     But it cannot =E2=80=94 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 =E2=94=82         Member of         =E2=94=82       Options   =
    =E2=94=82 Grantor =20
  =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=
=E2=95=90=E2=95=90=E2=95=90=E2=95=AA=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=
=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=
=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=
=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=AA=E2=95=90=E2=
=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=
=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=
=E2=95=90=E2=95=90=E2=95=90=E2=95=AA=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=
=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90
   a         =E2=94=82 b                         =E2=94=82 ADMIN, INHERIT, =
SET =E2=94=82 postgres
   b         =E2=94=82 c                         =E2=94=82 ADMIN, INHERIT, =
SET =E2=94=82 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 =E2=94=82         Member of         =E2=94=82       Options   =
    =E2=94=82 Grantor =20
  =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=
=E2=95=90=E2=95=90=E2=95=90=E2=95=AA=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=
=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=
=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=
=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=AA=E2=95=90=E2=
=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=
=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=
=E2=95=90=E2=95=90=E2=95=90=E2=95=AA=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=
=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90
   a         =E2=94=82 b                         =E2=94=82 ADMIN, INHERIT, =
SET =E2=94=82 postgres
   b         =E2=94=82 c                         =E2=94=82 INHERIT, SET    =
    =E2=94=82 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