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

Hi All,

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.

--
With Regards,
Ashutosh Sharma.

On Thu, Jan 30, 2025 at 8:45 AM Ashutosh Sharma <ashu.coek88@gmail.com> wrote:
> Imagine a superuser creates role u1. Since the superuser is creating
> u1, it won't have membership in any role. Now, suppose u1 creates a
> new role, u2. In this case, u1 automatically becomes a member of u2
> with the admin option. However, at this point, there is no dependency
> between u1 and u2, meaning that dropping u2 shouldn't impact u1. Now,
> if u2 creates yet another role, u3, that's when u1 will start
> depending on u2. This is because if u2 were dropped, u1 would lose the
> ability to administer u3. At this stage, a dependency between u1 and
> u2 is recorded.

This seems to me to be assuming that who can administer which roles
won't change, but it can. The superuser is free to grant the ability
to administer u3 to some new user u4 or an existing user such as u1,
and is also free to remove that ability from u2.

I think if you want to legislate that attempting to drop u2 fails, you
have to do that by testing what the situation is at the time of the
drop command, not by adding dependencies in advance.

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

Hi Robert,

On Tue, Feb 4, 2025 at 10:54 PM Robert Haas <robertmhaas@gmail.com> wrote:
>
> On Thu, Jan 30, 2025 at 8:45 AM Ashutosh Sharma <ashu.coek88@gmail.com> wrote:
> > Imagine a superuser creates role u1. Since the superuser is creating
> > u1, it won't have membership in any role. Now, suppose u1 creates a
> > new role, u2. In this case, u1 automatically becomes a member of u2
> > with the admin option. However, at this point, there is no dependency
> > between u1 and u2, meaning that dropping u2 shouldn't impact u1. Now,
> > if u2 creates yet another role, u3, that's when u1 will start
> > depending on u2. This is because if u2 were dropped, u1 would lose the
> > ability to administer u3. At this stage, a dependency between u1 and
> > u2 is recorded.
>
> This seems to me to be assuming that who can administer which roles
> won't change, but it can. The superuser is free to grant the ability
> to administer u3 to some new user u4 or an existing user such as u1,
> and is also free to remove that ability from u2.
>

You're right, I'll fix that in the next patch.

> I think if you want to legislate that attempting to drop u2 fails, you
> have to do that by testing what the situation is at the time of the
> drop command, not by adding dependencies in advance.

I agree, and I will give this some thought to see if we can find a way
forward along these lines.

--
Thanks & Regards,
Ashutosh Sharma.