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@xxxxxxxxxxxxxxxxx
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 //
