Re: Open items - Mailing list pgsql-hackers

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > ... We really should also support SET ROLE.  Perhaps if I have
> > time I'll go through the SQL spec looking at the specific requirements
> > of 'Basic Role Support' and 'Extended Role Support' and come up with
> > what we've got, what we're missing, and then we can decide which are
> > features, which are bugfixes, and what we can claim in the docs.
>
> Yes, that'd be a fine thing to do.

Here's the results of this.  I think we're pretty close to having both
"Basic roles" and "Extended roles" personally.  For 'Basic roles' we
need SET ROLE and some information schema tables.  For 'Extended roles'
I think we need '<default option> CURRENT_ROLE' (if this isn't already
taken care of because CURRENT_ROLE is a function?), REVOKE ROLE w/
CASCADE drop behavior.  There were a few other things in 'Extended
roles' that I didn't entirely follow but think we probably meet or would
meet with the above mentioned items...

Here's the complete list.  * = Already supported, ? = Might be
supported, others are to-do items.

Basic roles, Feature T331 * <role name> * CREATE ROLE * GRANT ROLE * DROP ROLE * REVOKE ROLE SET ROLE
INFORMATION_SCHEMA.ADMINISTRABLE_ROLE_AUTHORIZATIONSINFORMATION_SCHEMA.APPLICABLE_ROLES
INFORMATION_SCHEMA.ENABLED_ROLESINFORMATION_SCHEMA.ROLE_COLUMN_GRANTS INFORMATION_SCHEMA.ROLE_ROUTINE_GRANTS
INFORMATION_SCHEMA.ROLE_TABLE_GRANTSINFORMATION_SCHEMA.ROLE_TABLE_METHOD_GRANTS INFORMATION_SCHEMA.ROLE_USAGE_GRANTS
INFORMATION_SCHEMA.ROLE_UDT_GRANTS INFORMATION_SCHEMA.ADMIN_ROLE_AUTHS INFORMATION_SCHEMA.ROLE_ROUT_GRANTS 

Extended roles, Feature T332 (Implies Basic roles) ? <default option> CURRENT_ROLE * CURRENT_ROLE * CREATE ROLE w/
ADMINOPTION * REVOKE ROLE w/ <revoke option extension> GRANT OPTION FOR
(GRANTADMIN FOR?) REVOKE ROLE w/ <drop behavior> CASCADE 
 <revoke statement> containing <privileges> which contain an   <object name> where the owner of the SQL-schema that is
specified explicitly or implicitly in the <object name>   is not the current authorization identifier   (superuser()?) 
 <revoke statement> with privilege descriptor PD which satisfies:   (a) PD identifies the object identified by <object
name>simply       contained in <privileges> contained in the <revoke statement>       (CURRENT_ROLE?)   (b) PD
identifiesthe <grantee> identified by any <grantee> simply       contained in <revoke statement> and that <grantee>
doesnot       identify the owner of the SQL-schema that is specified        explicitly or implicitly in the <object
name>simply contained       in <privileges> contained in the <revoke statement>       (CURRENT_USER?)   (c) PD
identifiesthe action identified by the <action> simply        contained in <privileges> contained in the <revoke
statement>      (<drop bahavior> ?)   (d) PD indicates that the privilege is grantable       (GRANT ADMIN FOR?) 
Thanks,
    Stephen