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

Question

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.

Answer 1

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.

Answer 2

I suggest a function taking a regclass parameter that runs ALTER 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:

increment

The clause INCREMENT BY increment is optional. A positive value will make an ascending sequence, a negative one a descending sequence. If unspecified, the old increment value will be maintained.

However:

You must own the sequence to use ALTER SEQUENCE.

So we need to take care of privileges. You could make the function SECURITY DEFINER and owned by a superuser. If you don't REVOKE privileges from public 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 make randseq own the function (still with SECURITY DEFINER).

• To allow for selected roles only, REVOKE all privileges on the function from public and GRANT EXECUTE to said roles. You might use a group role to simplify privilege management.

Or combine both:

CREATE OR REPLACE FUNCTION nextval_rand(regclass)
  RETURNS int AS
$func$
BEGIN
EXECUTE format('ALTER SEQUENCE %s INCREMENT %s'
               , $1                          -- regclass automatically sanitized
               , (random() * 100)::int + 1); -- values between 1 and 100
RETURN nextval($1)::int;
END
$func$ LANGUAGE plpgsql SECURITY DEFINER;

-- to restrict usage:
ALTER FUNCTION nextval_rand(regclass) OWNER TO randseq;
REVOKE ALL ON FUNCTION nextval_rand(regclass) FROM public;
GRANT EXECUTE ON FUNCTION nextval_rand(regclass) TO randseq;
GRANT randseq TO ???;

Call:

SELECT nextval_rand('tbl_tbl_id_seq'::regclass);

All you have to do now is replace nextval() with nextval_rand() in the column default of any serial column. And possibly change the owner of the sequence.

ALTER SEQUENCE tbl_tbl_id_seq OWNER TO randseq;
ALTER TABLE tbl ALTER COLUMN tbl_id SET DEFAULT nextval_rand('tbl_tbl_id_seq'::regclass);

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 of ALTER SEQUENCE behavior.

There is a very slim chance for a race condition, where two concurrent operations each run ALTER SEQUENCE before calling nextval(). 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 is regclass, only valid sequence names can be passed and are automatically schema-qualified and escaped unambiguously.

Answer 3

Craig Ringer mentioned an important caveat -- for tables that have existing data, the pseudo_encrypt function might return a value that already exists in the table. Here is an example that can easily be modified to avoid the problem:

The example is for a MAX value, you would want a MIN value greater than the biggest existing integer ID:

CREATE FUNCTION bounded_pseudo_encrypt(VALUE int, MIN int) returns int AS $$
BEGIN
  LOOP
    VALUE := pseudo_encrypt_24(VALUE);
    EXIT WHEN VALUE >= MIN;
  END LOOP;
  RETURN VALUE;
END
$$ LANGUAGE plpgsql strict immutable;

Rather than passing a parameter, one can hard code a value bigger than your biggest existing integer ID.

CREATE FUNCTION bounded_pseudo_encrypt(VALUE int) returns int AS $$
BEGIN
  LOOP
    VALUE := pseudo_encrypt_24(VALUE);
    EXIT WHEN VALUE >= <hard_coded_integer>;
  END LOOP;
  RETURN VALUE;
END
$$ LANGUAGE plpgsql strict immutable;

Answer 4

I figured a little bit easier solution to the problem, and even though the question was already answered long ago, may be it can help someone in the future.

Assuming that we have a sequence that generates the ids, we can manipulate the current value of that sequence via a function, that takes in a regclass and 2 integers:

CREATE OR REPLACE FUNCTION nextval_random_between(
    sequence_name regclass,
    lower_bound bigint,
    higher_bound bigint)
  RETURNS bigint AS
$BODY$
SELECT setval(sequence_name, nextval(sequence_name::regclass) + (floor(random()*(higher_bound - lower_bound)+lower_bound-1)::bigint)); -- 
$BODY$
  LANGUAGE sql VOLATILE STRICT
  COST 4;

This function has very low cost, since it is written in sql, and it doesn't open a new transaction every function call. Later on you can use these function to generate a random number between arbitrary boundaries.

In order to let the function generate the ids for you, just run:

ALTER TABLE my_table ALTER COLUMN my_table_random_ids SET DEFAULT nextval_random_between(my_table_random_ids_seq::regclass, 1::bigint, 100::bigint)