Marko Kreen wrote:
> On 1/7/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > FYI, we could support USAGE just on sequences, and have it map to
> > > UPDATE, but pg_dump it out as USAGE.
> >
> > It seems the spec doesn't cover setval() and currval(), which is not
> > too surprising given those aren't standard.
> >
> > Here is a proposal:
> >
> > SELECT priv -> allows currval() and SELECT * FROM seq
> >
> > USAGE priv -> allows nextval() (required by SQL2003)
> >
> > UPDATE priv -> allows setval() and nextval()
> >
> > I was originally thinking of a separate privilege bit for setval(), but
> > that's sort of silly, as you can get (approximately) the effect of
> > nextval() via setval(). Not much point in prohibiting nextval() to
> > someone who can do setval().
> >
> > This is 100% upward compatible with our current definition, and it meets
> > both the SQL spec and Marko's desire to have a way of granting only
> > nextval() privilege.
>
> Good point about compatibility. But makes the common case ugly.
> "For regular usage you need to grant SELECT, USAGE ..." Huh? :)
>
> How about this:
>
> SELECT: currval
> INSERT: nextval
> UPDATE: nextval, setval
> USAGE: nextval, currval
>
> With this the user needs only to remember SQL2003 syntax
> to cover 99.9% use cases. And when he wants to play more
> finegrained then he can combine with the SELECT, INSERT, UPDATE.
I think we should use Tom's suggestion, for two reasons. First, the
common case currently needs both SELECT and UPDATE, and I have heard no
one complain about it. Second, I think USAGE is better assocated with
nextval() and UPDATE with both nextval() and setval().
> The above table seem bit messy, but I see it as much easier to explain
> to somebody.
I am confused about your list above, so I can't see how that would be
easy to explain.
--
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
