Re: Open items

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Stephen Frost <sfrost(at)snowman(dot)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_AUTHORIZATIONS
INFORMATION_SCHEMA.APPLICABLE_ROLES
INFORMATION_SCHEMA.ENABLED_ROLES
INFORMATION_SCHEMA.ROLE_COLUMN_GRANTS
INFORMATION_SCHEMA.ROLE_ROUTINE_GRANTS
INFORMATION_SCHEMA.ROLE_TABLE_GRANTS
INFORMATION_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/ ADMIN OPTION
* REVOKE ROLE w/ <revoke option extension> GRANT OPTION FOR
(GRANT ADMIN 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 identifies the <grantee> identified by any <grantee> simply
contained in <revoke statement> and that <grantee> does not
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 identifies the 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