Re: Role Self-Administration

> On Oct 7, 2021, at 9:05 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>
>> Hmmph. I think it would be strange if all of the following were true:
>>
>> 1) DROP ROLE bob CASCADE drops all objects owned by bob
>> 2) Roles can own other roles
>> 3) DROP ROLE bob CASCADE never cascades to other roles
>>
>> I'm assuming you see the inconsistency in that set of rules. So, one of them must be wrong. You've just replied that the spec is mute on the subject of #1. Is there any support in the spec for claiming that #2 is wrong?
>
> Pretty sure I mentioned this before, but the spec doesn't seem to really
> say anything about roles owning other roles, so #2 isn't part of the
> spec.

Regulations and specifications are usually thought about as either "permissive" or "prohibitory". Permissive rules allow anything that isn't expressly prohibited. Prohibitive rules prohibit anything that isn't explicitly permitted. I'm taking the SQL spec to be a permissive set of rules.

I'm reasonable enough to concede that even if something is not explicitly prohibited, it is still effectively prohibited if it cannot be done without also doing some other thing that is prohibited.

From your statements, I take it that #2 is allowed, at least if it doesn't necessarily lead to some other violation. So tentatively, I conclude that roles may own other roles.

> #1 also isn't supported by the spec from what I can see.

From that, I tentatively conclude that #1 is allowed, though I am aware that you may argue that it necessarily violates this next thing...

> When the statement is:
>
> DROP ROLE bob;
>
> or
>
> DROP ROLE bob RESTRICT;
>
> then the command "REVOKE bob FROM A RESTRICT;" is supposed to be run BUT
> is supposed to throw an exception if there are "any dependencies on the
> role."

Yeah, I don't think my proposal violates this.

> If the statement is:
>
> DROP ROLE bob CASCADE;
>
> then the command "REVOKE bob FROM A CASCADE;" is run and shouldn't throw
> an exception.

Right, and this will be run. It's just that other stuff, like dropping owned objects, will also be run. I'm not seeing a prohibition here, just a mandate, and the proposal fulfills the mandate.

> I don't think the spec supports any of the three rules you list.

And I'm not seeing that it prohibits any of them.

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