Обсуждение: Enforcing serial uniqueness?

I want to allow access to a table's rows without allowing that table to
be damaged.  A problem I have is with my serial primary key 'id' field.
  Although I can block its UPDATE, if users INSERT with an explicit 'id'
higher than the sequence, future INSERTs will fail due to the values
colliding.  Ditto if users modify the sequence (setval) to be lower than
existing 'id' fields.  This is rather bad, as it prevents
anyone/anything from INSERTing until fixed by manual intervention.

This is probably a common question - how do I fix the above problems?

I'd be happy with them not being able to explicitly set 'id' at all as
I'd rather they also not be able to cause the reuse of a deleted id, but
the 'select nextval, insert, use id' paradigm seems to require it.
Also, if I could block 'setval', that would seem to prevent the latter
problem, but it doesn't seem possible to block 'setval' while still
allowing 'nextval'.

On Tue, Mar 21, 2006 at 11:41:11PM -0800, Steven Brown wrote:
> I want to allow access to a table's rows without allowing that table to
> be damaged.  A problem I have is with my serial primary key 'id' field.
>  Although I can block its UPDATE, if users INSERT with an explicit 'id'
> higher than the sequence, future INSERTs will fail due to the values
> colliding.  Ditto if users modify the sequence (setval) to be lower than
> existing 'id' fields.  This is rather bad, as it prevents
> anyone/anything from INSERTing until fixed by manual intervention.

IIRC you can set the permissions on a sequence to allow nextval but not
setval.

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...

Have a nice dat,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Martijn van Oosterhout wrote:
> On Tue, Mar 21, 2006 at 11:41:11PM -0800, Steven Brown wrote:
>
>>I want to allow access to a table's rows without allowing that table to
>>be damaged.  A problem I have is with my serial primary key 'id' field.
>> Although I can block its UPDATE, if users INSERT with an explicit 'id'
>>higher than the sequence, future INSERTs will fail due to the values
>>colliding.  Ditto if users modify the sequence (setval) to be lower than
>>existing 'id' fields.  This is rather bad, as it prevents
>>anyone/anything from INSERTing until fixed by manual intervention.
>
> IIRC you can set the permissions on a sequence to allow nextval but not
> setval.

Wouldn't it be possible to use nextval as default column value (what
serial types do) and only allow to select currval? I suppose that
depends on 'who' calls nextval when it's the default value; would be
nice if that'd be the sequence owner (more likely the table owner) in
this case.

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

Just shooting some arrows, I may miss the target.

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
    7500 AK Enschede

// Integrate Your World //

> 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.

For example you will never be able to recover a sequence value if the
transaction which got it rolls back... no matter what other gimmicks you
do. And you will always get gaps when you delete some rows, so there's
really not much point in caring at all about the gaps.

I do understand that there are applications where the operators feel
more comfortable memorizing the ids they often use and typing in those
directly instead of choosing from drop-downs... I've been there, seen
that. I could do mostly nothing against that habit, typing is always
faster than choosing from drop-downs. In those cases it makes sense to
provide some special support for making the frequently used IDs small,
or otherwise streamlining the GUI so that choosing is not often needed,
and possibly do it by aliasing so that the aliases are not easily
overlapping when mistyped... in any case, typing in IDs is always error
prone even if it is faster. In the case I dealt with, there was a huge
mess of mistakenly introduced IDs (coming from the inherited application
where the only way to navigate was through typing in IDs), and I ended
up by coding some heuristics for searching for IDs in the DB which could
be a possible mistaken version of some given ID... interesting
experience regarding what are the most common mistypings, but the only
real conclusion was to avoid such stuff in any application I get the
chance to code from scratch.

So the real answer here is: you don't need IDs without gaps, and if you
think you need them, use letter codes instead, and even those so chosen
that mistyping won't get you to another existing code.

Cheers,
Csaba.

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.

I didn't suggest anything of the kind. I was just suggesting a (IMO)
cleaner way to allow access to the sequence, so that the users of the
OP's database don't make too much of a mess of it. As I said, it won't
matter much to the sequence. It's just a matter of wanting to do things
the 'proper' way.

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
    7500 AK Enschede

// Integrate Your World //

> I didn't suggest anything of the kind. I was just suggesting a (IMO)
> cleaner way to allow access to the sequence, so that the users of the
> OP's database don't make too much of a mess of it. As I said, it won't
> matter much to the sequence. It's just a matter of wanting to do things
> the 'proper' way.

OK, my fault not reading the original post, but "sequences" and "no gap"
have some kind of emotional trigger for me cause they remind me the ID
mess I mentioned.

I still didn't read completely the OP, and I don't have time to do it
now, so the following might not apply: I guess the OP should stop using
user accessible sequence IDs anyway, and use letter-code aliases instead
if really needed. Those won't have any sequencing, can be made human
readable and are still easy to type. And the data base WILL become
corrupted sooner or later if the users have direct access to it.

