Re: Enforcing serial uniqueness?

Steven Brown <swbrown(at)ucsd(dot)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