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@ucsd.edu>
To: Csaba Nagy <nagy@ecircle-ag.com>
Cc: Alban Hertroys <alban@magproductions.nl>, Martijn van Oosterhout <kleptog@svana.org>, Postgres general mailing list
<pgsql-general@postgresql.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 -------