Vous trouverez en ligne les différentes versions complètes de ce document.

Vous n’avez pas le droit d’utiliser cette création à des fins commerciales.

Si vous modifiez, transformez ou adaptez cette création, vous n’avez le droit de distribuer la création qui en résulte que sous un contrat identique à celui-ci.

Vous devez citer le nom de l’auteur original de la manière indiquée par l’auteur de l’œuvre ou le titulaire des droits qui vous confère cette autorisation (mais pas d’une manière qui suggérerait qu’ils vous soutiennent ou approuvent votre utilisation de l’œuvre). À chaque réutilisation ou distribution de cette création, vous devez faire apparaître clairement au public les conditions contractuelles de sa mise à disposition. La meilleure manière de les indiquer est un lien vers cette page web. Chacune de ces conditions peut être levée si vous obtenez l’autorisation du titulaire des droits sur cette œuvre. Rien dans ce contrat ne diminue ou ne restreint le droit moral de l’auteur ou des auteurs.

Le texte complet de la licence est disponible sur http://creativecommons.org/licenses/by-nc-sa/2.0/fr/legalcode

Cela inclut les diapositives, les manuels eux-mêmes et les travaux pratiques. Cette formation peut également contenir quelques images et schémas dont la redistribution est soumise à des licences différentes qui sont alors précisées.

Sur les versions précédentes susceptibles d’être encore rencontrées en production, seuls quelques points très importants sont évoqués, en plus éventuellement de quelques éléments historiques.

Sauf précision contraire, le système d’exploitation utilisé est Linux.

La boutique de Paul a beaucoup de clients. Paul demande à son ami Pierre, data scientist, des statistiques sur ses clients (âge moyen, etc.).

Pierre demande un accès direct à la base de données pour écrire ses requêtes SQL.

Jack est un employé de Paul, chargé des relations avec les divers fournisseurs de la boutique.

Paul respecte la vie privée de ses fournisseurs. Il doit masquer les informations personnelles à Pierre, mais Jack doit pouvoir lire les vraies données, et y écrire.

Crédits

Cet exemple pratique est un travail collectif de Damien Clochard, Be Hai Tran, Florent Jardin et Frédéric Yhuel.

La version originale en anglais est diffusée sous licence PostgreSQL.

Le présent document en est l’adaptation en français.

Paul’s Boutique est le second album studio du groupe de hip-hop américain les Beastie Boys, sorti le 25 juillet 1989 chez Capitol Records.

La photo ci-dessus est d’Erwin Bernal, sous licence CC BY 2.0.

postgresql_anonymizer est une extension pour masquer ou remplacer des données personnelles (ou PII pour personnally identifiable information) ou toute donnée sensible dans une base de données PostgreSQL.

Le projet a une approche déclarative de l’anonymisation. Vous pouvez déclarer les règles de masquage dans PostgreSQL avec du DDL (Data Definition Language, ou langage de définition des données) et spécifier votre stratégie d’anonymisation dans la définition de la table.

Une fois les règles de masquage définies, vous pouvez accéder aux données anonymisées de quatre manières différentes :

• le masquage statique : supprime les données personnelles en fonction des règles ;
• le masquage dynamique : cache les données personnelles uniquement des utilisateurs masqués ;
• les sauvegardes anonymisées : export des données masquées vers un fichier SQL (sauvegarde logique) ;
• la généralisation : crée des « vues brouillées » des données originales.

Cette présentation n’entrera pas dans le détail du RGPD et des principes généraux d’anonymisation. Pour plus d’informations, référez-vous à la présentation de Damien Clochard ci-dessous :

• Anonymisation, Au-delà du RGPD (vidéo) (PGSession 12, Paris 2019)
• Anonymisation, Au-delà du RGPD (PDF)
• Anonymization, Beyond GDPR (PDF en anglais)

Voir section « Installation » dans la documentation pour savoir comment installer l’extension dans votre instance PostgreSQL.

Par exemple :

Sous Rocky Linux 8

• Les dépôts du PGDG doivent être installés.
• Lancer :

sudo yum install postgresql_anonymizer_14

Par le PGXN

Sur Debian/Ubuntu, les paquets ne sont pas disponibles au moment où ceci est écrit. Le PGXN permet d’installer l’extension (ici en PostgreSQL 14) :

