RE: Key encryption and relational integrity

-----Original Message-----
From: Moreno Andreo <moreno(dot)andreo(at)evolu-s(dot)it>
Sent: Tuesday, March 26, 2019 11:09 AM
To: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>; PostgreSQL mailing lists <pgsql-general(at)postgresql(dot)org>
Subject: Re: Key encryption and relational integrity

In a master-detail relation, I need to encrypt one of master table PK or detail table FK, in order to achieve pseudonimization, required by GDPR in Europe when managing particular data Imagine I have Table users id surname last name
1 John Doe
2 Jane Doe
3 Foo Bar

Table medications
id user_id med
1 1 Medication
2 1 Ear check
...
...
medications.user_id is FK on users.id
we should achieve

Table medications
id user_id med
1 sgkighs98 Medication
2 sghighs98 Ear check

or the opposite (users.id encryption and medications.user_id kept plain)

At a first glance, it IS breaking relational integrity, so is there a way to manage this encryption internally so RI is kept safe?

===

To me, this is really more of an application question, not a DB question. When you dump or share the data, do your randomization then. If you want to do it in the DB, then add an extra column to your user table, let's call it "pseudonym" and when you insert a new user, fill that in with your randomization string. Then never dump or use the real name, but use the pseudonym column. Better if you can do it, just don't store the real name (though your application may not let you do that).

Honestly, dumping the user as an internal and meaningless number (user_id) seems pretty safe to me, but perhaps your GDPR doesn't allow for that.

HTH,
Kevin
This e-mail transmission, and any documents, files or previous e-mail messages attached to it, may contain confidential information. If you are not the intended recipient, or a person responsible for delivering it to the intended recipient, you are hereby notified that any disclosure, distribution, review, copy or use of any of the information contained in or attached to this message is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately notify us by reply e-mail, and destroy the original transmission and its attachments without reading them or saving them to disk. Thank you.