Re: Role Self-Administration - Mailing list pgsql-hackers
From | Stephen Frost |
---|---|
Subject | Re: Role Self-Administration |
Date | |
Msg-id | 20211008024438.GO20998@tamriel.snowman.net Whole thread Raw |
In response to | Re: Role Self-Administration (Mark Dilger <mark.dilger@enterprisedb.com>) |
Responses |
Re: Role Self-Administration
|
List | pgsql-hackers |
Greetings, * Mark Dilger (mark.dilger@enterprisedb.com) wrote: > > On Oct 7, 2021, at 12:31 PM, Mark Dilger <mark.dilger@enterprisedb.com> wrote: > > > > Let me see if I can find that again. > > 12.6 <drop role statement> > > <drop role statement> ::= > DROP ROLE <role name> > > Syntax Rules > 1) Let R be the role identified by the specified <role name>. > > General Rules > 1) Let A be any <authorization identifier> identified by a role authorization descriptor as having been granted > to R. > 2) The following <revoke role statement> is effectively executed without further Access Rule checking: > REVOKE R FROM A > 3) The descriptor of R is destroyed. > > > So DROP ROLE bob is expected to execute the revoke command. Let's see what that says.... > > <revoke role statement> ::= > REVOKE [ ADMIN OPTION FOR ] <role revoked> [ { <comma> <role revoked> }... ] > FROM <grantee> [ { <comma> <grantee> }... ] > [ GRANTED BY <grantor> ] > <drop behavior> > > 31) If RESTRICT is specified, and there exists an abandoned privilege descriptor, abandoned view, > abandoned table constraint, abandoned assertion, abandoned domain constraint, lost domain, lost column, > lost schema, or a descriptor that includes an impacted data type descriptor, impacted collation, impacted > character set, abandoned user-defined type, or abandoned routine descriptor, then an exception condition > is raised: dependent privilege descriptors still exist. > 33) Case: > a) If the <revoke statement> is a <revoke privilege statement>, then > ... SNIP ... > b) If the <revoke statement> is a <revoke role statement>, then: > i) If CASCADE is specified, then all abandoned role authorization descriptors are destroyed. > ii) All abandoned privilege descriptors are destroyed. > 34) For every abandoned view descriptor V, let S1.VN be the <table name> of V. The following <drop view > statement> is effectively executed without further Access Rule checking: > DROP VIEW S1.VN CASCADE > 35) For every abandoned table descriptor T, let S1.TN be the <table name> of T. The following <drop table > statement> is effectively executed without further Access Rule checking: > DROP TABLE S1.TN CASCADE > > The way I read that, DROP ROLE implies REVOKE ROLE, and I'm inferring that DROP ROLE CASCADE would therefore imply REVOKEROLE CASCADE. Then interpreting 31's description of how REVOKE ROLE RESTRICT works under the principle Expressio UniusEst Exclusio Alterius I conclude that REVOKE ROLE CASCADE must not raise an exception. That leads me to the conclusionthat DROP ROLE CASCADE must not raise an exception. I don't actually think REVOKE ROLE CASCADE must not fail, nor do I see that as explicit in anything you quote above. What also is missing from the quotes above is what actually defines an abandoned object. If you read back through how the spec explains when an object is considered to be 'abandoned', it's more complicated. The gist of it, however, is that if the role loses access rights to a type, for example, and that type is used in a table, then a cascade does remove that table (and various permutations of that for other object types). There isn't any equivilant for roles and it isn't really about 'ownership' but about USAGE rights. In some cases (such as that of a VIEW), while we don't explicitly perform the DROP that the spec calls for, we check the privileges at VIEW access time, making the view not usable if the owner of the view no longer has access to the underlying tables. I do appreciate that this illustrates that you can end up with things being DROP'd, if you explicitly follow the spec, due to a REVOKE CASCADE statement, something which I had argued seemed rather dangerous and counter-intuitive (and still do) but that case isn't quite the same and is something we've also already deviated from- in the direction of avoiding having objects get DROP'd in such cases. > Sorry for misremembering this as something you said. No worries. Thanks, Stephen
Attachment
pgsql-hackers by date: