Re: Orphaned users in PG16 and above can only be managed by Superusers

On Thu, Mar 13, 2025 at 1:44 AM Ashutosh Sharma <ashu(dot)coek88(at)gmail(dot)com> wrote:
> On Wed, Mar 12, 2025 at 9:06 PM Nathan Bossart <nathandbossart(at)gmail(dot)com> wrote:
> > I think this approach has other problems. For example, even if a role has
> > admin directly on the dropped role, we'll block DROP ROLE if it also has
> > admin indirectly:
> >
>
> This is exactly what we're aiming for. We don't want the ADMIN
> privilege, inherited by role 'a' on role 'c' via role 'b', to be
> removed by the drop command. Therefore, we expect the command to fail.

I'm not sure what the right thing to do is here. This argument does
make some sense to me...

> > postgres=# revoke c from b;
> > REVOKE ROLE
>
> 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.

...as does this one. But if I accept both of those arguments, then
it's correct for the superuser to be blocked trying to "DROP ROLE b"
and also correct for the superuser to succeed trying to "REVOKE c FROM
b," and I'm not sure that really makes sense. I can understand why we
don't want a non-superuser to create a situation where the only
remaining administrator for a certain role is the superuser. But the
superuser themselves is not forbidden from creating such situations,
so why does it matter whether they try to create that situation via
DROP ROLE or via REVOKE?

I in general dislike throwing up barriers that prevent objects from
being dropped. As a user, I find such rules frustrating, especially if
I'm still allowed to accomplish the same drop indirectly by some
series of commands (e.g. REVOKE first, then DROP). If I'm allowed to
do it indirectly, then I should also be allowed to it directly, at
least in cases where there's only one way of fixing the problem that
is preventing me from doing the DROP, which I think is the case here.
For example, if bob owns a tractor and I want to DROP bob but the
tractor is indestructible, then it's reasonable to make the operation
fail. I need to give away bob's tractor before I drop him. But here,
if I say I want to DROP ROLE b, I'm going to have to first REVOKE c
FROM b -- there is no real other alternative. So why not make that
happen automatically? When I say I want to DROP something, I'm
serious: I really want it gone.

--
Robert Haas
EDB: http://www.enterprisedb.com