Re: Orphaned users in PG16 and above can only be managed by Superusers - Mailing list pgsql-hackers

Hi,

On Thu, Mar 13, 2025 at 11:14 AM Ashutosh Sharma <ashu.coek88@gmail.com> wrote:
>
> Hi,
>
> On Wed, Mar 12, 2025 at 9:06 PM Nathan Bossart <nathandbossart@gmail.com> wrote:
> >
> > There are also other ways besides DROP ROLE that roles can end up without
> > any admins:
> >
> >         postgres=# create role a createrole;
> >         CREATE ROLE
> >         postgres=# set role a;
> >         SET
> >         postgres=> create role b createrole;
> >         CREATE ROLE
> >         postgres=> set role b;
> >         SET
> >         postgres=> create role c;
> >         CREATE ROLE
> >         postgres=> reset role;
> >         RESET
> >         postgres=# revoke c from b;
> >         REVOKE ROLE
> >
> > I wonder if we should adjust the requirements here to be "an operation
> > cannot result in a role with 0 admins."  That does mean you might lose
> > indirect admin privileges, but I'm not sure that's sustainable, anyway.
> > For example, if a role has 2 admins, should we block REVOKE from one of the
> > admins because another role inherited its privileges?  If nothing else, it
> > sounds like a complicated user experience.
> >
>
> I think this is acceptable because role 'b', from which role 'a'
> inherited the ADMIN privilege on role 'c', no longer has the privilege
> either. Therefore, it's understandable for role 'a' to lose the
> indirect privilege, as the role it was inheriting from has also lost
> it. Moreover, this action is being performed by a superuser, the same
> superuser who granted the ADMIN privilege on role 'c' to role 'b', and
> it should have the ability to revoke it at any time. Once role 'b'
> loses the ADMIN privilege on role 'c', it's expected that role 'a'
> will also lose the privilege, as it was inherited from role 'b'. After
> the ADMIN privilege is revoked, role 'a' will no longer have a
> dependency on role 'b', so dropping role 'b' should succeed.
>

Here’s an example demonstrating the existing behavior of the DROP ROLE
command when attempting to drop a grantor role, which closely
resembles the behavior of the DROP ROLE command trying to drop a role
through which admin privileges are inherited by other role(s).

ashu@postgres=# create user a createrole login;
CREATE ROLE
ashu@postgres=# set role a;
SET
ashu@postgres=> create user b createrole login;
CREATE ROLE
ashu@postgres=> create user c createrole login;
CREATE ROLE
ashu@postgres=> grant c to b with admin true;
GRANT ROLE
ashu@postgres=> reset role;
RESET
ashu@postgres=# drop role a;
ERROR:  2BP01: role "a" cannot be dropped because some objects depend on it
DETAIL:  privileges for membership of role b in role c
LOCATION:  DropRole, user.c:1303
ashu@postgres=# set role a;
SET
ashu@postgres=> revoke c from b;
REVOKE ROLE
ashu@postgres=# drop role a;
DROP ROLE

In the example above, the DROP ROLE command for role 'a' failed
because 'a' had granted role 'c' to role 'b'. However, once role 'c'
was revoked from role 'b', the DROP ROLE command succeeded.

--
With Regards,
Ashutosh Sharma.