Re: Enforcing serial uniqueness?

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.  Someone in the interactive docs ran into the same thing it seems:

http://www.postgresql.org/docs/8.0/interactive/sql-grant.html

"On my 8.0.1 installation, the select privilege on a sequence allowed
the granted user to select from the sequence (i.e. the underlying
table), but it did not allow nextval to be called. I had to also grant
update to allow a user to user nextval. A problematic catch to this is
that granting update also allows the granted user to use the setval
function, which I consider a risk in this design."

> Secondly, if you don't want people to be able to stuff with your ID
> column, you could set a BEFORE INSERT trigger to overwrite whatever
> they provide and a BEFORE UPDATE trigger to cancel any changes...

The problem is that to get the last inserted id, as far as I know, you
need to select a value ahead of time via nextval, then insert with it.
If I simply block/change all forced entries, I'll wind up blocking that,
and won't know what id the row I just inserted got.

I believe there's a way to get the last row oid and use that to figure
out what id was used, but I think that would require all clients to be
PostgreSQL-specific, so isn't too useful if you don't control all the
clients.