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

Role syntax (or, SQL99 versus sanity)

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Stephen Frost <sfrost(at)snowman(dot)net>
Cc: pgsql-hackers(at)postgreSQL(dot)org
Subject: Role syntax (or, SQL99 versus sanity)
Date: 2005-06-28 16:29:22
Message-ID: 21299.1119976162@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
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?

			regards, tom lane

Responses

pgsql-hackers by date

Next:From: Dave CramerDate: 2005-06-28 16:54:49
Subject: Re: Implementing SQL/PSM for PG 8.2 - debugger
Previous:From: Stephen FrostDate: 2005-06-28 16:23:03
Subject: Re: initdb -W failure with role-capable catalogs

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