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

Added a commitfest entry for this here:

https://commitfest.postgresql.org/patch/5608/

--
With Regards,
Ashutosh Sharma.

On Tue, Feb 18, 2025 at 2:54 PM Ashutosh Sharma <ashu(dot)coek88(at)gmail(dot)com> wrote:
>
> Hi Robert,
>
> On Tue, Feb 11, 2025 at 9:48 PM Ashutosh Sharma <ashu(dot)coek88(at)gmail(dot)com> wrote:
> >
> > Hi Robert,
> >
> > On Tue, Feb 4, 2025 at 10:54 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> > >
> > > On Thu, Jan 30, 2025 at 8:45 AM Ashutosh Sharma <ashu(dot)coek88(at)gmail(dot)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.
> >
>
> Attached is a patch that checks for role dependencies when the DROP
> ROLE command is executed. If dependencies are found, the command is
> prevented from succeeding. Please review the attached patch and share
> your feedback. thanks.!
>
> --
> With Regards,
> Ashutosh Sharma.