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

On Wed, Oct 6, 2021 at 3:29 PM Stephen Frost <sfrost@snowman.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.