* Tom Lane ([EMAIL PROTECTED]) wrote: > Stephen Frost <[EMAIL PROTECTED]> 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
signature.asc
Description: Digital signature