sudo apt install pgxnclient postgresql-server-dev-14
sudo pgxn install postgresql_anonymizer

S’il y a plusieurs versions de PostgreSQL installées, indiquer le répertoire des binaires de la bonne version ainsi :

sudo PATH=/usr/lib/postgresql/14/bin:$PATH   pgxn install postgresql_anonymizer

L’extension sera compilée et installée.

Sauf précision contraire, toutes les commandes sont à exécuter en tant que paul.

Le masquage statique est la manière la plus simple de cacher des données personnelles. L’idée est simplement de détruire les données originales et de les remplacer par des données artificielles.

NB : l’extension pgcrypto sera installée automatiquement. Ses binaires sont généralement livrés avec PostgreSQL.

Paul veut masquer le nom de famille et le numéro de téléphone de ses clients.

Pour cela, il utilise les fonctions fake_last_name() et partial().

Cette fonction ne fait qu’appliquer la règle et doit renvoyer True. Ensuite :

SELECT id, firstname, lastname, phone
FROM customer;

 id  | firstname | lastname |    phone
-----+-----------+----------+--------------
 107 | Sarah     | Okuneva  | 06X-XXX-XX11
 258 | Luke      | Okuneva  |
 341 | Don       | Boyle    | 34X-XXX-XX23

Déclarer une nouvelle règle de masquage et relancer l’anonymisation statique.

Paul réalise que le code postal est un bon indice sur l’endroit où vivent ses clients. Cependant, il voudrait pouvoir faire des statistiques par département.

Créer une règle de masquage pour remplacer les 3 derniers chiffres du code postal par ‘x’.

Agréger les clients selon le code postal anonymisé.

Paul veut des statistiques selon l’âge. Mais il veut aussi masquer les vraies dates de naissance de ses clients.

Remplacer toutes les dates de naissance par le 1er janvier, en conservant l’année réelle. Utiliser la fonction make_date.

Même si un client est correctement anonymisé, il est possible d’isoler un individu grâce à des données d’autres tables. Par exemple, il est possible d’identifier le meilleur client de Paul avec une telle requête :

WITH best_client AS (
    SELECT SUM(amount), fk_customer_id
    FROM payout
    GROUP BY fk_customer_id
    ORDER BY 1 DESC
    LIMIT 1
)
SELECT c.*
FROM customer c
JOIN best_client b ON (c.id = b.fk_customer_id) ;

 id  | firstname | lastname |    phone     |   birth    | postcode
-----+-----------+----------+--------------+------------+----------
 341 | Don       | Boyle    | 34X-XXX-XX23 | 1926-06-01 | 04520

Ce processus est appelé « singularisation » (singling out) d’une personne.

Il faut donc aller plus loin dans l’anonymisation, en supprimant le lien entre une personne et sa société. Dans la table des commandes order, ce lien est matérialisé par une clé étrangère sur le champ fk_company_id. Mais nous ne pouvons supprimer des valeurs de cette colonne ou y insérer de faux identifiants, car cela briserait la contrainte de clé étrangère.

Comment séparer les clients de leurs paiements tout en respectant l’intégrité des données ?

Trouver une fonction qui mélange les valeurs de fk_company_id dans la table payout. Consulter la section shuffling de la documentation.

S101

SECURITY LABEL FOR anon ON COLUMN customer.firstname
IS 'MASKED WITH FUNCTION anon.fake_first_name()' ;

SELECT anon.anonymize_table('customer') ;

La table anonymisée devient :

SELECT id, firstname, lastname
FROM customer ;

 id  | firstname | lastname
-----+-----------+----------
 107 | Hans       | Barton
 258 | Jacqueline | Dare
 341 | Sibyl      | Runte

S102

SECURITY LABEL FOR anon ON COLUMN customer.postcode
IS 'MASKED WITH FUNCTION anon.partial(postcode,2,$$xxx$$,0)' ;

SELECT anon.anonymize_table('customer') ;

Le code postal anonymisé devient :

SELECT id, firstname, lastname, postcode
FROM customer ;

 id  | firstname | lastname | postcode
-----+-----------+----------+----------
 107 | Curt      | O'Hara   | 90xxx
 258 | Agusta    | Towne    | 90xxx
 341 | Sid       | Hane     | 04xxx

Noter que les noms ont encore changé après application de anon.anonymize_table().

