Re: Role Self-Administration - Mailing 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