Re: Role Self-Administration

> On Oct 7, 2021, at 12:31 PM, Mark Dilger  wrote:
> 
> Let me see if I can find that again.


12.6 

 ::=
DROP ROLE 

Syntax Rules
1) Let R be the role identified by the specified .

General Rules
1) Let A be any  identified by a role authorization descriptor as having been granted
to R.
2) The following  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 [ ADMIN OPTION FOR ]  [ {   }... ]
FROM  [ {   }... ]
[ GRANTED BY  ]


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  is a , then
		... SNIP ...
b) If the  is a , 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  of V. The following  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  of T. The following  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