On Tue, Mar 26, 2019 at 9:39 AM Kevin Brannen <KBrannen@xxxxxxxx> wrote:
-----Original Message-----
From: Moreno Andreo <moreno.andreo@xxxxxxxxxx>
Sent: Tuesday, March 26, 2019 11:09 AM
To: Adrian Klaver <adrian.klaver@xxxxxxxxxxx>; PostgreSQL mailing lists <pgsql-general@xxxxxxxxxxxxxx>
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).
Agreed, OP could also encrypt the name columns with pgcrypto and keep the key out of the database.
-Michel