S103

SELECT postcode, COUNT(id)
FROM customer
GROUP BY postcode;

 postcode | count
----------+-------
 90xxx    |     2
 04xxx    |     1

S104

SECURITY LABEL FOR anon ON COLUMN customer.birth
IS 'MASKED WITH FUNCTION make_date(EXTRACT(YEAR FROM birth)::INT,1,1)';

SELECT anon.anonymize_table('customer');

Les dates de naissance anonymisées deviennent :

SELECT id, firstname, lastname, birth
FROM customer ;

 id  | firstname | lastname |   birth
-----+-----------+----------+------------
 107 | Pinkie    | Sporer   | 1965-01-01
 258 | Zebulon   | Gerlach  | 1951-01-01
 341 | Erna      | Emmerich | 1926-01-01

S105

Pour mélanger les valeurs de fk_customer_id :

SELECT anon.shuffle_column('payout','fk_customer_id','id') ;

Si l’on essaie à nouveau d’identifier le meilleur client :

WITH best_client AS (
    SELECT SUM(amount), fk_customer_id
    FROM payout
    GROUP BY fk_customer_id
    ORDER BY 1 DESC
    LIMIT 1
)
SELECT c.*
FROM customer c
JOIN best_client b ON (c.id = b.fk_customer_id) ;

 id  | firstname | lastname | phone |   birth    | postcode
-----+-----------+----------+-------+------------+----------
 258 | Zebulon   | Gerlach  |       | 1951-01-01 | 90xxx

Par exemple, si un client A a deux paiements, l’un se retrouvera associé à un client B, et l’autre à un client C. En d’autres termes, cette méthode de mélange respectera la contrainte d’intégrité technique, mais brisera l’intégrité des données. Pour certaines utilisations, ce peut être problématique.

Ici, Pierre ne pourra pas produire de rapport avec les données mélangées.

Avec le masquage dynamique, le propriétaire de la base peut masquer les données personnelles à certains utilisateurs, tout en laissant aux autres les droits de lire et modifier les données réelles.

Le rôle pierre devient « masqué », dans le sens où le super-utilisateur va pouvoir lui imposer un masque qui va changer sa vision des données.

En tant que Pierre, on essaie de lire la table des fournisseurs :

\c boutique pierre
SELECT * FROM supplier;

 id  | fk_company_id |    contact     |    phone    |   job_title
-----+---------------+----------------+-------------+---------------
 299 |           194 | Johnny Ryall   | 597-500-569 | CEO
 157 |           346 | George Clinton | 131-002-530 | Sales manager

Pour le moment, il n’y a pas de règle de masquage : Pierre peut voir les données originales dans chaque table.

Pierre essaie de lire la table des fournisseurs :

\c boutique pierre
SELECT * FROM supplier ;

 id  | fk_company_id |   contact    |    phone    |   job_title
-----+---------------+--------------+-------------+---------------
 299 |           194 | CONFIDENTIAL | 597-500-569 | CEO
 157 |           346 | CONFIDENTIAL | 131-002-530 | Sales manager

Si Jack essaie de lire les vraies données, ce sont encore les bonnes :

\c boutique jack
SELECT * FROM supplier;

 id  | fk_company_id |    contact     |    phone    |   job_title
-----+---------------+----------------+-------------+---------------
 299 |           194 | Johnny Ryall   | 597-500-569 | CEO
 157 |           346 | George Clinton | 131-002-530 | Sales manager

Masquer le nom du fournisseur n’est pas suffisant pour anonymiser les données.

Se connecter en tant que Pierre. Écrire une requête simple permettant de recouper certains fournisseurs en se basant sur leur poste et leur société.

Les noms des sociétés et les postes de travail sont disponibles dans de nombreux jeux de données publics. Une simple recherche sur LinkedIn ou Google révèle les noms des principaux dirigeants de la plupart des sociétés…

Nous devons donc anonymiser aussi la table company. Même si elle ne contient pas d’informations personnelles, certains champs peuvent être utilisés pour identifier certains de leurs employés…

Écrire deux règles de masquage pour la table company. La première doit remplacer le champ nom avec un faux nom. La seconde remplacer vat_id avec une suite aléatoire de dix caractères. NB : dans la documentation, consulter les générateurs de données factices et fonctions aléatoires (faking functions).

