Steven Brown <swbrown@ucsd.edu> writes:
> Martijn van Oosterhout wrote:
>> IIRC you can set the permissions on a sequence to allow nextval but not
>> setval.
> I've not been able to find a way - granting UPDATE grants the use of
> both.
Yes. This is intentional, on the grounds that being able to do NEXTVAL
enough times is equivalent to SETVAL. (Only strictly true for a CYCLE
sequence, but the point is that if you don't want to allow SETVAL you
shouldn't want to allow unrestricted use of NEXTVAL either.)
I think the solution for you is to use BEFORE triggers as suggested
upthread. The BEFORE INSERT trigger function should be SECURITY DEFINER
and owned by a user who has permission to NEXTVAL the sequence. The id
column should probably be declared plain integer (or bigint), not
SERIAL, because there's no percentage in setting a default that's just
going to be overwritten by the trigger.
regards, tom lane