> On Oct 6, 2021, at 9:01 AM, Stephen Frost <sfr...@snowman.net> wrote:
>
> I can see how what you describe as the behavior you'd like to see of
> DROP ROLE ... CASCADE could be useful... However, at least in the
> latest version of the standard that I'm looking at, when a
> DROP ROLE ... CASCADE is executed, what happens for all authorization
> identifiers is:
>
> REVOKE R FROM A DB
>
> Where R is the role being dropped and A is the authoriztaion identifier.
I'm not proposing that all roles with membership in bob be dropped when role
bob is dropped. I'm proposing that all roles *owned by* role bob also be
dropped. Postgres doesn't currently have a concept of roles owning other
roles, but I'm proposing that we add such a concept. Of course, any role with
membership in role bob would no longer have that membership, and any role
managed by bob would not longer be managed by bob. The CASCADE would not
result drop those other roles merely due to membership or management
relationships.
> In other words, the SQL committee seems to disagree with you when it
> comes to what CASCADE on DROP ROLE means (though I can't say I'm too
> surprised- generally speaking, CASCADE is about getting rid of the
> dependency so the system stays consistent, not as a method of object
> management...).
I'm not sure I understand how what they are saying disagrees with what I am
saying, unless they are saying that REVOKE R FROM A DB is the one and only
thing that DROP ROLE .. CASCADE can do. If they are excluding that it do
anything else, then yes, that would be an incompatibility.
As far as keeping the system consistent, I think that's what this does. As
soon as a role is defined as owning other stuff, then dropping the role cascade
means dropping the other stuff.
Could you elaborate more on the difference between object management and
consistency as it applies to this issue?
—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
