Re: role self-revocation

Greetings,

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > 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.

We haven't got any particularly special cases in the code today for what
happens if we run up the role hierarchy to a point that it ends and so
I'm not sure why adding in a whole new concept around role ownership,
which doesn't exist in the spec, would somehow leave us with fewer such
special cases.

> +1.
>
> Note that either case would also involve making entries in pg_shdepend;
> although for the case of roles owned by/granted to the bootstrap
> superuser, we could omit those on the usual grounds that we don't need
> to record dependencies on pinned objects.

That we aren't discussing the issues with the current GRANT ... WITH
ADMIN OPTION and how we deviate from what the spec calls for when it
comes to DROP ROLE, which seems to be the largest thing that's
'solved' with this ownership concept, is concerning to me.

If we go down the route of adding role ownership, are we going to
document that we explicitly go against the SQL standard when it comes to
how DROP ROLE works? Or are we going to fix DROP ROLE? I'd much prefer
the latter, but doing so then largely negates the point of this role
ownership concept. I don't see how it makes sense to do both.

Thanks,

Stephen