Re: [HACKERS] Inconsistent syntax in GRANT

On 1/7/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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.

The above table seem bit messy, but I see it as much easier to explain
to somebody.

> BTW, what about lastval()? I'm not sure we can usefully associate any
> privilege check with that, since it's not clear which sequence it
> applies to. Does it make sense to remember what sequence the value came
> from and privilege-check against that, or is that just too weird?

Hmm. So it means with lastval() user can see the state of sequences
he has no access to? Seems like the privilege check would be good
idea.

--
marko