Aller au contenu

16 Réplica Anonymisé

Transcription française complète du chapitre « Réplica Anonymisé » du manuel DEV42, révision 26.09, pages PDF 547 à 566.
DEV42

Réplica Anonymisé

Anonymisation et Réplication Logique

  • Réplica toujours à jour, toujours anonymisé
  • Utilise la réplication logique de PostgreSQL
  • Nouveauté depuis la version 3.0.0

L’histoire

  • Pierre a besoin d’un accès permanent aux données de la boutique
  • Paul refuse un accès direct à la production
  • Solution : un réplica dédié, anonymisé

Comment ça marche ?

Illustration du manuel DEV42

Objectifs

  • Configurer la publication côté source
  • Configurer le réplica anonymisé
  • Vérifier l’anonymisation en temps réel

Prérequis : réplication logique

wal_level = logical

Puis redémarrer l’instance.

La réplication logique nécessite wal_level = logical. Sur une instance fraîche ce paramètre est à replica ; il faut le monter d’un cran.

Un redémarrage de l’instance est nécessaire :

sudo systemctl restart postgresql-18

Vérifier :

SHOW wal_level;

Côté publication : rôle de réplication

\c boutique paul

CREATE ROLE anon_replicator LOGIN REPLICATION;

GRANT USAGE  ON SCHEMA public TO anon_replicator;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO anon_replicator;

Définir un mot de passe pour ce rôle :

\password anon_replicator

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

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

Mettre à jour le fichier pg_hba.conf de l’instance source pour autoriser la connexion en réplication depuis l’instance réplica :

# pg_hba.conf de l'instance source
host  boutique  anon_replicator  127.0.0.1/32  scram-sha-256
host  boutique  anon_replicator  ::1/128       scram-sha-256
SELECT pg_reload_conf();

Côté publication : créer la publication

\c boutique paul

CREATE PUBLICATION pub_boutique
  FOR TABLE company, supplier;

On publie uniquement les tables que Pierre doit pouvoir consulter. Les tables customer et payout ne sont pas publiées ici.

Vérifier la publication :

SELECT * FROM pg_publication;
SELECT * FROM pg_publication_tables;

Côté réplica : nouvelle instance

Créer une seconde instance sur le port 5433 :

# RHEL
sudo -u postgres /usr/pgsql-18/bin/initdb -D /var/lib/pgsql/18/replica
sudo sed -i "s/^#port = 5432/port = 5433/" \
  /var/lib/pgsql/18/replica/postgresql.conf
sudo -u postgres /usr/pgsql-18/bin/pg_ctl start \
  -D /var/lib/pgsql/18/replica

Côté réplica : créer la base

createuser -p 5433 -s paul
createuser -p 5433 pierre
createdb -p 5433 -O paul boutique_replica

Charger l’extension anon via shared_preload_libraries :

echo "shared_preload_libraries = 'anon'" \
  >> /var/lib/pgsql/18/replica/postgresql.conf
sudo -u postgres /usr/pgsql-18/bin/pg_ctl restart \
  -D /var/lib/pgsql/18/replica

L’extension doit passer par shared_preload_libraries (et non session_preload_libraries) car les workers de réplication logique ne chargent pas ces derniers : sans ce réglage, les règles de masquage ne s’appliqueraient pas aux données entrantes.

Côté réplica : activer le masquage

-- connexion à l'instance réplica
\c "port=5433 dbname=boutique_replica user=paul"

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

ALTER DATABASE boutique_replica
  SET anon.replica_masking TO on;

Se reconnecter, puis accorder les droits de lecture à Pierre :

\c "port=5433 dbname=boutique_replica user=paul"
GRANT USAGE ON SCHEMA public TO pierre;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO pierre;

Le paramètre anon.replica_masking doit être activé avant la création de la souscription. Il indique à l’extension d’intercepter les données entrantes pour y appliquer les règles de masquage. La reconnexion est nécessaire pour que la session prenne en compte la nouvelle valeur du paramètre.

Pierre dispose ainsi d’un accès en lecture seule aux données anonymisées, sans jamais accéder à la base de production.

Côté réplica : créer les tables

