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

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> if I say I want to DROP ROLE b, I'm going to have to first REVOKE c
> FROM b -- there is no real other alternative. So why not make that
> happen automatically? When I say I want to DROP something, I'm
> serious: I really want it gone.

For privileges on ordinary objects, we make you REVOKE them all first
before you can drop the grantee role, but that stems from an
implementation limitation: some of the privileges may be recorded in
the catalogs of other databases that we can't reach. (We can see in
the shared pg_shdepend catalog that some privilege exists in another
DB, but we can't do anything about it.)

For grants on roles, all the moving parts are in the shared
pg_auth_members catalog, so from the implementation standpoint there's
nothing stopping us from auto-dropping such grants. And AFAICS we do.
Certainly this is a bit inconsistent with the behavior for other
kinds of grants, but it's stood that way for awhile. If we were to
try to make this consistent, I think we'd look for a way to auto-drop
privileges on ordinary objects, not start failing DROP ROLE because
privileges on roles exist.

That being the case, I'm against imposing restrictions on DROP ROLE
because of the properties of particular role grants. If you get
into a situation where you need a superuser's help to undo something,
well hopefully you learned better and won't do that again.

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.

Furthermore, there is a standards-compliance argument. The SQL
standard is unambiguous that auto-dropping privileges is what's
supposed to happen:

<drop role statement> ::= DROP ROLE <role name>

Syntax Rules

1) Let R be the role identified by the specified <role name>.

Access Rules

1) At least one of the enabled authorization identifiers shall have
a role authorization identifier that authorizes R with the WITH
ADMIN OPTION.

General Rules

1) Let A be any <authorization identifier> identified by a role
authorization descriptor as having been granted to R.

2) The following <revoke role statement> is effectively executed
without further Access Rule checking:

REVOKE R FROM A

3) The descriptor of R is destroyed.

There is nothing in rule (2) that suggests that implementations are
allowed to fail the DROP if they don't agree with what privileges are
being dropped. (So our behavior for role privileges is spec-compliant
and our behavior for other privileges is not.)

In short, I don't like anything about this proposed patch and
think we should reject it.

regards, tom lane