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?

Answer 1

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.

Answer 2

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

• Advantages and Disadvantages of UUID and

• whats-your-opinion-on-using-uuids-as-database-row-identifiers-particularly-in

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

• pgcrypto generator function

• uuid-ossp generator functions

Hope this help you !!!