Re: Enforcing serial uniqueness?

From: Steven Brown <swbrown(at)ucsd(dot)edu>
To: kleptog(at)svana(dot)org, pgsql-general(at)postgresql(dot)org
Subject: Re: Enforcing serial uniqueness?
Date: 2006-03-22 13:04:32
Message-ID: 44214B60.608@ucsd.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andrus 2006-03-22 13:24:46 Re: How to release locks
Previous Message Csaba Nagy 2006-03-22 12:38:31 Re: Enforcing serial uniqueness?