Re: Role Self-Administration

Greetings,

On Wed, Oct 6, 2021 at 16:28 Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Wed, Oct 6, 2021 at 3:29 PM Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> > 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.
>
> Well, I think what we're talking about is saying something like:
>
> CREATE USER mybigcustomer CREATEROLE;
>
> And then having the mybigcustomer role be able to create other roles,
> which would be automatically dropped if I later said:
>
> DROP USER mybigcustomer CASCADE;
>
> Since AFAIK CREATEROLE is not in the specification, I think we're
> perfectly free to say that it alters the behavior of the subsequent
> DROP USER command in any way that we judge reasonable. I agree that we
> need to have SQL-standard syntax do SQL-standard things, but it
> doesn't have to be the case that the whole command goes unmentioned by
> the specification. Options that we add to CREATE USER or CREATE TABLE
> or any other command can modify the behavior of those objects, and the
> spec has nothing to say about it.
>
> Now that doesn't intrinsically mean that it's a good idea. I think
> what I hear you saying is that you find it pretty terrifying that
> "DROP USER mybigcustomer CASCADE;" could blow away a lot of users and
> a lot of tables and that could be scary. And I agree, but that's a
> design question, not a spec question. Today, there is not, in
> PostgreSQL, a DROP USER .. CASCADE variant. If there are objects that
> depend on the user, DROP USER fails. So we could for example decide
> that DROP USER .. CASCADE will cascade to other users, but not to
> regular objects. Or maybe that's too inconsistent, and we should do
> something like DROP ROLES OWNED BY [role]. Or maybe having both DROP
> OWNED BY and DROP ROLES OWNED BY is too weird, and the existing DROP
> OWNED BY [role] command should also cascade to roles. Those kinds of
> things seem worth discussing to me, to come up with the behavior that
> will work best for people. But I do disagree with the idea that we're
> not free to innovate here. We make up new SQL syntax and new
> configuration variables and all kinds of new things all the time, and
> I don't think this is any different.

This specific syntax, including the CASCADE bit, has, at minimum, at least
been contemplate by the SQL folks sufficiently to be described in one
specific way. I don’t have a copy of 2016 handy, unfortunately, and so I’m
not sure if it’s described that way in a “stable” version of the standard
or not (it isn’t defined in the 2006 draft I’ve seen), but ultimately I
don’t think we are really talking about entirely net-new syntax here…

If we were, that would be different and perhaps we would just be guessing
at what the standard might do in the future, but I don’t think it’s an open
ended question at this point..

(Even if it was, I have to say that the direction that they’re going in
certainly seems consistent to me, anyway, with what’s been done in the past
and I think it’d be bad of us to go in a different direction from that
since it’d be difficult for us to change it later when the new spec comes
out and contradicts what we decided to do..)

Thanks,

Stephen

>