Re: Role syntax (or, SQL99 versus sanity)

Tom Lane wrote:

>I'm looking at the problem Stephen Frost noted of not being able to
>duplicate the SQL99-specified syntax for GRANT/REVOKE with roles.
>
>The SQL99 spec has for GRANT (REVOKE has the identical issue):
>
> <grant privilege statement> ::=
> GRANT <privileges>
> TO <grantee> [ { <comma> <grantee> }... ]
> [ WITH HIERARCHY OPTION ]
> [ WITH GRANT OPTION ]
> [ GRANTED BY <grantor> ]
>
> <grant role statement> ::=
> GRANT <role granted> [ { <comma> <role granted> }... ]
> TO <grantee> [ { <comma> <grantee> }... ]
> [ WITH ADMIN OPTION ]
> [ GRANTED BY <grantor> ]
>
>Barring the appearance of one of the OPTION clauses, it is actually
>impossible to tell which kind of statement you are dealing with,
>other than by noticing whether the words appearing between GRANT and TO
>all look like known privilege keywords. The bison conflicts Stephen
>was seeing come from the fact that we treat most of the privilege
>keywords as unreserved words, and so the ambiguity is fatal as far
>as bison is concerned.
>
>The only grammar-level solution I can see is to promote all of the
>following into some category of reserved word:
> INSERT UPDATE USAGE DELETE RULE TRIGGER EXECUTE TEMPORARY TEMP
>which is pretty annoying, even though SQL99 gives us license to do so
>for most of them. (But reserving RULE or TEMP would be contrary to
>spec.)
>
>Alternatively we might consider not distinguishing GRANT PRIVILEGE
>from GRANT ROLE at parse time, but sorting it out later. The most
>extreme form of this would be to actually allow both things in the
>same GRANT:
>
> GRANT INSERT, role1, UPDATE TO joe;
>
>treating WITH GRANT OPTION and WITH ADMIN OPTION as interchangeable
>spellings of the same thing (which they very nearly are anyway).
>
>One objection to this is that misspelling a privilege keyword would
>give you a complaint about "unknown role", which might be a bit
>confusing; but I suspect we cannot avoid that anyway --- there is
>absolutely no basis on which we can say that
>
> GRANT INSIRT TO joe;
>
>isn't a GRANT ROLE operation, until we fail to find the role name.
>(Possibly we could alleviate this by adding a HINT.)
>
>These considerations also suggest that it'd be a good idea to disallow
>the privilege names (select insert etc) as role names.
>
>On the whole, the SQL99 committee should have followed Stephen's
>idea and made the syntax be "GRANT ROLE rolenames" ...
>
>Thoughts anyone?
>
>
>
>

going backwards ...

. getting to SQL99 syntax might be a pain but we should do it, however
unwise they were in choice of syntax.
. excluding named privileges from use as role names seems highly desireable
. thought on resolution
- not sure if we can play games with %prec - I suspect we can't
- could we get there if we declare the named privs as "reserved just
for this purpose"? That would probably imply partitioning the unreserved
keywords list.

cheers

andrew