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

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Wed, Mar 19, 2025 at 2:32 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Perhaps if we implemented RESTRICT/CASCADE here, that would
>> at least make it harder to fall into this trap?

> I have always assumed that the reason DROP ROLE blah CASCADE is not
> implemented is (1) it would have to cascade to objects in other
> databases which we can't do from an implementation perspective and (2)
> cascading from roles to tables would create a terrifying amount of
> room for user error. One could dismiss (2) if one were brave enough,
> but (1) seems like an irreducible problem. No?

Yeah, I don't care for having it cascade to physical objects either.
But our current behavior is "RESTRICT if there are owned objects or
permissions on objects, but auto-CASCADE to role grants". There's
no implementation reason why we couldn't make RESTRICT/CASCADE work
for role grants, and that'd be at least a smidge closer to what the
spec says. It's not clear to me though if that could help for the
concern at hand.

regards, tom lane