Re: Role Self-Administration

> On Oct 7, 2021, at 10:23 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>
>> And I'm not seeing that it prohibits any of them.
>
> I don't agree that we can decide to have random statements which are
> defined explicitly in the standard to do X end up doing X+Y, simply
> because the standard didn't explicitly say "you can't have Y happen when
> X does".

I agree that a clean design is important, and I wouldn't want to do this if I didn't think it was the cleanest way to go. But I am mindful of the problem you raised upthread about the spec going in some other direction, and ultimately prohibiting what I've proposed, after we've already gone and done it. I'm not as interested in what a bunch of philosophers writing a spec think, but if all the other major SQL databases go that direction and we're off in a different direction, I can certainly see the problems that would entail both for community Postgres and for my employer.

> I hate to think what the standard would look like if it was required
> that every possible thing that could happen when a statement is run had
> to be explicitly listed as "don't have this happen when this command
> runs" except for the few things that the standard defines the statement
> to do.
>
> The argument being presented here would allow us to have INSERTs perform
> CREATE ROLEs, or have DELETEs also TRUNCATE other tables that aren't
> even mentioned in the command, and still claim to be in compliance with
> the standard.

I don't mean to be flippant, but we do allow both of those things to be done with triggers. It's not the same as if we did them automatically, but there seems to be some wiggle room concerning what a system can do.

> Extending the language with new syntax and then deciding how that new
> syntax works is one thing, but taking existing, defined, syntax and
> making it do something other than what the standard is saying does, imv
> anyway, go against the standard. Sure, we've gone against the standard
> at times for good reasons, but I don't agree that this is anywhere close
> to a reasonable case for that.
>
> Let's just invent some new syntax for what you're looking for here that
> works the way you want and doesn't have this issue. As I said before, I
> agree with the general usefulness of this idea, and I can even generally
> get behind the idea of role ownership to allow us to do that, but we
> can't make 'DROP ROLE bob CASCADE;' do it, it needs to be something
> more, like 'DROP ROLE bob CASCADE OBJECTS;' or such.
>
> I really don't understand why there's so much push back to go in that
> direction. Why must 'DROP ROLE bob CASCADE;' drop all of bob's objects
> and roles "owned" by bob?

Because we've already decided how object ownership works. I didn't write any code to have roles get dropped when their owners get dropped. I just put ownership into the system and this is how it naturally works. So you are advocating that DROP...CASCADE works one way for every object type save one. I think that's an incredibly unclean design. Having DROP...CASCADE work the same way for all ownership relations for all object types without exception makes so much more sense to me.

What if we go with what you are saying, the spec never resolves in the direction you are predicting, and all the other database vendors go the way I'm proposing, and we're the only ones with this ugly wart that you have to use a different syntax for roles than for everything else? We'll be supporting that ugly wart for years and years to come, and look ridiculous, and rightly so. I don't want to invent an ugly wart unless I'm completely forced to do so.

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company