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

Hi,

On 2025-01-23 20:55:25 +0100, Tomas Vondra wrote:
> If this stopped working in PG16, then how/why did it work in PG15? Is
> that intentional change?

Yes, it was intentional:

Restrict the privileges of CREATEROLE and its ability to modify other roles (Robert Haas)

Previously roles with CREATEROLE privileges could change many
aspects of any non-superuser role. Such changes, including
adding members, now require the role requesting the change to
have ADMIN OPTION permission. For example, they can now change
the CREATEDB, REPLICATION, and BYPASSRLS properties only if they
also have those permissions.

> If we could make the DROP ROLE fail if it causes some other role to
> become orphaned, that'd be a solution too, I think. How difficult is it
> to check that, though?

I don't think it should be too difficult. DropRole() already scans
pg_auth_members to see if the to-be-dropped role has members or is a
member. Doing yet another scan in case we're removing a role membership that
has admin_option set shouldn't be too hard.

One slight difficulty might be that may need to be a bit more aggressive about
locking roles, otherwise two concurrent sessions dropping two different roles
could still result in an orphaned role.

> So it seems to me having a predefined role that allows managing all
> roles (including orphaned ones) might be the good alternative. I
> initially wrote "cleaner", but it feels a bit wrong to allow orphaned
> roles and then have to "fix" this by having this predefined role. Not
> allowing orphaned roles seems cleaner, but it's not a bug either.

Both seem somewhat weird in different ways. Not allowing orphaned roles has
the issue of the order of drop roles determining which role can be dropped and
which can't. An owner-of-last-resort seems somewhat dangerous to have,
e.g. dropping superusers could result in a role with superuser being granted
to the owner-of-last-resort.

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

That's not *really* comparable, because the role membership in question isn't
being dropped, but still.

> FWIW I'm assuming we're not looking for a "fix" for already released
> versions, right?

I suspect that changing it in a minor version would cause trouble for another
set of users...

Greetings,

Andres Freund