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

Cette licence interdit la réutilisation pour l’apprentissage d’une IA. Elle couvre 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.

Photo d’Erwin Bernal, Montreal, 2015, licence CC BY 2.0

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 à caractère personnel (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) ;
• les vues de masquage : créent 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 aux présentations 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)
• PostgreSQL Anonymizer and the battle for privacy (FOSDEM 2025)

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

Par exemple :

Sous Rocky Linux 8

• Ajouter le dépôt RPM Dalibo Labs

sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql
sudo dnf install -y postgresql17-server
sudo /usr/pgsql-17/bin/postgresql-17-setup initdb
sudo systemctl enable postgresql-17
sudo systemctl start postgresql-17

• Ajout du dépôt RPM DaLibo Labs :

sudo dnf install -y https://yum.dalibo.org/labs/dalibo-labs-4-1.noarch.rpm

• Lancer l’installation :

sudo dnf install -y postgresql_anonymizer_17

Sous Debian / Ubuntu

• L’utilisation des dépôts du PGDG est optionnelle, mais recommandée (pour le choix des versions de PostgreSQL et divers outils) :

sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh -y
sudo apt -y install postgresql-17

• Ajout du dépôt DaLibo Labs DEB :

sudo apt install -y curl lsb-release
echo deb http://apt.dalibo.org/labs $(lsb_release -cs)-dalibo main \
 |sudo tee /etc/apt/sources.list.d/dalibo-labs.list
sudo curl -fsSL -o /etc/apt/trusted.gpg.d/dalibo-labs.gpg \
 https://apt.dalibo.org/labs/debian-dalibo.gpg
sudo apt update

• Lancer :

sudo apt install -y postgresql_anonymizer_17

• Définir un mot de passe pour chaque utilisateur :

\password paul
\password pierre
\password jack

En tant qu’utilisateur système postgres, créer le fichier .pgpass, le compléter et mettre à jour les droits :

touch ~/.pgpass
echo "*:*:boutique:pierre:<mot_de_passe>" >> ~/.pgpass
echo "*:*:boutique:paul:<mot_de_passe>" >> ~/.pgpass
echo "*:*:boutique:jack:<mot_de_passe>" >> ~/.pgpass
chmod 0600 ~/.pgpass

Remplacer par le mot de passe attribué à chaque rôle

Mettre à jour le fichier pg_hba.conf, situé :

• sur RHEL : /var/lib/pgsql/17/data/pg_hba.conf
• sur Debian : /etc/postgresql/17/main/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
local   all             all                                     scram-sha-256

Les autres lignes peuvent être supprimées.

• Charger l’extension :

ALTER DATABASE boutique
  SET session_preload_libraries = 'anon';

La modification du paramètre session_preload_libraries s’applique aux nouvelles sessions, une déconnexion et reconnexion est nécessaire pour que les changements soient visibles.

Recharger la configuration pour valider les mises à jour du paramètre session_preload_libraries et du fichier pg_hba.conf:

SELECT pg_reload_conf();

On peut vérifier que la version installée est la bonne :

SELECT anon.version();

 version 
---------
 2.0.0-beta.1
(1 ligne)

Désormais, sauf précision contraire, toutes les commandes sont à exécuter en tant que paul (le propriétaire).

Le masquage statique est la méthode la plus simple pour 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.

Pour le masquage statique, la déclaration des règles de masquage est à faire avant leur application. Cette déclaration passe par la création de SECURITY LABEL.

Le masquage statique est exécuté par les fonctions anon.anonymize_table() et anon.anonymize_column()

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

Pour cela, il utilise les fonctions dummy_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

Les règles de masquage passent systématiquement par des SECURITY LABEL définis pour le schéma anon.

En cas d’erreur lors de la déclaration d’une fonction de masquage, il n’y a pas d’autre solution que de supprimer et de recréer le label.

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 date_trunc.

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

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

SECURITY LABEL FOR anon ON COLUMN customer.firstname
IS 'MASKED WITH FUNCTION anon.dummy_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

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

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

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

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

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

S104

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

SECURITY LABEL FOR anon ON FUNCTION pg_catalog.date_trunc(text,interval) IS 'TRUSTED';

SECURITY LABEL FOR anon ON COLUMN customer.birth IS
$$ MASKED WITH FUNCTION pg_catalog.date_trunc('year',birth) $$;

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

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

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.

Les rôles masqués peuvent être listés grâce à la requête ci-dessus :

  role  | label  | provider 
--------+--------+----------
 pierre | MASKED | anon

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 avec une jointure entre supplier et company, permettant ainsi 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

Se connecter en tant que Pierre. Écrire une requête avec une jointure entre supplier et company, permettant ainsi de recouper certains fournisseurs en se basant sur leur poste et leur société.

\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

É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. \

\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 que Paul, les données sont inchangées :

\c boutique paul

SELECT * FROM company;

 id  |        name         |     vat_id     
-----+---------------------+----------------
 952 | Shadrach            | FR62684255667
 194 | Johnny's Shoe Store | CHE670945644
 346 | Capitol Records     | GB663829617823

En tant que 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

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

\c boutique paul

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.

postgresql_anonymizer permet de générer des sauvegardes logiques anonymisées avec pg_dump.

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']) AS website_comment
FROM website_comment
WHERE id=1 ;

                      website_comment                       
------------------------------------------------------------
 {"meta": {"name": "Lee Perry", "ip_addr": "40.87.29.113"}}
(1 ligne)

Ici, ARRAY['content'] est un tableau (ARRAY) contenant une seule valeur, content. L’opérateur - est utilisé pour supprimer toutes les occurrences de content du tableau message.

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.

Saisir le mot du passe de l’utilisateur anon_dumper :

\password anon_dumper

En tant qu’utilisateur système postgres, compléter le fichier .pgpass :

echo "*:*:boutique:anon_dumper:<mot_de_passe>" >> ~/.pgpass

Remplacer par le mot de passe au rôle anon_dumper

On utilise l’outil habituel de sauvegarde pg_dump mais avec un rôle masqué.

Il est possible d’obtenir les données au format plain ou binaire.

Créer une base de données nommée boutique_anon. Charger l’extension anon dans la base de données boutique_anon, et 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.

\c boutique paul

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

Créer une base de données nommée boutique_anon. Charger l’extension anon dans la base de données boutique_anon, et y insérer les données anonymisées provenant de la base de données boutique.

dropdb -U paul --if-exists boutique_anon
createdb boutique_anon -U paul --owner paul

psql -U paul -d boutique_anon -c 'SELECT COUNT(*) FROM company'

     count
    -------
         3

Chargement de l’extension :

ALTER DATABASE boutique_anon
SET session_preload_libraries = 'anon';
\c boutique_anon
CREATE EXTENSION IF NOT EXISTS anon ;
SELECT anon.init() ;

Insertion des données anonymisées dans boutique_anon :

pg_dump -U anon_dumper -d boutique | psql --quiet -d boutique_anon

S302

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.

\c boutique_anon paul
CREATE SCHEMA IF NOT EXISTS my_masks ;

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$;

Suite à l’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

É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.

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

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

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

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

Le k-anonymat de cette vue est meilleur :

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

SELECT anon.k_anonymity('v_staff_per_year');

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