Aller au contenu

14 Sauvegardes anonymes

Transcription française complète du chapitre « Sauvegardes anonymes » du manuel DEV42, révision 26.09, pages PDF 521 à 532.
DEV42

Sauvegardes anonymes

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.

Les outils pg_dump_anon.sh et pg_dump_anon des versions 0.x ou 1.x de postgresql_anonymizer sont dépréciés. Modifiez vos scripts pour utiliser pg_dump.

L’histoire

  • Paul a un site web qui dispose d’une section commentaires où les utilisateurs peuvent partager leurs points de vue.

  • Paul a engagé un prestataire pour développer le nouveau design de son site web.

  • Le prestataire lui demande un export de la base de données.

  • Paul veut « nettoyer » le dump et y retirer toute information personnelle qui pourrait figurer dans la section commentaire.

Comment ça marche ?

Illustration du manuel DEV42

Objectifs

  • Extraire les données anonymisées de la base de données
  • Écrire une fonction de masquage personnalisée pour gérer une colonne de type JSON

Table « website_comment »

\c boutique paul

DROP TABLE IF EXISTS website_comment CASCADE ;

CREATE TABLE website_comment (
  id SERIAL PRIMARY KEY,
  message jsonb
) ;

Quelques données

curl -kLs https://dali.bo/website_comment -o /tmp/website_comment.tsv
head /tmp/website_comment.tsv
    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 "}
\c boutique paul
\copy website_comment from '/tmp/website_comment.tsv'
SELECT jsonb_pretty(message)
FROM website_comment
ORDER BY id ASC
LIMIT 1 ;
           jsonb_pretty
-----------------------------------
 {                                +
     "meta": {                    +
         "name": "Lee Perry",     +
         "ip_addr": "40.87.29.113"+
     },                           +
     "content": "Hello Nasty!"    +
 }

Activer l’extension

CREATE EXTENSION IF NOT EXISTS anon;
SELECT anon.init();

Masquer une colonne de type JSON

Généralement, les données non structurées sont difficiles à masquer…

SELECT message->'meta'->'name' AS name,
       message->'content' AS content
FROM website_comment
ORDER BY id ASC ;

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.

Fonctions de masquage personnalisées

\c boutique paul

CREATE SCHEMA IF NOT EXISTS my_masks;

SECURITY LABEL FOR anon ON SCHEMA my_masks IS 'TRUSTED';
CREATE OR REPLACE FUNCTION my_masks.remove_content(j jsonb)
RETURNS jsonb
AS $func$
  SELECT j - ARRAY['content']
$func$
LANGUAGE sql ;
  • Super-utilisateurs seulement !

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.

Seul un super-utilisateur devrait être capable d’ajouter des fonctions dans ce schéma !

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.

Utilisation de la fonction de masquage personnalisée

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

Sauvegarde anonymisée (1/2)

Créer un rôle dédié pour les exports anonymisés :

CREATE ROLE anon_dumper LOGIN ;

ALTER ROLE anon_dumper
SET anon.transparent_dynamic_masking TO TRUE;

SECURITY LABEL FOR anon ON ROLE anon_dumper IS 'MASKED';

GRANT pg_read_all_data TO anon_dumper;

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

Sauvegarde anonymisée (2/2)

Depuis l’utilisateur système postgres :

pg_dump -U anon_dumper boutique --table=website_comment > /tmp/anon_boutique.sql

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.

Exercices

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

E301 - Exporter les données anonymisées dans une nouvelle base de données

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

E302 - Pseudonymiser les métadonnées du commentaire

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

Solutions

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)';

Pour revenir au chapitre précédent, voir le chapitre précédent. Pour poursuivre la lecture, voir le chapitre suivant.

Développement avancé, Dalibo SCOP · CC BY-NC-SA 2.0 France, sous licence CC BY-NC-SA 2.0 France. Voir attribution.