Vérifier que Pierre ne peut pas voir les vraies données sur la société.

À cause du masquage dynamique, les valeurs artificielles sont différentes à chaque fois que Pierre lit la table. Ce n’est pas toujours très pratique.

Pierre préfère appliquer tout le temps les mêmes valeurs artificielles pour une même société. Cela correspond à la « pseudonymisation ».

Écrire une nouvelle règle de masquage à partir du champ name, grâce à une fonction de pseudonymisation.

S201

\c boutique pierre
SELECT s.id, s.contact, s.job_title, c.name
FROM supplier s
JOIN company c ON s.fk_company_id = c.id ;

 id  |   contact    |   job_title   |        name
-----+--------------+---------------+---------------------
 299 | CONFIDENTIAL | CEO           | Johnny's Shoe Store
 157 | CONFIDENTIAL | Sales manager | Capitol Records

S202

\c boutique paul
SECURITY LABEL FOR anon ON COLUMN company.name
IS 'MASKED WITH FUNCTION anon.fake_company()';

SECURITY LABEL FOR anon ON COLUMN company.vat_id
IS 'MASKED WITH FUNCTION anon.random_string(10)';

En tant Pierre, relire la table :

\c boutique pierre
SELECT * FROM company;

 id  |            name             |   vat_id
-----+-----------------------------+------------
 952 | Graham, Davis and Bauer     | LYFVSI3WT5
 194 | Martinez-Smith              | 9N62K8M6JD
 346 | James, Rodriguez and Nelson | OHB20OZ4Q3

À chaque lecture de la table, Pierre voit des données différentes :

SELECT * FROM company;

 id  |            name             |   vat_id
-----+-----------------------------+------------
 952 | Holt, Moreno and Richardson | KPAJP2Q4PK
 194 | Castillo Group              | NVGHZ1K50Z
 346 | Mccarthy-Davis              | GS3AHXBQTK

S203

\c boutique paul
ALTER FUNCTION anon.pseudo_company SECURITY DEFINER;

SECURITY LABEL FOR  anon ON COLUMN company.name
IS 'MASKED WITH FUNCTION anon.pseudo_company(id)';

Pour Pierre, les valeurs pseudonymisées resteront identiques entre deux appels (mais pas le code TVA) :

\c boutique pierre
SELECT * FROM company;

 id  |      name       |   vat_id
-----+-----------------+------------
 952 | Wilkinson LLC   | IKL88GJVT4
 194 | Johnson PLC     | VOOJ6UKR6H
 346 | Young-Carpenter | DUR78F15VD

SELECT * FROM company;

 id  |      name       |   vat_id
-----+-----------------+------------
 952 | Wilkinson LLC   | DIUAMTI653
 194 | Johnson PLC     | UND2DQGL4S
 346 | Young-Carpenter | X6EOT023AK

Dans beaucoup de situations, le besoin est simplement d’exporter les données anonymisées pour les importer dans une autre base de données, pour mener des tests ou produire des statistiques. C’est ce que permet de faire l’outil pg_dump_anon.

La colonne comment contient beaucoup d’informations personnelles. Le fait de ne pas utiliser un schéma standard pour les commentaires rend ici la tâche plus complexe.

Comme on peut le voir, les visiteurs du site peuvent écrire toutes sortes d’informations dans la section « commentaire ». La meilleure option serait donc de supprimer entièrement la clé JSON car il est impossible d’y exclure les données sensibles.

Il est possible de nettoyer la colonne comment en supprimant la clé content :

SELECT message - ARRAY['content']
FROM website_comment
WHERE id=1 ;

Créer en premier lieu un schéma dédié, ici my_masks, et le déclarer en trusted (« de confiance »). Cela signifie que l’extension anon va considérer les fonctions de ce schéma comme des fonctions de masquage valides.

Cette fonction de masquage se contente de supprimer du JSON le champ avec le message :

CREATE OR REPLACE FUNCTION my_masks.remove_content(j jsonb)
RETURNS jsonb
AS $func$
  SELECT j - ARRAY['content']
$func$
LANGUAGE sql ;

Exécuter la fonction :

SELECT my_masks.remove_content(message)
FROM website_comment ;

 {"meta": {"name": "Lee Perry", "ip_addr": "40.87.29.113"}}
 {"meta": {"name": "", "email": "biz@bizmarkie.com"}}
 {"meta": {"name": "Jimmy"}}

