On Tue, Mar 26, 2019 at 9:39 AM Kevin Brannen <kbran...@efji.com> wrote:
> -----Original Message----- > From: Moreno Andreo <moreno.and...@evolu-s.it> > Sent: Tuesday, March 26, 2019 11:09 AM > To: Adrian Klaver <adrian.kla...@aklaver.com>; PostgreSQL mailing lists < > pgsql-gene...@postgresql.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). > > Agreed, OP could also encrypt the name columns with pgcrypto and keep the key out of the database. -Michel
