Skip site navigation (1) Skip section navigation (2)

Re: Role syntax (or, SQL99 versus sanity)

From: Andrew Dunstan <andrew(at)dunslane(dot)net>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Stephen Frost <sfrost(at)snowman(dot)net>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Role syntax (or, SQL99 versus sanity)
Date: 2005-06-28 18:38:14
Message-ID: (view raw or whole thread)
Lists: pgsql-hackers

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:
>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
>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:
>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
>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.



In response to

pgsql-hackers by date

Next:From: Stephen FrostDate: 2005-06-28 18:45:06
Subject: Re: [PATCHES] Users/Groups -> Roles
Previous:From: Andrew DunstanDate: 2005-06-28 18:28:11
Subject: Re: Problem with dblink regression test

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group