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

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?

--
With Regards,
Ashutosh Sharma.