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

On 1/22/25 12:07, Ashutosh Sharma wrote:
> Hi Robert,
>
> On Tue, Jan 21, 2025 at 10:22 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>
>> On Thu, Jan 9, 2025 at 12:31 AM Ashutosh Sharma <ashu(dot)coek88(at)gmail(dot)com> wrote:
>>> Starting from PG16, it seems that orphaned users can only be managed
>>> by superusers. For example, if userA creates userB, and userB creates
>>> userC, then both userB (the parent of userC) and userA (the
>>> grandparent of userC) would typically have the ability to
>>> manage/administer userC. However, if userB is dropped, userA (the
>>> grandparent of userC) loses the ability to administer userC as well.
>>> This leads to a situation where only superusers can manage userC.
>>>
>>> Shouldn't userA retain the permission to manage userC even if userB is
>>> removed? Otherwise, only superusers would have the authority to
>>> administer userC (the orphaned user in this case), which may not be
>>> feasible for cloud environments where superuser access is restricted.
>>
>> This doesn't seem great, but it's not clear to me what we should do
>> about it. It doesn't really seem reasonable to me to change the role
>> grants that point to userB to make them point to userA instead. After
>> all, there could be multiple sets of role grants pointing to userB and
>> there could be multiple sets of role grants from userB pointing
>> elsewhere and they could all have different options (admin, set,
>> inherit). It doesn't feel right to have DROP ROLE make a bunch of
>> arbitrary decisions about what to do about that. We could make DROP
>> ROLE userB fail, perhaps, and tell the user they need to sort it out
>> first, but I'm not entirely sure that we have the right tools to allow
>> the user to do that in a convenient way. If userC were instead tableC,
>> DROP OWNED or REASSIGN OWNED could be used.
>
> Thanks for sharing your thoughts and inputs. I'm also not quite clear
> about the fix. Some of the solutions/changes you've mentioned above
> seem quite complex and may not be reasonable, as you pointed out. How
> about introducing a new predefined role, perhaps something like
> pg_admin_all, which, when granted to an admin user in the system,
> would allow them to manage all non-superusers on the server?
>

If this stopped working in PG16, then how/why did it work in PG15? Is
that intentional change?

I agree DROP ROLE shouldn't be doing decisions about which roles should
remain responsible for managing the orphaned roles. After all, if a role
has this privilege "indirectly" then why should it suddenly get it
"directly" after the intermediate role gets dropped? That would be quite
surprising/confusing,

If we could make the DROP ROLE fail if it causes some other role to
become orphaned, that'd be a solution too, I think. How difficult is it
to check that, though? I imagine we'd have to list which roles the
to-be-dropped role has ADMIN privilege on, and then check there's at
least one other role with the ADMIN privilege. I'm not very familiar
with this part of the code, so maybe I'm entirely wrong.

So it seems to me having a predefined role that allows managing all
roles (including orphaned ones) might be the good alternative. I
initially wrote "cleaner", but it feels a bit wrong to allow orphaned
roles and then have to "fix" this by having this predefined role. Not
allowing orphaned roles seems cleaner, but it's not a bug either.

FWIW I'm assuming we're not looking for a "fix" for already released
versions, right?

regards

--
Tomas Vondra