Re: [HACKERS] Inconsistent syntax in GRANT

Marko Kreen wrote:
> On 1/6/06, Bruce Momjian <pgman@candle.pha.pa.us> wrote:
> > Marko Kreen wrote:
> > > I found SQL2003 pdf's too ... from my reading it has only USAGE.
> > >
> > > 5WD-02-Foundation-2003-09.pdf:
> > > page 724 -> General Rules -> #2
> > > page 740 -> Syntax rules -> #3
> >
> > I admit I am terrible at understanding the standard, but I can't find
> > anything relevant on the page numbers you mentioned.  Are those the
> > document pages or the page numbers displayed by the PDF viewer?  What is
> > the section heading?  I am using the same filename you have.
>
> Those are print page numbers.  (In case you have dead-tree variant :)
> And I got them here: http://www.wiscorp.com/SQLStandards.html
>
> Uh, and they are bit wrong.  Ok here are they fully:
>
> 11.62 <sequence generator definition>
>   General rules (page 727 printed/751 real) point #2
>
> 12.3 <privileges>
>   Syntax rules (page 740 printed/764 real) point #3

OK, I see it now, and in an earlier email I quoted the part where I
think USAGE links in to nextval().  I was looking for something obvious. :-)

> > > Everything combined:
> > > SELECT: currval
> > > UPDATE: nextval
> > > USAGE: currval, nextval
> > > ALTER: setval
> > >
> > > Confusing?
> >
> > I see USAGE in the standard, but not ALTER.  We don't support USAGE so I
> > am guessing our SELECT/UPDATE behavior is OK.
>
> No, we still want to separate setval from nextval.

My point was that currval -> SELECT and nextval -> UPDATE was correct.
I see now that I am wrong and that the standard wants USAGE.

That combined was every db's behavior combined, right?  I got confused.

> > Does this mean we should
> > only allow owners to do setval(), rather than binding it to INSERT?
>
> My first reaction is that it should be grantable, although
> I can't find any reasons for it, except backwards compatibility.
>
> How about this:
>
> SELECT: currval
> INSERT: nextval
> USAGE: currval, nextval
> UPDATE: setval

I think nextval() is naturally UPDATE.  I am thinking setval would be
INSERT, and with setval() being used less, it would perhaps be a better
choice for a change anyway.

However, in doing the pg_dump part of the patch, I perhaps see a
problem.  If someone does:

    GRANT UPDATE ON seq1 TO PUBLIC;

do we give them nextval() and setval() permissions?  If they do:

    GRANT UPDATE ON SEQUENCE seq1 TO PUBLIC;
                    --------

they only set nextval()?  That seems quite confusing.  Can we change
UPDATE for both GRANT syntaxes, and somehow have people fix them up
after they load in 8.2?  How many non-owners do setval()?

FYI, we could support USAGE just on sequences, and have it map to
UPDATE, but pg_dump it out as USAGE.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073