La fonction va pouvoir ensuite être utilisée dans une règle de masquage.

Enfin, une sauvegarde logique anonymisée de la table peut être exportée avec l’utilitaire pg_dump_anon. Celui-ci est un script, livré avec l’extension, disponible dans le répertoire des binaires :

L’outil utilise pg_dump, il vaut mieux qu’il n’y ait pas d’ambiguïté sur le chemin :

export PATH=$PATH:$(pg_config --bindir)
pg_dump_anon.sh --help

export PATH=$PATH:$(pg_config --bindir)
export PGHOST=localhost
export PGUSER=paul
pg_dump_anon.sh boutique --table=website_comment > /tmp/dump.sql

En ne demandant que les données (option -a), le résultat contient notamment :

COPY public.website_comment (id, message) FROM stdin;
1       {"meta": {"name": "Lee Perry", "ip_addr": "40.87.29.113"}, "content": "Hello Nasty!"}
2       {"meta": {"name": "", "email": "biz@bizmarkie.com"}, "content": "Great Shop"}
3       {"meta": {"name": "Jimmy"}, "content": "Hi ! This is me, Jimmy James "}
\.

Le produit est en développement actif et la situation peut avoir changé depuis que ces lignes ont été écrites. Il est notamment prévu que le script bash soit remplacé par un script en go.

Créer une base de données nommée boutique_anon. y insérer les données anonymisées provenant de la base de données boutique.

Pierre compte extraire des informations générales depuis les métadonnées. Par exemple, il souhaite calculer le nombre de visiteurs uniques sur la base des adresses IP des visiteurs, mais une adresse IP est un identifiant indirect.

Paul doit donc anonymiser cette colonne tout en conservant la possibilité de faire le lien entre les enregistrements provenant de la même adresse.

Remplacer la fonction remove_content par la fonction clean_comment (ci-dessous), qui : - supprime la clé JSON content ; - remplace la valeur dans la colonne name par un faux nom ; - remplace l’adresse IP dans la colonne ip_address par sa somme de contrôle md5 ; - met à NULL la clé email.

CREATE OR REPLACE FUNCTION my_masks.clean_comment(message jsonb)
RETURNS jsonb
VOLATILE
LANGUAGE SQL
AS $func$
SELECT
  jsonb_set(
    message,
    ARRAY['meta'],
    jsonb_build_object(
        'name',anon.fake_last_name(),
        'ip_address', md5((message->'meta'->'ip_addr')::TEXT),
        'email', NULL
    )
  ) - ARRAY['content'];
$func$;

S301

export PATH=$PATH:$(pg_config --bindir)
export PGHOST=localhost
export PGUSER=paul
dropdb --if-exists boutique_anon
createdb boutique_anon --owner paul
pg_dump_anon.sh boutique | psql --quiet boutique_anon

export PGHOST=localhost
export PGUSER=paul
psql boutique_anon -c 'SELECT COUNT(*) FROM company'

     count
    -------
         3

S302

Suite à utilisation de la fonction personnalisée clean_comment, les données n’ont plus rien à voir :

SELECT my_masks.clean_comment(message)
FROM website_comment;

                                         clean_comment
-----------------------------------------------------------------------------------------------
 {"meta": {"name": "Heller", "email": null, "ip_address": "1d8cbcdef988d55982af1536922ddcd1"}}
 {"meta": {"name": "Christiansen", "email": null, "ip_address": null}}
 {"meta": {"name": "Frami", "email": null, "ip_address": null}}
(3 lignes)

On applique le masquage comme à l’habitude :

SECURITY LABEL FOR anon ON COLUMN website_comment.message
IS 'MASKED WITH FUNCTION my_masks.clean_comment(message)';

L’idée derrière la généralisation est de pouvoir flouter une donnée originale.

Par exemple, au lieu de dire « Monsieur X est né le 25 juillet 1989 », on peut dire « Monsieur X est né dans les années 1980 ». L’information reste vraie, bien que moins précise, et elle rend plus difficile l’identification de la personne.

Bien sûr, stocker les caractéristiques physiques d’employés est généralement illégal. Quoi qu’il en soit, il sera impératif de les masquer.

