Re: Role Self-Administration

On Wed, Oct 6, 2021 at 2:48 PM Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> What I'm saying above is that the command explicitly listed there
> *isn't* 'DROP ROLE A DB', even though that is something which the spec
> *could* have done, had they wished to. Given that they didn't, it seems
> very clear that making such a change would very much be a deviation and
> violation of the spec. That we invented some behind-the-scenes concept
> of role ownership where we track who actually created what role and then
> use that info to transform a REVOKE into a DROP doesn't make such a
> transformation OK.

If PostgreSQL implements extensions to the SQL specification, then we
get to decide how those features interact with the features that are
specified.

For example, I presume the spec doesn't say that you can drop a
function by dropping the extension that contains it, but that's just
because extensions as we have them in PostgreSQL are not part of the
SQL standard. It would be silly to have rejected that feature on those
grounds, because nobody is forced to use extensions, and if you don't,
then they do not cause any deviation from spec-mandated behavior.

In the same way, nobody would be forced to make a role own another
role, and if you don't, then you'll never notice any deviation from
spec-mandated behavior on account of that feature.

If the SQL specification says that roles can own other roles, but that
DROP has to have some special behavior in regards to that feature,
then we should probably try to do what the spec says. But if the spec
doesn't think that the concept of roles owning other roles even
exists, but we choose to invent such a concept, then I think we can
make it work however we like without worrying about
spec-compatibility. We've already invented lots of other things like
that, and the project is the better for it.

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