4

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?

Improve this question
3
  • 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. Commented Dec 20, 2015 at 4: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. Commented Dec 20, 2015 at 4: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. Commented Dec 20, 2015 at 4:43

3 Answers 3

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

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. This doesn't invalidate the answer it seems.
Where does it say in the docs gen_random_uuid() is cryptographically random? It just says Returns a version 4 (random) UUID.
shouldn't we use gen_random_bytes(count integer) instead?
1

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 !!!

Improve this answer

Comments

1

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.