Re: Role Self-Administration

> On Oct 7, 2021, at 12:31 PM, Mark Dilger <mark(dot)dilger(at)enterprisedb(dot)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 REVOKE ROLE CASCADE. Then interpreting 31's description of how REVOKE ROLE RESTRICT works under the principle Expressio Unius Est Exclusio Alterius I conclude that REVOKE ROLE CASCADE must not raise an exception. That leads me to the conclusion that DROP ROLE CASCADE must not raise an exception.

Sorry for misremembering this as something you said.

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