Ce fichier charge 16 lignes, dont :

SELECT full_name,first_day, hair, size, asthma
FROM employee
LIMIT 3 ;

  full_name   | first_day  | hair  | size | asthma
--------------+------------+-------+------+--------
 Luna Dickens | 2018-07-22 | blond | L    | t
 Paul Wolf    | 2020-01-15 | bald  | M    | f
 Rowan Hoeger | 2018-12-01 | dark  | XXL  | t

Paul souhaite savoir s’il y a une corrélation entre l’asthme et la couleur des yeux.

Il fournit à Pierre la vue ci-dessus, qui peut désormais écrire des requêtes sur cette vue :

SELECT *
FROM v_asthma_eyes
LIMIT 3;

 eyes  | asthma
-------+--------
 blue  | t
 brown | f
 blue  | t

SELECT
  eyes,
  100*COUNT(1) FILTER (WHERE asthma) / COUNT(1) AS asthma_rate
FROM v_asthma_eyes
GROUP BY eyes ;

 eyes  | asthma_rate
-------+-------------
 green |         100
 brown |          37
 blue  |          33

Paul vient de prouver que l’asthme est favorisé par les yeux verts, et surtout de trouver une corrélation entre deux champs.

SELECT anon.k_anonymity('v_asthma_eyes');

 k_anonymity
-------------
           2

La vue v_asthma_eyes a le niveau « 2-anonymity ». Cela signifie que chaque combinaison de quasi-identifiants (eyes-asthma) apparaît au moins 2 fois dans le jeu de données.

En d’autres termes, cela veut dire qu’un individu ne peut pas être distingué d’au moins un autre individu (k-1) dans cette vue.

Pour les détails sur le K-anonymat, voir cet article sur Wikipédia.

\c boutique pierre
SELECT *
FROM v_staff_per_month
LIMIT 3;

        first_day        |        last_day
-------------------------+-------------------------
 [2018-07-01,2018-08-01) | [2018-12-01,2019-01-01)
 [2020-01-01,2020-02-01) | (,)
 [2018-12-01,2019-01-01) | [2018-12-01,2019-01-01)

Pierre peut écrire une requête pour trouver le nombre d’employés embauchés en novembre 2021 :

SELECT COUNT(1)
       FILTER (
            WHERE make_date(2019,11,1)
            BETWEEN lower(first_day)
            AND COALESCE(upper(last_day),now())
       )
FROM v_staff_per_month ;

 count
-------
     4

Dans ce cas, le résultat est 1, ce qui veut dire qu’au moins une personne peut être directement identifiée par les dates de ses premier et dernier jour en poste.

Dans ce cas, la généralisation est insuffisante.

Généraliser les dates en mois n’est pas suffisant. > Écrire une autre vue > v_staff_per_year qui va généraliser les dates en années. > Simplifier également la vue en utilisant un intervalle de int pour stocker > l’année, plutôt qu’un intervalle de date.

Combien de personnes ont travaillé pour Paul chaque année entre 2018 et 2021 ?

Quel est le facteur k-anonymat de la vue v_staff_per_year ?

S401

Cette vue généralise les dates en années :

\c boutique paul
DROP MATERIALIZED VIEW IF EXISTS v_staff_per_year;
CREATE MATERIALIZED VIEW v_staff_per_year AS
SELECT
  int4range(
    extract(year from first_day)::INT,
    extract(year from last_day)::INT,
    '[]' -- include upper bound
  ) AS period
FROM employee;

SELECT *
FROM v_staff_per_year
LIMIT 3;

   period
-------------
 [2018,2019)
 [2020,)
 [2018,2019)

S402

Les personnes ayant travaillé pour Paul entre 2018 et 2021 sont :

SELECT
  year,
  COUNT(1) FILTER (
      WHERE year <@ period
  )
FROM
    generate_series(2018,2021) year,
    v_staff_per_year
GROUP BY year
ORDER BY year ASC;

 year | count
------+-------
 2018 |     4
 2019 |     6
 2020 |     9
 2021 |    10

S403

Le k-anonymat de cette vue est meilleur :

SECURITY LABEL FOR anon ON COLUMN v_staff_per_year.period
IS 'INDIRECT IDENTIFIER';

SELECT anon.k_anonymity('v_staff_per_year');

 k_anonymity
-------------
           2