Cryptographically random primary key in PostgreSQL

Question

I am trying to solve a problem where I need to generate a non-sequential and cryptographically random primary key as each record is inserted into a table.

The reason for this is each record is generated from an email list, but those records should not be able to be linked back to those email addresses (secret ballot situation). If someone managed to access the email list, they could derive from the insertion order who supplied the data for each record.

Is it possible to do this without generating an id of some kind in the application code, and be done purely in PostgreSQL? If not, what is the next best solution?

Hash the email with a cryptographic hash, you can parametrize it further by using HMAC. You can almost certainly twist postgres into doing this for you but it's much simpler at the application level. — pvg, Dec 20 '15 at 04:37
If the server key is compromised, and the email list is found, the id will be derivable, so that's not a good option. — Brendan Molloy, Dec 20 '15 at 04:39
Throw away the key? Pull numbers from your system's csrng? The threat model is not really clear for instance, why would someone be able to match email addresses to 'insertion order', how would they know what it is, etc. — pvg, Dec 20 '15 at 04:43

Brendan Molloy · Accepted Answer · 2016-01-02T00:53:54.470

3

It seems that the best choice is to use pgcrypto and do the following:

CREATE EXTENSION pgcrypto;

CREATE TABLE whatever (
  id       uuid PRIMARY KEY DEFAULT gen_random_uuid()
)

The PostgreSQL 9.4 documentation on pgcrypto states that gen_random_uuid() generates a cryptographically random V4 UUID, which suits this situation perfectly.

edited Jan 02 '16 at 00:53

answered Dec 20 '15 at 04:53

Brendan Molloy

1,784
14
22

2

Note that the random UID is of course not completely random. 6 bits are used to indicate that the UID is indeed random, the other bits are random but they are then encoded into the UID scheme. More information [here](http://security.stackexchange.com/a/93905/2651). This doesn't invalidate the answer it seems. – Maarten Bodewes Dec 20 '15 at 13:30
2

Where does it say in the docs gen_random_uuid() is cryptographically random? It just says _Returns a version 4 (random) UUID_. – RedShift Jun 20 '21 at 07:24

score 1 · Answer 2 · edited May 23 '17 at 12:04

Its good practice to Go with uuid as you can check out this link as well.

Note: You need to enable the pgcrypto (only PostgreSQL >= 9.4) or uuid-ossp extension to generate random UUIDs..

Hope this help you !!!

score 1 · Answer 3 · answered Dec 21 '15 at 15:18

Another option is to use encryption to generate the unique row keys. Simply encrypt the numbers 0, 1, 2, 3, ... in turn using a block cypher. Provided you always use the same cypher key, the outputs are guaranteed unique because the inputs are unique. For 64 bit row keys use 3DES, for 128 bit row keys use AES. For other sizes use Hasty Pudding cypher, which can work with effectively any block size you want.

Cryptographically random primary key in PostgreSQL

3 Answers3