Re: Key encryption and relational integrity

On Tue, Mar 26, 2019 at 9:39 AM Kevin Brannen <KBrannen(at)efji(dot)com> wrote:

> -----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).
>
>
Agreed, OP could also encrypt the name columns with pgcrypto and keep the
key out of the database.

-Michel