Les tables doivent exister avant la souscription :

CREATE TABLE company (
    id SERIAL PRIMARY KEY,
    name TEXT,
    vat_id TEXT UNIQUE
);

CREATE TABLE supplier (
    id SERIAL PRIMARY KEY,
    fk_company_id INT REFERENCES company(id),
    contact TEXT,
    phone TEXT,
    job_title TEXT
);

Côté réplica : règles de masquage

Les mêmes règles que pour le masquage dynamique :

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

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

SECURITY LABEL FOR anon ON COLUMN supplier.contact
  IS 'MASKED WITH VALUE $$CONFIDENTIAL$$';

Les règles de masquage sont déclarées sur le réplica, pas sur la source. C’est le réplica qui applique l’anonymisation au moment de la réception des données.

La pseudonymisation de company.name garantit que Pierre verra toujours le même nom fictif pour une même société, ce qui lui permet de faire des jointures cohérentes entre company et supplier.

SECURITY LABEL FOR anon ON COLUMN supplier.phone
  IS 'MASKED WITH FUNCTION anon.partial(phone,0,$$XX-XXX-XXX$$,0)';

Côté réplica : activer et souscrire

\c "port=5433 dbname=boutique_replica user=paul"

SELECT anon.start_replica_masking();

CREATE SUBSCRIPTION sub_boutique
  CONNECTION 'host=localhost port=5432
              user=anon_replicator
              password=CHANGEME
              dbname=boutique'
  PUBLICATION pub_boutique;

anon.start_replica_masking() installe le trigger logique qui intercepte les données entrantes.

Le mot de passe dans la chaîne de connexion peut être remplacé par une entrée dans le fichier .pgpass de l’utilisateur système qui exécute l’instance réplica.

Vérifier l’état de la souscription :

SELECT * FROM pg_stat_subscription;
 subname       | pid  | received_lsn | last_msg_receipt_time | latest_end_lsn
---------------+------+--------------+-----------------------+----------------
 sub_boutique  | 1234 | 0/1A2B3C4D   | 2025-03-24 ...        | 0/1A2B3C4D

Vérification

Sur le réplica, les données sont anonymisées, y compris pour Paul :

\c "port=5433 dbname=boutique_replica user=paul"
SELECT * FROM company;
 id  |      name       |   vat_id
-----+-----------------+------------
 952 | Wilkinson LLC   | IKL88GJVT4
 194 | Johnson PLC     | VOOJ6UKR6H
 346 | Young-Carpenter | DUR78F15VD

Contrairement au masquage dynamique, tous les utilisateurs voient les données anonymisées sur le réplica, car les données originales n’y sont jamais stockées.

\c boutique paul
SELECT * FROM company;
 id  |        name         |     vat_id
-----+---------------------+----------------
 952 | Shadrach            | FR62684255667
 194 | Johnny's Shoe Store | CHE670945644
 346 | Capitol Records     | GB663829617823

Attention aux clés primaires

  • La réplication logique identifie les lignes par leur clé primaire
  • Ne pas masquer les colonnes de la clé primaire !
  • Sinon les UPDATE et DELETE ne se propagent plus

Les clés primaires techniques (séquences, UUID) ne sont généralement pas des données personnelles et n’ont pas besoin d’être masquées.

Exercices

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

E501 - Créer une nouvelle instance

  • Vérifier que wal_level = logical est actif sur l’instance source.
  • Créer une seconde instance PostgreSQL sur le port 5433.
  • Créer la base boutique_replica sur cette instance.
  • Installer et initialiser l’extension anon dans boutique_replica.

E502 - Configurer le réplica anonymisé

  • Sur l’instance source, créer le rôle anon_replicator et la publication pour les tables company et supplier.
  • Sur le réplica, recréer le schéma des tables company et supplier.
  • Déclarer les règles de masquage sur le réplica (reprendre celles du masquage dynamique).
  • Activer le replica masking, lancer anon.start_replica_masking() et créer la souscription.

E503 - Vérifier la réplication anonymisée

  • Sur le réplica, vérifier que les données de company et supplier sont anonymisées.
  • Sur la source, insérer une nouvelle société et un nouveau fournisseur.
  • Sur le réplica, vérifier que les nouvelles données apparaissent et sont anonymisées.

