Primary key with randomly varying increments (so it cannot be guessed easily)

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.

As your goal is for:


the end users to not able to guess the primary keys by simply incrementing numbers.

then rather than just a variable increment of your keys (so they have to search a few tens or hundreds of values), why not go for something completely nonlinear?

There's a function in the PostgreSQL wiki that should suit your needs, pseudo_encrypt.

It takes a sequential input and returns a pseudo-random non-repeating output.

Instead of your usual SERIAL PRIMARY KEY, which expands to:

CREATE SEQUENCE mytable_colname_seq;

CREATE TABLE mytable (
    colname integer PRIMARY KEY DEFAULT nextval('tablename_colname_seq'),
    ...

);

ALTER SEQUENCE mytable_colname_seq OWNED BY mytable;

you can instead manually make the sequence and assign ownership and define the column with a DEFAULT that calls the function over the sequence, e.g.:

DEFAULT pseudo_encrypt(nextval('tablename_colname_id_seq'));

There's an important caveat here, though. This technique won't work without modification for tables that have existing data, because the pseudo_encrypt function might return a value that already exists in the table. It guarantees not to repeat its self, but knows nothing about anything already in the table. To copy with that, you could create a variant of pseudo_encrypt that took a second argument, the lowest ID it's allowed to return. If it calculated a lower ID, it'd just repeat the calculation with the next value from the sequence, discarding that ID.

StackExchange Database Administrators Q#71702, answer score: 4