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 1:55 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> I'm especially against making life more difficult for everyone who
>> uses Postgres in order to remove a problem that's only a problem for
>> people who don't have a superuser account available.

> You kind of lost me at this point. I mean, technically I agree that we
> don't want to make life worse for everyone to help people who don't
> have a superuser account available, but I don't see why it's written
> in stone that we should have to make life worse for
> superuser-administered installs in order to make it better for
> non-superuser-administered installs.

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.

BTW, I should note that I was quoting SQL99, because I have that
handy as plain text which is a lot easier to copy-n-paste from
than the PDF form of the later specs. I looked into SQL:2021
and noted that DROP ROLE has grown a RESTRICT/CASCADE option,
which we have not implemented. The RESTRICT form allows (if
I'm reading it right) failure if any indirect privilege grants
would have to be removed. I haven't thought about it hard
enough to be sure if the situation Ashutosh is concerned about
would amount to removal of an indirect grant. In any case,
CASCADE would still require such removal, and flat-out refusing
it would still be a spec violation.

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.

regards, tom lane