Solutions

S501

-- Sur l'instance source
SHOW wal_level;
 wal_level
-----------
 logical

Si la valeur est replica, modifier postgresql.conf et redémarrer.

Créer l’instance réplica :

sudo -u postgres /usr/pgsql-18/bin/initdb -D /var/lib/pgsql/18/replica
sudo sed -i "s/^#port = 5432/port = 5433/" \
  /var/lib/pgsql/18/replica/postgresql.conf
sudo -u postgres /usr/pgsql-18/bin/pg_ctl start \
  -D /var/lib/pgsql/18/replica

Créer les rôles et la base :

createuser -p 5433 -s paul
createuser -p 5433 pierre
createdb -p 5433 -O paul boutique_replica

Installer l’extension (via shared_preload_libraries, nécessaire pour les workers de réplication logique) :

echo "shared_preload_libraries = 'anon'" \
  >> /var/lib/pgsql/18/replica/postgresql.conf
pg_ctl restart -D /var/lib/pgsql/18/replica

psql -p 5433 -d boutique_replica <<EOF
CREATE EXTENSION IF NOT EXISTS anon;
SELECT anon.init();
EOF

S502

Sur la source (port 5432) :

\c boutique paul

CREATE ROLE anon_replicator LOGIN REPLICATION;
\password anon_replicator

GRANT USAGE ON SCHEMA public TO anon_replicator;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO anon_replicator;

CREATE PUBLICATION pub_boutique FOR TABLE company, supplier;

Sur le réplica (port 5433) :

\c "port=5433 dbname=boutique_replica user=paul"

CREATE TABLE company (
    id SERIAL PRIMARY KEY,
    name TEXT,
    vat_id TEXT UNIQUE
);

CREATE TABLE supplier (
    id SERIAL PRIMARY KEY,
    fk_company_id INT REFERENCES company(id),
    contact TEXT,
    phone TEXT,
    job_title TEXT
);

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

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

SECURITY LABEL FOR anon ON COLUMN supplier.contact
  IS 'MASKED WITH VALUE $$CONFIDENTIAL$$';

ALTER DATABASE boutique_replica
  SET anon.replica_masking TO on;

GRANT USAGE ON SCHEMA public TO pierre;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO pierre;

Se reconnecter puis activer et souscrire :

\c "port=5433 dbname=boutique_replica user=paul"

SELECT anon.start_replica_masking();

CREATE SUBSCRIPTION sub_boutique
  CONNECTION 'host=localhost port=5432
              user=anon_replicator
              password=CHANGEME
              dbname=boutique'
  PUBLICATION pub_boutique;

S503

Vérifier les données anonymisées sur le réplica :

\c "port=5433 dbname=boutique_replica user=paul"

SELECT * FROM company;
 id  |      name       |   vat_id
-----+-----------------+------------
 952 | Wilkinson LLC   | IKL88GJVT4
 194 | Johnson PLC     | VOOJ6UKR6H
 346 | Young-Carpenter | DUR78F15VD
SELECT s.contact, s.job_title, c.name
FROM supplier s
JOIN company c ON s.fk_company_id = c.id;
   contact    |   job_title   |      name
--------------+---------------+-----------------
 CONFIDENTIAL | CEO           | Johnson PLC
 CONFIDENTIAL | Sales manager | Young-Carpenter

Ajouter des données sur la source :

\c boutique paul

INSERT INTO company VALUES (501, 'Acme Corp', 'US123456789');
INSERT INTO supplier VALUES (42, 501, 'Wile E. Coyote', '555-0100', 'Buyer');

Vérifier la propagation sur le réplica :

\c "port=5433 dbname=boutique_replica user=paul"

SELECT s.contact, s.job_title, c.name
FROM supplier s
JOIN company c ON s.fk_company_id = c.id
WHERE s.id = 42;
   contact    | job_title |     name
--------------+-----------+--------------
 CONFIDENTIAL | Buyer     | Martinez Ltd

La donnée a été répliquée et anonymisée automatiquement.

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.