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

Hi All,

On Fri, Jan 24, 2025 at 9:34 PM Ashutosh Sharma <ashu(dot)coek88(at)gmail(dot)com> wrote:
>
> On Fri, Jan 24, 2025 at 8:23 PM Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> >
> >
> > On 2025-01-23 Th 4:06 PM, Robert Haas wrote:
> > > On Thu, Jan 23, 2025 at 3:51 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
> > >> I wonder if it's a mistake that a role membership that has WITH ADMIN on
> > >> another role is silently removed if the member role is removed. We e.g. do
> > >> *not* do that for pg_auth_members.grantor:
> > >>
> > >> ERROR: 2BP01: role "r1" cannot be dropped because some objects depend on it
> > >> DETAIL: privileges for membership of role r2 in role r3
> > > Yeah, I'm not sure about this either, but this is the kind of thing I
> > > was thinking about when I replied before, saying that maybe dropping
> > > role B shouldn't just succeed. Maybe dropping a role that doesn't have
> > > privileges to administer any other role should be different than
> > > dropping one that does.
> > >
> >
> > That seems reasonable and consistent with what we do elsewhere, as
> > Andres noted.
> >
>
> Thank you all for your valuable inputs and suggestions. Based on the
> consensus, we will move forward with this solution. I'll start working
> on the coding part and share the patch for review by next week.
>

PFA WIP patch, the approach it follows is outlined below:

When a new role is created, the patch identifies all members of the
creator role (the role attempting to create the new role) who have the
admin option enabled on the creator role. If such members are found, a
shared dependency is added between the member role and the creator
role in pg_shdepend. Let me explain this with an example:

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.

To address this, the patch ensures that when a new role is created, it
identifies all members of the creator role who have the admin option.
If such members exist (e.g., u1 in this case), a dependency is
recorded between the member and the creator role (in this case, u1 and
u2). This ensures that when someone attempts to drop a role (e.g.,
u2), the shared dependencies are checked before the role can be
removed. Please take a look at the attached patch and let me know any
comments/feedback/thoughts.

Thanks,

--
With Regards,
Ashutosh Sharma.