Re: Role Self-Administration - Mailing list pgsql-hackers

Greetings,

* Robert Haas (robertmhaas@gmail.com) wrote:
> On Wed, Oct 6, 2021 at 2:48 PM Stephen Frost <sfrost@snowman.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.

Does that mean that we also get to change what a specific set of
commands, which are all well-defined in the standard, do even when that
goes against what an SQL compliant implementation would do?  I really
don't think so.  If this was *new* syntax to go along with some new
feature or extension in PG, sure, we can define what that syntax does
because the standard doesn't.  In this case we're talking entirely about
objects and statements which the standard does define.

> 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.

The prior example that I used didn't include *any* non-SQL standard
statements, so I don't view this argument as applicable.

> 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.

So you're suggesting that roles created by other roles wouldn't
*automatically* by owned by the creating role and that, instead, someone
would have to explicitly say something like:

ALTER ROLE x OWNED BY y;

after the role is created, and only then would a DROP ROLE y CASCADE;
turn into DROP ROLE x CASCADE; DROP ROLE y CASCADE; and that, absent
that happening, a DROP ROLE y CASCADE; would do what the standard says,
and not actually DROP all the associated objects but only run the REVOKE
statements?

I'll accept that, in such a case, we could argue that we're no longer
following the spec because the user has started to use some PG extension
to the spec, but, I've got a really hard time seeing how such a massive
difference in what DROP ROLE x CASCADE; does would be acceptable or at
all reasonable.

One could lead to hundreds of tables being dropped out of the database
and a massive outage while the other would just mean some role
memberships get cleaned up as part of a role being dropped.  Having one
command that does two vastly different things like that is a massive,
loaded, foot-pointed gun.

> 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.

The SQL spec doesn't say that roles can own other roles.  I don't think
that means we get to rewrite what DROP ROLE ... CASCADE does.  Extend
DROP ROLE with other parameters which are relevant to our extension of
the spec?  Sure, perhaps, but not entirely redefine what the base
command does to be different from what the SQL spec says it does.

Thanks,

Stephen