Hi Robert, On Tue, Jan 21, 2025 at 10:22 PM Robert Haas <robertmh...@gmail.com> wrote: > > On Thu, Jan 9, 2025 at 12:31 AM Ashutosh Sharma <ashu.coe...@gmail.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.
