Re: Enforcing serial uniqueness?

create a view with insert/update/delete rules and DON'T let the users assign to the serial col. remove
insert/upload/delete permission to the base table and only allow access via the view.

---------- Original Message -----------
From: Steven Brown <swbrown(at)ucsd(dot)edu>
To: Csaba Nagy <nagy(at)ecircle-ag(dot)com>
Cc: Alban Hertroys <alban(at)magproductions(dot)nl>, Martijn van Oosterhout <kleptog(at)svana(dot)org>, Postgres general mailing list
<pgsql-general(at)postgresql(dot)org>
Sent: Wed, 22 Mar 2006 05:44:06 -0800
Subject: Re: [GENERAL] Enforcing serial uniqueness?

> Csaba Nagy wrote:
> >> That way they really can't touch the sequence; otherwise they still
> >> could call nextval multiple times erroneously (people do that...). It
> >> doesn't matter much to the sequence, of course... It just leaves the
> >> ugly gaps out :P
> >
> > The sequence facility was NOT designed with no-gap sequencing in mind,
> > but with good parallel performance in mind.
>
> Gaps are fine. All I want is safe uniqueness. What is an issue for me
> is a user having INSERT permission being able to shut down all INSERTs
> from everyone else until someone manually figures out what happened and
> fixes it, ditto for UPDATE permission on a sequence (which they need in
> order to use nextval so they know what id the row they inserted will
> have, right?), which seems extremely dangerous to me.
>
> For example, forcing a value above the sequence position:
>
> CREATE TABLE foo(id SERIAL PRIMARY KEY);
> -- Forcing a value above the sequence position,
> INSERT INTO foo(id) VALUES(1);
> -- Causes future INSERT failures for everyone:
> INSERT INTO foo DEFAULT VALUES;
>
> If cache=1, possibly using a trigger on id to check that the next value
> of the sequence will be greater than it would solve this if there's not
> some reason that's unsafe/unworkable - e.g., is the sequence's position
> guaranteed to have been updated before a BEFORE trigger (needed if
> nextval is the default as in serial columns), and will the default taken
> be available to a BEFORE?
>
> And the other example:
>
> CREATE TABLE foo(id SERIAL PRIMARY KEY);
> INSERT INTO foo DEFAULT VALUES;
> -- User with UPDATE for foo_id_seq can call setval as well as nextval,
> SELECT setval('foo_id_seq', 1, false);
> -- Causing future INSERT failures for everyone:
> INSERT INTO foo DEFAULT VALUES;
>
> I'm not sure how to solve this given UPDATE permission on sequences is
> for both nextval and setval. If I could block/restrict setval somehow
> that would fix this.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
------- End of Original Message -------