Re: Key encryption and relational integrity

On 3/26/19 9:08 AM, Moreno Andreo wrote:
> Il 26/03/2019 15:24, Adrian Klaver ha scritto:
>> On 3/26/19 7:19 AM, Moreno Andreo wrote:
>>> Hello folks :-)
>>>
>>> Is there any workaround to implement key encryption without breaking
>>> relational integrity?
>>
>> This is going to need more information.
> OK, I'll try to be as clearer as I can
>> For starters 'key' has separate meanings for encryption and RI. I
>> could make some guesses about what you want, but to avoid false
>> assumptions a simple example would be helpful.
> 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?

Not that I know of. RI is based on maintaining a link between parent and
child. So by definition you would be able to get to the parent record
via the child.

A quick search on pseudonymisation found a boatload of interpretations
of how to implement this:

"Pseudonymisation' means the processing of personal data in such a
manner that the personal data can no longer be attributed to a specific
data subject without the use of additional information, provided that
such additional information is kept separately and is subject to
technical and organisational measures to ensure that the personal data
are not attributed to an identified or identifiable natural person."

To me it would seem something like:

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

Table users
id surname last name
sgkighs98 John Doe
jkopkl1 Jane Doe
uepoti21 Foo Bar

Where there is no direct link between the two. Instead permissions would
prevent linking from medications to users even via a SELECT. One could
also use pgcrypto:

https://www.postgresql.org/docs/10/pgcrypto.html

on the users table to further hide the personal info.

*NOTE* I am not a lawyer so any advice on my part as to meeting legal
requirements are just me thinking out loud. I would suggest, if not
already done, getting proper legal advice on what the section quoted
above actually means.

>
> Thanks
>
> Moreno.-
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com