I would like the primary keys to be auto-incremented and generated but in varying increments. For ex, if I have increment range as 100… then the auto generated keys would be something like below:
– 20 (random number between 1 and 100)
– 30 (add random number 10 that's between 1 and 100)
– 113 (add random number 83 that's between 1 and 100)
– 118 (add random number 5 that's between 1 and 100)
– 217 (add random number 99 that's between 1 and 100)
– 220 (add random number 3 that's between 1 and 100)
The data domains are often exposed via HTTP REST endpoints, and I would like the end users to not able to guess the primary keys by simply incrementing numbers.
I'm trying to avoid UUID/GUIDs if possible. My REST URLs are longer, often with parent-child identifiers. (Yes, it's avoidable but I prefer it for simpler testing/troubleshooting). So I prefer numbers as identifiers.
Is there any simpler solution that I'm not aware of? I'm using PostgreSQL but any generic solution is also fine.
Best Answer
I suggest a function taking a
regclass
parameter that runsALTER SEQUENCE
with a new randomly generated increment before it returns the next value from a given sequence.Can be used as drop-in replacement for
nextval()
.Per documentation on
ALTER SEQUENCE
:However:
So we need to take care of privileges. You could make the function
SECURITY DEFINER
and owned by a superuser. If you don'tREVOKE
privileges frompublic
it works for anyone on any sequence. There are two basic strategies to restrict usage:To allow for selected sequences only, change the owner of those sequences to some dedicated role, say
randseq
and makerandseq
own the function (still withSECURITY DEFINER
).To allow for selected roles only,
REVOKE
all privileges on the function frompublic
andGRANT EXECUTE
to said roles. You might use a group role to simplify privilege management.Or combine both:
Call:
All you have to do now is replace
nextval()
withnextval_rand()
in the column default of any serial column. And possibly change the owner of the sequence.Notes
ALTER SEQUENCE
is designed not to block concurrent transactions. It takes effect immediately and cannot be rolled back. It should work reliably in a multi-user environment. Read the Notes section of the manual page for the fine print ofALTER SEQUENCE
behavior.There is a very slim chance for a race condition, where two concurrent operations each run
ALTER SEQUENCE
before callingnextval()
. Since we are operating with random numbers anyway, this really doesn't matter.Since we are running dynamic SQL I would normally
SET search_path = public, pg_temp
for the function. But since the parameter isregclass
, only valid sequence names can be passed and are automatically schema-qualified and escaped unambiguously.