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

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.

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