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

On Wed, Mar 19, 2025 at 2:32 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I didn't assert that that's a general problem. I meant that this
> particular patch makes life worse, by causing DROP ROLE to fail
> unexpectedly.

I think that's only true of this particular version of the patch. I
believe it's likely resolvable somehow. As I see it, we're still
debating what the exact design should be.

> Perhaps if we implemented RESTRICT/CASCADE here, that would
> at least make it harder to fall into this trap? In the spec,
> that's simply passed on to the implied REVOKE commands, and
> we do already support REVOKE ... RESTRICT/CASCADE, so maybe
> that's not a hugely difficult patch.

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?

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