Re: role self-revocation

On Fri, Mar 11, 2022 at 10:46 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> The bootstrap superuser clearly must be a special case in some way.
> I'm not convinced that that means there should be other special
> cases. Maybe there is a use-case for other "unowned" roles, but in
> exactly what way would that be different from deeming such roles
> to be owned by the bootstrap superuser?

I think that just boils down to how many useless catalog entries you
want to make.

If we implement the link between the creating role and the created
role as role ownership, then we are surely just going to add a
rolowner column to pg_authid, and when the role is owned by nobody, I
think we should always just store a valid OID in it, rather than
sometimes storing 0. It just seems simpler. Any time we would store 0,
store the bootstrap superuser's pg_authid.oid value instead. That way
the OID is always valid, which probably lets us get by with fewer
special cases in the code.

If we implement the link between the creating role and the created
role as an automatically-granted WITH ADMIN OPTION, then we could
choose to put (CREATOR_OID, CREATED_OID, whatever, TRUE) into
pg_auth_members for the creating superuser or, indeed, every superuser
in the system. Or we can leave it out. The result will be exactly the
same. Here, I would favor leaving it out, because extra catalog
entries that don't do anything are usually a thing that we do not
want. See a49d081235997c67e8aab7a523b17e8d1cb93184, for example.

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