Just my 2c,
Csaba.

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.

Steven Brown schrieb:
> Martijn van Oosterhout wrote:
>
...
>> 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.

Just turn it around (which has the advantage you dont have to
transfer to and fro the database in 2 steps):

INSERT INTO thetable (col1,col2,col3) VALUES ( ... );
SELECT currval('thetable_id_seq');

since your insert above would call nextval() per default,
its save to use currval() in the same transaction.

For sequences (instead of just an int column with default)
there is even a function to find the sequence for that
column.

> 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.

No, you dont mess around with oids.

Regards
Tino

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.

> 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.

No doubt that you will be able to solve your problem by the permission
system somehow, but I wonder how stable a system can be if you allow
users to insert directly to the data base ?

If you would have a set of fixed/approved SQLs which are controlled by
an application (and the application in turn controlled by responsible
developers you trust), and users can only access data through that
application, these kind of problems won't occur at all...

Cheers,
Csaba.

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 -------

Tino Wildenhain wrote:

> since your insert above would call nextval() per default,
> its save to use currval() in the same transaction.

Ah, I didn't realize currval() was handled session-local - that removes
my need to support any non-default value to my serial column.  So, if I
can identify use of a non-default value via a trigger, that solves my
first problem.  I'll try writing such a trigger tomorrow.

That leaves only preventing use of setval() as a problem.  If I revoke
UPDATE, it also blocks the use of nextval() by the default value for the
serial column on INSERT.

Steven Brown <swbrown@ucsd.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

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Steven Brown <swbrown@ucsd.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.)

What if you don't mind someone being able to use up unused values but don't
want someone to be able to rewind the sequence and generate duplicate key
violations breaking the application?

--
greg

Tom Lane wrote:
[...]
> 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.

Wrote this up today and it works great - currval() is still
session-local which I didn't expect would work with the sequence behind
a security definer and updated by trigger.  The only thing missing,
which is quite minor, is I can't detect on INSERT if the caller
attempted to override the default with the default itself (e.g, INSERT
INTO foo(id) VALUES(id's default)), so they won't properly get an
exception in that case.  Not sure if there's a way to catch that.

In case it helps anyone else running into this thread, here's the solution:


-- Create a sequence that your normal users can read but not update.
CREATE SEQUENCE foo_id_seq;
GRANT SELECT ON foo_id_seq TO GROUP (normal user group);

-- Create a table where 'id' will be treated as serial.
CREATE TABLE foo(id integer NOT NULL DEFAULT 0 PRIMARY KEY, something TEXT);
GRANT SELECT, INSERT, UPDATE, DELETE ON foo TO GROUP (normal user group);

-- On INSERT, fill id from the sequence - creator has UPDATE permission.
-- Block attempts to force the id.
CREATE OR REPLACE FUNCTION foo_id_insert_procedure() RETURNS trigger
SECURITY DEFINER AS '
   BEGIN
     IF NEW.id != 0 THEN
       RAISE EXCEPTION ''Setting id to a non-default is not allowed'';
     ELSE
       NEW.id := nextval(''foo_id_seq'');
     END IF;
     RETURN NEW;
   END;
' LANGUAGE plpgsql;

-- Block all UPDATEs to the id.
CREATE OR REPLACE FUNCTION foo_id_update_procedure() RETURNS trigger
SECURITY DEFINER AS '
   BEGIN
     IF NEW.id != OLD.id THEN
       RAISE EXCEPTION ''Setting id to a non-default is not allowed'';
     ELSE
       RETURN NEW;
     END IF;
   END;
' LANGUAGE plpgsql;

CREATE TRIGGER "foo_id_insert_trigger" BEFORE INSERT ON foo
FOR EACH ROW EXECUTE PROCEDURE foo_id_insert_procedure();

CREATE TRIGGER "foo_id_update_trigger" BEFORE UPDATE ON foo
FOR EACH ROW EXECUTE PROCEDURE foo_id_update_procedure();

On Mar 23, 2006, at 3:33 AM, Steven Brown wrote:
> -- On INSERT, fill id from the sequence - creator has UPDATE
> permission.
> -- Block attempts to force the id.
> CREATE OR REPLACE FUNCTION foo_id_insert_procedure() RETURNS
> trigger SECURITY DEFINER AS '
>   BEGIN
>     IF NEW.id != 0 THEN
>       RAISE EXCEPTION ''Setting id to a non-default is not allowed'';
>     ELSE
>       NEW.id := nextval(''foo_id_seq'');

BTW, with some clever use of TG_RELNAME you could probably make that
function generic, so that you could use it with any table; ie:
NEW.id := nextval( TG_RELNAME || ''_id_seq'');

Same holds true for the update function. You might also want to
create a function that does all the legwork of defining the sequence
and triggers for you.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461