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

On Fri, Jan 24, 2025 at 09:53:09AM -0500, Andrew Dunstan 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.

+1, if this is doable, I would prefer that over a new predefined role. A
pg_admin_all role might still be useful, but IMHO it's a rather big hammer
for this particular problem.

--
nathan