Re: Key encryption and relational integrity

Il 26/03/2019 18:08, Adrian Klaver ha scritto:
> 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.
That's what I was afraid of :-(
>
> 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. 

Are you sure there isn't?... the key "sgkighs98" is present on both 
tables and I can join tables on that field, so the pseudonimysation does 
not apply, it's just "separation" (that was OK with the last privacy 
act, but not with GDPR

The problem is not on the application side... there you can do almost 
anything you want to do. The prolem is that if someone breaks in the 
server (data breach) it is easy to join patients and their medications.

> 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.
That's what I used to try to encrypt first name, last name, street 
address and some other fields (that would be the best solution because 
RI was not impacted at all), but the customer stated that they have to 
perform real-time search (like when you type in the Google search box), 
and the query that has to decrypt all names and return only the ones 
that begin with a certain set of characters is way too slow (tried on a 
good i7 configuration, that's about 2 seconds for each key pressed on a  
2500-row table). So I dropped this approach.
>
> *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.
Relax, I'm not here to ask and then sue anyone :-)