Le 25 septembre dernier, PostgreSQL sortait sa toute dernière version, PostgreSQL 18.
Cette version apporte de nombreux correctifs mais aussi des nouveautés et des améliorations sur les performances globales.

Une nouveauté est particulièrement pertinente pour cette version. Il s’agit des “asynchronous IO” (AIO).

Présentation

Une lecture en mode synchrone se fait lorsque PostgreSQL demande au kernel l’accès à une page disque. PostgreSQL attend alors sa mise à disposition pour être traitée et montée en mémoire partagée.
L’OS écoute la requête de la part de PostgreSQL et ne peut anticiper les éventuelles demandes.
Dans ce contexte, PostgreSQL est très dépendant des performances disques avec d’éventuels “bottlenecks”.

Avec le mode asynchrone IO, l’OS est capable de traiter en parallèle les demandes faites par PostgreSQL et peut donc facilement travailler sur plusieurs requêtes en lecture.

Synchronous IO                                                                                                            Asynchronous IO

Un premier travail avait été fait avec les versions récentes de PostgreSQL afin de faire du “prefetch” de données via la méthode système “posix_fadvise“, ceci permettait d’anticiper les déclarations d’accès à certaines données ou pages de fichiers en déclarant un offset de départ et une longueur défini. Ceci évite des allers-retours sur le même fichier traitant des pages contiguës.
Malheureusement, avec PostgreSQL, ce mécanisme ne permet pas de monter les pages dans le “shared_buffer“, mais s’appuie sur le cache disque.

Avec les asynchronous IO, PostgreSQL est particulièrement performant lors des lectures séquentielles (seq scans, bitmap heap scan).
Un parallèle peut être fait avec le mécanisme d’offloading fait par Oracle Exadata et les “smart scans” montés directement vers la PGA.

Paramétrage

Avec la version 18, PostgreSQL met l’accent sur les lectures asynchrones avec de nouveaux paramètres.

io_method qui peut prendre 3 valeurs différentes

• sync : exécute les IO en mode synchrone comme pour les versions PostgreSQL antérieures
• worker : valeur par défaut. Les lectures se font via des processus en parallèles lancés par la requête parente (3 par défaut). Ces processus font des appels auprès du kernel et montent les données dans le “shared_buffers” pour être traitées.
• io_uring : utilisation de la méthode “io_uring” qui consiste, depuis la version 5.1 du kernel Linux, à utiliser des “shared ring buffers”. Ce procédé utilise des “queue rings” entre PostgreSQL et le kernel , avec “completion queue” pour la partie kernel et “submission queue” géré pour les demandes PostgreSQL.
  Je vous invite à lire cet article pour plus de précisions sur ce mécanisme.

io_worker représentant le nombre de process en parallèle qui vont traiter les demandes de mise en cache des pages.
Par défaut, le nombre est de 3, mais nous pouvons monter au-delà.

effective_io_concurrency est déjà présent sur les anciennes versions de PostgreSQL. Cependant, dans le cas des IO asychrones (io_method=worker ou io_methode=io_uring), les IO se font en concurrence directement dans PostgreSQL.

io_combine_limit est la taille maximale d’IO.

Système

Vérifier que votre serveur Linux est compatible avec la méthode “io_uring”. Pour cela exécuter cette commande, qui doit vous renvoyer 0

$ cat /proc/sys/kernel/io_uring_disabled
0

Sinon, forcer la valeur à 0, sous “root”.

# echo 0 > /proc/sys/kernel/io_uring_disabled

La validation peut se faire également sur la configuration du kernel au démarrage

 # cat /boot/config-6.12.48+deb13-cloud-amd64 | grep -i io_uring
CONFIG_IO_URING=y

PostgreSQL

Pour utiliser le mode “io_method=io_uring“, il faudra compiler PostgreSQL 18 avec ce mode -> with-liburing.

Il faut donc récupérer les sources depuis le site officiel PostgreSQL

Puis configurer avec l’option suivante :

# ./configure --with-liburing
# make
# make install

Benchmark

Pour nos tests, nous utilisons l’outil “pgbench”.
Nous allons créer un jeu de test avec un facteur de 500 sur le nombre de lignes créées par défaut dans les tables. Notre plus grosse table “pgbench_accounts” devrait donc dépasser les 50M de lignes.

Attention, chaque test se fait avec données “à froid”. Le cache est vidé à chaque interrogation. C’est à la première exécution que le test est le plus représentatif car PostgreSQL n’a pas de pages dans son cache et doit donc solliciter le kernel.

$ pgbench -i pgbench -s 500
dropping old tables...
NOTICE: table "pgbench_accounts" does not exist, skipping
NOTICE: table "pgbench_branches" does not exist, skipping
NOTICE: table "pgbench_history" does not exist, skipping
NOTICE: table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
vacuuming...
creating primary keys...
done in 42.40 s (drop tables 0.00 s, create tables 0.01 s, client-side generate 25.13 s, vacuum 3.35 s, primary keys 13.92 s).

La suite consiste à lancer un “SELECT COUNT” sur la table “pgbench_accounts” et relever les temps d’exécution entre les différentes versions de PostgreSQL et les différentes méthodes de lectures asynchrones.

PostgreSQL 17

$  select count(abalance) from "pgbench_accounts";
count
----------
50000000
(1 row)

Time: 48937.120 ms (00:48.937)

Nous mettons un peu plus de 49 secondes pour compter les 50M de lignes de la table.

le plan d’exécution est le suivant

$  explain (analyze, buffers) select count(abalance) from "pgbench_accounts";
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=1081089.88..1081089.89 rows=1 width=8) (actual time=69053.114..69053.219 rows=1 loops=1)
Buffers: shared hit=2518 read=817155
- Gather (cost=1081089.67..1081089.88 rows=2 width=8) (actual time=69051.505..69053.209 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=2518 read=817155
- Partial Aggregate (cost=1080089.67..1080089.68 rows=1 width=8) (actual time=69021.569..69021.571 rows=1 loops=3)
Buffers: shared hit=2518 read=817155
- Parallel Seq Scan on pgbench_accounts (cost=0.00..1028006.33 rows=20833333 width=4) (actual time=4.259..41669.680 rows=16666667 loops=3)
Buffers: shared hit=2518 read=817155
Planning Time: 0.056 ms
Execution Time: 69053.251 ms

Le “parallel seq scan” sur “pgbench_accounts” est fait avec 2 workers en parallèle.

PostgreSQL 18

Avec la valeur “io_methode=sync“, nous retrouvons à peu près le même temps, même si notre serveur sur PostgreSQL 18 est peu plus puissant et dispose de plus de RAM.Le “shared_buffer” a été taillé en conséquence.

pgbench=# select count(abalance) from "pgbench_accounts";
count
----------
10000000
(1 row)

Time: 49704.009 ms (00:49.704)

le plan est à peu près le même avec nos 2 workers effectuant du “parallel seq scan” sur “pgbench_accounts”.

  - Parallel Seq Scan on public.pgbench_accounts (cost=0.00..205601.67 rows=4166667 width=4) (actual time=0.838..42937.512 rows=3333333.33 loops=3)
Output: aid, bid, abalance, filler
Buffers: shared read=163935
Buffers: shared read=54826

Avec “io_method=worker” = 3, là, nous gagnons déjà quelques secondes en terme de temps d’exécution puisque notre requête est proche des 40 sec.

Puis avec “io_method=io_uring“, c’est là que nous sommes le plus performant puisque nous descendons à moins de 25 secondes.

Nous obtenons les résultats suivants

A noter que pendant des opérations de lectures asynchrones, nous pouvons suivre l’évolution de celles ci via la vue ‘pg_aios” mise à disposition par PostgreSQL.

$  select * from pg_aios;
pid | io_id | io_generation | state | operation | off | length | target | handle_data_len | raw_result | result | target_desc | f_sync | f_localmem | f_buffered
-------+-------+---------------+-----------+-----------+-----------+--------+--------+-----------------+------------+---------+--------------------------------------------------+--------+------------+------------
41329 | 192 | 9233 | SUBMITTED | readv | 570818560 | 131072 | smgr | 16 | NULL | UNKNOWN | blocks 200752..200767 in file "base/16440/16456" | f | f | t
41329 | 193 | 9290 | SUBMITTED | readv | 570556416 | 131072 | smgr | 16 | NULL | UNKNOWN | blocks 200720..200735 in file "base/16440/16456" | f | f | t
41329 | 194 | 9239 | SUBMITTED | readv | 570949632 | 131072 | smgr | 16 | NULL | UNKNOWN | blocks 200768..200783 in file "base/16440/16456" | f | f | t
41329 | 195 | 9229 | SUBMITTED | readv | 570687488 | 131072 | smgr | 16 | NULL | UNKNOWN | blocks 200736..200751 in file "base/16440/16456" | f | f | t
41329 | 197 | 9225 | SUBMITTED | readv | 571473920 | 131072 | smgr | 16 | NULL | UNKNOWN | blocks 200832..200847 in file "base/16440/16456" | f | f | t
41329 | 198 | 6372 | SUBMITTED | readv | 569638912 | 131072 | smgr | 16 | NULL | UNKNOWN | blocks 200608..200623 in file "base/16440/16456" | f | f | t
41329 | 200 | 6420 | SUBMITTED | readv | 571080704 | 131072 | smgr | 16 | NULL | UNKNOWN | blocks 200784..200799 in file "base/16440/16456" | f | f | t
41329 | 201 | 9247 | SUBMITTED | readv | 571211776 | 131072 | smgr | 16 | NULL | UNKNOWN | blocks 200800..200815 in file "base/16440/16456" | f | f | t
41329 | 202 | 9248 | SUBMITTED | readv | 571604992 | 131072 | smgr | 16 | NULL | UNKNOWN | blocks 200848..200863 in file "base/16440/16456" | f | f | t
41329 | 203 | 9221 | SUBMITTED | readv | 569769984 | 131072 | smgr | 16 | NULL | UNKNOWN | blocks 200624..200639 in file "base/16440/16456" | f | f | t
41329 | 207 | 9223 | SUBMITTED | readv | 569507840 | 131072 | smgr | 16 | NULL | UNKNOWN | blocks 200592..200607 in file "base/16440/16456" | f | f | t
41329 | 208 | 9226 | SUBMITTED | readv | 571736064 | 131072 | smgr | 16 | NULL | UNKNOWN | blocks 200864..200879 in file "base/16440/16456" | f | f | t
41329 | 210 | 282085 | SUBMITTED | readv | 571342848 | 131072 | smgr | 16 | NULL | UNKNOWN | blocks 200816..200831 in file "base/16440/16456" | f | f | t
(13 rows)

Cette vue est alimentée via la fonction ‘pg_get_aios”.

Nous pouvons voir l’offset de la page sur laquelle la lecture pointe ainsi que la longueur de l’opération IO en cours.

Conclusion

Pour profiter de l’efficacité des lectures asynchrones, ne pas oublier de configurer la valeur de “io_method” à “worker” ou “io_uring 

A volumes équivalents, les plans d’exécutions nous permettent de voir que l’on traite un nombre de “shared read” similaire entre PostgreSQL 18 qu’avec PostgreSQL 17 mais avec un temps plus faible pour PostgreSQL 18.

Attention cependant, les gains sont effectifs sur les opérations de type “seq scan” ou “bitmap heap scan”. Pas de gain possible sur des opérations d’écritures.

En d’autres termes, la configuration Asynchrones IO sera parfaitement adaptée pour des requêtes décisionnelles avec datawarehouse volumineux.

Autre point à savoir, il a été relevé également des risques en terme de sécurité avec le mode io_uring configuré dans le kernel.
Certains sites comme celui-ci font états de potentiels processus de type “malwares” qui pourraient s’attaquer au kernel et mettre en péril la sécurité du serveur.

Sur l’article Wikipédia dédié à “io_uring“, il est d’ailleurs noté ->

“In June 2023, Google’s security team reported that 60% of the exploits submitted to their bug bounty program in 2022 were exploits of the Linux kernel’s io_uring vulnerabilities. As a result, io_uring was disabled for apps in Android, and disabled entirely in ChromeOS as well as Google servers.^[11] Docker also consequently disabled io_uring from their default seccomp profile”

Bonne fin de journée !Continuez votre lecture sur le blog :

• PostgreSQL 13 : présentation (Emmanuel RAMI) [PostgreSQL]
• “Pruning” de partitions sous PostgreSQL ou comment bien élaguer ! (Capdata team) [PostgreSQL]
• PostgreSQL : optimiser vos opérations vacuum et analyze ! (Emmanuel RAMI) [PostgreSQL]
• Nouveautés pg_stat_statements avec PostgreSQL 15 (David Baffaleuf) [PostgreSQL]
• PostgreSQL Basics : lire un plan d’exécution comme un·e pro (ou presque) (Sarah FAVEERE) [PostgreSQL]

L’article PostgreSQL 18 : des IO asynchrones performantes ! est apparu en premier sur Capdata TECH BLOG.

Introduction :

Quand une requête PostgreSQL est considérée comme lente ou que ses performances se dégradent soudainement, il y a un réflexe à toujours avoir : utiliser les plans d’exécution. Je n’apprends certainement rien à la majorité des personnes qui liront cet article, mais si cela permet de garder vigilants les débutants et de leur enseigner quelques ficelles, alors cet article vaut le coup.

Si vous avez déjà été dans le cas de figure où un plan d’exécution est trop gros pour que vous sachiez par quel bout le prendre, ou si vous avez l’impression qu’on vous parle chinois quand on évoque les index scan ou les “cost” d’une requête, alors vous êtes au bon endroit.

Dans cet article, premier d’une série sur les bases de PostgreSQL, nous allons démystifier l’optimisation des requêtes en utilisant EXPLAIN comme point de départ, et les plans d’exécution générés comme fil conducteur.

L’objectif : vous aider à identifier rapidement les informations clés, repérer les goulots d’étranglement, et gagner en autonomie pour diagnostiquer les performances de vos requêtes.

C’est parti !

EXPLAIN ANALYZE : c’est quoi exactement et pourquoi l’utiliser ?

Pour commencer, il faut savoir qu’un moteur de base de données, que ce soit PostgreSQL ou un autre ne “lit” pas une requête comme un humain pourrait la lire, de gauche à droite. Il élabore plutôt un plan d’exécution : il créé un ensemble d’étape qui se veulent le plus optimisées possible pour aller chercher les données que vous lui demander de la façon la plus efficace possible.

Il y a un moyen simple de voir ce plan d’exécution, c’est d’utiliser la commande EXPLAIN ANALYZE. Elle nous permet en plus de comprendre de quelle façon le moteur à réellement exécuté notre ordre SQL (et pas seulement comment il pensait le faire). C’est un peu comme lire un journal de bord de l’exécution de la requête.

Prenons un exemple simple :

Imaginons que nous avons une table users, constituée comme tel :

CREATE TABLE users (
id integer PRIMARY KEY,
name vachar(30),
age integer,
email varchar(80) );

Et que nous souhaitons utiliser la requête suivante dans notre application :

SELECT * FROM users WHERE email = 'foo@example.com';

Sur cette table, nous n’avons pas d’index. Voici donc ce à quoi pourrait ressembler notre plan d’éxecution si nous utilisons la commande :

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'foo@example.com';

Seq Scan on users (cost=0.00..35.50 rows=1 width=100)
Filter: (email = 'foo@example.com')
Rows Removed by Filter: 999
Actual time=0.010..0.420 rows=1 loops=1

Les termes du plan d’exécution :

A première vue, il n’est pas évident de comprendre tout les termes que l’on voit dans le plan d’exécution généré.

Voici un récapitulatif des principaux :

• Seq Scan / Index Scan : Le type de parcours utilisé (séquentiel ou via un index)
• cost=… : Estimation du “coût” total de l’opération, selon PostgreSQL. Le coût n’est pas exprimé dans une unité particulière, c’est juste un indicatif. Plus il est élevé, plus l’opération est “coûteuse”, notre but étant d’éviter les coûts énormes pour que tout soit plus simple.
• rows=… : Estimation du nombre de lignes que l’étape va retourner
• actual time=… : Le temps réel que cette étape a pris (en millisecondes)
• Rows Removed by Filter : Nombre de lignes lues mais éliminées par un filtre
• loops=1 : Nombre de fois que cette étape a été exécutée (ex. dans une boucle)

Puis un exemple plus compliqué :

Attention, tout les plans d’exécution de requête ne sont pas aussi simples que celui que je viens de vous présenter. Bien souvent ils se présentent sous la forme de plusieurs nœuds indentés  qu’il faut lire dans le bon ordre. Imaginons une nouvelle table orders qui répertorie les commandes passées par un user :

CREATE TABLE orders (
id_orders integer PRIMARY KEY,
created_at timestamp,
total numeric,
status text,
user_id integer references users(id));

Nous pouvons alors imaginer la requête suivante :

SELECT u.name, o.total, o.created_at
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed'
ORDER BY o.created_at DESC
LIMIT 10;

Qui remonte les dix premières commandes,  avec le nom de l’acheteur, le total de la commande et sa date, pour les 10 premières commandes dans l’ordre des dates dont le statut est “completed”.

Si on execute un explain analyze sur cette requête, on obtient un plan d’execution de cet ordre :

 Limit (cost=123.45..123.48 rows=10 width=40) (actual time=1.234..1.239 rows=10 loops=1)
   -> Sort (cost=123.45..130.00 rows=2620 width=40) (actual time=1.234..1.236 rows=10 loops=1)
      Sort Key: o.created_at DESC
      Sort Method: top-N heapsort Memory: 25kB
       -> Nested Loop (cost=0.42..100.00 rows=2620 width=40) (actual time=0.045..0.980 rows=200 loops=1)
            -> Index Scan using idx_orders_created_at on orders o (cost=0.29..45.00 rows=2620 width=24) (actual time=0.030..0.340 rows=200 loops=1)
                 Filter: (status = 'completed')
                 Rows Removed by Filter: 50
            -> Index Scan using users_pkey on users u (cost=0.13..0.20 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=200)
                 Index Cond: (id = o.user_id)

Si on devait visualiser ce plan d’exécution de manière graphique pour qu’il soit plus simple à lire, ça donnerait ça :

Comment lire un plan d’exécution :

Pour lire un plan d’exécution, on part toujours du nœud le plus indenté, puis on remonte vers les nœuds supérieur. Dans notre cas, on partirais des deux index scan pour remonter ensuite sur le Nested Loop, puis sur le Sort, et enfin sur le Limit.
La raison est simple : le plan d’exécution reflète l’ordre réel d’exécution de la requête par PostgreSQL.
Chaque étape du plan consomme les résultats produits par les étapes précédentes. Autrement dit : PostgreSQL commence par les opérations de lecture (accès aux tables, scans d’index…), puis applique les jointures, les filtres, les tris, etc. en remontant vers le haut du plan.
Le nœud le plus “haut” du plan (celui avec le moins d’indentation) correspond à l’opération finale, celle qui retourne les résultats à l’utilisateur. Les blocs en dessous (plus indentés) sont les dépendances nécessaires pour y arriver.
C’est donc une logique de pipeline de traitement :

1. Lire les données
2. Les filtrer
3. Les combiner
4. Les trier / agréger
5. Les retourner

Lire un plan d’exécution “du plus profond vers le haut”, c’est suivre le chemin de vie d’une ligne de résultat depuis le disque jusqu’à votre terminal.

Petite encyclopédie des nœuds les plus courants dans un plan d’exécution

Voici une sélection des nœuds que vous croiserez régulièrement dans les plans d’exécution PostgreSQL, avec une explication simple et directe pour chacun :

• Seq Scan
  Lecture séquentielle de toute la table.
  À surveiller : Normal sur petites tables, mais sur les grosses, cela peut indiquer un index manquant.

• Index Scan
  Parcours d’un index pour chercher les lignes correspondantes.
  À surveiller : Rapide si l’index est bien choisi. Peut devenir lent avec beaucoup de loops.

• Index Only Scan
  Comme un Index Scan, mais sans lire la table si toutes les colonnes nécessaires sont déjà dans l’index.
  À surveiller : Ultra-performant ! À viser si possible.

• Bitmap Index Scan + Bitmap Heap Scan
  PostgreSQL construit une “carte” des lignes à lire, puis les récupère en une seule passe.
  À surveiller : Très performant pour des filtres avec beaucoup de résultats.

• Nested Loop
  Pour chaque ligne de la première table, PostgreSQL cherche dans la seconde.
  À surveiller : Bien sur de petits volumes, mais peut exploser sur de grandes tables (effet quadratique).

• Hash Join
  PostgreSQL construit une table de hachage en mémoire pour faire la jointure.
  À surveiller : Performant si la mémoire le permet. Attention à la taille des jeux de données.

• Merge Join
  Jointure optimisée entre deux sources déjà triées.
  À surveiller : Excellent en perfs si les colonnes jointes sont indexées ou triées à l’avance.

• Aggregate
  Calcule une agrégation (COUNT, SUM, AVG, …).
  À surveiller : Peut être coûteux si l’agrégation se fait sur de gros volumes sans index.

• Sort
  Trie les données selon une ou plusieurs colonnes.
  À surveiller : Peut consommer beaucoup de mémoire ; un bon index peut éviter cette étape.

• Limit
  Tronque le résultat à N lignes.
  À surveiller : Très utile combiné avec ORDER BY, car PostgreSQL peut s’arrêter dès qu’il a assez de lignes triées.

• CTE Scan
  Utilisé quand vous avez une clause WITH (CTE – Common Table Expression).
  À surveiller : Si le CTE n’est pas matérialisé, il peut être recalculé à chaque appel.

Options utiles de EXPLAIN / EXPLAIN ANALYZE

La commande EXPLAIN (et sa variante EXPLAIN ANALYZE) peut être enrichie avec plusieurs options facultatives pour mieux comprendre ce que PostgreSQL fait avec vos requêtes. Voici une présentation des principales.

ANALYZE : exécuter la requête pour de vrai

Cette option (souvent appelée “EXPLAIN ANALYZE”) exécute réellement la requête et mesure les temps d’exécution.

 EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'foo@example.com'; 

Sans cette option, PostgreSQL ne fait qu’estimer le plan, il ne l’exécute pas réellement.

VERBOSE: plus de détails sur les colonnes et les expressions

Affiche le nom exact des colonnes internes et les expressions utilisées dans chaque étape du plan.

 EXPLAIN (ANALYZE, VERBOSE) SELECT name FROM users WHERE age > 30; 

Très utile quand on travaille avec des fonctions, des agrégats ou des vues complexes.

BUFFERS : détail des lectures mémoire et disque

Montre combien de blocs de données ont été lus en mémoire (cache partagé) et depuis le disque.

 EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'completed'; 

Idéal pour identifier si une requête est ralentie par des accès disques trop nombreux.

WAL : suivi des écritures dans le journal de transactions

Affiche l’impact de la requête sur le WAL (Write-Ahead Logging), c’est-à-dire les écritures nécessaires à la durabilité.

 EXPLAIN (ANALYZE, WAL) INSERT INTO logs SELECT * FROM events; 

Surtout utile pour comprendre le coût caché des requêtes d’écriture.

COSTS : afficher ou masquer les coûts estimés

Permet de désactiver les lignes cost=… si on veut se concentrer uniquement sur les temps réels (actual time).

 EXPLAIN (ANALYZE, COSTS OFF) SELECT * FROM users; 

Pratique pour alléger la lecture d’un plan quand on n’a pas besoin des estimations.

SETTINGS : voir les paramètres ayant influencé le plan

Affiche les paramètres de configuration de PostgreSQL qui ont eu un impact sur la génération du plan.

 EXPLAIN (ANALYZE, SETTINGS) SELECT * FROM users; 

Très utile pour le debug avancé, ou si certains paramètres sont modifiés via SET.

SUMMARY : afficher ou non les temps globaux

Contrôle l’affichage du résumé final (Planning Time, Execution Time).

 EXPLAIN (ANALYZE, SUMMARY OFF) SELECT * FROM users; 

Par défaut activé, mais vous pouvez le désactiver si vous ne souhaitez pas ces infos à la fin du plan.

TIMING : activer ou désactiver la mesure des temps internes

PostgreSQL mesure le actual time pour chaque nœud du plan. Cette option permet de désactiver ces mesures (utile pour les très petites requêtes ou les benchmarks massifs).

 EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM users; 

Désactive les mesures fines, ce qui peut légèrement améliorer les performances du plan d’analyse lui-même.

FORMAT : changer la sortie (TEXT, JSON, YAML)

Permet d’obtenir un plan dans un format structuré (parfait pour les outils externes comme explain.dalibo.com).

 EXPLAIN (ANALYZE, FORMAT JSON) SELECT * FROM users; 

Pratique pour générer un plan visuel, l’analyser en script, ou l’intégrer dans des outils de perf.

OK super, mais une fois qu’on sait ça, on en fait quoi ?

On pourrait passer des heures à décortiquer les différentes lignes d’un plan d’exécution sans pour autant avancer plus que ça. L’important est maintenant de savoir quoi en faire. Parce que c’est bien beau de l’afficher, encore faut-il savoir quoi y chercher.

Voici une petite liste non exhaustive des indices à repérer dans un plan d’exécution qui pourraient vous mener à une raison pour la lenteur de votre requête :

Une lecture séquentielle sur une grosse table :

Symptôme :

Pourquoi c’est un souci
PostgreSQL lit toute la table ligne par ligne. C’est très lent et parfaitement inutile, surtout si vous ne voulez que quelques lignes de cette table.

Comment le voir

• On note la présence d’un nœud SEQ SCAN dans notre plan d’exécution
• Nombre de lignes parcourues très élevé (puce rows ou présence de row removed by filter)

Solutions

• Ajouter un index sur la colonne filtrée peut souvent aider dans ce genre de cas.
• Réécrire la requête pour qu’elle utilise une clause mieux optimisée (exclusion, inclusion…)

Trop de Loop

Symptôme :

Pourquoi c’est un souci
Une opération lente est répétée pour chaque ligne de l’opération extérieure, souvent dans une jointure en boucle (Nested Loop).

Comment le voir

• Nombre de loop très élevés
• Souvent précédé d’un Nested Loop plus haut dans le plan d’exécution

Solutions

• Changer le type de jointure pour quelque chose de plus simple a traiter (hash join ou merge join)

• Réécrire la requête pour faire moins de “aller-retours”

• Ajouter des index pour faciliter les jointures

Des estimations loin de la réalité

Symptôme :

Pourquoi c’est un souci
PostgreSQL s’est trompé dans son estimation, ce qui l’a peut-être mené à choisir un mauvais plan.

Comment le voir
Comparez le nombre de rows (estimation) avec les actual rows. Si l’écart est très important, les statistiques sont probablement obsolètes.

Solutions

• Rafraichir les statistiques dans ce cas ne peut pas faire de mal : Analyze ou vaccuum analyze si nécessaire.

• Ajuster les statistiques

• Éviter les expressions trop complexes qui biaisent les estimations

Un tri qui consomme trop

Symptôme :

Pourquoi c’est un souci
Le tri est trop gros, PostgreSQL n’arrive plus à le faire en RAM → il passe sur disque → c’est lent.

Comment le voir

• Nœud SORT avec un SORT METHOD lent (EXTERNAL MERGE)

• Consommation mémoire élevée

• Étape qui prend beaucoup de temps

Solutions

• Ajouter un index sur les colonnes utilisées dans le ORDER BY

• Réduire la quantité de lignes à trier avec un LIMIT en amont

• Éviter les sous-requêtes non filtrées avant tri

Une clause Limit inefficace

Symptôme :

Pourquoi c’est un souci
PostgreSQL trie toute la table avant d’en extraire 10 lignes. S’il y a 1 million de lignes, c’est pas optimal.

Comment le voir

• Le SORT est au-dessus du LIMIT

• Le tri prend beaucoup de temps même pour un LIMIT 10

Solutions

• Utiliser un index qui permet de lire déjà trié (ORDER BY created_at DESC → index DESC)

• Repenser la requête pour éviter le tri global (ex : préfiltrage ou pagination efficace)

Des filtres appliqués trop tard

Symptôme :

Pourquoi c’est un souci
Le filtre est appliqué après avoir lu la majorité des lignes → PostgreSQL fait beaucoup de travail inutile.

Comment le voir

• Présence de FILTER (…) en bas de plan

• Très grand nombre de ROWS REMOVED BY FILTER

Solutions

• Indexer la colonne du filtre

• Réécrire la requête pour que le filtre soit pris en compte plus tôt dans le plan

• Éviter les fonctions non indexables dans le WHERE (ex : LOWER(email) → préférer un index fonctionnel)

Conclusion

Comme souvent avec PostgreSQL, il est difficile — voire impossible — de tout couvrir en un seul article. Chaque plan d’exécution est unique, chaque requête a ses subtilités, et chaque base de données a ses petites surprises. Il n’existe pas de recette miracle qui marcherait à tous les coups.

Mais avec les bons réflexes, quelques outils et un peu de méthode, on peut rapidement progresser : repérer les symptômes, lire les signes, poser les bonnes questions… et surtout, tester, encore et toujours.

EXPLAIN ANALYZE n’est pas réservé aux DBA ou aux experts en perfs. C’est un compagnon de route pour toute personne qui écrit des requêtes, et qui veut comprendre ce qui se passe sous le capot.

Et si vous vous sentez encore un peu perdu·e face à un plan trop verbeux : pas de panique. Avec l’habitude, ça devient un langage qu’on apprend à lire presque instinctivement. Et ça commence maintenant.

Continuez votre lecture sur le blog :

• “Pruning” de partitions sous PostgreSQL ou comment bien élaguer ! (Capdata team) [PostgreSQL]
• PostgreSQL 13 : présentation (Emmanuel RAMI) [PostgreSQL]
• Nouveautés pg_stat_statements avec PostgreSQL 15 (David Baffaleuf) [PostgreSQL]
• PostgreSQL 18 : des IO asynchrones performantes ! (Emmanuel RAMI) [Non classéPostgreSQL]
• PostgreSQL : optimiser vos opérations vacuum et analyze ! (Emmanuel RAMI) [PostgreSQL]

L’article PostgreSQL Basics : lire un plan d’exécution comme un·e pro (ou presque) est apparu en premier sur Capdata TECH BLOG.

Hello

pour commencer cette année 2025 , voici un petit article PostgreSQL ou l’on vous présente comment optimiser les opérations de maintenance que sont les VACUUM et les ANALYZE.

Ces 2 opérations sont essentielles pour conserver des performances optimales pour notre instance et garantir au planner de construire des plans d’exécutions optimisés.

les opérations VACUUM et/ou ANALYZE peuvent être longues et sources de nombreuses écritures dans les WALs sur des tables volumineuses.
C’est pourquoi, et ce depuis la version PostgreSQL 16, il est possible de modifier le comportement de ces opérations en affectant une taille de buffer. Il s’agit du “buffer_usage_limit“.

Principe de fonctionnement.

Ce procédé s’appuie sur le principe de “ring buffer” configuré pour PostgreSQL.

Attention, à ne pas confondre, évidement, avec les “rings buffer” de SQL Server !!

Pour rappel, PostgreSQL utilise cette stratégie de “ring buffer” afin de dédier un espace mémoire pour les opérations lourdes , telles, la lecture séquentielle sur une table volumineuse, un CREATE TABLE AS SELECT, un COPY…. mais aussi un VACUUM !

En fait, cet espace est utilisé pour éviter de “flusher” sur disque de manière trop brutale les pages en mémoire montées dans le “shared buffer“. Cela pénaliserait en grande partie toute opération concurrente à notre traitement actif puisqu’elle n’aurait plus d’espace pour mettre ses propres pages en mémoire.

Jusqu’à la version PostgreSQL 16, cet espace mémoire était défini à 256Ko. Ainsi, au cours d’une lecture séquentielle, chaque page  de 8Ko par défaut, est montée en mémoire dans cet espace si le nombre de pages totales à traiter pour la table, dépasse 1/4 du paramètre “shared_buffer“.

Il en est de même pour une opération VACUUM ou ANALYZE qui utilise également ce ring buffer et permet d’optimiser cette opération.

Nouveautés PostgreSQL 16 et PostgreSQL 17

Depuis la version PostgreSQL 16, il est possible de configurer la taille du buffer de façon unitaire. Par exemple, lors d’un VACUUM, il est tout à fait possible de choisir une valeur pour “BUFFER_USAGE_LIMIT“.

Depuis le version PostgreSQL 17, la valeur par défaut affectée à “BUFFER_USAGE_LIMIT” est de 2Mo.

Il vous est possible de paramétrer la valeur de 128Ko jusqu’à 16Go. Attention, cependant, cette valeur ne peux excéder 1/8 du paramètre “shared_buffer“.
Si vous faites le calcul, pour un serveur comportant 32Go de RAM, vous ne pourrez obtenir, au plus, 1Go pour votre ring buffer.

Cas d’utilisation pour un VACUUM

Sur une instance PostgreSQL 13, nous lançons un VACUUM simple sur une table de 2,5Go. Nous utilisons une base exemple créée via “pgbench”.

Nous avons utilisé les options “DISABLE_PAGE_SKIPPING” pour analyser, dans un premier temps, tous les blocs de notre table et ne pas sur baser sur les informations de la visibility_map.

(postgres@[local]:5433) [pgbenchmark] primaire $  vacuum (verbose,DISABLE_PAGE_SKIPPING) public.pgbench_accounts;
INFO: 00000: aggressively vacuuming "public.pgbench_accounts"
LOCATION: lazy_scan_heap, vacuumlazy.c:797
INFO: 00000: "pgbench_accounts": found 0 removable, 20000000 nonremovable row versions in 327869 out of 327869 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 196215
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 1.34 s, system: 0.67 s, elapsed: 18.90 s.
LOCATION: lazy_scan_heap, vacuumlazy.c:1759
VACUUM
Time: 18920.292 ms (00:18.920)

Le temps passé pour cette opération est d’un peu plus de 18 secondes en temps CPU pour analyser les 327869 blocs de notre table. Soit une taille de 2.5Go.

Nous effectuons la même opération sur cette même table, mais sur un moteur PostgreSQL 17. Nous positionnons le paramètre BUFFER_USAGE_LIMIT  à 8Mo.

(postgres@[local]:5437) [pgbenchmark] primaire $  vacuum (verbose,DISABLE_PAGE_SKIPPING,BUFFER_USAGE_LIMIT '8MB') public.pgbench_accounts;
INFO: 00000: aggressively vacuuming "pgbenchmark.public.pgbench_accounts"
LOCATION: heap_vacuum_rel, vacuumlazy.c:475
INFO: 00000: finished vacuuming "pgbenchmark.public.pgbench_accounts": index scans: 0
pages: 0 removed, 327869 remain, 327869 scanned (100.00% of total)
tuples: 0 removed, 20000000 remain, 0 are dead but not yet removable
removable cutoff: 208163, which was 0 XIDs old when operation ended
new relfrozenxid: 208163, which is 5 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
avg read rate: 134.208 MB/s, avg write rate: 0.034 MB/s
buffer usage: 330350 hits, 325508 misses, 83 dirtied
WAL usage: 84 records, 83 full page images, 684232 bytes
system usage: CPU: user: 1.41 s, system: 0.64 s, elapsed: 18.94 s
LOCATION: heap_vacuum_rel, vacuumlazy.c:763
VACUUM
Time: 18955.006 ms (00:18.955)

C’est  à peu de chose près, dans le même temps d’exécution. soir 18 secondes.

La suite consiste à redémarrer l’instance PostgreSQL 17 et constater les temps d’exécution pour chaque occurrence de lancement.
Nous exécutons donc, les mêmes ordres VACUUM, mais sans l’option “DISABLE_PAGE_SKIPPING”

Sur la version PostgreSQL 13, nous voyons qu’à la première exécution, juste après redémarrage, nous sommes à 32 millisecondes. Et à chaque exécution suivante, nous ne descendons pas en dessous de 15 millisecondes….

(postgres@[local]:5433) [pgbenchmark] primaire $  vacuum public.pgbench_accounts;
VACUUM
Time: 32.149 ms

(postgres@[local]:5433) [pgbenchmark] primaire $  vacuum public.pgbench_accounts;
VACUUM
Time: 15.001 ms

(postgres@[local]:5433) [pgbenchmark] primaire $  vacuum public.pgbench_accounts;
VACUUM
Time: 15.295 ms

En version PostgreSQL 17, nous faisons également un “flush” des pages dans le buffer cache à chaque exécution, tout en modifiant la valeur de “BUFFER_USAGE_LIMIT“.

(postgres@[local]:5437) [pgbenchmark] primaire $  vacuum (BUFFER_USAGE_LIMIT '128kB') public.pgbench_accounts;
VACUUM
Time: 18.098 ms

(postgres@[local]:5437) [pgbenchmark] primaire $  vacuum (BUFFER_USAGE_LIMIT '8MB') public.pgbench_accounts;
VACUUM
Time: 6.461 ms

(postgres@[local]:5437) [pgbenchmark] primaire $  vacuum (BUFFER_USAGE_LIMIT '16MB') public.pgbench_accounts;
VACUUM
Time: 4.333 ms

Le constat est simple, plus nous augmentons le “BUFFER_USAGE_LIMIT“, et plus le temps d’exécution du VACUUM diminue.

Nous comprendrons donc que sur une table de plus de 100Go, le gain peut être assez important.

Cas d’utilisation pour un ANALYZE

Pour l’instance PostgreSQL 13, nous exécutons le calcul de statistiques sur cette même table

(postgres@[local]:5433) [pgbenchmark] primaire $  vacuum (analyze,verbose) public.pgbench_accounts;
INFO: 00000: vacuuming "public.pgbench_accounts"
LOCATION: lazy_scan_heap, vacuumlazy.c:802
INFO: 00000: "pgbench_accounts": found 0 removable, 52 nonremovable row versions in 1 out of 327869 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 196278
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
LOCATION: lazy_scan_heap, vacuumlazy.c:1759
INFO: 00000: analyzing "public.pgbench_accounts"
LOCATION: do_analyze_rel, analyze.c:336
INFO: 00000: "pgbench_accounts": scanned 30000 of 327869 pages, containing 1830000 live rows and 0 dead rows; 30000 rows in sample, 20000009 estimated total rows
LOCATION: acquire_sample_rows, analyze.c:1190
VACUUM
Time: 29526.404 ms (00:29.526)

Nous sommes autour de 29 secondes pour analyser 30000 pages sur les 32769 que composent cette table.
Le sample est choisi en fonction de la valeur de “default_statistics_target“, par défaut à 100, avec 30000 lignes analysées par défaut.

Sur la version PostgreSQL 17, les résultats sont les suivants

(postgres@[local]:5437) [pgbenchmark] primaire $  vacuum (analyze,verbose,BUFFER_USAGE_LIMIT '128kB') public.pgbench_accounts;
INFO: 00000: vacuuming "pgbenchmark.public.pgbench_accounts"
LOCATION: heap_vacuum_rel, vacuumlazy.c:480
INFO: 00000: finished vacuuming "pgbenchmark.public.pgbench_accounts": index scans: 0
pages: 0 removed, 327869 remain, 1 scanned (0.00% of total)
tuples: 0 removed, 20000000 remain, 0 are dead but not yet removable
removable cutoff: 208163, which was 0 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
avg read rate: 46.211 MB/s, avg write rate: 0.000 MB/s
buffer usage: 37 hits, 100 misses, 0 dirtied
WAL usage: 0 records, 0 full page images, 0 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.01 s
LOCATION: heap_vacuum_rel, vacuumlazy.c:763
INFO: 00000: analyzing "public.pgbench_accounts"
LOCATION: do_analyze_rel, analyze.c:321
INFO: 00000: "pgbench_accounts": scanned 30000 of 327869 pages, containing 1830000 live rows and 0 dead rows; 30000 rows in sample, 20000009 estimated total rows
LOCATION: acquire_sample_rows, analyze.c:1301
VACUUM
Time: 8151.201 ms (00:08.151)

(postgres@[local]:5437) [pgbenchmark] primaire $  vacuum (analyze,BUFFER_USAGE_LIMIT '8MB') public.pgbench_accounts;
VACUUM
Time: 7282.546 ms (00:07.283)

Les différences de gains sont moins impressionnantes que sur un simple VACUUM à chaque changement de “BUFFER_USAGE_LIMIT“, mais on voit qu’en version PostgreSQL 17, nous sommes tout de même 4 fois plus rapide qu’en version PostgreSQL 13.

Remarques

Gardez à l’esprit que la valeur de “BUFFER_USAGE_LIMIT” est plafonnée à 1/8 de “shared_buffer”. Inutile donc de mettre à 1024Mo, si vous ne possédez que 8Go de RAM.

Attention si vous mettez une valeur trop grande, les transactions concurrentes effectuant des lectures séquentielles seront pénalisées par les opérations VACUUM. D’ailleurs, il est possible de mettre “BUFFER_USAGE_LIMIT” à 0, mais ceci n’est pas conseillé lors d’une activité transactionnelle en cours.

Pour aller plus loin dans l’optimisation d’une opération de vacuum, vous pouvez également passer le paramètre “INDEX_CLEANUP” à off. Ceci aura pour effet de ne pas s’occuper de traiter les entrées des index qui pointent sur les lignes mortes de la table.
Un “REINDEX” sera alors nécessaire à la fin du VACUUM sur les index de la table.

De plus, il est possible de positionner l’option “SKIP_DATABASE_STATS” afin d’indiquer à l’ordre VACUUM de ne pas rechercher l’ID de transaction le plus ancien pour l’ensemble des tables de la base et de geler celui-ci (datfrozenid).

Les opérations VACUUM sur les grosses tables seront bien entendu optimisées mais attention aux plages de maintenance choisies !!

Bonne journée à vous.

Emmanuel Rami

 Continuez votre lecture sur le blog :

• PostgreSQL 18 : des IO asynchrones performantes ! (Emmanuel RAMI) [Non classéPostgreSQL]
• PostgreSQL 13 : présentation (Emmanuel RAMI) [PostgreSQL]
• “Pruning” de partitions sous PostgreSQL ou comment bien élaguer ! (Capdata team) [PostgreSQL]
• Requêtes consommatrices sous PostgreSQL (épisode 1) (David Baffaleuf) [PostgreSQL]
• Nouveautés MySQL 8.0 : Les Histogrammes (Capdata team) [MySQL]

L’article PostgreSQL : optimiser vos opérations vacuum et analyze ! est apparu en premier sur Capdata TECH BLOG.

Introduction :

Dans le monde des bases de données, garantir une disponibilité continue est une exigence incontournable, surtout pour les systèmes critiques où chaque minute d’arrêt peut entraîner des pertes significatives. Lorsqu’il s’agit de migrer une base de données vers une nouvelle version, ce défi prend une toute autre dimension. Comment mettre à jour votre système sans interrompre les services, tout en préservant l’intégrité des données ?

PostgreSQL offre une solution élégante : la réplication logique. Cet outil permet de transférer des données de manière fluide entre différentes versions de PostgreSQL, tout en maintenant la base de données source opérationnelle. Dans cet article, nous allons explorer étape par étape comment utiliser cette fonctionnalité pour réaliser une montée de version sans temps d’arrêt, du déploiement initial à la bascule finale vers la nouvelle version.

Que vous soyez en train de planifier une migration ou simplement curieux de découvrir les possibilités offertes par PostgreSQL, suivez ce guide pratique qui vous permettra de transformer un défi complexe en une opération maîtrisée et efficace.

Le test :

1. Préparation

Pour tester cette nouvelle méthode, nous aurons besoin de deux instances PostgreSQL. Pour cet article j’ai choisit de démontrer la technique en migrant d’une version 14 à une version 17 de PostgreSQL.

Je commence donc par installer les versions sur deux machines différentes pouvant communiquer entre elles (c’est important) :

Sur les deux machines nous pouvons exécuter les commandes suivantes :

root@ip-192-1-1-246:~# sudo apt update sudo apt upgrade -y

...

root@ip-192-1-1-246:~# sudo apt -y install gnupg2 wget vim

...

root@ip-192-1-1-246:~# sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
root@ip-192-1-1-246:~# curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc|sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg

root@ip-192-1-1-246:~# sudo apt -y update
Get:1 file:/etc/apt/mirrors/debian.list Mirrorlist [38 B]
Get:2 file:/etc/apt/mirrors/debian-security.list Mirrorlist [47 B]
Hit:3 https://cdn-aws.deb.debian.org/debian bookworm InRelease
Hit:4 https://cdn-aws.deb.debian.org/debian bookworm-updates InRelease
Hit:5 https://cdn-aws.deb.debian.org/debian bookworm-backports InRelease
Hit:6 https://cdn-aws.deb.debian.org/debian-security bookworm-security InRelease
Get:7 http://apt.postgresql.org/pub/repos/apt bookworm-pgdg InRelease [129 kB]
Get:8 http://apt.postgresql.org/pub/repos/apt bookworm-pgdg/main amd64 Packages [359 kB]
Fetched 489 kB in 1s (348 kB/s)
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
All packages are up to date.

Puis sur notre première machine :

root@ip-192-1-1-246:~# sudo apt install postgresql-14
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following additional packages will be installed:
libcommon-sense-perl libgdbm-compat4 libio-pty-perl libipc-run-perl
libjson-perl libjson-xs-perl libllvm16 libperl5.36 libpq5 libsensors-config
libsensors5 libtypes-serialiser-perl libxslt1.1 libz3-4 logrotate perl
perl-modules-5.36 postgresql-client-14 postgresql-client-common
postgresql-common ssl-cert sysstat

...

root@ip-192-1-1-246:~# systemctl status postgresql@14-main.service
● postgresql@14-main.service - PostgreSQL Cluster 14-main
Loaded: loaded (/lib/systemd/system/postgresql@.service; enabled-runtime;>
Active: active (running) since Wed 2024-12-04 09:43:55 UTC; 2min 55s ago
Process: 15248 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect >
Main PID: 15253 (postgres)
Tasks: 7 (limit: 4633)
Memory: 17.3M
CPU: 239ms
CGroup: /system.slice/system-postgresql.slice/postgresql@14-main.service
├─15253 /usr/lib/postgresql/14/bin/postgres -D /var/lib/postgresq>
├─15255 "postgres: 14/main: checkpointer "
├─15256 "postgres: 14/main: background writer "
├─15257 "postgres: 14/main: walwriter "
├─15258 "postgres: 14/main: autovacuum launcher "
├─15259 "postgres: 14/main: stats collector "
└─15260 "postgres: 14/main: logical replication launcher "

Dec 04 09:43:53 ip-192-1-1-246 systemd[1]: Starting postgresql@14-main.service>
Dec 04 09:43:55 ip-192-1-1-246 systemd[1]: Started postgresql@14-main.service >

Puis sur la deuxième machine :

admin@ip-192-1-1-89:~$ sudo apt install postgresql-17
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following additional packages will be installed:
libcommon-sense-perl libgdbm-compat4 libio-pty-perl libipc-run-perl
libjson-perl libjson-xs-perl libllvm16 libperl5.36 libpq5 libsensors-config
libsensors5 libtypes-serialiser-perl libxslt1.1 libz3-4 logrotate perl
perl-modules-5.36 postgresql-client-17 postgresql-client-common
postgresql-common ssl-cert sysstat

admin@ip-192-1-1-89:~$ systemctl status postgresql@17-main.service
● postgresql@17-main.service - PostgreSQL Cluster 17-main
Loaded: loaded (/lib/systemd/system/postgresql@.service; enabled-runtime; >
Active: active (running) since Wed 2024-12-04 09:52:33 UTC; 2min 13s ago
Process: 15235 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect 1>
Main PID: 15240 (postgres)
Tasks: 6 (limit: 4633)
Memory: 20.5M
CPU: 332ms
CGroup: /system.slice/system-postgresql.slice/postgresql@17-main.service
├─15240 /usr/lib/postgresql/17/bin/postgres -D /var/lib/postgresql>
├─15241 "postgres: 17/main: checkpointer "
├─15242 "postgres: 17/main: background writer "
├─15244 "postgres: 17/main: walwriter "
├─15245 "postgres: 17/main: autovacuum launcher "
└─15246 "postgres: 17/main: logical replication launcher "

Dec 04 09:52:31 ip-192-1-1-89 systemd[1]: Starting postgresql@17-main.service ->
Dec 04 09:52:33 ip-192-1-1-89 systemd[1]: Started postgresql@17-main.service ->

Nos deux instances sont maintenant installées. Sur notre première base de données, nous allons créer une base, avec deux tables, et quelques lignes.

postgres@ip-192-1-1-246:/etc/postgresql/14/main$ psql
psql (14.15 (Debian 14.15-1.pgdg120+1))
Type "help" for help.

postgres=# CREATE DATABASE mydb;
CREATE DATABASE
postgres=# \c mydb
You are now connected to database "mydb" as user "postgres".
mydb=# CREATE TABLE customers (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE,
created_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE
mydb=# CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INT REFERENCES customers(id),
amount NUMERIC(10,2) NOT NULL,
order_date TIMESTAMP DEFAULT NOW()
);
CREATE TABLE
mydb=# INSERT INTO customers (name, email) VALUES
('Alice', 'alice@example.com'),
('Bob', 'bob@example.com'),
('Charlie', 'charlie@example.com');
INSERT 0 3
mydb=# INSERT INTO orders (customer_id, amount) VALUES
(1, 50.75),
(2, 20.00),
(1, 75.00);
INSERT 0 3

2. Configurer la base de données source

Sur notre première machine, nous allons modifier les paramètres du fichier de configuration de PostgreSQL pour permettre de pouvoir créer la réplication :

root@ip-192-1-1-246:~# su - postgres
postgres@ip-192-1-1-246:~$ cd /etc/postgresql/14/main
postgres@ip-192-1-1-246:/etc/postgresql/14/main$ vi postgresql.conf

Il s’agit de modifier les paramètres suivants :

wal_level = logical
max_replication_slots = 4
max_wal_senders = 4

Nous modifierons ensuite le pg_hba pour rajouter l’autorisation de connexion entre les deux machines :

postgres@ip-192-1-1-246:/etc/postgresql/14/main$ vi pg_hba.conf

Il suffira de rajouter une ligne :

host replication all <destination_ip> scram-sha-256

host replication all <source_ip> scram-sha-256

host all replication <destination_ip> scram-sha-256

host all replication <source-ip> scram-sha-256

Il ne faut pas oublier de redémarrer le serveur PostgreSQL une fois ces modifications effectuées :

root@ip-192-1-1-246:~# systemctl stop postgresql@14-main.service
root@ip-192-1-1-246:~# systemctl start postgresql@14-main.service

3. Configurer la base de donnée de destination

Après avoir configuré notre base de donnée depuis laquelle nous allons faire notre migration, il nous faut a présent configurer celle qui va recevoir la nouvelle base de donnée migrée.

Pour cela, nous allons répéter les étapes de configuration de la base de donnée source, en les adaptant sur notre base de donnée de destination : modifier le postgresql.conf, puis le pg_hba.conf, redémarrer ensuite la base de données

postgres@ip-192-1-1-89:~$ cd /etc/postgresql/17/main/
postgres@ip-192-1-1-89:/etc/postgresql/17/main$ vi postgresql.conf

wal_level = logical
max_replication_slots = 4
max_wal_senders = 4

postgres@ip-192-1-1-246:/etc/postgresql/14/main$ vi pg_hba.conf

host replication all <destination_ip> scram-sha-256

host replication all <source_ip> scram-sha-256

host all replication <destination_ip> scram-sha-256

host all replication <source-ip> scram-sha-256

root@ip-192-1-1-246:~# systemctl stop postgresql@14-main.service
root@ip-192-1-1-246:~# systemctl start postgresql@14-main.service

Il ne faudra pas oublier de créer la base de donnée ainsi que toutes les structures de tables et autres objets dans notre base cible pour qu’elle puisse recevoir les données. Pour avoir les scripts de création de la base de données, vous pouvez faire un pg_dump avec l’option

postgres@ip-192-1-1-89:~$ psql
psql (17.2 (Debian 17.2-1.pgdg120+1))
Type "help" for help.

postgres=# CREATE DATABASE mydb;
CREATE DATABASE

N’oubliez pas de donner tout les droits à votre utilisateur de replication pour qu’il puisse lire, écrire… Sur votre base de données repliquée, sur la source, comme sur la destination :

postgres=# GRANT ALL PRIVILEGES ON DATABASE "mydb" to replication;
GRANT

mydb=# GRANT ALL PRIVILEGES ON all tables in schema public to replication;
GRANT

4. Mise en place de la réplication logique

Maintenant que nos deux environnement sont bien en place, nous sommes prêts à mettre en route le processus de réplication logique pour commencer à transférer les données. Les étapes du dessous ont demandé une première intervention hors horaire de prod, notamment pour redémarrer le service postgreSQL, mais le but d’une migration avec réplication logique, c’est de pouvoir ensuite n’avoir rien à toucher jusqu’au moment de basculer les applicatifs d’une ip a une autre.

Sur notre machine source, on créé la publication qui va nous servir à transférer nos tables :

postgres@ip-192-1-1-246:~$ psql
psql (14.15 (Debian 14.15-1.pgdg120+1))
Type "help" for help.

postgres=# \c mydb
You are now connected to database "mydb" as user "postgres".
mydb=# CREATE PUBLICATION my_pub FOR ALL TABLES;
CREATE PUBLICATION

On va ensuite créé la souscription sur la base de données cible de notre migration :

mydb=# create subscription my_sub connection 'host=192.1.1.246 port=5432 dbname=mydb user=replication password=replication'publication my_pub;
NOTICE: created replication slot "my_sub" on publisher
CREATE SUBSCRIPTION

Maintenant que la subscription est en place, on peut vérifier qu’elle fonctionne. Pendant ce temps, la vrai production, sur la version 14, peut continuer à fonctionner, elle sera automatiquement repliquée sur la nouvelle version 17.

On peut vérifier ou en est notre replication avec la commande SELECT * FROM pg_stat_subscription;

mydb=# SELECT * FROM pg_stat_subscription;
-[ RECORD 1 ]---------+------------------------------
subid | 16422
subname | my_sub
worker_type | apply
pid | 16076
leader_pid |
relid |
received_lsn | 0/1733988
last_msg_send_time | 2024-12-04 14:23:59.873074+00
last_msg_receipt_time | 2024-12-04 14:23:59.872357+00
latest_end_lsn | 0/1733988
latest_end_time | 2024-12-04 14:23:59.873074+00

5. Test de replication, bascule, et nettoyage

Une fois que la synchronisation de votre replication logique est terminée, ce qui peut prendre un certain temps si vous avez beaucoup de données, vous pouvez constater de vous même sur les lignes que vous ajoutez, modifiez ou supprimez sur votre instance source sont repliquées sur l’instance de destination.

Par exemple, ajoutons un nouveau customer sur notre base source :

postgres@ip-192-1-1-246:~$ psql
psql (14.15 (Debian 14.15-1.pgdg120+1))
Type "help" for help.

postgres=# \c mydb
You are now connected to database "mydb" as user "postgres".
mydb=# INSERT INTO customers (name, email) VALUES ('Diana', 'diana@example.com');
INSERT 0 1

Si nous allons requêter sur notre instance de destination :

mydb=# select * from customers where name='Diana';
id | name | email | created_at
----+-------+-------------------+----------------------------
4 | Diana | diana@example.com | 2024-12-04 14:31:05.708031
(1 row)

Quand vous vous êtes bien assuré que tout fonctionne, vous pouvez alors rediriger les drivers odbc de vos applications vers le nouveau serveur et non plus l’ancien.

Une fois que cela est fait, vous pouvez alors supprimer le lien de replication, puisque l’ancienne instance ne sera plus alimentée, et même supprimer l’ancienne version si vous n’en avez plus l’utilité.

Sur la destination, notre nouveau serveur de prod :

DROP SUBSCRIPTION my_sub;

Sur la source, ancien serveur qui va être supprimé :

DROP PUBLICATION my_pub;

Conclusion

La réplication logique se distingue comme l’une des meilleures solutions pour minimiser le temps d’arrêt lors d’une migration de version PostgreSQL. En permettant une synchronisation continue des données entre deux instances, elle garantit une transition en douceur sans jamais interrompre les services en cours. Cela en fait un choix idéal pour les environnements critiques où la disponibilité est primordiale.

Avantages :

Zéro downtime : la source reste opérationnelle pendant toute la migration.
Flexibilité : possibilité de migrer vers une infrastructure différente (nouveau matériel, cloud, etc.).
Granularité : la réplication logique peut se limiter à certaines tables si nécessaire.

Inconvénients :

Complexité initiale : la configuration et les tests nécessitent une bonne maîtrise des paramètres de PostgreSQL.
Impact sur les performances : la charge de réplication peut légèrement affecter les performances de la base source, surtout avec un grand volume de données.
Non pris en charge pour certains types de données : les types spécifiques ou les extensions non standards ne sont pas toujours compatibles avec la réplication logique.

Si la réplication logique est souvent la méthode privilégiée pour des mises à jour critiques, elle n’est pas la seule option. Des alternatives comme les outils de sauvegarde et restauration ou la réplication physique peuvent répondre à d’autres besoins spécifiques, notamment pour des bases de données très volumineuses ou des scénarios nécessitant une réplication complète du système.

Dans tous les cas, le choix de la méthode dépendra de votre contexte, de vos contraintes techniques et de vos objectifs métier. Prenez le temps d’évaluer les différentes options pour garantir une migration réussie et sans surprise.Continuez votre lecture sur le blog :

• Réplication logique avec PostgreSQL (Capdata team) [PostgreSQL]
• Migration PostgreSQL via SLONY-I ou comment réduire le temps de coupure (Capdata team) [PostgreSQL]
• Migrer d’un cluster Galera MariaDB 10.3 vers MariaDB 10.5 avec la réplication logique (David Baffaleuf) [ContainerMySQLNon classé]
• Comparatif des gestionnaires de VIP dans un cluster Patroni : épisode 1 (KEEPALIVED) (David Baffaleuf) [ContainerPostgreSQL]
• Pyrseas et Postgresql : Comparer facilement des schémas de base de données (Sarah FAVEERE) [PostgreSQL]

L’article La montée de version en zero-downtime : merci la réplication ! est apparu en premier sur Capdata TECH BLOG.

1. Introduction : L’intelligence artificielle et le rôle des bases de données

L’intelligence artificielle (IA) connaît une popularité croissante, des assistants virtuels aux voitures autonomes, en passant par les recommandations de films et de produits. Mais pour que ces technologies fonctionnent, elles ont besoin de données, souvent en grande quantité. C’est là qu’interviennent les bases de données : elles stockent, gèrent et permettent d’accéder à ces données de manière efficace.

Les bases de données, comme PostgreSQL, ont donc un rôle clé dans l’IA. Mais l’IA ne traite pas toujours des informations simples comme des noms ou des chiffres ; souvent, elle doit manipuler des informations complexes, comme des représentations numériques d’images, de sons, ou de textes. Pour gérer ces données spécifiques, il faut des outils adaptés, et c’est là que l’extension pg_vector de PostgreSQL entre en jeu.

2. Les vecteurs en informatique et dans pg_vector

Dans le cadre de l’informatique, un vecteur est simplement une liste de nombres. Ces nombres peuvent représenter n’importe quoi : les caractéristiques d’un produit, les mots d’un texte ou même une image. Par exemple, pour un document texte, chaque mot peut être transformé en une série de nombres qui capture son sens dans un certain contexte.

L’extension pg_vector permet à PostgreSQL de stocker et de manipuler ces vecteurs. Elle offre un moyen simple de les utiliser directement dans une base de données. Imaginons que nous avons des centaines de documents et que nous souhaitions rechercher les plus similaires à un texte donné : en stockant les représentations numériques (ou embeddings) de ces documents sous forme de vecteurs, nous pouvons facilement comparer leur similarité grâce à pg_vector.

3. Le lien entre l’IA et les vecteurs

L’intelligence artificielle repose sur la capacité à comprendre et traiter des informations complexes. Par exemple, quand une IA doit reconnaître une image, elle ne “voit” pas comme nous. Au lieu de cela, l’image est transformée en une série de nombres, un vecteur, qui représente ses caractéristiques (couleurs, formes, etc.).

Le même principe s’applique au texte. Les modèles de traitement du langage, comme ceux utilisés par les moteurs de recherche ou les chatbots, transforment chaque mot ou phrase en vecteur. Ces vecteurs capturent le sens des mots et permettent à l’IA de manipuler des informations complexes sans “comprendre” le langage humain.

C’est ici que les embeddings entrent en jeu. Un embedding est un vecteur qui représente des données sous une forme que l’IA peut utiliser. Par exemple, dans un système de recommandation, chaque produit est converti en un embedding, et les produits les plus proches de celui que nous venons de consulter (en termes de vecteur) nous seront recommandés. Grâce à pg_vector, ces embeddings peuvent être stockés et comparés directement dans une base de données.

4. Pourquoi est-ce utile ?

L’extension pg_vector est très utile pour des applications qui nécessitent la recherche par similarité. Par exemple, dans un moteur de recherche, si nous voulons trouver les documents les plus proches d’un texte donné, pg_vector permet de comparer les vecteurs (ou embeddings) de chaque document pour voir lesquels sont les plus similaires.

Autre exemple, dans une plateforme de streaming musical, chaque chanson peut être convertie en vecteur qui représente ses caractéristiques (comme le tempo, la tonalité, etc.). Grâce à pg_vector, on peut facilement recommander des chansons similaires à celles que nous écoutons.

L’avantage de pg_vector, c’est qu’il permet de gérer ces vecteurs directement dans la base de données, ce qui évite de passer par des systèmes externes plus complexes. Cela simplifie le développement et améliore la performance, car tout est géré au même endroit.

5. Le test

Pour démontrer le fonctionnement de l’extension, rien de tel qu’un petit test pour éprouver les fonctionnalités qu’elle propose. Le test sera plutôt simple et succinct pour être accessible. Le prérequi est d’avoir une version PostgreSQL 14 ou plus récente d’installée.

Etape 1 :

On commence par installer l’extension pg_vector. Pour cela, nous allons avoir besoin d’un certain nombre d’outils pour le faire fonctionner. Une partie de ces outils sont disponible dans la distribution dev de PostgreSQL

root@ip-192-1-1-201:~# sudo apt install postgresql-server-dev-14

Nous aurons également besoin de gcc et make :

root@ip-192-1-1-201:~# apt install make
root@ip-192-1-1-201:~# apt-get install gcc

On effectue ensuite un git clone du projet :

root@ip-192-1-1-201:~# git clone https://github.com/pgvector/pgvector.git

Et une fois que c’est fait, on l’installe avec make :

root@ip-192-1-1-201:~# cd pgvector
root@ip-192-1-1-201:~# make && sudo make install

Etape 2 :

On se connecte à PostgreSQL pour créer l’extension. Au passage, on créé aussi une base de données pour faire nos test.

root@ip-192-1-1-201:~# su - postgres
postgres@ip-192-1-1-201:~$ psql
psql (14.13 (Ubuntu 14.13-0ubuntu0.22.04.1))
Type "help" for help.

postgres=# create database test_vector;
CREATE DATABASE
postgres=# \c test_vector
You are now connected to database "test_vector" as user "postgres".
test_vector=# CREATE EXTENSION vector;
CREATE EXTENSION

Et dans la foulée, on crée une table qui contient les vecteurs sur lesquels nous allons faire les test

test_vector=# CREATE TABLE documents (
id SERIAL PRIMARY KEY,
title TEXT,
embedding vector(3) -- vecteur de dimension 3 pour cet exemple
);
CREATE TABLE

Insertion des données d’exemple :

test_vector=# INSERT INTO documents (title, embedding) VALUES
('Document 1', '[0.1, 0.2, 0.3]'),
('Document 2', '[0.4, 0.5, 0.6]'),
('Document 3', '[0.9, 0.8, 0.7]');
INSERT 0 3

Etape 3 :

Nous avons deux types de choses à tester pour montrer l’efficacité de notre extension. En effet, pour rechercher un vecteur, deux modes s’offrent à nous :

1. La distance cosinus

La distance cosinus mesure non pas combien deux vecteurs sont éloignés, mais l’angle entre eux. C’est un peu comme comparer la direction dans laquelle pointent deux vecteurs plutôt que la distance réelle entre eux.

Imaginons que nous sommes en train de lancer deux flèches. La distance cosinus nous dira si les deux flèches pointent dans la même direction (sont similaires) ou si elles pointent dans des directions très différentes (sont moins similaires).

Dans le cadre de l’IA, cette mesure est souvent utilisée pour comparer des embeddings (représentations numériques complexes), car elle se concentre sur la relation entre les éléments, indépendamment de leur taille exacte.

Exemple simple :

Prenons les deux films :

• Film A : [1, 5, 50, 120]
• Film B : [2, 4, 45, 110]

La distance cosinus ne va pas se soucier de la différence de valeur entre chaque composant, mais va regarder si les deux films ont des proportions similaires. Autrement dit, est-ce que leur “profil” général est proche ou éloigné ?

Pour tester cette distance, dans notre pg vector, on utilise la méthode suivante :

test_vector=# SELECT title, embedding, embedding <=> '[0.2, 0.1, 0.3]' AS distance
FROM documents
ORDER BY embedding <=> '[0.2, 0.1, 0.3]' ASC
LIMIT 3;
title | embedding | distance
------------+---------------+---------------------
Document 2 | [0.4,0.5,0.6] | 0.05582537807240784
Document 1 | [0.1,0.2,0.3] | 0.07142855242198809
Document 3 | [0.9,0.8,0.7] | 0.09815280896106982
(3 rows)

Le symbole <=> représente une distance cosinus.

2. La distance Euclidienne

Imaginons que nous sommes sur une carte avec deux points : le point A et le point B. La distance euclidienne, c’est la façon la plus intuitive de mesurer la distance entre ces deux points, comme si nous tracions une ligne droite entre eux. Pour parler en terme simple, c’est la “distance à vol d’oiseau”.

Dans le cadre des vecteurs, la distance euclidienne mesure la différence entre deux vecteurs, un peu comme si chaque vecteur était un point sur une carte en plusieurs dimensions. Plus cette distance est petite, plus les deux vecteurs (et donc les objets qu’ils représentent) sont similaires.

Exemple simple :

Imaginons deux films représentés par les vecteurs suivants :

• Film A : [1, 5, 50, 120]
• Film B : [2, 4, 45, 110]

La distance euclidienne va calculer la différence entre chaque nombre des deux vecteurs et déterminer à quel point ces films sont proches en termes de caractéristiques (genre, nombre d’acteurs, budget, etc.).

Dans pg_vector on peut le tester ainsi :

test_vector=# SELECT title, embedding, embedding <-> '[0.2, 0.1, 0.3]' AS distance
FROM documents
ORDER BY embedding <-> '[0.2, 0.1, 0.3]' ASC
LIMIT 3;
title | embedding | distance
------------+---------------+--------------------
Document 1 | [0.1,0.2,0.3] | 0.1414213612422477
Document 2 | [0.4,0.5,0.6] | 0.5385165006363984
Document 3 | [0.9,0.8,0.7] | 1.0677078185041473
(3 rows)

Elle est représentée par le cigle <-> dans pg_vector.

3. Quand choisir l’une ou l’autre des distances ?

• La distance euclidienne est utile quand tu veux mesurer la différence globale entre deux objets. Elle est facile à comprendre et à utiliser pour des comparaisons directes.
• La distance cosinus est utile quand tu veux savoir si deux objets sont globalement similaires dans leur profil, indépendamment de leur taille ou de leur échelle. Elle est souvent utilisée pour comparer des documents textuels ou des données complexes en IA

Conclusion

L’extension pg_vector apporte une fonctionnalité puissante à PostgreSQL, permettant de manipuler des données complexes sous forme de vecteurs. Que ce soit pour des systèmes de recommandation, des moteurs de recherche ou toute autre application liée à l’intelligence artificielle, elle offre un moyen simple et efficace d’intégrer l’IA dans les bases de données. Et tout cela, sans avoir besoin de comprendre des mathématiques avancées : il suffit de savoir que ces vecteurs permettent de traiter des informations complexes de manière très efficace.Continuez votre lecture sur le blog :

• pg_recursively_delete : Simplifier les suppressions récursives (Sarah FAVEERE) [PostgreSQL]
• Pyrseas et Postgresql : Comparer facilement des schémas de base de données (Sarah FAVEERE) [PostgreSQL]
• PostgreSQL : planifier une tâche avec pg_cron (Emmanuel RAMI) [Non classéPostgreSQL]
• Transparent Data Encryption pour PostgreSQL (Sarah FAVEERE) [PostgreSQL]
• pg_dirtyread où comment réparer facilement un delete sauvage (Sarah FAVEERE) [PostgreSQL]

L’article pg_vector : l’IA et PostgreSQL est apparu en premier sur Capdata TECH BLOG.

Bonjour

Les 11 et 12 juin derniers, nous étions aux journées PGDAY à Lille pour découvrir les nouveautés autour de PostgreSQL.
Cette conférence regroupe différents professionnels, de la communauté francophone, qui agissent en contribuant sur des sujets techniques mais aussi sur les bonnes pratiques afin d’utiliser PostgreSQL dans les meilleurs conditions.

Un article m’a particulièrement intéressé cette année, c’est celui de Stefan Fercot Senior DBA PostgreSQL qui vit en Belgique, et travaille pour une société allemande experte dans les solutions PostgreSQL. Sa présentation portait sur le sujet “démystifier les sauvegardes incrémentales sous PostgreSQL”.

J’ai écouté sa conférence tout en ayant hâte de tester sa mise en place dès mon retour de Lille.

Je tiens à remercier Stefan pour son travail sur ce sujet sauvegardes PostgreSQL.

Tout d’abord, il faut savoir que les sujets sauvegardes incrémentales ont été déjà abordés avec des outils comme Barman ou Pg_BackRest, et que certaines instances PostgreSQL de production sont sauvegardées via ces mécanismes depuis quelques années maintenant.

Ici, nous parlons de la solution “backup incremental” inclu nativement dans le moteur PostgreSQL, et disponible avec l’outil “pg_basebackup“. C’est d’ailleurs ce point que Stefan a souligné durant la journée PGDAY du 11 juin dernier.

Cette nouvelle fonctionnalité fait partie de la version PostgreSQL 17 qui est pour le moment, en version Beta 2.
Celle ci devrait sortir, comme à l’accoutumé, au cour de l’automne prochain.

Preuve que PostgreSQL est en perpétuel évolution, et rejoint la liste des SGBD étant capable, comme peuvent le faire Oracle et SQL Server, de proposer nativement des sauvegardes incrémentales.

Installation de PostgreSQL 17

Pour tester cette fonctionnalité, nous devons installer la toute dernière version de PostgreSQL , la 17 beta 2. Attention, celle ci n’étant pas disponible dans les dépôts PGDG, nous devons nous charger d’installer cette version via le site postgresql.org

https://download.postgresql.org/pub/repos/yum/testing/17/redhat/rhel-8-x86_64/

Nous disposons d’un serveur Linux fork Red Hat 8 (Rocky Linux). Il nous faut donc télécharger les “rpm” liés à cette version.

Les packages dont nous avons besoin sont les suivants

# ls -lrt postgresql1* | awk '{print$9}'
postgresql17-contrib-17-beta2_1PGDG.rhel8.x86_64.rpm
postgresql17-17-beta2_1PGDG.rhel8.x86_64.rpm
postgresql17-libs-17-beta2_1PGDG.rhel8.x86_64.rpm
postgresql17-server-17-beta2_1PGDG.rhel8.x86_64.rpm

Nous les installons avec le compte root de notre serveur.

[root@ tmp]# rpm -i postgresql17-libs-17-beta2_1PGDG.rhel8.x86_64.rpm
[root@ tmp]# rpm -i postgresql17-17-beta2_1PGDG.rhel8.x86_64.rpm
[root@ tmp]# rpm -i postgresql17-server-17-beta2_1PGDG.rhel8.x86_64.rpm
[root@ tmp]# rpm -i postgresql17-contrib-17-beta2_1PGDG.rhel8.x86_64.rpm

Comme nous sommes sur un environnement “Red Hat like”, la création d’une première instance via “initdb” est nécessaire.
Surtout, ne pas oublier d’activer les “data checksums” (option -k), nous verrons pourquoi dans la suite de cet article. La suite est à faire avec le compte postgres.

[postgres ~]$ initdb -D /data/postgres/17/pg_data -k
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are enabled.

creating directory /data/postgres/17/pg_data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default "max_connections" ... 100
selecting default "shared_buffers" ... 128MB
selecting default time zone ... UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

pg_ctl -D /data/postgres/17/pg_data -l logfile start

Démarrer cette instance pour s’assurer que tout fonctionne

[postgres ~]$ pg_ctl -D /data/postgres/17/pg_data -l logfile start
waiting for server to start.... done
server started

Notre version enregistrée est bien une Beta 2. Version qui ne doit pas être mise sur un environnement de production comme le rappelle le site de la communauté PostgreSQL.

[postgres ~]$ psql
(postgres@[local]:5437) [postgres] > select * from version();
version
------------------------------------------------------------------------------------------------------------
PostgreSQL 17beta2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-22), 64-bit
(1 row)

Upgrade de version

Comme nous disposions deja d’une version PostgreSQL15 sur ce serveur, nous passons par un upgrade via l’outil “pg_upgrade” toujours disponible dans cette nouvelle version.

Lancer pg_upgrade en mode check

[postgres ~]$ pg_upgrade -b /usr/pgsql-15/bin/ -B /usr/pgsql-17/bin/ -c -d /data/postgres/15/pg_data/ -D /data/postgres/17/pg_data/ -p 5434 -P 5437
.....
.....

*Clusters are compatible*
"/usr/pgsql-17/bin/pg_ctl" -w -D "/data/postgres/17/pg_data" -o "" -m smart stop  "/data/postgres/17/pg_data/pg_upgrade_output.d/20240708T085906.955/log/pg_upgrade_server.log" 

la log est générée dans le $PGDATA de la version 17.

Puis lancer l’exécution de pg_upgrade

[postgres ~]$ pg_upgrade -b /usr/pgsql-15/bin/ -B /usr/pgsql-17/bin/ -d /data/postgres/15/pg_data/ -D /data/postgres/17/pg_data/ -p 5434 -P 5437

Effectuer une sauvegarde

Prérequis

Avant de pouvoir effectuer une première sauvegarde avec l’outil “pg_basebackup” natif, il est primordial de respecter certains prérequis important.

• L’instance PostgreSQL doit être créée avec les ‘data checksums’ activés. Si ce n’est pas le cas, utiliser l’outil “pg_checksums” avec l’option “-e“.

• Si vous lancez une sauvegarde full puis une incrémentale immédiatement, vous avez toutes les chances de tomber sur cette erreur

pg_basebackup: error: could not initiate base backup: ERROR: incremental backups cannot be taken unless WAL summarization is enabled

En effet, pour avoir toutes les informations concernant les blocks modifiés, PostgreSQL a besoin de tracer dans les WALs toutes les modifications sur les objets en base.
Pour les DBA Oracle, le “block change tracking” de la version Enterprise Edition vous parlera très certainement….
Il s’agit ici de la même fonctionnalité, c’est à dire, tracer les modifications effectuées dans les blocks de données.
Cette option est le “summarize_wal“.

Pour activer l’option, nous aurons 2 paramètres à modifier, soit via un ALTER SYSTEM directement sous psql, ou bien dans le fichier “postgresql.conf”.

[postgres backup]$ vi $PGDATA/postgresql.conf
...

# - WAL Summarization -

#summarize_wal = off # run WAL summarizer process?
#wal_summary_keep_time = '10d' # when to remove old summary files, 0 = never

Le premier paramètre permet d’activer cette option.
Le second définit un temps de conservation des informations concernant les blocks modifiés entre une sauvegarde FULL et un incrémentale.

Nous activons donc l’option “summarize_wal” et la passons à ON et laissons à 10 jours le “wal_summary_keep_time“.

Attention, activez ces deux paramètres avant votre première sauvegarde FULL. Si vous le faites après, vous risquez de rencontrer l’erreur suivante

pg_basebackup: error: could not initiate base backup: ERROR: WAL summaries are required on timeline 1 from 1/AA000028 to 1/AC000060, but the summaries for that timeline and LSN range are incomplete
DETAIL: The first unsummarized LSN in this range is 1/AA000028.

Le LSN pris lors de la première sauvegarde FULL n’est pas reconnu, et donc la sauvegarde incrémentale ne peut s’appuyer dessus.

Redémarrer l’instance une fois les modifications effectuées

[postgres ~]$ pg_ctl -D /data/postgres/17/pg_data/ restart

Lancer une sauvegarde FULL

Voici la nouvelle option présente pour l’outil “pg_basebackup”

[postgres -]$ pg_basebackup --help
pg_basebackup takes a base backup of a running PostgreSQL server.

Usage:
pg_basebackup [OPTION]...

Options controlling the output:
-D, --pgdata=DIRECTORY receive base backup into directory
-F, --format=p|t output format (plain (default), tar)
-i, --incremental=OLDMANIFEST
take incremental backup
-r, --max-rate=RATE maximum transfer rate to transfer data directory
(in kB/s, or use suffix "k" or "M")

.... 

Depuis la version 13 de PostgreSQL, nous disposons pour chaque sauvegarde, d’un fichier nommé “backup_manifest”. Il s’agit d’un fichier json qui recense entièrement les objets bases de données sauvegardés avec leur emplacement, leur taille, leur date de modification et leur “checksum”.

Celui ci est essentiel pour vérifier l’intégrité de notre sauvegarde avec “pg_verifybackup“.

Nous pouvons à présent faire une première sauvegarde FULL de notre instance PG17.

[postgres -]$ pg_basebackup -D /data/postgres/backup/pg_basebackup/PG17 -F p -l "Full Backup PG17" -P -v
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 1/AD000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_8048"
3097788/3097788 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 1/AD000158
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed

Puis on effectue quelques transactions : création d’une table et insertions de données sur cette table de test

(postgres@[local]:5437) [manu] $ > create table backup (nom varchar(20), type varchar(20), date_backup date);
CREATE TABLE
Time: 3.344 ms

(postgres@[local]:5437) [manu] $ > insert into backup values ('sauvegarde','FULL','2024-07-08 12:00:00');
INSERT 0 1
Time: 3.612 ms
(postgres@[local]:5437) [manu] $ > insert into backup values ('sauvegarde','incremental','2024-07-08 13:00:00');
INSERT 0 1
Time: 1.461 ms

(postgres@[local]:5437) [manu] $ > select * from backup;
nom | type | date_backup
------------+-------------+-------------
sauvegarde | FULL | 2024-07-08
sauvegarde | incremental | 2024-07-08
(2 rows)

Repérer le fichier “backup_manifest” de la sauvegarde FULL réalisée dans le dossier “/data/postgres/backup/pg_basebackup/PG17”

[postgres PG17]$ ls -lrt backup*
-rw-------. 1 postgres postgres 218 Jul 8 09:19 backup_label
-rw-------. 1 postgres postgres 433295 Jul 8 09:20 backup_manifest

Effectuer une sauvegarde incrémentale

A partir de là, lancer une sauvegarde incrémentale. Nous utilisons l’option “-i” pour indiquer à pg_basebackup ou est situé le “backup_manifest” de la dernière sauvegarde FULL.

[postgres - ]$ pg_basebackup -D /data/postgres/backup/pg_basebackup/PG17_incr -l "Incremental Backup PG17" -P -v -i /data/postgres/backup/pg_basebackup/PG17/backup_manifest
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 1/AF000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_8139"
12485/3097787 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 1/AF000120
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed

S’il l’on compare les deux répertoires de sauvegardes “/data/postgres/backup/pg_basebackup/PG17” et “/data/postgres/backup/pg_basebackup/PG17_incr“, nous voyons que les tailles sont bien différentes

[postgres - ]$ du -h /data/postgres/backup/pg_basebackup/PG17
......
3.0G /data/postgres/backup/pg_basebackup/PG17

[postgres - ]$ du -h /data/postgres/backup/pg_basebackup/PG17_incr
......
35M /data/postgres/backup/pg_basebackup/PG17_incr

Un volume de 3Go pour la sauvegarde FULL de l’instance contre 35Mo pour l’incrémentale.
La taille occupée par les objets dans chacune des bases est bien plus faible dans la sauvegarde incrémentale.

Nous continuons à insérer des données :

 [postgres - ]$ psql -d manu

(postgres@[local]:5437) [manu] $ > select * from backup;
nom | type | date_backup
------------+-------------+-------------
sauvegarde | FULL | 2024-07-08
sauvegarde | incremental | 2024-07-08
(2 rows)

Time: 0.614 ms
(postgres@[local]:5437) [manu] $ > insert into backup values ('sauvegarde','incremental 2','2024-07-08 14:00:00');
INSERT 0 1
Time: 1.436 ms
(postgres@[local]:5437) [manu] $ > select * from backup;
nom | type | date_backup
------------+---------------+-------------
sauvegarde | FULL | 2024-07-08
sauvegarde | incremental | 2024-07-08
sauvegarde | incremental 2 | 2024-07-08
(3 rows)

Puis on lance une seconde sauvegarde incrémentale :

[postgres - ]$ pg_basebackup -D /data/postgres/backup/pg_basebackup/PG17_incr_2 -l "Incremental 2 Backup PG17" -P -v -i /data/postgres/backup/pg_basebackup/PG17_incr/backup_manifest
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 1/B1000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_8313"
12260/3097787 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 1/B1000120
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed

Nous remarquons l’appel au “backup manifest” de la dernière sauvegarde incrémentale présente dans le répertoire “/data/postgres/backup/pg_basebackup/PG17_incr”

Si l’on regarde la taille de ce nouveau backup

[postgres pg_basebackup]$ du -h PG17_incr_2
.......
35M PG17_incr_2

A nouveau 35 Mo, mais vu le peu de modifications effectuées, la taille n’est pas très représentative.

Ce qu’il faut retenir, c’est qu’en fonction du fichier “backup manifest” pris lors de l’appel à pg_basebackup, vous pourrez faire soit
– une sauvegarde incrémentale qui prendra les dernières modifications depuis la dernière sauvegarde incrémentale effectuée.
– une sauvegarde différentielle qui prendra les modifications faites depuis la dernière sauvegarde FULL si vous vous appuyez toujours sur le “backup manifest” de votre sauvegarde FULL.

C’est donc ce fichier json “backup manifest” qui a un rôle essentiel dans l’élaboration de votre stratégie de sauvegarde au fur et à mesure du temps.

Et la restauration , comment ca se passe ?

Si l’on souhaite restaurer tous ces jeux de sauvegardes, nous utilisons un nouvel outil qui est “pg_combinebackup“.
Cet outil permet de “merger” les différentes sauvegardes dans un et un seul dossier que l’on restaurera par la suite.

Dans notre exemple, nous avons fait 1 sauvegarde FULL puis 2 incrémentales.
Nous allons donc restaurer ces 3 jeux de sauvegardes afin de retrouver les données. A noter qu’il existe une option “–dry-run” pour tester la commande

Exécuter la commande en prenant en paramètre les dossiers de sauvegardes dans l’ordre chronologique.

[postgres - ]$ pg_combinebackup -n -o /data/postgres/backup/pg_basebackup/PG17_ALL /data/postgres/backup/pg_basebackup/PG17 /data/postgres/backup/pg_basebackup/PG17_incr /data/postgres/backup/pg_basebackup/PG17_incr_2 

Si aucune erreur en sortie, on exécute sans l’option “dry run”.

 [postgres - ]$ pg_combinebackup -o /data/postgres/backup/pg_basebackup/PG17_ALL /data/postgres/backup/pg_basebackup/PG17 /data/postgres/backup/pg_basebackup/PG17_incr /data/postgres/backup/pg_basebackup/PG17_incr_2 

Le répertoire “/data/postgres/backup/pg_basebackup/PG17_ALL” ainsi généré, doit avoir une taille très légèrement supérieure au dossier de la sauvegarde FULL.

[postgres - ]$ du -h PG17_ALL
....
3.0G PG17_ALL

Dernière étape, nous passons à la restauration des données.

Nous arrêtons l’instance PG17

[postgres - ]$ pg_ctl -D /data/postgres/17/pg_data/ stop
waiting for server to shut down.... done
server stopped

Nous supprimons les données dans $PGDATA

[postgres - ]$ rm -rf /data/postgres/17/pg_data/* 

Puis nous restaurons ce jeu complet de données avec une simple copie.

[postgres - ]$ cp -r /data/postgres/backup/pg_basebackup/PG17_ALL/* /data/postgres/17/pg_data/ 

Enfin redémarrons l’instance

[postgres - ]$ pg_ctl -D /data/postgres/17/pg_data/ start
waiting for server to start....2024-07-08 10:51:45.671 UTC [8909] LOG: redirecting log output to logging collector process
2024-07-08 10:51:45.671 UTC [8909] HINT: Future log output will appear in directory "log".
done
server started

Puis contrôler que nous récupérons bien toutes les lignes de notre table “backup”.

[postgres@ip-172-44-2-96 pg_basebackup]$ psql -d manu
(postgres@[local]:5437) [manu] primaire $ > select * from backup;
nom | type | date_backup
------------+---------------+-------------
sauvegarde | FULL | 2024-07-08
sauvegarde | incremental | 2024-07-08
sauvegarde | incremental 2 | 2024-07-08

Remarques

• Attention, toujours vérifier les sauvegardes à chaque étape avec l’outil pg_verifybackup car rien ne garantit qu’au moment de l’appel à pg_combinebackup les différents jeux de sauvegardes FULL et/ou incrémentales ne soient pas corrompus.

• Assurez vous d’être en mode “data_checksum” activé et ne pas changer de mode entre les jeux de backup. Le “backup manifest” s’appuie sur ce paramétrage pour valider les checksums de chaque fichier.

• Le mode TAR pour pg_basebackup n’est pas compatible pour les sauvegardes full et incrémentales même si celui ci est possible. Mais c’est à vous de détarer les fichiers “base.tar.gz” Et au moment de la restauration  avec “pg_combinebackup“, une possible corruption est rencontrée.

[postgres - ]$ pg_combinebackup -o /data/postgres/backup/pg_basebackup/PG17_all_tar /data/postgres/backup/pg_basebackup/PG17_TAR /data/postgres/backup/pg_basebackup/PG17_incr_TAR
pg_combinebackup: error: could not write to file "/data/postgres/backup/pg_basebackup/PG17_all_tar/base/25284/25332", offset 122470400: wrote 380928 of 409600
pg_combinebackup: removing output directory "/data/postgres/backup/pg_basebackup/PG17_all_tar" 

La compression a potentiellement ajoutée une corruption ne rendant pas possible l’opération de “merge” des données.

• La restauration PITR est possible bien entendu. N’oubliez pas de créer le “recovery.signal” dans $PGDATA et de définir dans le fichier “postgresql.conf” les quelques paramètres suivants
  • recovery_target_name 
  • recovery_target_time
  • recovery_target_xid
  • recovery_target_lsn
  • recovery_target_inclusive = off ou on
  • recovery_target_timeline = ‘latest’
  • recovery_target_action = ‘pause’ 

 Continuez votre lecture sur le blog :

• PostgreSQL : la streaming replication en 12. (Emmanuel RAMI) [PostgreSQL]
• PostgreSQL 13 : présentation (Emmanuel RAMI) [PostgreSQL]
• Oracle RDS : effectuer des backup RMAN en mode PaaS. (Emmanuel RAMI) [AWSNon classéOracle]
• PostgreSQL : Comparatif entre Barman et pgBackRest (Capdata team) [PostgreSQL]
• Haute disponibilité de PostgreSQL avec Patroni (Ludovic AUGEREAU) [PostgreSQL]

L’article PostgreSQL 17 : des sauvegardes incrémentales avec pg_basebackup est apparu en premier sur Capdata TECH BLOG.

La gestion efficace des clusters PostgreSQL dans un environnement Kubernetes est un défi complexe auquel sont confrontées de nombreuses entreprises aujourd’hui. PGO offre une solution déclarative qui automatise la gestion des clusters PostgreSQL, simplifiant ainsi le déploiement, la mise à l’échelle et la gestion des bases de données PostgreSQL dans un environnement Kubernetes.

Pour faire suite à l’article de David sur PGO et à la demande d’un de nos clients, j’ai réalisé une étude approfondie de plusieurs fonctionnalités de PGO.
Cet article va faire un petit tour d’horizon des outils principaux inclus dans l’implémentation de PGO. Que ce soit pour la sauvegarde avec pgbackrest, pour la balance des connexion avec pgbouncer ou pour le monitoring avec prometheus, PGO ne manque pas d’utilitaire dont l’utilisation est facilitée par la solution tout embarqué.

Pgbackrest :

Utilité :

PgBackRest est une solution de sauvegarde et de restauration pour les bases de données PostgreSQL qui propose plusieurs fonctionnalités, telles que la sauvegarde et la restauration parallèles, la compression, les sauvegardes complètes, différentielles et incrémentielles, la rotation des sauvegardes et l’expiration des archives, l’intégrité des sauvegardes, etc. Il prend en charge plusieurs référentiels, qui peuvent être situés localement ou à distance via TLS/SSH, ou être des stockages fournis par le cloud comme S3/GCS/Azure.
L’architecture de pgbackrest pour PGO est la suivante :

Mise en place :

On peut imaginer plusieurs moyens de mettre en place le pgbackrest. Dans un premier temps, nous avons la sauvegarde classique en système de fichier, comme dans notre exemple sur le blog :

1) La sauvegarde sur volume persistant Kubernetes :

- name: repo1
  volume:
    volumeClaimSpec:
      accessModes:
      - "ReadWriteOnce"
      resources:
        requests:
          storage: 1Gi

Ce type de sauvegarde utilise un volume persistant de Kubernetes pour recueillir nos sauvegardes et les garder.
Une PersistentVolumeClaim (PVC) est une demande de stockage faite par un utilisateur. Elle est similaire à un Pod. Les Pods consomment des ressources de nœud et les PVC consomment des ressources de PV (PersistentVolume). Les Pods peuvent demander des niveaux spécifiques de ressources (CPU et mémoire). Les revendications peuvent demander une taille spécifique et des modes d’accès spécifiques (par exemple, elles peuvent être montées en ReadWriteOnce, ReadOnlyMany, ReadWriteMany, ou ReadWriteOncePod, voir AccessModes).

2) Le stockage pour S3 :

Pour pouvoir faire du stockage dans S3, il faut rajouter un fichier de configuration dans notre dossier de déploiement. Le fichier doit s’appeler s3.conf. Ce fichier contient les crédential de connexion à un AWS S3 :

repo1-s3-key=$YOUR_AWS_S3_KEY
repo1-s3-key-secret=$YOUR_AWS_S3_KEY_SECRET

Une fois que c’est configuré dans votre fichier, il ne reste plus qu’à modifier le postgresql.yaml, et configurer dans la partie backup :

backups:
    pgbackrest:
      image: registry.developers.crunchydata.com/crunchydata/crunchy-pgbackrest:ubi8-2.49-0
      configuration:
      - secret:
          name: pgo-s3-creds
      global:
        repo1-path: /pgbackrest/postgres-operator/pgcluster1/repo1
      repos:
      - name: repo1
        s3:
          bucket: "<YOUR_AWS_S3_BUCKET_NAME>"
          endpoint: "<YOUR_AWS_S3_ENDPOINT>"
          region: "<YOUR_AWS_S3_REGION>"

Une fois configuré, et le job mis dans le cron, vous devriez voir apparaitre les sauvegardes sur le volume S3.

3) Le stockage GCS :

Comme pour Amazon S3 on peut sauvegarder nos backups dans Google Cloud Storage. Pour pouvoir le faire fonctionner il vous faut copier votre GCS key secret (qui est un fichier JSON) dans un gcs.conf que vous allez placer dans votre dossier Kustomize.
Il vous suffit ensuite de modifier votre fichier postgres.yaml pour ajouter dans la partie backup la configuration pour une sauvegarde gcs :

backups:
    pgbackrest:
      image: registry.developers.crunchydata.com/crunchydata/crunchy-pgbackrest:ubi8-2.49-0
      configuration:
      - secret:
          name: pgo-gcs-creds
      global:
        repo1-path: /pgbackrest/postgres-operator/pgcluster1/repo1
      repos:
      - name: repo1
        gcs:
          bucket: "<YOUR_GCS_BUCKET_NAME>"

Il ne vous reste plus qu’à regénérer vos pods, et votre sauvegarde arrivera directement dans votre Google Cloud Service.

4) Le stockage Azur Blob Storage :

Comme pour les deux points précédents, vous pouvez également stocker vos sauvegardes sur le blob storage d’Azure. Pour cela il vous faut créer un fichier dans votre kustomize, avec à l’intérieur la configuration pour votre point de sauvegarde Azure. Il vous faut l’appeler azure.conf et il devra contenir les lignes suivantes :

repo1-azure-account=$YOUR_AZURE_ACCOUNT
repo1-azure-key=$YOUR_AZURE_KEY

Il faut ensuite intégrer ces modifications dans votre fichier postgres.yaml :

backups:
    pgbackrest:
      image: registry.developers.crunchydata.com/crunchydata/crunchy-pgbackrest:ubi8-2.49-0
      configuration:
      - secret:
          name: pgo-azure-creds
      global:
        repo1-path: /pgbackrest/postgres-operator/pgcluster/repo1
      repos:
      - name: repo1
        azure:
          container: "<YOUR_AZURE_CONTAINER>"

Bien sur rien ne vous interdit, et c’est même conseillé, de joindre plusieurs moyens de sauvegarde. Cela permet notamment de s’assurer une plus grande fiabilité du système de sauvegarde, en s’assurant qu’elles sont disponibles à plusieurs endroits.
Une fois que vous avez décidé d’où vous allez stocker vos sauvegardes, et que vous l’avez configuré, il faut maintenant décider des différents paramètres de ces sauvegardes : la programmation, la rétention…

5) La programmation des sauvegardes :

Il faut savoir que par défaut, PGO sauvegarde automatiquement les WAL dans la méthode de sauvegarde que vous lui avez configuré. C’est donc une forme de sauvegarde en soit.
Mais dans le cadre d’une récupération après incident majeur, il peut aussi être utilise d’avoir des sauvegardes full programmées. Pgbackrest, qui est l’outil utilisé par PGO permet de mettre en place trois types de sauvegarde : les incrémentales, les différentielles et les fulls.
Chaque type de sauvegarde peut être programmée en suivant une notation identique à celle des crontab. Par exemple :

backups:
    pgbackrest:
      repos:
      - name: repo1
        schedules:
          full: "0 1 * * 0"
          differential: "0 1 * * 1-6"

Le fait d’implémenter ces planifications créera des CronJobs dans Kubernetes.

6) La rétention des backups :

Vous pouvez définir une rétention maximum pour vos backups sur le support de backup de votre choix. Une fois que cette rétention sera atteinte, pgbackrest fera le ménage tout seul des sauvegardes et des WAL qui lui sont reliées.
Il y a deux types de rétentions que l’on peut définir : les rétentions « count » basées sur le nombre de backup que l’on souhaite garder et les rétentions « time » basées sur le nombre de jours ou vous souhaitez garder votre sauvegarde.

backups:
    pgbackrest:
      global:
        repo1-retention-full: "14"
        repo1-retention-full-type: time

7) La sauvegarde unique :

Si dans le cadre d’un besoin particuliers, une grosse modification ou une migration par exemple, vous avez besoin de prendre une sauvegarde immédiate sans forcément attendre que le cron n’arrive, vous pouvez le faire.
Pour la configuration de cette sauvegarde, il faudra l’annoter comme « manuelle » :

  backups:
    pgbackrest:
      manual:
        repoName: repo1
        options:
         - --type=full

Il vous faudra ensuite déclencher cette sauvegarde avec une commande manuelle. Dans le cadre de notre cluster exemple pgcluster1 :
kubectl annotate -n postgres-operator postgrescluster pgcluster1 \ postgres-operator.crunchydata.com/pgbackrest-backup=”$(date)”

8) Faire un clone à partir d’un repo :

Quand on a configuré un repo sur notre instance primaire, on peut facilement créer un clone de notre instance à l’aide de notre sauvegarde. Ainsi, on créer un tout nouveau Pods à partir des informations stockées à propos du pod que l’on possède déjà. Ici, nous allons créer un nouveau pod à partir de notre pod pgcluster1 :

apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PostgresCluster
metadata:
  name: pgcluster2
spec:
  dataSource:
    postgresCluster:
      clusterName: pgcluster1
      repoName: repo1
  image: registry.developers.crunchydata.com/crunchydata/crunchy-postgres:ubi8-16.2-0
  postgresVersion: 16
  instances:
    - dataVolumeClaimSpec:
        accessModes:
        - "ReadWriteOnce"
        resources:
          requests:
            storage: 1Gi
  backups:
    pgbackrest:
      image: registry.developers.crunchydata.com/crunchydata/crunchy-pgbackrest:ubi8-2.49-0
      repos:
      - name: repo1
        volume:
          volumeClaimSpec:
            accessModes:
            - "ReadWriteOnce"
            resources:
              requests:
                storage: 1Gi

Ici on peut noter entre autres la partie spec de la configuration, qui est le morceau de yaml nous permettant de dire qu’on s’appuie sur le cluster existant pour créer un clone indépendant :

spec:
  dataSource:
    postgresCluster:
      clusterName: pgcluster1
      repoName: repo1

9) Point in Time Recovery :

De la même façon, si l’on veut faire une restauration PITR, nous allons remplir la balise spec de notre yaml. Attention cependant, pour faire une restauration PITR, nous avons besoin de posséder encore la sauvegarde. On ne peut pas faire une restauration PITR sur une sauvegarde lointaine qu’on ne possèderait plus. Imaginons que je souhaite repartir d’une sauvegarde datant d’hier soir à 20h30 de mon instance pgcluster1 sur mon instance pgcluster2, la configuration serait la suivante :

apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PostgresCluster
metadata:
  name: pgcluster2
spec:
  dataSource:
    postgresCluster:
      clusterName: pgcluster1
      repoName: repo1
      options:
      - --type=time
      - --target="2024-04-09 20:30:00-00"
  image: registry.developers.crunchydata.com/crunchydata/crunchy-postgres:ubi8-16.2-0
  postgresVersion: 16
  instances:
    - dataVolumeClaimSpec:
        accessModes:
        - "ReadWriteOnce"
        resources:
          requests:
            storage: 1Gi
  backups:
    pgbackrest:
      image: registry.developers.crunchydata.com/crunchydata/crunchy-pgbackrest:ubi8-2.49-0
      repos:
      - name: repo1
        volume:
          volumeClaimSpec:
            accessModes:
            - "ReadWriteOnce"
            resources:
              requests:
                storage: 1Gi

La partie qui nous intéresse ici est la partie spec, ou nous avons rajouter un type de restauration (ici time) et une heure target. Cela indique à pgbackrest qu’il doit aller chercher tous les fichiers de sauvegarde et WAL sur notre point de sauvegarde repo1 venant de l’instance pgcluster1 pour les réappliquer sur notre nouveau cluster pgcluster2.
Vous pouvez également vouloir réaliser une restauration In Place, c’est-à-dire écraser l’instance présente pour la remplacer par la restauration. Auquel cas, plutôt que de préciser comment s’appellera notre nouveau cluster, il faut alors passer par la balise restore :

spec:
  backups:
    pgbackrest:
      restore:
        enabled: true
        repoName: repo1
        options:
        - --type=time
        - --target="2024-04-09 20:30:00-00"

Ici, comme précédemment, nous restaurons à l’heure de 20 :30 hier soir, et cela sur notre propre instance. Ne reste plus qu’à lancer la restauration :

kubectl annotate -n postgres-operator postgrescluster pgcluster1 --overwrite \ postgres-operator.crunchydata.com/pgbackrest-restore="$(date)"

A noter qu’il ne faut pas oublier de désactiver ensuite le restore en le passant à false si vous ne souhaitez pas qu’il soit de nouveau écrasé au prochain changement de configuration.

10) Restaurer une base de données spécifique :

Si votre besoin est de restaurer une base de données spécifique plutôt que l’intégralité de l’instance, vous pouvez le préciser dans les paramètres de votre restauration.
Attention cependant, ce n’est pas une restauration comme le serais un pg_dump. Ici si vous restaurez simplement une seule base de données et pas le reste du cluster, les autres bases que vous n’avez pas choisit de restaurer deviendront inaccessibles.
Si nous voulons restaurer une base de données, et uniquement elle, voici la procédure :

spec:
backups:
  pgbackrest:
    restore:
      enabled: true
      repoName: repo1
      options:
        - --db-include=capdata

Ici, on ne restaurera que la base de données capdata, et aucunes autres bases à partir de notre repo1.

PgBouncer :

Utilité :

PgBouncer est un pooler de connexion pour PostgreSQL. Un pooler de connexion permet de maintenir ouvertes des sessions entre lui-même et le serveur, ce qui rend plus rapide l’ouverture de sessions depuis les clients, une application Web par exemple.
PgBouncer permet aussi de mutualiser les sessions dans le serveur, économisant ainsi des ressources. PgBouncer propose plusieurs modes de partage : par requête (default), par transaction ou par session.

Mise en place :

Pour ajouter un bouncer à notre configuration c’est une réalité très simple. Il suffit d’ajouter dans notre fichier postgres.yaml la rubrique proxy :

proxy:
  pgBouncer:
    image: registry.developers.crunchydata.com/crunchydata/crunchy-pgbouncer:ubi8-1.21-3

Une fois que vous avez rajouté cela dans la configuration, il n’y a plus qu’à appliquer celle-ci :

 kubectl apply -k kustomize/keycloak 

Quand PGO créé un nouveau connexion pooler sur notre instance déployée, il modifier le fichier secrets de l’utilisateur.
On voit que plusieurs champs qui concerne pg_bouncer sont apparus. Ils constituent les informations qui vont vous permettre de vous connecter sur votre bouncer nouvellement créé :

{
    "apiVersion": "v1",
    "data": {
        "dbname": "cGdjbHVzdGVyMQ==",
        "host": "cGdjbHVzdGVyMS1wcmltYXJ5LnBvc3RncmVzLW9wZXJhdG9yLnN2Yw==",
        "jdbc-uri": "amRiYzpwb3N0Z3Jlc3FsOi8vcGdjbHVzdGVyMS1wcmltYXJ5LnBvc3RncmVzLW9wZXJhdG9yLnN2Yzo1NDMyL3BnY2x1c3RlcjE/cGFzc3dvcmQ9NXNSaSUzRCU1QmZZbSUzQ2lSSGslMkElNUIlM0VuWGhqaiU3Q1EmdXNlcj1wZ2NsdXN0ZXIx",
        "password": "NXNSaT1bZlltPGlSSGsqWz5uWGhqanxR",
        "pgbouncer-host": "cGdjbHVzdGVyMS1wZ2JvdW5jZXIucG9zdGdyZXMtb3BlcmF0b3Iuc3Zj",
        "pgbouncer-jdbc-uri": "amRiYzpwb3N0Z3Jlc3FsOi8vcGdjbHVzdGVyMS1wZ2JvdW5jZXIucG9zdGdyZXMtb3BlcmF0b3Iuc3ZjOjU0MzIvcGdjbHVzdGVyMT9wYXNzd29yZD01c1JpJTNEJTVCZlltJTNDaVJIayUyQSU1QiUzRW5YaGpqJTdDUSZwcmVwYXJlVGhyZXNob2xkPTAmdXNlcj1wZ2NsdXN0ZXIx",
        "pgbouncer-port": "NTQzMg==",
        "pgbouncer-uri": "cG9zdGdyZXNxbDovL3BnY2x1c3RlcjE6NXNSaT0lNUJmWW0lM0NpUkhrJTJBJTVCJTNFblhoamolN0NRQHBnY2x1c3RlcjEtcGdib3VuY2VyLnBvc3RncmVzLW9wZXJhdG9yLnN2Yzo1NDMyL3BnY2x1c3RlcjE=",
        "port": "NTQzMg==",
        "uri": "cG9zdGdyZXNxbDovL3BnY2x1c3RlcjE6NXNSaT0lNUJmWW0lM0NpUkhrJTJBJTVCJTNFblhoamolN0NRQHBnY2x1c3RlcjEtcHJpbWFyeS5wb3N0Z3Jlcy1vcGVyYXRvci5zdmM6NTQzMi9wZ2NsdXN0ZXIx",
        "user": "cGdjbHVzdGVyMQ==",
        "verifier": "U0NSQU0tU0hBLTI1NiQ0MDk2OlgyQ3NQRU1FZjh3QkVlc05McDFJTkE9PSRKcDhKakl5Q0o1ZEpFRVhia1ptUERTNE5rR3d0V00rczdrMElsQmx0YkpvPTpEaHg3VzNCOE5vNDRYSHJ1Qm1RdENMQW9jNEtnSUZQa2dIeStUMkVWUUowPQ=="
    },
    "kind": "Secret",
    "metadata": {
        "creationTimestamp": "2024-04-09T16:37:36Z",
        "labels": {
            "postgres-operator.crunchydata.com/cluster": "pgcluster1",
            "postgres-operator.crunchydata.com/pguser": "pgcluster1",
            "postgres-operator.crunchydata.com/role": "pguser"
        },
        "name": "pgcluster1-pguser-pgcluster1",
        "namespace": "postgres-operator",
        "ownerReferences": [
            {
                "apiVersion": "postgres-operator.crunchydata.com/v1beta1",
                "blockOwnerDeletion": true,
                "controller": true,
                "kind": "PostgresCluster",
                "name": "pgcluster1",
                "uid": "7260b882-116f-4b02-b51a-18d4fe3a8038"
            }
        ],
        "resourceVersion": "9495",
        "uid": "1fbdf1d2-48ea-4a45-b7d6-01248317dbee"
    },
    "type": "Opaque"
}

Pour se connecter à notre pgbouncer, il suffit d’utiliser les informations fournies par le fichier de secret à la place de nos infos de connexion habituelles, et cela nous permet d’accéder directement au bouncer et non plus à l’instance elle-même.

Cette connexion peut être facilement modifiée en utilisant la documentation de pgbouncer afin de pouvoir configurer à notre guise notre pgbouncer. Un exemple de configuration qu’on pourrais rencontrer serait :

  proxy:
    pgBouncer:
      image: {{.Values.image.pgBouncer }}
      config:
        global:
          default_pool_size: "100"
          max_client_conn: "10000"
          pool_mode: transaction

Pour cet exemple on voit qu’on a définit un nombre de client maximum, la taille du pool à 100 et un mode transaction pour notre pool.

PGO et Prometheus

Utilité :

Prometheus est une trousse à outils de surveillance et d’alerte des systèmes en open source.
Prometheus collecte et stocke ses métriques sous forme de données de séries temporelles, c’est-à-dire que les informations de métriques sont stockées avec le timestamp auquel elles ont été enregistrées, aux côtés de paires clé-valeur optionnelles appelées labels.
– Un modèle de données multidimensionnel avec des données de séries temporelles identifiées par le nom de la métrique et des paires clé-valeur
– PromQL, un langage de requête flexible pour exploiter cette dimensionnalité
– Aucune dépendance sur le stockage distribué ; les nœuds de serveur individuels sont autonomes
– La collecte de séries temporelles se fait via un modèle de tirage sur HTTP
– La poussée de séries temporelles est prise en charge via une passerelle intermédiaire
– Les cibles sont découvertes via la découverte de service ou la configuration statique
– Prise en charge de plusieurs modes de graphiques et de tableaux de bord

Mise en place :

Pour pouvoir mettre en place une surveillance pour notre cluster, il est plus simple de télécharger et compléter le modèle fournit dans les exemples de pgo.
Ainsi, on peut récupérer les exemples à l’aide de git :

YOUR_GITHUB_UN="$YOUR_GITHUB_USERNAME"
git clone --depth 1 "git@github.com:${YOUR_GITHUB_UN}/postgres-operator-examples.git"
cd postgres-operator-examples

Les différentes configurations se trouvent dans le dossier kustomize/monitoring.
Pour activer le monitoring de notre instance, il faut ajouter la balise monitoring à notre fichier postgres.yaml :

monitoring:
  pgmonitor:
    exporter:
      image: registry.developers.crunchydata.com/crunchydata/crunchy-postgres-exporter:ubi8-5.5.1-0

Une fois notre configuration modifiée, on l’applique afin que PGO détecte les changements et configure tout seul l’exporter pour qu’il puisse se connecter à nos bases de données et récupérer les métriques.

kubectl apply -k kustomize/postgres

Il faut ensuite appliquer la configuration de base de pgmonitor pour qu’il créé lui-même les fichiers de configuration pour prometheus (il le fera en même temps pour Grafana et Alertmanager qui sont deux autres outils de surveillance). Pour cela on applique le kustomize présent dans le dossier monitoring :

$kubectl apply -k kustomize\postgres
postgrescluster.postgres-operator.crunchydata.com/pgcluster1 configured
$kubectl apply -k kustomize\monitoring
serviceaccount/alertmanager created
serviceaccount/grafana created
serviceaccount/prometheus created
clusterrole.rbac.authorization.k8s.io/prometheus created
clusterrolebinding.rbac.authorization.k8s.io/prometheus created
configmap/alert-rules-config created
configmap/alertmanager-config created
configmap/crunchy-prometheus created
configmap/grafana-dashboards created
configmap/grafana-datasources created
secret/grafana-admin created
service/crunchy-alertmanager created
service/crunchy-grafana created
service/crunchy-prometheus created
persistentvolumeclaim/alertmanagerdata created
persistentvolumeclaim/grafanadata created
persistentvolumeclaim/prometheusdata created
deployment.apps/crunchy-alertmanager created
deployment.apps/crunchy-grafana created
deployment.apps/crunchy-prometheus created

Nos services ont été correctement déployés, il ne nous reste plus qu’à utiliser celui qui nous intéresse, ici service/crunchy-prometheus et lui indiquer de commencer à envoyer les informations sur notre prometheus :

$kubectl -n postgres-operator port-forward service/crunchy-prometheus 9090:9090
Forwarding from 127.0.0.1:9090 -> 9090
Forwarding from [::1]:9090 -> 9090
Handling connection for 9090
Handling connection for 9090

Afin d’accéder à notre service prometheus, il ne nous reste plus qu’à se connecter avec l’adresse de notre machine, sur le port 9090 préalablement ouvert, pour voir apparaitre le dashboard de prometheus :

PGO Client :

Utilité :

Pour pouvoir gérer plus facilement le cluster créé par PGO, CrunchyData à développé une surcouche à kubectl qui permet de faciliter les commandes que nous pouvons réaliser sur le cluster.
Cela permet de ne pas avoir à taper les longues lignes de commandes qui permettent par exemple de démarrer les sauvegardes unitaires.

Mise en place :

Pour pouvoir installer cette surcouche, il faut télécharger la version qui correspond au système d’exploitation à partir du GIT de pgo client :

# wget https://github.com/CrunchyData/postgres-operator-client/releases/download/v0.4.1/kubectl-pgo-linux-arm64
--2024-04-11 12:07:45--  https://github.com/CrunchyData/postgres-operator-client/releases/download/v0.4.1/kubectl-pgo-linux-arm64
Resolving github.com (github.com)... 140.82.121.4
Connecting to github.com (github.com)|140.82.121.4|:443... connected.
HTTP request sent, awaiting response... 302 Found
Resolving objects.githubusercontent.com (objects.githubusercontent.com)... 185.199.109.133, 185.199.111.133, 185.199.110.133, ...
Connecting to objects.githubusercontent.com (objects.githubusercontent.com)|185.199.109.133|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 47895849 (46M) [application/octet-stream]
Saving to: ‘kubectl-pgo-linux-arm64’

kubectl-pgo-linux-arm64                                     100%[========================================================================================================================================>]  45.68M  --.-KB/s    in 0.1s

2024-04-11 12:07:45 (373 MB/s) - ‘kubectl-pgo-linux-arm64’ saved [47895849/47895849]

On renome le fichier téléchargé en kubectl-pgo et on le déplace dans nos bin pour pouvoir les utiliser :

# mv kubectl-pgo-linux-arm64 kubectl-pgo
# sudo mv kubectl-pgo /usr/local/bin/kubectl-pgo
# sudo chmod +x /usr/local/bin/kubectl-pgo
Une fois que ces actions sont réalisées, on peut tester le fonctionnement :
# kubectl pgo version
Client Version: v0.4.1
Operator Version: v5.5.1

Les commandes disponibles avec cette extension sont les suivantes :
– backup : Backup cluster
– create : Create a resource
– delete : Delete a resource
– help : Help about any command
– restore : Restore cluster
– show Show : PostgresCluster details
– start : Start cluster
– stop : Stop cluster
– support : Crunchy Support commands for PGO
– version : PGO clientContinuez votre lecture sur le blog :

• PGO : opérateurs kubernetes pour PostgreSQL, la suite ! (David Baffaleuf) [ContainerDevopsPostgreSQL]
• Kubegres : l’opérateur Kubernetes clé en main pour PostgreSQL (David Baffaleuf) [ContainerDevopsPostgreSQL]
• PostgreSQL : Comparatif entre Barman et pgBackRest (Capdata team) [PostgreSQL]
• PostgreSQL sur la solution Kubernetes locale Minikube (Emmanuel RAMI) [ContainerPostgreSQL]
• Sauvegardes SQL Server dans un Azure Blob Storage (Capdata team) [AzureSQL Server]

L’article PGO : la suite est apparu en premier sur Capdata TECH BLOG.

Si vous avez été amené au fil de votre carrière à manipuler de gros volumes de données contenus dans plusieurs tables possédant des références croisées entre elles, dépendantes d’autres tables, qui elles-mêmes dépendent d’autres tables, vous savez à quel point il peut être compliqué de remonter l’intégralité de l’arbre de dépendance pour supprimer la moindre ligne. Cela peut être long et fastidieux.

Vous ne savez pas vraiment ce que vous supprimez, dans quelles tables, et quels impacts cela peut avoir sur votre base de données. Si les dépendances sont nombreuses, il est d’autant plus compliqué de tout retracer et d’être sûr à 100 % de ce que votre DELETE va entraîner.

Dans cet article, je vais vous présenter rapidement un petit outil sous la forme d’une extension que je trouve pratique à utiliser dans ce cas de figure. L’outil s’appelle pg_recursively_delete, et il permet de tracer avant d’exécuter l’ordre de suppression de votre ligne, et d’avoir une arborescence des différentes données que vous allez impacter.

Installation d’un moteur et de l’extension :

Pour cet article, j’ai choisi d’utiliser PostgreSQL en version 16 pour tester si l’extension fonctionnait toujours.

root:~#sudo apt update && sudo apt upgrade
root:~#sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
root:~#wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
root:~#sudo apt -y update
root:~#sudo apt -y install postgresql-16

Notre moteur de base de données est installé, à présent il nous faut télécharger les sources de l’extension, et l’installer.

root:~# git clone https://github.com/trlorenz/PG-recursively_delete.git
Cloning into 'PG-recursively_delete'...
remote: Enumerating objects: 155, done.
remote: Counting objects: 100% (95/95), done.
remote: Compressing objects: 100% (62/62), done.
remote: Total 155 (delta 41), reused 74 (delta 29), pack-reused 60
Receiving objects: 100% (155/155), 38.55 KiB | 3.21 MiB/s, done.
Resolving deltas: 100% (70/70), done.
root:~# cd PG-recursively_delete/
root:~/PG-recursively_delete# make
cp sql/recursively_delete.sql sql/recursively_delete--0.1.5.sql
root:~/PG-recursively_delete# sudo make install
/bin/mkdir -p '/usr/share/postgresql/16/extension'
/bin/mkdir -p '/usr/share/postgresql/16/extension'
/bin/mkdir -p '/usr/share/doc/postgresql-doc-16/extension'
/usr/bin/install -c -m 644 .//recursively_delete.control '/usr/share/postgresql/16/extension/'
/usr/bin/install -c -m 644 .//sql/recursively_delete--0.1.5.sql  '/usr/share/postgresql/16/extension/'
/usr/bin/install -c -m 644 .//doc/changelog.md '/usr/share/doc/postgresql-doc-16/extension/'

Mise en place de l’environnement

Pour illustrer le fonctionnement de l’extension, je vais utiliser la base de données de démonstration dvdrental. Nous allons donc la télécharger et la charger dans une toute nouvelle base de données que nous aurons créée sur notre instance fraîchement créée :

 postgres:~$ wget https://www.postgresqltutorial.com/wp-content/uploads/2019/05/dvdrental.zip
--2024-03-11 08:34:54--  https://www.postgresqltutorial.com/wp-content/uploads/2019/05/dvdrental.zip
Resolving www.postgresqltutorial.com (www.postgresqltutorial.com)... 104.21.2.174, 172.67.129.129, 2606:4700:3037::6815:2ae, ...
Connecting to www.postgresqltutorial.com (www.postgresqltutorial.com)|104.21.2.174|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 550906 (538K) [application/zip]
Saving to: ‘dvdrental.zip’

dvdrental.zip                                               100%[========================================================================================================================================>] 537.99K  --.-KB/s    in 0.01s

2024-03-11 08:34:54 (46.0 MB/s) - ‘dvdrental.zip’ saved [550906/550906]  

Une fois téléchargée, on la dezippe :

postgres:~$ ls -l
total 544
drwxr-xr-x 3 postgres postgres   4096 Mar 11 08:30 16
-rw-rw-r-- 1 postgres postgres 550906 May 12  2019 dvdrental.zip
postgres:~$ unzip dvdrental.zip
Archive:  dvdrental.zip
  inflating: dvdrental.tar
postgres:~$ ls -l
total 3316
drwxr-xr-x 3 postgres postgres    4096 Mar 11 08:30 16
-rw-rw-r-- 1 postgres postgres 2835456 May 12  2019 dvdrental.tar
-rw-rw-r-- 1 postgres postgres  550906 May 12  2019 dvdrental.zip

On créé la base de données pour accueillir nos données, et on charge le fichier de sauvegarde :

postgres:~$ psql
psql (16.2 (Ubuntu 16.2-1.pgdg22.04+1))
Type "help" for help. 

postgres=# create database dvdrental;
CREATE DATABASE
postgres=# \l
                                                   List of databases
   Name    |  Owner   | Encoding | Locale Provider | Collate |  Ctype  | ICU Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+---------+---------+------------+-----------+-----------------------
 dvdrental | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           |
 postgres  | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           |
 template0 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c/postgres          +
           |          |          |                 |         |         |            |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c/postgres          +
           |          |          |                 |         |         |            |           | postgres=CTc/postgres
(4 rows)

postgres:~$ pg_restore -U postgres -d dvdrental dvdrental.tar

Une fois que c’est fait, on peut se connecter pour vérifier que tout a bien été chargé :

postgres:~$ psql
psql (16.2 (Ubuntu 16.2-1.pgdg22.04+1))
Type "help" for help.

postgres=# \c dvdrental
You are now connected to database "dvdrental" as user "postgres".
dvdrental=# \dt
             List of relations
 Schema |     Name      | Type  |  Owner
--------+---------------+-------+----------
 public | actor         | table | postgres
 public | address       | table | postgres
 public | category      | table | postgres
 public | city          | table | postgres
 public | country       | table | postgres
 public | customer      | table | postgres
 public | film          | table | postgres
 public | film_actor    | table | postgres
 public | film_category | table | postgres
 public | inventory     | table | postgres
 public | language      | table | postgres
 public | payment       | table | postgres
 public | rental        | table | postgres
 public | staff         | table | postgres
 public | store         | table | postgres
(15 rows)

L’extension :

Pour tester l’extension, nous allons essayer de supprimer un client de la liste des clients.
Le schéma de la base de données dvdrental est le suivant :

Si l’on observe attentivement le schéma ci-dessus, en voulant supprimer une donnée de la table customer, cela devrait avoir un impact sur les tables rental et payment qui sont directement liées à la table customer. De plus, ces deux tables sont également liées entre elles, ce qui signifie que supprimer une donnée dans la table rental modifiera nécessairement la table payment.

Prenons l’exemple de la suppression du client numéro 1. Si nous recherchons les dépendances de ce client dans la table rental, nous obtenons 32 lignes associées au customer_id 1 :

 dvdrental=# select count(*) from rental where customer_id = 1;
 count
-------
    32
(1 row)

Et si nous allons maintenant chercher toutes les occurrences de ce même client dans la table des paiements, nous obtenons :

dvdrental=# select count(*) from payment where customer_id = 1;
 count
-------
    30
(1 row) 

À présent, avec l’extension recursive_delete, nous allons chercher à obtenir le schéma de suppression pour vérifier si les résultats que nous avons trouvés sont corrects :

dvdrental=# create extension recursively_delete;
CREATE EXTENSION
dvdrental=# \set VERBOSITY terse
dvdrental=# select recursively_delete('customer', 1);
INFO:  DAMAGE PREVIEW (recursively_delete v0.1.5)
INFO:
INFO:          1     customer
INFO:         30 r   | payment.["customer_id"]
INFO:         32 r   | rental.["customer_id"]
INFO:          ~ n   | | payment.["rental_id"]
INFO:
 recursively_delete
--------------------
                  0
(1 row) 

La fonction de suppression de l’extension fonctionne avec les paramètres suivants :

• Le nom de la table en premier paramètre
• La clause WHERE du DELETE en second paramètre, qui peut être de multiples types (des entiers, des chaînes de caractères, des listes, des UUID…)
• Le mode de fonctionnement de l’extension, par défaut à false, qui indique au programme de ne pas effectuer les suppressions, mais simplement de dresser le schéma. Le passer à true entraînerait les suppressions.

Pour interpréter le schéma, voici la composition de chaque nœud :

1. La première colonne correspond au nombre de lignes
2. Le type de contraintes qui expliquent l’implication de la table dans le schéma : ‘a’, ‘r’, ‘c’, ‘n’, ou ‘d’ (‘no action’, ‘restrict’, ‘cascade’, ‘set null’, ou ‘set default’)
3. Un indicateur de si oui ou non le champ en question participe à une référence circulaire.

En examinant le résultat renvoyé par notre extension, nous constatons que nous obtenons les mêmes résultats : 30 lignes pour payment et 32 lignes pour rental. Nous obtenons également une dernière ligne qui nous indique que payment possède une référence à rental dans sa structure, et qu’il va lui aussi procéder à des suppressions en fonction du rental_id. Cela pourrait être par exemple le cas où une location effectuée par un client serait payée par un autre.

Pour effectuer la suppression, il suffit simplement de préciser true en troisième paramètre.

dvdrental=# select recursively_delete('customer', 1, true);
 recursively_delete
--------------------
                  1
(1 row)

Et à présent, si nous consultons notre table customer, la ligne 1 a disparu, ainsi que toutes les lignes qui la concernent dans d’autres tables également.

dvdrental=# select count(*) from customer where customer_id = 1;
 count
-------
     0
(1 row)

dvdrental=# select count(*) from rental where customer_id = 1;
 count
-------
     0
(1 row)

dvdrental=# select count(*) from payment where customer_id = 1;
 count
-------
     0
(1 row)

Nos lignes ont bel et bien disparu.

Cette extension fonctionne également avec les clés primaires composites. Il suffit de préciser entre crochets les deux valeurs de notre clé primaire, et le tour est joué.

Pour illustrer davantage le fonctionnement, je vais réaliser une suppression sur la table film. Cette table possède quelques dépendances.
Disons que nous souhaitons supprimer les 10 premiers films de notre liste, car ils ne sont plus loués étant trop anciens (plus personne n’a de magnétoscope pour regarder de bonnes vieilles cassettes !).

dvdrental=# select recursively_delete('film', (SELECT array_agg(film_id) FROM film  WHERE film_id between 1 and 10));
INFO:  DAMAGE PREVIEW (recursively_delete v0.1.5)
INFO:
INFO:         10     film
INFO:         62 r   | film_actor.["film_id"]
INFO:         10 r   | film_category.["film_id"]
INFO:         52 r   | inventory.["film_id"]
INFO:        165 r   | | rental.["inventory_id"]
INFO:          ~ n   | | | payment.["rental_id"]
INFO:
 recursively_delete
--------------------
                  0
(1 row)

Nous observons donc que notre suppression de 10 films (dans un array) entraîne la suppression d’acteurs, de catégories, d’inventaires, et par extension, de locations et de paiements

Conclusion :

En conclusion, l’extension pg_recursively_delete offre une solution pratique pour supprimer récursivement des données dans PostgreSQL, simplifiant ainsi les tâches de maintenance et de nettoyage des bases de données. Cependant, malgré ses avantages, cette extension présente certaines limites en termes de performances.

L’une des principales limitations réside dans le fait que la suppression récursive peut entraîner des opérations coûteuses en termes de temps d’exécution, surtout lorsque les données concernées sont fortement imbriquées ou que la base de données est volumineuse. Les performances peuvent également être affectées lorsque les tables impliquées dans la suppression ont des index complexes ou des contraintes de clés étrangères.

De plus, il est crucial de reconnaître les risques associés à la suppression de données ayant de nombreuses dépendances dans une base de données. La suppression inconsidérée de telles données peut entraîner des incohérences dans la base de données, des erreurs d’intégrité référentielle et même des pertes de données importantes. Il est donc essentiel de procéder avec prudence et de prendre en compte toutes les implications potentielles avant d’utiliser cette extension.

En résumé, bien que l’extension pg_recursively_delete offre une fonctionnalité utile pour gérer les opérations de suppression récursive dans PostgreSQL, il est essentiel pour les utilisateurs de comprendre ses limites en termes de performances et les risques potentiels associés à la suppression de données avec de nombreuses dépendances. Une utilisation judicieuse et une évaluation minutieuse des scénarios d’utilisation sont indispensables pour garantir l’intégrité et la performance de la base de données.Continuez votre lecture sur le blog :

• pg_dirtyread où comment réparer facilement un delete sauvage (Sarah FAVEERE) [PostgreSQL]
• Pyrseas et Postgresql : Comparer facilement des schémas de base de données (Sarah FAVEERE) [PostgreSQL]
• PostgreSQL : planifier une tâche avec pg_cron (Emmanuel RAMI) [Non classéPostgreSQL]
• PostgreSQL Anonymizer (Sarah FAVEERE) [PostgreSQL]
• La montée de version en zero-downtime : merci la réplication ! (Sarah FAVEERE) [PostgreSQL]

L’article pg_recursively_delete : Simplifier les suppressions récursives est apparu en premier sur Capdata TECH BLOG.

Imaginez le scénario : nous sommes vendredi soir, en fin de journée, et pour une raison quelconque, un développeur qui a eu une semaine fatiguante se dit qu’il serait bien de supprimer les lignes qu’il a modifiées dans la base de données de développement, afin que lundi il puisse reprendre le travail à zéro. Il se connecte donc et, à l’aide d’une commande de suppression sur la table concernée, il efface ses quelques lignes… Avant de se rendre compte qu’il vient de le faire en production.

Cela nous est arrivé à tous, au moins une fois dans notre carrière. Un manque d’attention, une absence de sécurisation, une erreur est si vite arrivée. Oui, mais voilà. La solution pour pallier à ce genre de problèmes, c’est généralement de recharger une sauvegarde de la base de données, pour ne surtout pas perdre cette ligne essentielle des paiements enregistrés… C’est long. La base est volumineuse… Et puis, nous n’avons pas un plan de sauvegarde fiable… Au secours !

Avant de céder à la panique, peut-être existe-t-il une autre solution à votre problème.

L’extension pg_dirtyread pourrait être une idée. Cette extension permet aux utilisateurs de lire des données supprimées dans les tables, ce qui est normalement impossible en temps normal. Cette extension est disponible gratuitement sur GIT, et elle peut vous sauver la vie dans le scénario évoqué juste avant. Nous allons voir ensemble comment l’utiliser :

Installer PostgreSQL et pg_dirtyread :

C’est assez rare pour le souligner, mais pg_dirtyread possède son propre package Ubuntu tout inclus. Ce package, si vous pouvez vous en servir, contient l’installation du moteur, l’installation des dépendances de développement de PostgreSQL, le client, et l’extension elle-même. Cela simplifie grandement le travail. Vous n’avez qu’à télécharger le package dans la version qui vous intéresse. Pour cet article, j’ai choisi de télécharger et installer la dernière version de PostgreSQL ainsi que la dernière version de l’extension.

 root:~/pg_dirtyread# sudo apt -y install postgresql-16-dirtyread
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following additional packages will be installed:
  libcommon-sense-perl libjson-perl libjson-xs-perl libpq5 libsensors-config libsensors5 libtypes-serialiser-perl postgresql-16 postgresql-client-16 postgresql-client-common postgresql-common ssl-cert sysstat
Suggested packages:
  lm-sensors postgresql-doc-16 isag
The following NEW packages will be installed:
  libcommon-sense-perl libjson-perl libjson-xs-perl libpq5 libsensors-config libsensors5 libtypes-serialiser-perl postgresql-16 postgresql-16-dirtyread postgresql-client-16 postgresql-client-common postgresql-common ssl-cert sysstat
0 upgraded, 14 newly installed, 0 to remove and 0 not upgraded.
Need to get 21.3 MB of archives.
...
Running kernel seems to be up-to-date.

No services need to be restarted.

No containers need to be restarted.

No user sessions are running outdated binaries.

No VM guests are running outdated hypervisor (qemu) binaries on this host.

Il existe une autre méthode d’installation, la plus classique. Il suffit de télécharger les sources depuis le dépôt GIT et de les compiler sur votre machine en utilisant les commandes ‘make’ et ‘make install’.

Mise en place d’un environnement :

Pour notre test, j’ai choisit de me servir encore une fois de la base de données dvdrental, accessible à tous. Il me faut donc la télécharger :

 postgres:~$ wget https://www.postgresqltutorial.com/wp-content/uploads/2019/05/dvdrental.zip
--2024-03-11 08:34:54--  https://www.postgresqltutorial.com/wp-content/uploads/2019/05/dvdrental.zip
Resolving www.postgresqltutorial.com (www.postgresqltutorial.com)... 104.21.2.174, 172.67.129.129, 2606:4700:3037::6815:2ae, ...
Connecting to www.postgresqltutorial.com (www.postgresqltutorial.com)|104.21.2.174|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 550906 (538K) [application/zip]
Saving to: ‘dvdrental.zip’

dvdrental.zip                                               100%[========================================================================================================================================>] 537.99K  --.-KB/s    in 0.01s

2024-03-11 08:34:54 (46.0 MB/s) - ‘dvdrental.zip’ saved [550906/550906]  

Une fois téléchargée, on la dezippe :

postgres:~$ ls -l
total 544
drwxr-xr-x 3 postgres postgres   4096 Mar 11 08:30 16
-rw-rw-r-- 1 postgres postgres 550906 May 12  2019 dvdrental.zip
postgres:~$ unzip dvdrental.zip
Archive:  dvdrental.zip
  inflating: dvdrental.tar
postgres:~$ ls -l
total 3316
drwxr-xr-x 3 postgres postgres    4096 Mar 11 08:30 16
-rw-rw-r-- 1 postgres postgres 2835456 May 12  2019 dvdrental.tar
-rw-rw-r-- 1 postgres postgres  550906 May 12  2019 dvdrental.zip

On créé la base de données pour accueillir nos données, et on charge le fichier de sauvegarde :

postgres:~$ psql
psql (16.2 (Ubuntu 16.2-1.pgdg22.04+1))
Type "help" for help. 

postgres=# create database dvdrental;
CREATE DATABASE
postgres=# \l
                                                   List of databases
   Name    |  Owner   | Encoding | Locale Provider | Collate |  Ctype  | ICU Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+---------+---------+------------+-----------+-----------------------
 dvdrental | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           |
 postgres  | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           |
 template0 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c/postgres          +
           |          |          |                 |         |         |            |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c/postgres          +
           |          |          |                 |         |         |            |           | postgres=CTc/postgres
(4 rows)

postgres:~$ pg_restore -U postgres -d dvdrental dvdrental.tar

Une fois que c’est fait, on peut se connecter pour vérifier que tout a bien été chargé :

postgres:~$ psql
psql (16.2 (Ubuntu 16.2-1.pgdg22.04+1))
Type "help" for help.

postgres=# \c dvdrental
You are now connected to database "dvdrental" as user "postgres".
dvdrental=# \dt
             List of relations
 Schema |     Name      | Type  |  Owner
--------+---------------+-------+----------
 public | actor         | table | postgres
 public | address       | table | postgres
 public | category      | table | postgres
 public | city          | table | postgres
 public | country       | table | postgres
 public | customer      | table | postgres
 public | film          | table | postgres
 public | film_actor    | table | postgres
 public | film_category | table | postgres
 public | inventory     | table | postgres
 public | language      | table | postgres
 public | payment       | table | postgres
 public | rental        | table | postgres
 public | staff         | table | postgres
 public | store         | table | postgres
(15 rows)

Premier scénario : Suppression de lignes dans une table

Prenons ici le cas de la situation décrite dans l’introduction. Une ou plusieurs lignes ont été supprimées d’une ou plusieurs tables. Recharger l’intégralité d’une base de données juste pour ces quelques lignes, aussi essentielles soient-elles, demande un investissement énorme.

Pour notre exemple, je vais supprimer un certain nombre de lignes de la table ‘payment’.

dvdrental=# select * from payment limit 10;
 payment_id | customer_id | staff_id | rental_id | amount |        payment_date
------------+-------------+----------+-----------+--------+----------------------------
      17503 |         341 |        2 |      1520 |   7.99 | 2007-02-15 22:25:46.996577
      17504 |         341 |        1 |      1778 |   1.99 | 2007-02-16 17:23:14.996577
      17505 |         341 |        1 |      1849 |   7.99 | 2007-02-16 22:41:45.996577
      17506 |         341 |        2 |      2829 |   2.99 | 2007-02-19 19:39:56.996577
      17507 |         341 |        2 |      3130 |   7.99 | 2007-02-20 17:31:48.996577
      17508 |         341 |        1 |      3382 |   5.99 | 2007-02-21 12:33:49.996577
      17509 |         342 |        2 |      2190 |   5.99 | 2007-02-17 23:58:17.996577
      17510 |         342 |        1 |      2914 |   5.99 | 2007-02-20 02:11:44.996577
      17511 |         342 |        1 |      3081 |   2.99 | 2007-02-20 13:57:39.996577
      17512 |         343 |        2 |      1547 |   4.99 | 2007-02-16 00:10:50.996577
(10 rows)

dvdrental=# delete from payment where payment_id between 17523 and 17532;
DELETE 10

dvdrental=# select * from payment where payment_id between 17523 and 17532;
 payment_id | customer_id | staff_id | rental_id | amount | payment_date
------------+-------------+----------+-----------+--------+--------------
(0 rows)

La première chose à faire, en toute urgence, c’est de désactiver l’auto-vacuum. C’est votre pire ennemi dans notre scénario. C’est même la première chose à faire lorsque vous vous rendez compte que l’erreur a été commise :

dvdrental=# alter table payment set (autovacuum_enabled=false);

Si l’auto-vacuum est passé sur la table avant que vous n’ayez eu le temps de le désactiver, malheureusement, pg_dirtyread ne vous servira plus à rien. Une fois cela fait, on initialise l’extension :

dvdrental=# CREATE EXTENSION pg_dirtyread;
CREATE EXTENSION

L’extension étant maintenant créée, on peut requêter les données perdues. La syntaxe d’utilisation exige que vous rappeliez le schéma de la table en question au moment de requêter les lignes supprimées. Vous pouvez choisir de ne pas inclure toutes les colonnes, ou même d’en ajouter, comme nous le verrons à la fin de l’article. Dans notre exemple, la requête pour consulter les données effacées sera la suivante :

dvdrental=# select * from pg_dirtyread('payment') AS t(payment_id integer, customer_id smallint, staff_id smallint, rental_id integer, amount numeric(5,2), payment_date timestamp without time zone);
 payment_id | customer_id | staff_id | rental_id | amount |        payment_date
------------+-------------+----------+-----------+--------+----------------------------
      17523 |         345 |        1 |      1457 |   4.99 | 2007-02-15 18:34:15.996577
      17524 |         345 |        2 |      1550 |   0.99 | 2007-02-16 00:27:01.996577
      17525 |         345 |        2 |      2766 |   4.99 | 2007-02-19 16:13:41.996577
      17526 |         346 |        1 |      1994 |   5.99 | 2007-02-17 09:35:32.996577
      17527 |         346 |        2 |      3372 |   2.99 | 2007-02-21 12:02:45.996577
      17528 |         346 |        1 |      3421 |   2.99 | 2007-02-21 15:51:24.996577
      17529 |         347 |        2 |      1711 |   8.99 | 2007-02-16 12:40:18.996577
      17530 |         347 |        2 |      2274 |   0.99 | 2007-02-18 04:59:41.996577
      17531 |         347 |        1 |      3026 |   4.99 | 2007-02-20 10:16:26.996577
      17532 |         347 |        1 |      3092 |   8.99 | 2007-02-20 14:33:08.996577
      17533 |         347 |        1 |      3326 |   7.99 | 2007-02-21 07:33:16.996577
      17534 |         348 |        1 |      1654 |   2.99 | 2007-02-16 08:11:14.996577
      17535 |         348 |        1 |      2041 |   8.99 | 2007-02-17 12:47:26.996577
      17536 |         348 |        2 |      2499 |   0.99 | 2007-02-18 21:30:02.996577

On voit bien apparaître en début de liste nos lignes qui sont censées ne plus exister. Cela nous permet éventuellement d’extraire les données qu’elles contiennent pour pouvoir les réinjecter ensuite avec un INSERT. On peut également filtrer les données pour ne rechercher que celles que l’on a supprimées :

dvdrental=# select * from pg_dirtyread('payment') payment(payment_id integer, customer_id smallint, staff_id smallint, rental_id integer, amount numeric(5,2), dropped_6 timestamp without time zone) where payment_id = 17523;
 payment_id | customer_id | staff_id | rental_id | amount |         dropped_6
------------+-------------+----------+-----------+--------+----------------------------
      17523 |         345 |        1 |      1457 |   4.99 | 2007-02-15 18:34:15.996577
(1 row)

Deuxième scénario : On a supprimé une colonne entière

Deuxième cas typique, sûrement plus rare, celui où une colonne entière est supprimée. Pour notre exemple, je vais supprimer la colonne contenant la date de paiement :

dvdrental=# alter table payment drop column payment_date;
ALTER TABLE

pg_dirtyread nous permet de retrouver les informations de cette colonne à condition qu’aucun VACUUM FULL ou CLUSTER n’ait été exécuté. Pour ce faire, il faut connaître la position de la colonne dans l’ordre des colonnes de la table. Pour notre exemple, la colonne de date est la dernière colonne de la table, donc en position 6. Pour retrouver nos données, on peut donc exécuter la commande suivante :

dvdrental=# select * from pg_dirtyread('payment') payment(payment_id integer, customer_id smallint, staff_id smallint, rental_id integer, amount numeric(5,2), dropped_6 timestamp without time zone);
 payment_id | customer_id | staff_id | rental_id | amount |         dropped_6
------------+-------------+----------+-----------+--------+----------------------------
      17523 |         345 |        1 |      1457 |   4.99 | 2007-02-15 18:34:15.996577
      17524 |         345 |        2 |      1550 |   0.99 | 2007-02-16 00:27:01.996577
      17525 |         345 |        2 |      2766 |   4.99 | 2007-02-19 16:13:41.996577
      17526 |         346 |        1 |      1994 |   5.99 | 2007-02-17 09:35:32.996577
      17527 |         346 |        2 |      3372 |   2.99 | 2007-02-21 12:02:45.996577

Le cas d’une restauration d’une colonne entière supprimée est compliqué à gérer. Il faudrait la recréer, puis modifier toutes les lignes une à une pour réajouter les valeurs de cette fameuse colonne. Cela peut s’avérer un peu complexe à réaliser. Cependant, si vous ne disposez pas d’une sauvegarde pg_dump, vous n’aurez peut-être pas d’autres alternatives que cela, à part recharger entièrement la base.

Bonus : Récupérer les informations système des lignes supprimées

Avec pg_dirtyread, il est possible de récupérer les informations système des lignes qui ont été supprimées. Pour ce faire, rien de plus simple : il suffit d’indiquer dans la requête les informations que vous souhaitez récupérer. De plus, il existe une colonne qui indique si la ligne est supposément supprimée ou non, ce qui pourrait être pratique pour trier les différentes lignes selon leur état :

dvdrental=#  SELECT * FROM pg_dirtyread('payment') AS t(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean);
 tableoid |   ctid    | xmin | xmax | cmin | cmax | dead
----------+-----------+------+------+------+------+------
    16505 | (0,21)    |  835 |  941 |    0 |    0 | f
    16505 | (0,22)    |  835 |  941 |    0 |    0 | f
    16505 | (0,23)    |  835 |  941 |    0 |    0 | f
    16505 | (0,24)    |  835 |  941 |    0 |    0 | f
    16505 | (0,25)    |  835 |  941 |    0 |    0 | f
    16505 | (0,26)    |  835 |  941 |    0 |    0 | f
    16505 | (0,27)    |  835 |  941 |    0 |    0 | f
    16505 | (0,28)    |  835 |  941 |    0 |    0 | f
    16505 | (0,29)    |  835 |  941 |    0 |    0 | f
    16505 | (0,30)    |  835 |  941 |    0 |    0 | f

Conclusion :

En conclusion, l’extension pg_dirtyread offre une solution précieuse pour accéder aux données supprimées dans une base de données PostgreSQL. Son utilisation peut être cruciale dans des cas d’incidents critiques tels que la récupération de données perdues accidentellement ou la résolution d’incohérences de données. Cependant, il est essentiel de rappeler que son application reste extrêmement situationnelle et que pour qu’elle puisse être efficace, l’autovacuum doit être désactivé, ce qui n’est pas forcément recommandé.Continuez votre lecture sur le blog :

• pg_recursively_delete : Simplifier les suppressions récursives (Sarah FAVEERE) [PostgreSQL]
• Pyrseas et Postgresql : Comparer facilement des schémas de base de données (Sarah FAVEERE) [PostgreSQL]
• PostgreSQL : planifier une tâche avec pg_cron (Emmanuel RAMI) [Non classéPostgreSQL]
• AWS RDS : les extensions PostgreSQL (Emmanuel RAMI) [AWSPostgreSQL]
• Pseudonymiser vos bases PostgreSQL (Sarah FAVEERE) [PostgreSQL]

L’article pg_dirtyread où comment réparer facilement un delete sauvage est apparu en premier sur Capdata TECH BLOG.

Suite au premier article publié ce mois ci concernant “native network encryption” , je vous propose, pour ce sujet chiffrement et sécurité, de découvrir le fonctionnement de Transparent Data Encryption (TDE) pour Oracle.

Le sujet TDE a plusieurs fois été abordé au sein de notre blog, pour la partie PostgreSQL sur cet article, mais également sur une instance de bases de données SQL Server sur ce lien.

Je vous invite donc à lire, ou relire, les présentations faites pour ces SGBD en question.

Pour le moment, intéressons nous à ce que propose Oracle.

Présentation

Oracle TDE permet de chiffrer des données sensibles dans une base de données Oracle (multitenant ou pas) de façon la plus transparente possible pour l’application. En outre, l’application n’a pas besoin d’embarquer de stratégie de chiffrement puisque celle-ci est intégralement gérée coté serveur Oracle.

Les données, dans les colonnes, sont chiffrées une fois inscrites dans les datafiles. Il ne sera donc pas possible de les récupérer (via ALTER SYSTEM DUMP DATAFILE) sans avoir la clé.
Les syntaxes SQL utilisées par l’application restent inchangées puisque celles-ci sont directement envoyées depuis l’application cliente et chiffrées sur le serveur base de données locale (encryption at rest).

Oracle TDE utilise, pour son processus, un mécanisme de clés (master key et clé de cryptage) afin de valider l’ouverture du “wallet”, et du chiffrement de la donnée.

À partir d’Oracle 10gr2, Oracle TDE a la possibilité de traiter l’algorithme de chiffrement sur une colonne de table (un champs adresse ou RIB par exemple).
C’est à partir de la version Oracle 11gr1 qu’Oracle TDE peut travailler sur le chiffrement d’un tablespace entier. Ceci a l’avantage de traiter davantage d’objets en même temps.

Mais avant d’aller plus loin sur le fonctionnement de TDE, et comme nous sommes sur Oracle, il convient d’aller vérifier, point de vu licensing, ce que nous devons choisir comme version, pour configurer cette fonctionnalité et être en “règle” avec Oracle.

Cette fois ci, nous serons bien obligés de nous tourner vers la version Enterprise Edition Oracle, avec en plus, l’option payante “Advanced Security”.

Extrait du site Oracle -> lien

Pour une version Oracle 19c, nous choisirons donc de prendre une licence Enterprise Edition. A noter que l’option “Advanced Security” est incluse dans la version Personnal Edition On prem, alors qu’elle est en “extra coût” pour une version Enterprise Edition On prem.

Les algorithmes de chiffrement

Voici la liste des algorithmes utilisés par Oracle pour TDE, et ce, avec une instance de bases de données Oracle 19c

Oracle TDE travaille par défaut, avec l’algorithme Advanced Encryption Standard sur 192 bits, soit AES192 si vous souhaitez chiffrer une colonne. Pour un tablespace, c’est le même algorithme, mais en 128 bits.

D’autres algorithmes comme ARIA ou GOST pourront être choisis également.

Pour le chiffrement sur colonne, Oracle TDE utilisera par défaut SALT pour renforcer la sécurité des données. Le procédé suivant est réalisé ; une chaine sera ajoutée de façon aléatoire dans la donnée avant d’être chiffrée. Cela empêchera de trouver facilement la valeur d’un caractère à partir d’un motif de cryptage.

C’est lors de l’appel à la clause “ENCRYPT” que vous pouvez choisir un autre algorithme pour la colonne ou le tablespace.

Le fonctionnement

Oracle TDE se charge du chiffrement d’une donnée dans une colonne de table applicative en utilisant  un fonctionnement de type ESM (External Security Module) ce qui permet de générer des clés de chiffrement qui servent lors des opérations de cryptage et decryptage.

Ces clés de chiffrement sont stockées en interne dans la base de données. Une clé de chiffrement peut gérer une ou plusieurs colonnes de tables applicatives.

Ces mêmes clés de chiffrement sont stockées en interne dans une colonne du dictionnaire de données (vues v$encryption…) .
C’est la clé que l’on appelle « master key » qui permet de crypter/decrypter la colonne du dictionnaire de données afin d’utiliser ces clés de chiffrement de données aux personnes autorisées.

On peut représenter le mécanisme via le graphique suivant afin de comprendre le cheminement du processus Oracle TDE :

Dans tous les cas, il nous faut disposer d’un rôle DBA (ou bien du rôle SYSKM depuis la version Oracle 12c), afin de manipuler cette « master key ».
Cette clé est stockée hors de la base de données, c’est un répertoire de l’OS (ou ASM depuis la version Oracle 12c) qui portera cette clé.
Afin de la sécuriser encore plus, on utilisera un wallet.

Prérequis à la mise en place d’un wallet (keystore)

Pour mettre en place Oracle TDE,  nous devons créer un wallet.
Celui-ci sera créer par défaut sous « $ORACLE_BASE/admin/$ORACLE_SID/wallet » ou bien dans un autre répertoire que nous pourrons spécifier.

Pour notre instance CAPDATADB

[oracle@ip-172-44-2-141 admin]$ ls -lrt /opt/app/oracle/admin/CAPDATADB/
total 20
drwxr-x---. 2 oracle dba 4096 Jun 3 2021 scripts
drwxr-x---. 2 oracle dba 44 Jun 3 2021 xdb_wallet
drwxr-x---. 2 oracle dba 20 Jun 3 2021 dpdump
drwxr-x---. 2 oracle dba 34 Jun 3 2021 pfile
drwxr-x---. 2 oracle dba 12288 Mar 13 16:08 adump
drwxr-xr-x. 2 oracle dba 6 Mar 13 16:10 wallet

Depuis la version Oracle 12c, il est maintenant possible de stocker le wallet (keystore) sur ASM.

La master key communique alors en base directement afin de pouvoir utiliser la clé de cryptage / décryptage.
On peut schématiser de la façon suivante (attention c’est un vue obsolète depuis Oracle 19c) :

Afin de pouvoir créer et administrer un wallet, on parle maintenant de keystore en 12c, nous utilisons 2 méthodes;
– soit l’utilisation de l’outil Oracle Wallet Manager fourni par Oracle,
– soit passer les commandes directement en base via les syntaxes « ADMINISTER KEY MANAGEMENT ».

Depuis la version 19c, il y a eu quelques modifications quant à la prise en charge du wallet Oracle.
La variable “ENCRYPTION_WALLET_LOCATION” n’est plus utilisée pour définir l’emplacement du répertoire dédié au wallet.

Nous disposons de 2 nouvelles variables qui sont

– “WALLET_ROOT“, répertoire par défaut dédié au wallet Oracle.
– “TDE_CONFIGURATION“, le type d’emplacement pour TDE. Ce paramètre s’appuie sur le paramètre “KEYSTORE_CONFIGURATION“.

Il est possible de définir ces variables, soit dans le SPFILE.ORA de l’instance, soit au niveau du SQLNET.ORA du ORACLE_HOME.

Pour notre cas d’étude, nous choisissons de placer ces paramètres directement dans le spfile.
En effet, cela a pour avantage de pouvoir activer TDE uniquement pour notre base, et non pas les autres bases du même ORACLE_HOME avec l’option SQLNET.ORA.

SQL> ALTER SYSTEM SET WALLET_ROOT = '/opt/app/oracle/admin/CAPDATADB/wallet' scope=spfile;

System altered.

Redémarrer l’instance pour une prise en compte immédiate du paramètre “WALLET_ROOT‘.

Une fois la base redémarrer, configurer la variable “TDE_CONFIGURATION” qui nous sert à spécifier le type de stockage de la clé.
Dans notre exemple, ce sera un fichier.

SQL> ALTER SYSTEM SET TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" scope=both;

System altered.

Vérifier via “show parameters” que ces 2 variables sont prises en compte

SQL> show parameters wallet_root

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
wallet_root                          string      /opt/app/oracle/admin/CAPDATADB/wallet


SQL> show parameters tde_configuration

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
tde_configuration                    string      KEYSTORE_CONFIGURATION=FILE

Création d’un keystore

Pour la création de la ‘master key’, Oracle TDE  s’appuie sur le format Public Key Cryptography Standards (PKCS), soit un fichier PKCS#12 sous une extension *.p12.
On se connecte au serveur de bases de données sous notre environnement Oracle 19c Enterprise Edition.

Le répertoire choisi pour le stockage de la ‘master key’ est ainsi géré automatiquement avec d’une part “WALLET_ROOT” et d’autre part le  “TDE_CONFIGURATION“.

Nous pouvons le vérifier sur cette base en interrogeant la vue “v$encryption_wallet”.

SQL> select WRL_TYPE,WRL_PARAMETER,STATUS,WALLET_TYPE,WALLET_ORDER,FULLY_BACKED_UP from v$encryption_wallet;

WRL_TYPE             WRL_PARAMETER                                      STATUS                         WALLET_TYPE          WALLET_OR  FULLY_BAC
-------------------- -------------------------------------------------- ------------------------------ -------------------- ---------  ---------
FILE                 /opt/app/oracle/admin/CAPDATADB/wallet/tde/        NOT_AVAILABLE                  UNKNOWN              SINGLE     UNDEFINED

Gérer le keystore

On crée le keystore en utilisant comme password celui de SYS par exemple

 SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE IDENTIFIED BY **********;

keystore altered.

On peut voir immédiatement, dans le répertoire représenté par la variable “WALLET_ROOT“, un nouveau répertoire nommé “tde” .

[oracle@ip-172-44-2-141 admin]$ ls -l /opt/app/oracle/admin/CAPDATADB/wallet
total 0
drwxr-x---. 2 oracle dba 25 Mar 13 17:35 tde

Dans ce répertoire “tde”, nous avons bien un fichier *.p12  créé.

[oracle@ip-172-44-2-141 admin]$ ls -lrt /opt/app/oracle/admin/CAPDATADB/wallet/tde
total 4
-rw-------. 1 oracle dba 2555 Mar 13 17:35 ewallet.p12

A partir de la, on peut déclarer notre master key dans la base de données.
La première étape consiste à ouvrir ce nouveau keystore avant de créer la master key.

Celui-ci est pour le moment marquée CLOSED

SQL> select WRL_TYPE,WRL_PARAMETER,STATUS,WALLET_TYPE from v$encryption_wallet;

WRL_TYPE             WRL_PARAMETER                                      STATUS                         WALLET_TYPE
-------------------- -------------------------------------------------- ------------------------------ --------------------
FILE                 /opt/app/oracle/admin/CAPDATADB/wallet/tde/        CLOSED                         UNKNOWN

Nous l’ouvrons via la commande

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY *******;

keystore altered.

Le wallet est ouvert, mais notre clé n’est toujours pas présente, « OPEN_NO_MASTER_KEY »

SQL> select WRL_TYPE,WRL_PARAMETER,STATUS,WALLET_TYPE from v$encryption_wallet;

WRL_TYPE             WRL_PARAMETER                                      STATUS                         WALLET_TYPE
-------------------- -------------------------------------------------- ------------------------------ --------------------
FILE                 /opt/app/oracle/admin/CAPDATADB/wallet/tde/        OPEN_NO_MASTER_KEY             PASSWORD

Cette première étape est primordiale pour la mise en place de TDE dans une base de données.

Afin d’éviter d’avoir à faire cette manipulation à chaque redémarrage de la base, nous avons le choix d’utiliser le mode AUTO LOGIN pour le keystore.

TDE utilisera le « Single Sign-On » afin de valider l’ouverture du keystore pour cette base de données.
En outre, un fichier comportant l’extension .SSO sera alors créé à cet effet dans le répertoire

SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE IDENTIFIED BY *********;

keystore altered.

Vérifier la présence du fichier sso dans le dossier “tde”

[oracle@ip-172-44-2-141 admin]$ ls -lrt /opt/app/oracle/admin/CAPDATADB/wallet/tde
total 8
-rw-------. 1 oracle dba 2555 Mar 13 17:35 ewallet.p12
-rw-------. 1 oracle dba 2600 Mar 13 17:46 cwallet.sso

La master key

La master key est stockée dans le keystore préalablement créé.
Une fois ouvert, le keystore peut accueillir la nouvelle ‘master key‘.

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY ***** WITH BACKUP;

keystore altered.

Pour valider la creation de la master key, on contrôle les vues « v$encryption_wallet » et « v$encryption_keys ».

La première vue indiquera le status OPEN du keystore, ainsi que le type SINGLE SIGN ON pour ce key store.

SQL> select WRL_TYPE,WRL_PARAMETER,STATUS,WALLET_TYPE,WALLET_ORDER,FULLY_BACKED_UP from v$encryption_wallet;

WRL_TYPE             WRL_PARAMETER                                       STATUS                         WALLET_TYPE          WALLET_OR        FULLY_BAC
-------------------- --------------------------------------------------  ------------------------------ -------------------- ---------------- ----------------
FILE                 /opt/app/oracle/admin/CAPDATADB/wallet/tde/         OPEN                           PASSWORD             SINGLE           NO

La seconde requête donne les infos sur la master key, son ID, son type et surtout sa date d’activation. On voit ici que la master key est donc active depuis le 13 mars 2024 à 17h49:

SQL> select KEY_ID,KEYSTORE_TYPE,CREATOR_DBNAME,ACTIVATION_TIME,KEY_USE,ORIGIN from v$encryption_keys;


KEY_ID KEYSTORE_TYPE
------------------------------------------------------------------------------ -----------------
CREATOR_DBNAME
--------------------------------------------------------------------------------------------------------------------------------
ACTIVATION_TIME                                                             KEY_USE    ORIGIN
--------------------------------------------------------------------------- ---------- -----------------------------------------
AVFKfh8jPk/Rv1rHHavj4rsAAAAAAAAAAAAAAAAAAAAAAAAAAAAA SOFTWARE KEYSTORE
CAPDATADB
13-MAR-24 05.49.58.737772 PM +00:00                                         TDE        LOCAL

A noter également que la master key est géré en locale (champs ORIGIN=LOCAL), nous verrons par la suite dans quelle mesure ce champs peut avoir une autre valeur.

Cas d’utilisation de TDE

Chiffrement d’une colonne

Depuis Oracle 10gr2, nous pouvons utiliser Oracle TDE pour chiffrer une colonne (en choisissant l’algo de chiffrement si besoin).

On crée une table pour tester le chiffrement. Nous utilisons l’algorithme AES256.

SQL> connect manu
SQL> create table infos_employes (prenom varchar2(40),  nom varchar2(40),
  adresse varchar2(40) encrypt using 'AES256',
  code_postal number(6) encrypt using 'AES256'); 

Table created.

SQL> insert into infos_employes values ('Emmanuel','Rami','19 rue Crebillon Nantes','44000');

1 row created.

Il s’agit ensuite de vérifier les colonnes chiffrées avec leurs algorithmes utilisés. Le check d’intégrité (checksum) est effectué via SHA-1.

SQL> select * from dba_encrypted_columns;

OWNER           TABLE_NAME           COLUMN_NAME          ENCRYPTION_ALG                SAL INTEGRITY_AL
--------------- -------------------- -------------------- ----------------------------- --- ------------
MANU            INFOS_EMPLOYES       ADRESSE              AES 256 bits key              YES SHA-1
MANU            INFOS_EMPLOYES       CODE_POSTAL          AES 256 bits key              YES SHA-1

Il est possible de modifier une table en ajoutant un chiffrement à une colonne,  ajouter une colonne chiffrée ou même déchiffrée une colonne existante.
On peut également changer l’algo de chiffrement sur une colonne.

Chiffrement d’un tablespace

Depuis la version 11gr1, il est possible d’utiliser TDE sur un tablespace entier. Nous utilisons toujours le même algorihme.

SQL> create tablespace CAPDATA_TBS datafile '/data/oradata/CAPDATADB/capdata_tbs01.dbf' size 100M
  encryption using 'AES256'
  default storage (ENCRYPT);

Tablespace created.

La vue “v$encrypted_tablespace” nous confirme la prise en charge de ce tablespace

SQL> select T.name,E.ENCRYPTIONALG,E.ENCRYPTEDTS,E.MASTERKEYID,E.BLOCKS_ENCRYPTED,E.BLOCKS_DECRYPTED,E.STATUS 
         from v$tablespace t inner join v$encrypted_tablespaces e on (t.ts# = e.tS#);

NAME                           ENCRYPT ENC MASTERKEYID                      BLOCKS_ENCRYPTED BLOCKS_DECRYPTED STATUS
------------------------------ ------- --- -------------------------------- ---------------- ---------------- ----------
CAPDATA_TBS                    AES256  YES 514A7E1F233E4FD1BF5AC71DABE3E2BB 126              0                NORMAL

Comme nous sommes sur un keystore en mode AUTO_LOGIN, un redémarrage de base ne va pas nécessiter une saisie du password du keystore pour l’ouverture.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2147481648 bytes
Fixed Size 8898608 bytes
Variable Size 436207616 bytes
Database Buffers 1694498816 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> select * from manu.infos_employes;

PRENOM                                   NOM                                      ADRESSE                                  CODE_POSTAL
---------------------------------------- ---------------------------------------- ---------------------------------------- -----------
Emmanuel                                 Rami                                     19 rue Crebillon Nantes                  44000

Validation du chiffrement et tests de sécurité

Prenons l’exemple d’une table non chiffrée, que nous appelons “infos_societe”, dans laquelle nous ajoutons un ligne exemple.

SQL> create table infos_societes (nom varchar2(40),
  raison varchar2(40),
  adresse varchar2(40),
  code_postal number(6) ); 

Table created.

SQL> insert into infos_societes values ('Capdata','SA','9 rue de la porte de Buc Versailles','78000');

1 row created.

Ici, nous n’utilisons pas de chiffrement. Il nous est donc possible potentiellement d’aller lire la donnée directement dans le block Oracle via un “dump”.

Tout d’abord, on repère le ROW_ID de notre ligne insérée

SQL> select rowid from manu.infos_societes where NOM='Capdata';

ROWID
------------------
AAASEqAAHAAAAF0AAA

Puis on cherche le numéro de block dans lequel notre ligne est écrite.

SQL> select DBMS_ROWID.ROWID_BLOCK_NUMBER('AAASEqAAHAAAAF0AAA') "Block number" from DUAL;

Block number
------------
372

Ainsi que le FILE_ID du tablespace

SQL> select SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,EXTENT_ID,FILE_ID from dba_extents where SEGMENT_NAME='INFOS_SOCIETES';

SEGMENT_NAME         SEGMENT_TYPE       TABLESPACE_NAME                EXTENT_ID  FILE_ID
-------------------- ------------------ ------------------------------ ---------- ----------
INFOS_SOCIETES       TABLE              USERS                          0          7

Il nous reste donc à effectuer le dump du block en question

 SQL> alter system dump datafile 7 block 372;

System altered.

Puis vérifier la trace généré

[oracle@ip-172-44-2-141 trace]$ ls -rtl
.....
-rw-r-----. 1 oracle dba 964 Mar 14 16:56 CAPDATADB_ora_3063.trm
-rw-r-----. 1 oracle dba 13073 Mar 14 16:56 CAPDATADB_ora_3063.trc

Et si l’édite la trace, nous avons les informations lisibles des données de la ligne

[bach][oracle@ip-172-44-2-141 trace]$ vi CAPDATADB_ora_3063.trc
.......
Block dump from disk:
buffer tsn: 4 rdba: 0x01c00174 (7/372)
scn: 0x2d6b05 seq: 0x01 flg: 0x06 tail: 0x6b050601
frmt: 0x02 chkval: 0x6336 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F5EAFC45000 to 0x00007F5EAFC47000
7F5EAFC45000 0000A206 01C00174 002D6B05 06010000 [....t....k-.....]
7F5EAFC45010 00006336 00000001 0001212A 002D6B04 [6c......*!...k-.]
7F5EAFC45020 00008000 00320002 01C00170 001A0007 [......2.p.......]
7F5EAFC45030 000003DA 010007DD 003700CA 00002001 [..........7.. ..]
7F5EAFC45040 002D6B05 00000000 00000000 00000000 [.k-.............]
7F5EAFC45050 00000000 00000000 00000000 00000000 [................]
7F5EAFC45060 00000000 00010100 0014FFFF 1F4E1F62 [............b.N.]
7F5EAFC45070 00001F4E 1F620001 00000000 00000000 [N.....b.........]
7F5EAFC45080 00000000 00000000 00000000 00000000 [................]
Repeat 499 times
7F5EAFC46FC0 00000000 012C0000 61430704 74616470 [......,...Capdat]
7F5EAFC46FD0 41530261 72203923 64206575 616C2065 [a.SA#9 rue de la]
7F5EAFC46FE0 726F7020 64206574 75422065 65562063 [ porte de Buc Ve]
7F5EAFC46FF0 69617372 73656C6C 5108C303 6B050601 [rsailles...Q...k]
Block header dump: 0x01c00174
end_of_block_dump
End dump data blocks tsn: 4 file#: 7 minblk 372 maxblk 372[/bash]

Reprenons maintenant notre table avec le chiffrement activé à savoir la table “infos_employes”

 SQL> select rowid from manu.infos_employes where NOM='Rami';

ROWID
------------------
AAASEcAAHAAAAFsAAA

Rappelons que les colonnes adresse et code postal sont chiffrées.

SQL> select DBMS_ROWID.ROWID_BLOCK_NUMBER('AAASEcAAHAAAAFsAAA') "Block number" from DUAL;

Block number
------------
364

Le tablespace est le même que la table “infos_societes”. Nous pouvons lancer le dump du block de données.

SQL> alter system dump datafile 7 block 364;

System altered.

Et lors d’une tentative de lecture sur la trace générée

[oracle@ip-172-44-2-141 trace]$ vi CAPDATADB_ora_3131.trc
.....
Block dump from disk:
buffer tsn: 4 rdba: 0x01c0016c (7/364)
scn: 0x2d5c3b seq: 0x01 flg: 0x06 tail: 0x5c3b0601
frmt: 0x02 chkval: 0xc56d type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007FC2BF1F4000 to 0x00007FC2BF1F6000
7FC2BF1F4000 0000A206 01C0016C 002D5C3B 06010000 [....l...;\-.....]
7FC2BF1F4010 0000C56D 00000001 0001211C 002D5BD0 [m........!...[-.]
7FC2BF1F4020 00008000 00320002 01C00168 00110005 [......2.h.......]
7FC2BF1F4030 0000042C 0100019D 000A014D 00002002 [,.......M.... ..]
7FC2BF1F4040 002D5C3B 00000000 00000000 00000000 [;\-.............]
7FC2BF1F4050 00000000 00000000 00000000 00000000 [................]
7FC2BF1F4060 00000000 00020100 0016FFFF 1E6A1E80 [..............j.]
7FC2BF1F4070 00001E6A 1F0D0002 00001E80 00000000 [j...............]
7FC2BF1F4080 00000000 00000000 00000000 00000000 [................]
Repeat 485 times
7FC2BF1F5F60 8EC02273 4486D297 E07C4F05 9C90F57B [s".....D.O|.{...]
7FC2BF1F5F70 04012C44 6D6D4508 65756E61 6152046C [D,...Emmanuel.Ra]
7FC2BF1F5F80 8844696D 7068CF63 36DD3BAB 98AD8A29 [miD.c.hp.;.6)...]
7FC2BF1F5F90 09A57855 59F7EC5E 18DDCBC6 22B4D7D7 [Ux..^..Y......."]
7FC2BF1F5FA0 46B26F31 45302B6F F053AA6D 81ECCB82 [1o.Fo+0Em.S.....]
7FC2BF1F5FB0 F74A0CC2 5735C61A 58C03130 C2BA128F [..J...5W01.X....]
7FC2BF1F5FC0 6193530D 34C00E2B F231A006 9EB73BA2 [.S.a+..4..1..;..]
7FC2BF1F5FD0 DE7291C6 2B5EBC99 02CEA21C E627E11B [..r...^+......'.]
7FC2BF1F5FE0 0620CDFA 810E446B A5D64062 955C5360 [.. .kD..b@..`S\.]
7FC2BF1F5FF0 0092AA85 8215A721 844747EB 5C3B0601 [....!....GG...;\]
Block header dump: 0x01c0016c
 50 67 87 7b f4 69 c8 de e0 a1 49 ad 7e 2e f6 c6 78 e9 56 09 10 ef 78 bd a0
31 d3 10 ba 21 19 8e a3 fe 4d 6b 0d c8 52 a9 7e a5 08 c1 b2 fd 65 a2 ce 87
03 b1 b9 df 03 58 93 e3 32 2c a6 63 19 47 1e ae ff 52
col 3: [52]
ab d0 54 ec ca 0a 6c 64 d5 42 a9 68 ed 3e cb 53 db 11 33 0b 27 38 9b 08 39
50 de 4e a5 9f 39 ea 86 02 f8 73 22 c0 8e 97 d2 86 44 05 4f 7c e0 7b f5 90
9c 44
end_of_block_dump
End dump data blocks tsn: 4 file#: 7 minblk 364 maxblk 364

Nous récupérons le nom et prénom , mais les informations adresse et code postal ne sont pas lisibles directement.

Les restrictions

Oracle TDE comporte également certaines restrictions, ne pouvant fonctionner sous les conditions suivantes.

Il ne sera pas possible d’utiliser le chiffrage sur une table du schéma SYS, ni même chiffrer une colonne avec des type LONG ou LOB.

Pour les colonnes clés primaires ou clés étrangères, il ne sera pas possible d’utiliser le mécanisme SALT. L’option NO SALT sera alors utiliser. Une erreur sera rencontrée si tel est le cas

ORA-28338: cannot encrypt indexed column(s) with salt

Pour chaque donnée chiffrée sur une colonne, il faudra prendre en compte le fait que celle-ci utilise 20 bytes de plus en raison de l’utilisation du check d’intégrité (checksum via SHA-1).

L’utilisation de Oracle TDE n’est réservé qu’aux index de type balancé (B-TREE), de plus, comme le stockage de la donnée est chiffrée, l’index ne peut trouver de correspondance logique, en outre, il ne sera pas possible d’effectuer du « range scan » dans la clause WHERE d’une opération SQL (pas de WHERE T > 1000).
Seuls les prédicats d’égalité sur une valeur sont possibles (WHERE T=1).

Le seul moyen de faire du range scan sur un index est de chiffré le tablespace et que cet index y soit inclus.

TDE ne gère pas non plus les tablespaces de type transportables.

Pour faire un export des données chiffrées , il est indispensable d’utiliser DataPump en passant alors dans la commande expdp le password de la master key :

$ expdp « ‘/ as sysdba’ » DIRECTORY=<DIR>  ENCRYPTION_PASSWORD=*******

Attention également aux performances, les benchmarks que l’on peut trouver concernant TDE indiquent en moyenne des valeurs supérieures de 10 à 35% en terme de consommation CPU.

Pour aller plus loin …

Afin de pouvoir gérer une solution de sécurité globale, Oracle a créé une appliance nommée Oracle Key Vault (OKV).
Cet outil permet de stocker et centraliser des informations relatives à la sécurité d’un parc informatique, celles-ci pouvant correspondre à des wallets mais aussi des keystores java (JKS), des fichiers de type credential ou keystore JCE (java cryptography extension).

Oracle Key vault fournit donc une plateforme de sécurité ou l’on peut centraliser un ensemble de stratégies Oracle TDE au sein d’une entreprise.
Cette appliance centralise l’ensemble des wallets des serveurs d’une entreprise.
Les informations de connexions ainsi que les requêtes de cryptage/decryptage transitent via le réseau entre le Oracle Key Vault et les serveurs bases de données.

L’appliance Oracle Key Vault communique avec différents composants.
Ceux-ci pouvant se caractériser par :

• Des composants Oracle TDE (colonnes ou tablespaces cryptée)
• Des keystores files , ou JCEKS
• La management console, qui servira à l’administration de OKV
• Une appliance backup pour les besoins de sauvegarde de OKV
• Les wallets de connexion et Java keystores

N’hésitez pas à laisser un commentaire.

Continuez votre lecture sur le blog :

• Le chiffrement Oracle : native network encryption (Emmanuel RAMI) [Oracle]
• Oracle 19c : Les partitions hybrides (Emmanuel RAMI) [Oracle]
• Le chiffrement et SQL Server – Episode 1 : Transparent Data Encryption (TDE) vs Always Encrypted (Capdata team) [SQL Server]
• Le chiffrement et SQL Server – Episode 2 : Mise en oeuvre de TDE (Capdata team) [SQL Server]
• Oracle Text pour DBA Oracle : Partie 1 (Capdata team) [Oracle]

L’article Le chiffrement Oracle : Transparent Data Encryption sur Oracle 19c est apparu en premier sur Capdata TECH BLOG.

Pour continuer dans la série “chiffrement et bases de données”, nous allons évoquer le sujet “native network encryption” et “data integrity” dans le cadre d’une connexion client /serveur sur une base de données Oracle 19c.

Présentation

Le mécanisme consiste à chiffrer le trafic réseau entre un client Oracle et la base cible distante. Pour cela, nous allons tester, via des traces, ce que nous pouvons relever dans les informations SQLNet entre le client et le serveur de bases de données.

Attention, point important à remonter, cette fonctionnalité est accessible pour toutes les éditions Oracle et ce pour toutes versions. C’est plutôt une bonne nouvelle quand on connaît le coût d’une licence Entreprise Edition avec “Advanced Security”.

Consulter l’information sur ce lien

Les algorithmes de chiffrement

Pour procéder au chiffrement client/server, Oracle travaille sur une liste d’algorithmes que l’on peut utiliser.

Voici une liste, extraite du site Oracle, pouvant être prise en charge.

Depuis la version 19c, seuls les algorithmes AES (Advanced Encryption Standard) ont été validés. On peut ajouter à cette liste AES192.

Pour utiliser les nouveaux algorithmes AES, Oracle recommande l’installation d’un patch de prise en charge à prendre en compte dans la note 2118136.2. Ceci pour les anciennes versions Oracle.

L’algorithme 3DES peut également être choisi, mais attention aux soucis de performances récemment relevés par Oracle. A n’utiliser qu’avec des systèmes très performants en CPU.

Data Integrity

Il faudra penser à conserver l’intégrité des données, en effectuant un « checksum » sur celles-ci.
Oracle utilise pour cela les algorithmes SHA pour procéder: SHA1, SHA256, SHA384, and SHA512.

Attention, MD5 est encore compatible mais plus recommandé.

Performances

Voici quelques tests de performances effectués permettant d’avoir un retour sur les temps de réponses des
connexions passant à travers le chiffrement. Ces tests ont été faits sur certains algorithmes qui sont dépréciés aujourd’hui.

Un exemple, trouvé sur le net, est le suivant :

“on interroge 100 fois la table des objets d’une base (dba_objects).
On relèvera les temps écoulés pour chacune de ces opérations.”
Chacun des tests est exécuté 3 fois sur les différents algorithmes utilisés avec chacun des checksums.
Ce qui donne le tableau suivant

On considère une base 100% une connexion sans paramètres de chiffrement défini.

Configuration

Afin de mettre en œuvre le chiffrement, nous devrons passer des paramètres dans le fichier “sqlnet.ora”
coté client, et coté serveur.

Les paramètres qui seront à configurer cotés client sont:

SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT
SQLNET.CRYPTO_SEED
SQLNET.ENCRYPTION_TYPES_CLIENT
SQLNET.CRYPTO_CHECKSUM_CLIENT
SQLNET.ENCRYPTION_CLIENT

Au vu des résultats sur les performances et des recommandations Oracle, nous utiliserons, pour le test,
les algorithmes AES256 pour le chiffrement et SHA384 pour le checksum.

A noter, que l’on pourra définir une clé “crypto_seed” pour la connexion, en choisissant une valeur de
10 à 70 caractères alphanumériques.
Pour la partie serveur, les paramètres à définir sont les suivants :

SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER
SQLNET.CRYPTO_SEED
SQLNET.ENCRYPTION_TYPES_SERVER
SQLNET.CRYPTO_CHECKSUM_SERVER
SQLNET.ENCRYPTION_SERVER

Les algorithmes devront être identiques (encryption et crypto_checksum) entre le client et le serveur
pour que la communication se fasse.

Les 4 valeurs que l’on peut choisir pour le paramètre SQLNET.ENCRYPTION coté client et serveur sont les suivantes :

REQUESTED : le chiffrement est demandé mais si ce n'est pas possible, le flux client/serveur se passera via trafic non chiffré.
REJECTED : Pas de chiffrement demandé.
REQUIRED : seul le trafic avec chiffrement sera accepté.
ACCEPTED : le client ou le serveur accepte toute connexion chiffrée ou non chiffrée. Valeur par défaut si "native network encryption" n'est pas activé

Le tableau suivant donnera la matrice de compatibilité des différents modes de négociations utilisés
pour une connexion.

Il est possible, selon les paramètres SQLNET.ENCRYPTION et SQLNET.CRYPTO_CHECKSUM choisis d’activer ou non le chiffrement.

Ce tableau est extrait du site Oracle sur ce lien

Lorsque la connexion échoue, le client reçoit une erreur ORA-12650.
C’est le cas, si nous sommes en REJECTED coté client et REQUIRED coté serveur, par exemple.

Test avec chiffrement actif

Nous testons dans un premier temps une connexion avec chiffrement actif.
Aussi, nous choisissons, coté serveur le mode ACCEPTED.

Puis coté client REQUESTED, ceci pour
l’encryption et le checksum.

En résumé, voici les paramètres que nous définirons dans le sqlnet.ora coté client :

SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT= (SHA384)
SQLNET.CRYPTO_SEED = ‘”i5rrruweotcadsfdsafjkdsfqp5f201p45mxskdlfdasf”‘
SQLNET.ENCRYPTION_TYPES_CLIENT= (AES256)
SQLNET.CRYPTO_CHECKSUM_CLIENT = requested
SQLNET.ENCRYPTION_CLIENT = requested

et le sqlnet.ora coté serveur :

SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER= (SHA384)
SQLNET.CRYPTO_SEED = ‘”4fhfguweotcadsfdsafjkdsfqp5f201p45mxskdlfdasf”‘
SQLNET.ENCRYPTION_TYPES_SERVER= (AES256)
SQLNET.CRYPTO_CHECKSUM_SERVER = accepted
SQLNET.ENCRYPTION_SERVER = accepted

Les opérations se déroulent avec les caractéristiques suivantes

• Pour la partie client, une installation d’Oracle Client 12.2.
• Pour la partie serveur, 1 base CAPDATADB version 19c avec le patchser 19.11 d’avril 2021

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> show user
USER is "MANU"

Voici le contexte de connexion coté base de données. On s’assure ainsi que le chiffrement client/serveur est bien actif

SQL> set pages 3000 linesize 190
SQL> select c.sid, c.serial#, c.network_service_banner,c.client_connection from v$session_connect_info c
2* inner join v$session s on (s.sid=c.sid) and s.username ='MANU';

SID        SERIAL#    NETWORK_SERVICE_BANNER                                                                          CLIENT_CONNEC
---------- ---------- ----------------------------------------------------------------------------------------------- -------------
32         1490       TCP/IP NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production                           Homogeneous
32         1490       Encryption service for Linux: Version 19.0.0.0.0 - Production                                   Homogeneous
32         1490       AES256 Encryption service adapter for Linux: Version 19.0.0.0.0 - Production                    Homogeneous
32         1490       Crypto-checksumming service for Linux: Version 19.0.0.0.0 - Production                          Homogeneous
32         1490       SHA384 Crypto-checksumming service adapter for Linux: Version 19.0.0.0.0 - Production           Homogeneous

L’algorithme AES256 et le checksum SHA384 apparaissent dans les infos de connexions.
On pourra aussi mettre en place une trace sqlnet afin de voir que les données envoyées via le réseau sont bien chiffrées durant cette connexion.

Les paramètres à mettre en place sur le “sqlnet.ora” du client sont les suivants :

TRACE_UNIQUE_CLIENT = on
TRACE_DIRECTORY_CLIENT = /opt/oracle/product/12.2/dbhome_1/network/trace
TRACE_FILE_CLIENT = trace_client
TRACE_LEVEL_CLIENT = 16
DIAG_ADR_ENABLED = OFF

Les traces sont donc générées, coté client, dans le répertoire « /opt/oracle/product/12.2/dbhome_1/network/trace ».

Nous passons le paramètre TRACE_LEVEL_CLIENT à 16 ou niveau SUPPORT afin de récupérer le maximum
d’informations, y compris les trames envoyées vers le serveur.

On teste une connexion, avec une simple interrogation sur la vue d’instance (v$instance).

[oracle@ip-172-44-2-250 ]$ sqlplus manu@CAPDATADB

SQL*Plus: Release 12.2.0.1.0 Production on Wed Feb 28 15:34:23 2024

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Enter password:
Last Successful login time: Wed Feb 28 2024 15:20:13 +00:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> select INSTANCE_NAME,HOST_NAME,VERSION,STATUS,DATABASE_STATUS from v$instance;

INSTANCE_NAME
----------------
HOST_NAME
----------------------------------------------------------------
VERSION           DATABASE_STATUS
----------------- ------------ -----------------
CAPDATADB
ip-172-44-2-141.capdata-aws.fr
19.0.0.0.0        ACTIVE

Une trace est alors générée dans le répertoire $ORACLE_HOME/network/trace. Le pid de notre connexion Oracle est le 5129.

[oracle@ip-172-44-2-250 trace]$ ls -rtl
total 332
-rw-rw----. 1 oracle oinstall 337246 Feb 28 15:36 trace_client_5129.trc

Si l’on observer les packets envoyés par le client, on voit que ceux-ci sont bien chifrrés. La requête
select sur la vue v$instance n’apparait pas en clair, les trames reçus par le serveur sont chiffrés :

On retrouve bien les informations d’encryption dans les trames des packets.

Test sans chiffrement actif

Nous effectuons le même test avec une connexion sans chiffrage actif.

S’il l’on se réfère au tableau de compatibilité client/serveur sur les mode ENCRYPTION, en plaçant les algorithmes d’encryption sur REJECTED coté
client et ACCEPTED coté serveur, nous sommes dans un scénario ou le chiffrement est désactivé.

Les paramètres du sqlnet.ora sont les suivants coté client :

SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT= (SHA384)
SQLNET.CRYPTO_SEED = ‘”i5rrruweotcadsfdsafjkdsfqp5f201p45mxskdlfdasf”‘
SQLNET.ENCRYPTION_TYPES_CLIENT= (AES256)
SQLNET.CRYPTO_CHECKSUM_CLIENT = rejected
SQLNET.ENCRYPTION_CLIENT = rejected

Coté serveur, on ne modifie rien, on laisse à ACCEPTED.

Les opérations se déroulent à l’identique, avec les mêmes environnements que précédemment.

Si l’on interroge les contextes de connexion du user MANU sur la base

SQL> select c.sid, c.serial#, c.network_service_banner,c.client_connection from v$session_connect_info c
2* inner join v$session s on (s.sid=c.sid) and s.username ='MANU'



SID        SERIAL#     NETWORK_SERVICE_BANNER                                                                          CLIENT_CONNEC
---------- -------     ----------------------------------------------------------------------------------------------- -------------
32         24306       TCP/IP NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production                           Homogeneous
32         24306       Encryption service for Linux: Version 19.0.0.0.0 - Production                                   Homogeneous
32         24306       Crypto-checksumming service for Linux: Version 19.0.0.0.0 - Production                          Homogeneous

On voit à présent que les algorithmes AES256 et SHA384 n’apparaissaient pas dans les informations de
connexion.

S’il l’on se réfère à nouveau au fichier trace, on cherche si les informations sont plus « parlantes ».
On réexécute un test de connexion, avec la même requête sur un simple select dans la vue v$instance .

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> set pages 3000 linesize 200
SQL> select INSTANCE_NAME,HOST_NAME,VERSION,STATUS,DATABASE_STATUS from v$instance;

INSTANCE_NAME     HOST_NAME                                                        VERSION           STATUS       DATABASE_STATUS
----------------  ---------------------------------------------------------------- ----------------- ------------ -----------------
CAPDATADB         ip-172-44-2-141.capdata-aws.fr                                   19.0.0.0.0        OPEN         ACTIVE

La trace générée est la suivante , avec le pid 6116 cette fois ci.

[oracle@ip-172-44-2-250 trace]$ ls -lrt
total 568
-rw-rw----. 1 oracle oinstall 337246 Feb 28 15:36 trace_client_5129.trc
-rw-rw----. 1 oracle oinstall 239231 Feb 28 15:58 trace_client_6116.trc

Nous voyons dans la trace que le chiffrement est bien inactif :

Nous disposons mêmes des informations du user connecté, son nom, son programme, mais aussi, la
machine depuis laquelle il est connecté, et le PID attachée à la session (ici 616 ce qui correspond au
nom de la trace généré).

Et surtout, nous retrouvons bien notre requête  select sur la vue from v$instance  dans les packets envoyés au
serveur :

Avec, en clair, le résultat obtenu pour cette requête :

Points d’attention

Selon les besoins en sécurité, nous choisirons d’activer, ou non, le chiffrement sur les connexions client/serveur en fonction du degré de criticité du serveur et de son exposition au réseau.

Etant donné que l’option se place sur le fichier sqlnet.ora, toutes les bases attachés à ce ORACLE_HOME prendront en compte le chiffrement.

Attention également à désactiver les traces coté client, une fois les divers tests terminés.

En effet, les répertoires ‘trace’ dans $ORACLE_HOME/network/trace peuvent vite être saturés.

Dans le sqlnet.ora du client :

TRACE_LEVEL_CLIENT = OFF

 Continuez votre lecture sur le blog :

• Le chiffrement et SQL Server – Episode 2 : Mise en oeuvre de TDE (Capdata team) [SQL Server]
• Le chiffrement Oracle : Transparent Data Encryption sur Oracle 19c (Emmanuel RAMI) [Oracle]
• Le chiffrement et SQL Server – Episode 1 : Transparent Data Encryption (TDE) vs Always Encrypted (Capdata team) [SQL Server]
• Se connecter à SQL Server à travers Oracle, quelle drôle d’idée ? (Capdata team) [OracleVintage]
• Le chiffrement et SQL Server – Episode 3 : Always Encrypted (Capdata team) [AzureSQL Server]

L’article Le chiffrement Oracle : native network encryption est apparu en premier sur Capdata TECH BLOG.

Dans l’ère numérique actuelle, où la sécurité des données occupe une place centrale, la pseudonymisation émerge comme une stratégie cruciale pour renforcer la confidentialité des informations stockées dans les bases de données. Cette approche, bien que semblable à l’anonymisation, se distingue par son objectif spécifique de préserver l’utilité des données tout en masquant l’identité réelle des individus. Dans le contexte de PostgreSQL, la pseudonymisation offre un équilibre délicat entre protection des renseignements sensibles et préservation de la fonctionnalité des données.

Principe de la Pseudonymisation :

La pseudonymisation implique la substitution des données réelles par des données fictives, mais conservant leur structure originale. Contrairement à l’anonymisation, qui supprime complètement toute référence à l’identité d’un individu, la pseudonymisation permet la réversibilité du processus. Ainsi, les données restent utilisables à des fins légitimes : l’analyse statistique, ou la réalisation de tests, tout en garantissant la protection des informations confidentielles.

L’utilité fondamentale de la pseudonymisation réside dans sa capacité à concilier deux impératifs apparemment contradictoires : la protection de la vie privée des individus et la nécessité d’accéder et de traiter des données. Dans un paysage où les fuites de données et les violations de la vie privée sont de plus en plus fréquentes, la pseudonymisation devient une réponse pragmatique aux exigences de conformité réglementaire tout en préservant la valeur analytique des données.

Souvent confondue avec la pseudonymisation, l’anonymisation diffère par son caractère irréversible. Alors que l’anonymisation supprime toute possibilité de relier des données à une identité spécifique, la pseudonymisation offre une réversibilité calculée, permettant une utilisation future des données tout en maintenant un niveau élevé de sécurité. Cette distinction cruciale souligne l’importance de choisir la méthode la plus appropriée en fonction des besoins spécifiques de sécurité et des objectifs opérationnels.

Etat de la pseudonymisation actuellement sur PostgreSQL :

Le seul outil actuellement disponible sur le marché permettant de réaliser une pseudonymisation sur PostgreSQL est une extension. Nous l’avions déjà évoquée lors d’un précédent article : PostgreSQL Anonymizer. Pour rappel, cette extension est développée par Dalibo, et contient également de nombreuses options d’anonymisation.

Dans cet article, nous allons rappeler l’installation de cette extension puis nous la verrons à l’œuvre dans quelques exemples de pseudonymisation de données.

Etape 1 : Installation

La machine choisie pour mon test est une Ubuntu. Il n’y a pas de package prêt à l’emploi sur ce système d’exploitation. Nous devons donc réaliser nous même la compilation de l’extension pour qu’elle puisse fonctionner. Nous avons au préalable installé une version 15 de PostgreSQL sur notre machine.

Nous commençons par installer les outils de développement de PostgreSQL :

root@sarah:~# sudo apt-get install postgresql-server-dev-15
Reading package lists... Done
Building dependency tree
Reading state information... Done
...
Setting up postgresql-server-dev-15 (13.7-1.pgdg18.04+1) ...
Processing triggers for man-db (2.8.3-2ubuntu0.1) ...
Processing triggers for mime-support (3.60ubuntu1) ...
Processing triggers for ureadahead (0.100.0-21) ...
Processing triggers for install-info (6.5.0.dfsg.1-2) ...
Processing triggers for libc-bin (2.27-3ubuntu1.5) ...
Processing triggers for systemd (237-3ubuntu10.53)

Puis on récupère depuis le git de Dalibo les sources à la dernière version disponible :

root@sarah:~# git clone <a href="https://gitlab.com/dalibo/postgresql_anonymizer.git">https://gitlab.com/dalibo/postgresql_anonymizer.git</a>
Cloning into 'postgresql_anonymizer'...
remote: Enumerating objects: 5145, done.
remote: Counting objects: 100% (487/487), done.
remote: Compressing objects: 100% (271/271), done.
remote: Total 5145 (delta 327), reused 277 (delta 216), pack-reused 4658
Receiving objects: 100% (5145/5145), 25.71 MiB | 19.23 MiB/s, done.
Resolving deltas: 100% (3304/3304), done. 

Une fois les sources récupérées, on se positionne dans le répertoire créé par Git ou les sources ont été déposées et on fait un make extension :

root@sarah:~/postgresql_anonymizer# make extension 
mkdir -p anon
cp anon.sql anon/anon--1.1.0.sql
cp data/*.csv anon/
cp python/populate.py anon/ 

Et enfin un Make install pour installer le tout. Il est important de préciser qu’il est nécessaire que vous ayez installé gcc pour pouvoir compiler les sources de l’extension (apt install gcc) :

root@sarah:~/postgresql_anonymizer# sudo make install 
cp anon.sql anon/anon--1.1.0.sql
cp data/*.csv anon/
cp python/populate.py anon/
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing- format-attribute -Wimplicit-fallthrough=3 -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno- format-truncation -g -g -O2 -fstack-protector-strong -Wformat -Werror= format-security -fno-omit-frame-pointer -fPIC -Wno-unused-variable -I. -I./ -I/usr/include/postgresql/13/server -I/usr/include/postgresql/internal    -Wdate- time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2     -c -o anon.o anon.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing- format-attribute -Wimplicit-fallthrough=3 -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno- format-truncation -g -g -O2 -fstack-protector-strong -Wformat -Werror= format-security -fno-omit-frame-pointer -fPIC -Wno-unused-variable anon.o -L/usr/lib/x86_64-linux-gnu -Wl,-Bsymbolic-functions -Wl,-z,relro -Wl,-z,now -L/usr/lib/llvm-6.0/lib    -Wl,--as-needed  -shared -o anon.so
/usr/bin/clang-6.0 -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -Wno-unused- command-line-argument -O2  -I. -I./ -I/usr/include/postgresql/13/server -I/usr/include/postgresql/internal    -Wdate- time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2    -flto=thin -emit-llvm -c -o anon. bc anon.c
/bin/mkdir -p  '/usr/share/postgresql/15/extension'
/bin/mkdir -p  '/usr/share/postgresql/15/extension/anon'
/bin/mkdir -p  '/usr/lib/postgresql/15/lib'
install -d /usr/lib/postgresql/15/bin
install -m 0755 bin/pg_dump_anon.sh /usr/lib/postgresql/15/bin
/usr/bin/install -c -m 644 .//anon.control  '/usr/share/postgresql/15/extension/'
/usr/bin/install -c -m 644 .//anon/*   '/usr/share/postgresql/15/extension/anon/'
/usr/bin/install -c -m 755  anon.so  '/usr/lib/postgresql/15/lib/'
/bin/mkdir -p  '/usr/lib/postgresql/15/lib/bitcode/anon'
/bin/mkdir -p  '/usr/lib/postgresql/15/lib/bitcode'/anon/
/usr/bin/install -c -m 644 anon. bc '/usr/lib/postgresql/15/lib/bitcode'/anon/./
cd '/usr/lib/postgresql/15/lib/bitcode' && /usr/lib/llvm-6.0/bin/llvm-lto -thinlto -thinlto-action=thinlink -o anon.index. bc anon/anon. bc

Pour ce test, j’ai importé la base de données exemple dvdrental de PostgreSQL. Elle me permettra d’illustrer la pseudonymisation facilement et sur une quantité respectable de données. Pour importer cette base de données, rien de plus simple :

On la récupère en la téléchargeant sur le site d’hébergement :

postgres@sarah:~$ wget https://www.postgresqltutorial.com/wp-content/uploads/2019/05/dvdrental.zip

--2023-11-29 13:42:52-- https://www.postgresqltutorial.com/wp-content/uploads/2019/05/dvdrental.zip
Resolving www.postgresqltutorial.com (www.postgresqltutorial.com)... 104.21.2.174, 172.67.129.129, 2606:4700:3037::6815:2ae, ...
Connecting to www.postgresqltutorial.com (www.postgresqltutorial.com)|104.21.2.174|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 550906 (538K) [application/zip]
Saving to: ‘dvdrental.zip’

dvdrental.zip 100%[========================================================================================================================================>] 537.99K --.-KB/s in 0.01s

2023-11-29 13:42:52 (45.5 MB/s) - ‘dvdrental.zip’ saved [550906/550906] 

On la dézippe ensuite :

postgres@sarah:~$ unzip dvdrental.zip
Archive: dvdrental.zip
inflating: dvdrental.tar

On obtient ainsi une archive qu’on peut utiliser avec un pg_restore pour charger la base de données. Je me suis d’abbord connectée sur l’instance pour créer une base de données que j’ai appelé dvdrental:

postgres@ip-172-44-2-72:~$ psql
psql (15.5 (Ubuntu 15.5-1.pgdg22.04+1))
Type "help" for help.

postgres=# create database dvdrental;
CREATE DATABASE

Puis j’ai restauré la base :

postgres@ip-172-44-2-72:~$ pg_restore -U postgres -d dvdrental dvdrental.tar

Nous sommes donc prêts à commencer.

Il existe un petit nombre de fonctions de pseudonymisation dans PostgreSQL anonymiser. Elles fonctionnent comme les fonctions d’anonymisation et peuvent être utilisée en masquage statique ou en masquage dynamique.

Les fonctions disponibles sont les suivantes :

• anon.pseudo_first_name('seed','salt')qui retourne un prénom générique
• anon.pseudo_last_name('seed','salt') qui retourne un nom de famille générique
• anon.pseudo_email('seed','salt') qui retourne une adresse email générique et existante
• anon.pseudo_city('seed','salt') qui retourne le nom d’une ville existante
• anon.pseudo_country('seed','salt') qui retourne un pays existant
• anon.pseudo_company('seed','salt') qui retourne un nom de société générique
• anon.pseudo_iban('seed','salt') qui retourne un IBAN valide
• anon.pseudo_siret('seed','salt') qui retourne un SIRET valide

Il est important de définir un salt différent pour chaque base de données. Si un utilisateur quelconque trouve le salt de votre base de données, il sera ensuite capable de procéder à une attaque par force brute sur toutes les autres données afin de les révéler.

dvdrental=# alter database dvdrental set anon.salt = 'This_is_a_salt';

Pour prendre un exemple, nous pouvons tenter de pseudonymiser les données des clients de notre base dvdrental :

dvdrental=# select * from customer limit 5;
customer_id | store_id | first_name | last_name | email | address_id | activebool | create_date | last_update | active
-------------+----------+------------+-----------+-------------------------------------+------------+------------+-------------+-------------------------+--------
524 | 1 | Jared | Ely | jared.ely@sakilacustomer.org | 530 | t | 2006-02-14 | 2013-05-26 14:49:45.738 | 1
1 | 1 | Mary | Smith | mary.smith@sakilacustomer.org | 5 | t | 2006-02-14 | 2013-05-26 14:49:45.738 | 1
2 | 1 | Patricia | Johnson | patricia.johnson@sakilacustomer.org | 6 | t | 2006-02-14 | 2013-05-26 14:49:45.738 | 1
3 | 1 | Linda | Williams | linda.williams@sakilacustomer.org | 7 | t | 2006-02-14 | 2013-05-26 14:49:45.738 | 1
4 | 2 | Barbara | Jones | barbara.jones@sakilacustomer.org | 8 | t | 2006-02-14 | 2013-05-26 14:49:45.738 | 1
(5 rows)

Lors d’un premier essai, en suivant la documentation officielle disponible ici, j’ai utilisé la fonction suivante :

dvdrental=# SECURITY LABEL FOR anon ON COLUMN customer.first_name is 'MASKED WITH FUNCTION anon.pseudo_first_name('seed', 'salt')';
SECURITY LABEL

Au moment d’utiliser le masquage pour pseudonymiser mes données, je suis tombée sur l’erreur suivante :

dvdrental=# SECURITY LABEL FOR anon ON COLUMN customer.first_name IS 'MASKED WITH FUNCTION anon.pseudo_first_name('seed','salt')';
ERROR: syntax error at or near "seed" LINE 2: IS 'MASKED WITH FUNCTION anon.pseudo_first_name('seed','salt...

Il faut le savoir, car ce n’est pas forcément bien explicité dans la documentation : le simple guillemet ne permet pas d’échapper correctement les caractères. Il est donc nécessaire d’ajouter d’autres caractères d’échappement. Dans ce cas, j’ai choisi d’utiliser le symbole “$”.
Ainsi, au deuxième essai, cela m’a donné :

dvdrental=# SECURITY LABEL FOR anon ON COLUMN customer.first_name is 'MASKED WITH FUNCTION anon.pseudo_first_name($$seed$$, $$salt$$)';
SECURITY LABEL

Ca avait l’air de fonctionner, j’ai donc lancé mon anonymisation pour vérifier :

dvdrental=# SELECT anon.anonymize_database();
DEBUG: Anonymize table public.customer with first_name = anon.pseudo_first_name($$seed$$)
ERROR: could not determine polymorphic type because input has type unknown
CONTEXT: SQL statement "UPDATE public.customer SET first_name = anon.pseudo_first_name($$seed$$)"
PL/pgSQL function anon.anonymize_table(regclass) line 38 at EXECUTE
SQL function "anonymize_database" statement 1 

Il s’avère que la documentation ne précise pas qu’il faut typer les deux champs qu’on utilise pour notre fonction, sinon PostgreSQL ne sait pas quoi en faire. Cela donne donc :

dvdrental=# SECURITY LABEL FOR anon ON COLUMN customer.first_name is 'MASKED WITH FUNCTION anon.pseudo_first_name(CAST($$seed$$ as text), cast($$salt$$ as text))';
SECURITY LABEL

Et on applique ensuite les différentes fonctions :

dvdrental=# SELECT anon.anonymize_database();
DEBUG: Anonymize table public.customer with first_name = anon.pseudo_first_name(CAST($$seed$$ as text), cast($$salt$$ as text))
anonymize_database
--------------------
t
(1 row)

On va ensuite vérifier nos données dans la table pour voir si cela a été appliqué :

dvdrental=# select * from customer limit 5;
customer_id | store_id | first_name | last_name | email | address_id | activebool | create_date | last_update | active
-------------+----------+------------+-----------+-----------------------------------+------------+------------+-------------+----------------------------+--------
524 | 1 | Taniya | Ely | jared.ely@sakilacustomer.org | 530 | t | 2006-02-14 | 2024-01-29 09:26:48.268084 | 1
15 | 1 | Taniya | Harris | helen.harris@sakilacustomer.org | 19 | t | 2006-02-14 | 2024-01-29 09:26:48.268084 | 1
16 | 2 | Taniya | Martin | sandra.martin@sakilacustomer.org | 20 | t | 2006-02-14 | 2024-01-29 09:26:48.268084 | 0
17 | 1 | Taniya | Thompson | donna.thompson@sakilacustomer.org | 21 | t | 2006-02-14 | 2024-01-29 09:26:48.268084 | 1
18 | 2 | Taniya | Garcia | carol.garcia@sakilacustomer.org | 22 | t | 2006-02-14 | 2024-01-29 09:26:48.268084 | 1
(5 rows)

On se rend alors compte que toutes les données pseudonymisées l’ont été avec le même résultat.

dvdrental=# select distinct first_name from customer;
first_name
------------
Taniya
(1 row)

Il est en effet précisé dans la documentation que si on voulait obtenir des pseudo différents pour chaque ligne, il fallait ajouter un custom dataset d’un nombre supérieur de ligne au nombres d’entrées dans notre table.

Il existe un dataset fournit par dalibo (en français uniquement), disponible à l’adresse suivante .

Pour pouvoir l’intégrer dans votre extension, il vous faut le télécharger sur votre serveur, le placer dans le dossier de votre choix, et utiliser la commande :

dvdrental=#SELECT anon.init('/path/to/custom_csv_files/')

Vous pouvez également créer votre propre dataset sous la forme d’un fichier csv avec un script par exemple, pour peupler vos exemples avec vos propres données personnalisées.

On peut tenter de réaliser la même opération mais pour une autre donnée. On voit en effet que notre adresse email contient toujours les noms de familles des personnes, ils n’ont pas été pseudonymisés. On va donc changer cela :

dvdrental=# security label for anon on column customer.email is 'MASKED WITH FUNCTION anon.pseudo_email(CAST($$seed$$ as text), cast($$salt$$ as text))';
SECURITY LABEL 

Et une fois appliqué on obtient les données suivantes :

dvdrental=# select * from customer limit 5;
customer_id | store_id | first_name | last_name | email | address_id | activebool | create_date | last_update | active
-------------+----------+------------+-----------+----------------------------+------------+------------+-------------+----------------------------+--------
524 | 1 | Taniya | Ely | rowesally@kelly-dorsey.com | 530 | t | 2006-02-14 | 2024-01-29 09:42:03.560633 | 1
235 | 1 | Taniya | Lynch | rowesally@kelly-dorsey.com | 239 | t | 2006-02-14 | 2024-01-29 09:42:03.560633 | 1
15 | 1 | Taniya | Harris | rowesally@kelly-dorsey.com | 19 | t | 2006-02-14 | 2024-01-29 09:42:03.560633 | 1
16 | 2 | Taniya | Martin | rowesally@kelly-dorsey.com | 20 | t | 2006-02-14 | 2024-01-29 09:42:03.560633 | 0
17 | 1 | Taniya | Thompson | rowesally@kelly-dorsey.com | 21 | t | 2006-02-14 | 2024-01-29 09:42:03.560633 | 1
(5 rows)

Les limites de la pseudonymisation Postgres :

Actuellement il n’existe qu’une seule extension permettant de faire de la pseudonymisation sur PostgreSQL. Et elle présente plusieurs limites :

1. Les fonctions de pseudonymisation de postgresql anonymizer sont déterministes. C’est à dire que pour deux valeurs identiques, elles donneront toujours le même résultat.
2. Les données pseudonymisée restent des données personnes ! Le RGPD précise très clairement que : « les données à caractère personnel qui ont fait l’objet d’une pseudonymisation […] devraient être considérées comme des informations concernant une personne physique identifiable. »
3. Les coûts en performance, comme évoqué dans mon précédent article pour cette même extension, sont élevés. C’est quelque chose à considérer quand on veut utiliser cette méthode.
4. Il faut pouvoir gérer correctement les clés qu’on utilises pour la pseudonymisation, c’est une charge supplémentaire.
5. La documentation n’est pas forcément tout à fait bien mise à jour, et certaines choses ne sont pas évidentes à appréhender.
6. L’extension est toujours en développement et en changements constants, nous ne sommes pas à l’abris de rencontrer des bugs au fil des versions. Pour écrire cet article, j’ai du contacter directement le développeur car au début de sa rédaction, les fonctions de pseudonymisation ne fonctionnaient pas.

Conclusion :

En conclusion, la pseudonymisation des données dans PostgreSQL se révèle être une stratégie de protection de la vie privée et de conformité réglementaire particulièrement robuste. En adoptant cette approche, les entreprises peuvent tirer parti des avantages significatifs tels que la préservation de la confidentialité des données tout en permettant l’analyse et le traitement des informations sensibles.

D’un côté, la pseudonymisation offre une solution efficace pour équilibrer la nécessité d’accéder aux données avec le respect des réglementations de confidentialité. Les données pseudonymisées demeurent utiles pour les analyses internes tout en limitant le risque d’exploitation malveillante.

Cependant, il est crucial de reconnaître que la pseudonymisation n’est pas une panacée. Elle peut présenter des défis en termes de gestion des clés de pseudonymisation, de complexité accrue dans la maintenance des bases de données, et de potentielles vulnérabilités si elle est mal mise en œuvre.

Dans une perspective plus large, il est également pertinent de considérer l’anonymisation des données comme une alternative. Bien que l’anonymisation puisse offrir un niveau supérieur de protection, elle peut également rendre les données moins utiles pour certaines applications, limitant ainsi leur valeur pour les analyses internes.

En définitive, le choix entre la pseudonymisation et l’anonymisation dépend des besoins spécifiques de chaque organisation, du contexte réglementaire et des compromis acceptables entre la protection de la vie privée et l’utilité des données. En élaborant une stratégie de gestion des données judicieuse, les entreprises peuvent naviguer avec succès dans le paysage complexe de la confidentialité des données dans PostgreSQL.Continuez votre lecture sur le blog :

• PostgreSQL Anonymizer (Sarah FAVEERE) [PostgreSQL]
• pg_recursively_delete : Simplifier les suppressions récursives (Sarah FAVEERE) [PostgreSQL]
• pg_dirtyread où comment réparer facilement un delete sauvage (Sarah FAVEERE) [PostgreSQL]
• PostgreSQL : planifier une tâche avec pg_cron (Emmanuel RAMI) [Non classéPostgreSQL]
• HypoPG et les index hypothétiques (Sarah FAVEERE) [PostgreSQL]

L’article Pseudonymiser vos bases PostgreSQL est apparu en premier sur Capdata TECH BLOG.

  Bonjour à toutes et tous et bonne année 2024 !

Nous débutons cette nouvelle année avec un article, une fois de plus, en lien avec la sécurité ! C’est un sujet que nous avons grandement abordé au cours de l’année 2023, sur les SGBD SQL Server et PostgreSQL.
Pour débuter 2024 dans la même voie, je vous propose une présentation de la brique SQL Firewall présente dans la version Oracle 23c.

Pour qualifier et tester les nouveautés Oracle 23c, nous avons la possibilité de télécharger et installer l’édition FREE Edition qui, de plus, est utilisable “on prem”.

Ce tableau, fourni par Oracle, nous montre les différentes offres présentes :

Vous remarquerez qu’il n’y a, à ce jour, ni Standard Edition 2, ni offre Enterprise Edition on prem.

Une note Oracle indique cependant une éventuelle prise en charge de cette version dès le premier semestre 2024 sous Linux x86-64

Release Schedule of Current Database Releases (Doc ID 742060.1)

Nous suivrons plus précisément les informations à ce sujet au cour de ce début d’année.

Installation Oracle 23c FREE Edition

Nous disposons d’une VM EC2 de type Rocky Linux 8.6 pour tester la nouvelle version Oracle 23c

[oracle@ etc]$ cat os-release
NAME="Rocky Linux"
VERSION="8.6 (Green Obsidian)"
ID="rocky"
ID_LIKE="rhel centos fedora"
VERSION_ID="8.6"
PLATFORM_ID="platform:el8"
PRETTY_NAME="Rocky Linux 8.6 (Green Obsidian)"
ANSI_COLOR="0;32"
CPE_NAME="cpe:/o:rocky:rocky:8:GA"
HOME_URL="https://rockylinux.org/"
BUG_REPORT_URL="https://bugs.rockylinux.org/"
ROCKY_SUPPORT_PRODUCT="Rocky Linux"
ROCKY_SUPPORT_PRODUCT_VERSION="8"
REDHAT_SUPPORT_PRODUCT="Rocky Linux"
REDHAT_SUPPORT_PRODUCT_VERSION="8"

L’installation de la version Oracle 23c Free Edition sur un fork Red Hat est on ne peut plus simple :

• Télécharger le package rpm de preinstallation ->  oracle-database-preinstall-23c-1.0-1.el8.x86_64.rpm
• Télécharger le package rpm d’installation du moteur Oracle 23c -> oracle-database-free-23c-1.0-1.el8.x86_64.rpm

Une fois téléchargés et copiés vers le serveur linux, passer à l’installation via rpm sous “root”

— Le package de preinstallation permettant la configuration OS propre à Oracle.

[root@ ~]# rpm -iv /tmp/oracle-database-preinstall-23c-1.0-0.5.el8.x86_64.rpm
warning: /tmp/oracle-database-preinstall-23c-1.0-0.5.el8.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ad986da3: NOKEY
Verifying packages...
Preparing packages...
oracle-database-preinstall-23c-1.0-0.5.el8.x86_64

— Puis le package d’installation du moteur Oracle 23c. Attention, l’installation va se faire dans un répertoire “/opt”. Prévoir une place d’au moins 8Go sur ce montage.

[root@ ~]# rpm -iv /tmp/oracle-database-free-23c-1.0-1.el8.x86_64.rpm
warning: /tmp/oracle-database-free-23c-1.0-1.el8.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ad986da3: NOKEY
Verifying packages...
Preparing packages...
oracle-database-free-23c-1.0-1.x86_64
[INFO] Executing post installation scripts...
[INFO] Oracle home installed successfully and ready to be configured.
To configure Oracle Database Free, optionally modify the parameters in '/etc/sysconfig/oracle-free-23c.conf' and then run '/etc/init.d/oracle-free-23c configure' as root.

Valider l’installation des packages

[root@ ~]# rpm -qav | grep -i oracle
oracle-database-preinstall-23c-1.0-0.5.el8.x86_64
oracle-database-free-23c-1.0-1.x86_64

[root@ ~]# ls /opt/oracle/product/23c/dbhomeFree/
addnode clone ctx deinstall env.ora instantclient jdk LICENSE nls OPatch ord plsql R root.sh slax sqlplus usm
assistants crs cv demo has inventory jlib md odbc opmn oss precomp racg runInstaller sqlcl srvm utl
bin crypto data diagnostics hs javavm ldap mgw olap oracore oui python rdbms schagent.conf sqlj ss_oracle.sdo.acl xdk
cfgtoollogs css dbs dv install jdbc lib network oml4py oraInst.loc perl QOpatch relnotes sdk sqlpatch ucp

[root@ ~]# /opt/oracle/product/23c/dbhomeFree/root.sh
Check /opt/oracle/product/23c/dbhomeFree/install/root_************_2023-12-20_11-38-30-060078626.log for the output of root script

— Comme indiqué en fin d’installation, lancer ‘/etc/init.d/oracle-free-23c configure’ sous “root” pour créer une nouvelle instance. Le script est interactif et vous serez amener à saisir certaines informations comme le mot de passe SYS/SYSTEM et PDB_ADMIN.

[root@~]# /etc/init.d/oracle-free-23c configure
Specify a password to be used for database accounts. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9]. Note that the same password will be used for SYS, SYSTEM and PDBADMIN accounts:
Confirm the password:
Configuring Oracle Listener.
Listener configuration succeeded.
Configuring Oracle Database FREE.
Enter SYS user password:
************
Enter SYSTEM user password:
**************
Enter PDBADMIN User Password:
************
Prepare for db operation
7% complete
Copying database files
29% complete
Creating and starting Oracle instance
30% complete
33% complete
36% complete
39% complete
[WARNING] ORA-20002: Directory creation failed
ORA-06512: at "SYS.DBMS_QOPATCH", line 1644
ORA-06512: at "SYS.DBMS_QOPATCH", line 1521
ORA-06512: at line 1

43% complete
Completing Database Creation
47% complete
49% complete
50% complete
Creating Pluggable Databases
54% complete
71% complete
Executing Post Configuration Actions
93% complete
Running Custom Scripts
100% complete
Database creation complete. For details check the logfiles at:
/opt/oracle/cfgtoollogs/dbca/FREE.
Database Information:
Global Database Name:FREE
System Identifier(SID):FREE
Look at the log file "/opt/oracle/cfgtoollogs/dbca/FREE/FREE1.log" for further details.

Database configuration failed. Check logs under '/opt/oracle/cfgtoollogs/dbca'.

Ne pas tenir compte de l’erreur lors de la création du DIRECTORY Oracle, celui ci pointe vers un répertoire inexistant sur la machine. Il nous sera possible d’en créer un ultérieurement.

Présentation SQL Firewall

Comme son nom l’indique , Oracle 23c SQL Firewall est un firewall applicatif qui, au delà d’un firewall web classique (WAF), est capable d’interpréter le code SQL en entrée directement en base.
Comme le montre l’image extraite de la documentation Oracle officielle, le processus passe par une phase d’apprentissage des différentes requêtes envoyées à la base de données.

Une liste des requêtes dites “autorisées” doit être générée afin de valider les opérations business officielles d’une production classique au cour de la journée.
Ceci peut bien évidemment être facilité par un éditeur qui connait parfaitement son application, et donc son modèle conceptuel de données et les requêtes SQL qui en découlent.

Une fois cette phase d’apprentissage établie, nous allons pouvoir valider les requêtes capturées  et dresser la liste “verte” des requêtes autorisées.

C’est à partir de la que l’on pourra potentiellement, empêcher tout autre code SQL de passer en base.
Le filtrage peut se faire selon plusieurs contextes à savoir, un utilisateur en particulier, une adresse IP où bien un programme.

Les prérequis pour Oracle 23c SQL Firewall

Rappelons que depuis la version 21c, Oracle nous impose la gestion du multitenant avec la possibilité de créer 3 PDBs gratuitement.
L’instance FREE comporte donc bien une PDB exploitable en lecture/écriture

[oracle@ ~]$ . oraenv
ORACLE_SID = [oracle] ? FREE
The Oracle base has been set to /opt/oracle
[oracle@ ~]$ sqlplus / as sysdba

SQL*Plus: Release 23.0.0.0.0 - Production on Wed Dec 20 11:46:08 2023
Version 23.3.0.23.09

Copyright (c) 1982, 2023, Oracle. All rights reserved.

Connected to:
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09

SQL> show pdbs;

CON_ID     CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
2          PDB$SEED                       READ ONLY  NO
3          FREEPDB1                       READ WRITE NO

Il sera donc nécessaire, pour se connecter à la PDB FREEPPDB1 depuis un compte applicatif, d’ajouter une entrée dans le tnsnames.

FREEPDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ip-********.eu-west-3.compute.internal)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = FREEPDB1)
)
)

Configuration des données.

Pour notre cas pratique, nous créons 3 utilisateurs Oracle dans la PDB. 1 utilisateur administrateur du Firewall, 1 utilisateur propriétaire des objets et 1 utilisateur applicatif utilisant des ordres DML sur les objets. A noter le rôle “sql_firewall_admin” pour le compte “FW_ADMIN”.

SQL> alter session set container = FREEPDB1;

SQL> create user FW_ADMIN identified by passwdfwadmin;
SQL> grant create session, sql_firewall_admin, audit_admin to FW_ADMIN;
SQL> create user application_owner identified by passwdappliowner default tablespace USERS quota unlimited on USERS;
SQL> grant create session, create table, create view, create procedure, create synonym to application_owner;
SQL> create user application identified by passwdappli default tablespace USERS quota unlimited on USERS;
SQL> grant create session to application;
SQL> grant select any table on schema application_owner to application;

Création des objets et du jeu de données

SQL>  create table salaries ( id number(15), name VARCHAR2(128), address VARCHAR2(400), date_entrée VARCHAR2(128));
SQL>  create table entreprise (ent_id VARCHAR2(128), raison_sociale varchar2(20),taille_salaries number(5));

SQL>  insert into salaries values (154484, 'Manuel', '14 rue voltaire', '16/12/2020');
SQL>  insert into salaries values (275558, 'Jack', '24 rue du départ', '12/10/2005');
SQL>  insert into salaries values (285548, 'Cyril', '27 avenue Pasteur', '01/02/2006');
SQL>  insert into salaries values (472245, 'Thomas', '12 avenue principale', '15/02/2021');

SQL>  insert into entreprise values (12232,'SARL',1200);
SQL>  insert into entreprise values (13456,'SARL', 500);
SQL>  insert into entreprise values (22522,'SA',288);
SQL>  insert into entreprise values (25485,'SA', 144);
SQL>  insert into entreprise values (31411,'SA',524);
SQL>  insert into entreprise values (36879,'SARL', 56);
SQL>  insert into entreprise values (40125,'EURL', 120);
SQL>  insert into entreprise values (44588, 'SA', 2510);

SQL>  create or replace view application_owner.somme_salaries_sa
as
select raison_sociale, sum(taille_salaries) as "Somme_SA"
from application_owner.entreprise
where raison_sociale='SA'
group by raison_sociale;

SQL>  create or replace procedure maj_salarie_addresse (id number, address varchar2)
is
req varchar2(1000);
begin
req := 'BEGIN UPDATE salaries SET address = ''' || address || ''' WHERE id = ''' || id || '''; COMMIT; END;';
DBMS_OUTPUT.PUT_LINE('Query: ' || req);
execute immediate req;
end;
/

SQL>  CREATE OR REPLACE PROCEDURE maj_entreprise_salaries ( id number, taille_salaries number)
IS
req VARCHAR2(1000);
BEGIN

req := 'BEGIN UPDATE entreprise SET taille_salaries =''' || taille_salaries || ''' WHERE ent_id = ''' || id || '''; COMMIT; END;';
DBMS_OUTPUT.PUT_LINE('Query: ' || req);
EXECUTE IMMEDIATE req;
END;
/

SQL>  grant execute on application_owner.maj_salarie_addresse to application;
SQL>  grant execute on application_owner.maj_entreprise_salaries to application;
SQL>  grant select on application_owner.somme_salaries_sa to application;
SQL>  grant insert, update, delete on application_owner.salaries to application;
SQL>  grant insert, update, delete on application_owner.entreprise to application;
SQL>  create public synonym somme_salaries_sa for application_owner.somme_salaries_sa;
SQL>  create public synonym maj_salarie_addresse for application_owner.maj_salarie_addresse;
SQL>  create public synonym maj_entreprise_salaries for application_owner.maj_entreprise_salaries;

Capture des requêtes dans Oracle 23c SQL Firewall

A cette étape, nous pouvons commencer à capturer de l’activité afin de débuter “l’apprentissage” pour le SQL Firewall.

Tout d’abord, il nous faut activer le Firewall et vérifier son statut avec le compte FW_ADMIN.

SQL> connect fw_admin@freepdb1
Enter password:
Connected.
SQL> exec dbms_sql_firewall.enable;

PL/SQL procedure successfully completed.

SQL>  select STATUS,STATUS_UPDATED_ON from dba_sql_firewall_status;

STATUS   STATUS_UPDATED_ON
-------- ---------------------------------------------------------------------------
ENABLED  20-DEC-23 03.45.23.677823 PM +00:00

Démarrer la capture d’activité pour l’utilisateur Oracle nommé application.

SQL>  exec dbms_sql_firewall.create_capture('APPLICATION'); 

Vérifier le statut de la capture

SQL> col LAST_STARTED_ON for a35
SQL> col LAST_STOPPED_ON for a35
SQL> select * from dba_sql_firewall_captures where username='APPLICATION';

USERNAME        TOP_LEVEL_ONLY STATUS   LAST_STARTED_ON                     LAST_STOPPED_ON
--------------- -------------- -------- ----------------------------------- -----------------------------------
APPLICATION     N              ENABLED  20-DEC-23 03.49.48.573900 PM +00:00                                     

La suite consiste donc à générer de l’activité avec le compte APPLICATION

SQL> conn application@FREEPDB1
Connected.
SQL> execute maj_salarie_addresse(154484, '18 rue voltaire');

SQL> execute maj_entreprise_salaries(25485, 146);

SQL> select name, address from application_owner.salaries where id = 472245;

SQL> insert into application_owner.salaries values (510024, 'Marie', '12 rue de l eglise', '20/12/2023');

SQL> select name, address from application_owner.salaries where id = 510024;

SQL> delete from application_owner.salaries where id = 510024;

SQL> select name, address application_owner.salaries where id = 510024;

SQL> select * from application_owner.somme_salaries_sa;

SQL> select count(*) from application_owner.salaries where date_entrée > '01/01/2015';

Allons voir à présent ce que le Firewall a capturé comme requêtes. Stoppons la capture et allons lire dans les logs du Firewall

SQL> exec dbms_sql_firewall.stop_capture('APPLICATION');

PL/SQL procedure successfully completed.

SQL> select username, ip_address, login_time, client_program, os_user from dba_sql_firewall_session_logs order by login_time;

USERNAME        IP_ADDRESS      LOGIN_TIME                          CLIENT_PROGRAM                                     OS_USER
--------------- --------------- ----------------------------------- -------------------------------------------------- --------------------
APPLICATION     172.44.****     20-DEC-23 04.08.38.884619 PM +00:00 sqlplus@ip-********.fr (TNS V1-V3)                 oracle
APPLICATION     172.44.****     20-DEC-23 04.15.26.633200 PM +00:00 sqlplus@ip-********.fr (TNS V1-V3)                 oracle
APPLICATION     172.44.****     20-DEC-23 04.19.46.220001 PM +00:00 sqlplus@ip-********.fr (TNS V1-V3)                 oracle

Nous avons capturé les différentes connexions avec le compte APPLICATION, grâce à cela, les informations sur les SQL lancés sont enregistrées.
A noter que les variables enregistrées sont “bindés”

SQL> select username, top_level, command_type, sql_text,sql_signature,accessed_objects from dba_sql_firewall_capture_logs where username = 'APPLICATION' order by command_type, sql_signature;

USERNAME TOP_LEVEL COMMAND_TYPE
--------------- --------- ----------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_SIGNATURE
----------------------------------------------------------------
ACCESSED_OBJECTS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
APPLICATION Y DELETE
DELETE FROM APPLICATION_OWNER.SALARIES WHERE ID=:"SYS_B_0"
0D3C48AFA7C43F32036A1398B2C9FED8250C57D00C9731332E8B6BFAAD25F3A0
"APPLICATION_OWNER"."SALARIES"

APPLICATION N EXECUTE
BEGIN UPDATE ENTREPRISE SET TAILLE_SALARIES=? WHERE ENT_ID=?; COMMIT; END;
243A35B41DAB8171B60C30BE90A3D725DEEDA3A22C8E118B5EB7E99C8D73E890
"APPLICATION_OWNER"."ENTREPRISE"

APPLICATION Y EXECUTE
BEGIN MAJ_SALARIE_ADDRESSE (?,?); END;
6A8D28786FE3BFEBFEBEE70CF2AF81A3587F5FE8239696BE9939D443EFB52789
"APPLICATION_OWNER"."MAJ_SALARIE_ADDRESSE"

APPLICATION N EXECUTE
BEGIN UPDATE SALARIES SET ADDRESS=? WHERE ID=?; COMMIT; END;
ED3DA8C122700A399BEB2D6C0CC88E5FC1B46A61961BF515E5DB0526856AD86A
"APPLICATION_OWNER"."SALARIES"

APPLICATION Y EXECUTE
BEGIN MAJ_ENTREPRISE_SALARIES (?,?); END;
F4F779482E86BC0537A6BC59470DB7E8A0EBBCF3B6F2CB21CE3242DC2863C45A
"APPLICATION_OWNER"."MAJ_ENTREPRISE_SALARIES"

APPLICATION Y INSERT
INSERT INTO APPLICATION_OWNER.SALARIES VALUES (:"SYS_B_0",:"SYS_B_1",:"SYS_B_2",:"SYS_B_3")
C24B251483E84751353F7B379414FFFBEBCC593E3E1CC32C0E419B69A66CA807
"APPLICATION_OWNER"."SALARIES"

APPLICATION Y SELECT
SELECT NAME,ADDRESS FROM APPLICATION_OWNER.SALARIES WHERE ID=:"SYS_B_0"
633C0FBB9B54CB5F6BC0A75B9BA34A2EA453671A78A799644CF4D7956F6EEE4B
"APPLICATION_OWNER"."SALARIES"

APPLICATION Y SELECT
SELECT NAME,ADDRESS FROM APPLICATION_OWNER.SALARIES WHERE ID=:"SYS_B_0"
633C0FBB9B54CB5F6BC0A75B9BA34A2EA453671A78A799644CF4D7956F6EEE4B
"APPLICATION_OWNER"."SALARIES"

APPLICATION Y SELECT
SELECT DECODE (USER,:"SYS_B_0",XS_SYS_CONTEXT (:"SYS_B_1",:"SYS_B_2"),USER) FROM SYS.DUAL
8CD0E5550A8AF32553BDED7C77B8CC1FD103C51F438E11F1BC5F9CA315102794
"SYS"."DUAL"

APPLICATION Y SELECT
SELECT DECODE (USER,:"SYS_B_0",XS_SYS_CONTEXT (:"SYS_B_1",:"SYS_B_2"),USER) FROM SYS.DUAL
8CD0E5550A8AF32553BDED7C77B8CC1FD103C51F438E11F1BC5F9CA315102794
"SYS"."DUAL"

APPLICATION Y SELECT
SELECT DECODE (USER,:"SYS_B_0",XS_SYS_CONTEXT (:"SYS_B_1",:"SYS_B_2"),USER) FROM SYS.DUAL
8CD0E5550A8AF32553BDED7C77B8CC1FD103C51F438E11F1BC5F9CA315102794
"SYS"."DUAL"

APPLICATION Y SELECT
SELECT * FROM APPLICATION_OWNER.SOMME_SALARIES_SA
BCCB5D0F6B4DE96D7C9E52C8678C489698D4ED23F8FEEA120FFC701560C99D0C
"APPLICATION_OWNER"."SOMME_SALARIES_SA"

APPLICATION Y SELECT
SELECT * FROM APPLICATION_OWNER.SOMME_SALARIES_SA
BCCB5D0F6B4DE96D7C9E52C8678C489698D4ED23F8FEEA120FFC701560C99D0C
"APPLICATION_OWNER"."SOMME_SALARIES_SA"

APPLICATION Y SELECT
SELECT COUNT (*) FROM APPLICATION_OWNER.SALARIES WHERE DATE_ENTR??E >:"SYS_B_0"
EE000C28DC61F8D21DCDC9BB6880A315EB12CC3682E0D3CD47A01EACF915EF98
"APPLICATION_OWNER"."SALARIES"

APPLICATION Y SELECT
SELECT COUNT (*) FROM APPLICATION_OWNER.SALARIES WHERE DATE_ENTR??E >:"SYS_B_0"
EE000C28DC61F8D21DCDC9BB6880A315EB12CC3682E0D3CD47A01EACF915EF98
"APPLICATION_OWNER"."SALARIES"

APPLICATION N UPDATE
UPDATE ENTREPRISE SET TAILLE_SALARIES=:"SYS_B_0" WHERE ENT_ID=:"SYS_B_1"
36FE5B2C529FD88D46DD6C69649D30C12719CD2600945F8EF2D4B3D039B4CD06
"APPLICATION_OWNER"."ENTREPRISE"

APPLICATION N UPDATE
UPDATE SALARIES SET ADDRESS=:"SYS_B_0" WHERE ID=:"SYS_B_1"
6D68C8BB02FFE46E37900E60275B0AB0698CF1217B95B3CA1C789E29FE8D0B6B
"APPLICATION_OWNER"."SALARIES"

Chaque ordre SQL a une signature propre à lui. C’est ce qui permet au SQL Firewall de reconnaitre par la suite, tout ordre faisant parti de la liste.

Générer la liste “verte” de requêtes autorisées

La liste “verte” se crée avec le compte FW_ADMIN

SQL> connect fw_admin@FREEPDB1
Enter password:
Connected.
SQL> exec dbms_sql_firewall.generate_allow_list('APPLICATION');

PL/SQL procedure successfully completed.

SQL>; col GENERATED_ON for a35
SQL> select USERNAME,GENERATED_ON,STATUS,STATUS_UPDATED_ON,TOP_LEVEL_ONLY from dba_sql_firewall_allow_lists where username='APPLICATION';

USERNAME              GENERATED_ON                        STATUS   STATUS_UPDATED_ON                   TOP_LEVEL_ONLY
---------------       ----------------------------------- -------- ----------------------------------- --------------
APPLICATION           20-DEC-23 04.49.56.169700 PM +00:00 DISABLED 20-DEC-23 04.49.56.169700 PM +00:00 N

Pour le moment, le statut de la liste est à DISABLED car nous l’avons juste générée.

Comme évoqué quelques lignes au dessus, le contexte peut se faire via l’adresse IP

SQL> select * from sys.dba_sql_firewall_allowed_ip_addr where username='APPLICATION';

USERNAME             IP_ADDRESS
-------------------- ------------------------------
APPLICATION          172.44.*********

Le programme associé

SQL> select * from sys.dba_sql_firewall_allowed_os_prog where username='APPLICATION';

USERNAME               OS_PROGRAM
--------------------   --------------------------------------------------------------------------------------------------------------------------------
APPLICATION            sqlplus@ip-172-44-*************s.fr (TNS V1-V3)

Ou bien le user OS

SQL> select * from sys.dba_sql_firewall_allowed_os_user where username='APPLICATION';

USERNAME               OS_USER
--------------------   --------------------------------------------------------------------------------------------------------------------------------
APPLICATION            oracle

Activer la liste “verte”

la suite consiste à valider et surtout, activer cette liste. Ceci se fait avec le compte FW_ADMIN.

SQL> connect fw_admin@FREEPDB1
Enter password:
Connected.
SQL> exec dbms_sql_firewall.enable_allow_list('APPLICATION');

PL/SQL procedure successfully completed.

SQL> select username, status, top_level_only, enforce, block from dba_sql_firewall_allow_lists where username='APPLICATION';

USERNAME             STATUS          TOP_LEVEL_ONLY ENFORCE         BLOCK
-------------------- --------------  -------------- --------------- --------------
APPLICATION          ENABLED         N              ENFORCE_ALL     N

Cette liste “verte” est donc activée, elle “trappe” les futures requêtes qui ne matcheraient pas avec celles qui sont enregistrées. Mais, le “BLOCK” est à N, donc l’utilisateur n’a pas de message d’erreur en cas de saisie d’une requête non reconnue.

Le user APPLICATION peut tout à fait faire un SELECT sur une table.

SQL> connect application@FREEPDB1
Enter password:
Connected.
SQL> select name from application_owner.salaries where id > 300000;

NAME
--------------------------------------------------------------------------------------------------------------------------------
Manuel
Jack
Cyril

Mais une fois connecté avec le user FW_ADMIN, une simple interrogation dans la vue DBA_SQL_FIREWALL_VIOLATIONS nous donne l’entrée suivante

SQL> select USERNAME,COMMAND_TYPE,SQL_TEXT,IP_ADDRESS,OS_USER,OCCURRED_AT from dba_sql_firewall_violations;

USERNAME             COMMAND_TYPE                  SQL_TEXT                                                                              IP_ADDRESS          OS_USER</pre>
-------------------- ---------------               --------------------------------------------------------------------------------      ---------------     ---------------
OCCURRED_AT
---------------------------------------------------------------------------
APPLICATION          SELECT                        SELECT NAME FROM APPLICATION_OWNER.SALARIES WHERE ID <:"SYS_B_0"                      172.44.******        oracle
21-DEC-23 02.09.53.047973 PM +00:00

La requête lancée par le compte APPLICATION est donc bien enregistrée dans la liste des violations des règles du firewall.

Bloquer les requêtes non désirées

Le blocage de requêtes s’effecctue, avec le compte FW_ADMIN, en activant le mode BLOCK sur la liste “verte”

SQL> connect fw_admin@FREEPDB1
Enter password:
Connected.
SQL> exec dbms_sql_firewall.update_allow_list_enforcement('APPLICATION', block=>TRUE);

PL/SQL procedure successfully completed.

Et lorsque nous souhaitons interroger la même requête avec le compte APPLICATION

SQL> connect application@FREEPDB1
Enter password:
Connected.
SQL> select name from application_owner.salaries where id > 300000;
select name from application_owner.salaries where id > 300000
*
ERROR at line 1:
ORA-47605: SQL Firewall violation
Help: https://docs.oracle.com/error-help/db/ora-47605/

Nous avons une belle erreur “ORA-47605” nous indiquant une violation des règles du Firewall. Petite nouveauté avec la 23c, Oracle nous donne l’URL pour rechercher directement la définition du message d’erreur.

Ceci se vérifie pour toute autre requête ne faisant pas partie de la liste “verte”

SQL> select * from application_owner.entreprise where taille_salaries > 10000;
select * from application_owner.entreprise where taille_salaries > 10000
*
ERROR at line 1:
ORA-47605: SQL Firewall violation
Help: https://docs.oracle.com/error-help/db/ora-47605/

Dans la vue DBA_SQL_FIREWALL_VIOLATIONS, ces 2 dernières requêtes nous sont relevées

SQL> connect fw_admin@FREEPDB1
Enter password:
Connected.
SQL> select USERNAME,COMMAND_TYPE,SQL_TEXT,OCCURRED_AT from dba_sql_firewall_violations;

USERNAME COMMAND_TYPE SQL_TEXT
-------------------- --------------- --------------------------------------------------------------------------------
OCCURRED_AT
---------------------------------------------------------------------------
APPLICATION SELECT SELECT * FROM APPLICATION_OWNER.ENTREPRISE WHERE TAILLE_SALARIES >:"SYS_B_0"
21-DEC-23 02.30.31.690170 PM +00:00

APPLICATION SELECT SELECT NAME FROM APPLICATION_OWNER.SALARIES WHERE ID <:"SYS_B_0"
21-DEC-23 02.09.53.047973 PM +00:00

APPLICATION SELECT SELECT NAME FROM APPLICATION_OWNER.SALARIES WHERE ID <:"SYS_B_0"
21-DEC-23 02.24.29.523017 PM +00:00

Il est bien entendu possible de purger la table de log des requêtes interdites afin de réinitialiser son contenu.

SQL> exec dbms_sql_firewall.purge_log('APPLICATION', NULL, dbms_sql_firewall.VIOLATION_LOG);

PL/SQL procedure successfully completed.

SQL> select USERNAME,COMMAND_TYPE,SQL_TEXT,OCCURRED_AT from dba_sql_firewall_violations;

no rows selected

Conclusion

Gardez à l’esprit que cette fonctionnalité SQL Firewall de Oracle peut vous protéger de toute injection SQL non désirée, mais ceci sous entend surtout que la phase “d’apprentissage” soit correctement maitrisée afin de ne pas se retrouver avec une application potentiellement bloquée par des ordres SQL qui ne s’exécutent plus.

C’est bien pour cela que cette phase peut être longue, et nécessite le recensement de très nombreuses requêtes trappées dans DBA_SQL_FIREWALL_ALLOWED_SQL.
La valeur de “SQL_SIGNATURE” est essentielle , dans la mesure ou le relevé des variables est “bindé”, les requêtes suivantes seront autorisées même si les résultats sont différents  :

SQL> select name, address from application_owner.salaries where id = 510024;

SQL> select name, address from application_owner.salaries where id = 285548;

Pensez également à purger la vue DBA_SQL_FIREWALL_VIOLATIONS de façon régulière, tout en portant attention sur ce qui aura été relevé durant les périodes de production.

Continuez votre lecture sur le blog :

• Installation Oracle 64 bits sur Red Hat 5 (Capdata team) [OracleVintage]
• Oracle Text pour DBA Oracle : Partie 1 (Capdata team) [Oracle]
• Le chiffrement Oracle : native network encryption (Emmanuel RAMI) [Oracle]
• Réplication logique avec PostgreSQL (Capdata team) [PostgreSQL]
• Un trigger fait-il parti d’une transaction ? (Benjamin VESAN) [GénéralMySQLOracleSQL ServerSybase]

L’article Oracle 23c : se protéger des attaques par injections SQL grâce à SQL Firewall est apparu en premier sur Capdata TECH BLOG.

Pour faire tourner son service SQL Server sous Windows, il y a différentes écoles. Certains veulent un compte de service créé dans l’Active Directory, afin d’y appliquer des GPO et bien les identifier avec un nom explicite. D’autres préfèrent garder les choses simples et laissent les Virtual Service Account (NT Service\MSSQLSERVER).

Le problème avec le passage sur un compte de service dédié est le risque de mauvaise administration et de s’en servir pour ouvrir une session interactive, combiné avec l’éventualité que le mot de passe soit récupéré par un utilisateur malicieux. On peut donc mettre en place une stratégie d’expiration de mot de passe, mais alors la rotation des mots de passe dans le parc peut vite devenir infernale !

C’est là qu’intervient une solution assez méconnue : les Managed Service Account. L’une des principales raisons pour laquelle cette solution est méconnue est qu’elle nécessite que le niveau fonctionnel de la forêt Active Directory soit au minimum au niveau fonctionnel de Windows 2012.

Maintenant que nous sommes en 2023, on peut supposer que la plupart des infrastructures Active Directory sont dans des niveaux supérieurs.

Les MSA étaient le premier nom donné à ce type de compte, et ne fonctionnait que pour les service qui tournaient sur une seule machine. Peu de temps après a été ajouté la possibilité de fonctionner sur un cluster avec la notion de Group Managed Service Account. Mais les commandes restent les mêmes.

Pré-requis à l’utilisation d’un Managed Service Account pour SQL Server (MSA/gMSA) :

Comme indiqué, le premier pré-requis est au niveau de l’Active Directory qui doit être au niveau fonctionnel 2012 ou supérieur.

Pour SQL Server, si c’est pour travailler avec une instance “standalone” , il faudra un SQL Server 2014. Pour de l’AlwaysOn et du Failover Cluster Instance (FCI), cela nécessitant la couche cluster, et donc un gMSA, il faudra un SQL Server 2016.

Pour créer un (g)MSA, il faudra soit être administrateur du domaine ou bien disposer du privilège de création des objets de type “msDS-GroupManagedServiceAccount”.

Un accès PowerShell avec l’extension Active Directory (disponible par exemple en installant le feature Windows “Remote Management”) doit être présent sur les machines SQL Server.

Afin de faire fonctionnaire les MSA, ils est également nécessaire qu’une infrastructure KDS soit présente sur le domaine. Si vous ne savez pas si vous en avez une, vous pouvez interrogez votre domaine avec la commande PowerShell Get-KDSRootKey :

On voit ici qu’une clé a été créée le 29/11/2023.

Si jamais vous n’en avez pas, il faut la créer avec la commande :

Add-KdsRootKey -EffectiveImmediately

Bien que le paramètre EffectiveImmediately permette son usage immédiatement, j’ai rencontré des délais avant que mes machines SQL Server puissent utiliser les MSA, il se peut donc qu’il faille attendre également chez vous. Par ailleurs, si vous avez plusieurs contrôleurs de domaine, le temps de réplication peut atteindre 10 heures.

Création d’un gMSA pour SQL Server :

Notre but ici est de créer un compte de service managé pour un SQL Server 2022 avec un groupe de disponibilité. Nous sommes dans le scénario le plus compliqué, où SQL Server a déjà été installé et le groupe de disponibilité est déjà présent.

Notre configuration est telle que nous avons deux serveurs LAB1SQL1 et LAB1SQL2 avec un listener pour le groupe de disponibilité LAB1_LSTN. Nous voulons un compte de service commun pour nos deux serveurs SQL que l’on appellera LAB1_gMSA. Nous voulons également que les SPN soient enregistrés correctement sans intervention supplémentaire.

Création d’un groupe AD pour les machines autorisées à utiliser le gMSA :

Dans la console Active Directory Users and Computers, on va créer un groupe de sécurité et y ajouter les deux comptes ordinateurs de notre groupe de disponibilité :

Création du gMSA :

La création du gMSA se fait avec la commande PowerShell “New-ADServiceAccount”. Elle n’est pas possible par l’interface graphique “Active Directory Users and Computers”.

 
New-ADServiceAccount -Name LAB1_gMSA -DNSHostName LAB1SQL1.LAB1.local -ManagedPasswordIntervalInDays 90 -PrincipalsAllowedToRetrieveManagedPassword LAB1_SQL_Group -ServicePrincipalNames MSSQLSvc/LAB1SQL1.lab1.local, MSSQLSvc/LAB1SQL1.lab1.local:1433, MSSQLSvc/LAB1_LSTN.lab1.local, MSSQLSvc/LAB1_LSTN.lab1.local:1434 -Enabled $true

On aura donc un groupe de disponibilité avec les paramètres suivants:

• Son nom sera LAB1_gMSA
• La rotation automatique des mots de passe du compte aura lieu tous les 90 jours (par défaut 30)
• La liste des machines autorisées à utiliser le gMSA se trouve dans le groupe LAB1_SQL_Group
• Les SPN créés seront : MSSQLSvc/LAB1SQL1.lab1.local, MSSQLSvc/LAB1SQL1.lab1.local:1433, MSSQLSvc/LAB1_LSTN.lab1.local et MSSQLSvc/LAB1_LSTN.lab1.local:1434
• Il sera actif dès sa création

On peut désormais le voir dans la console “Active Directory Users and Computers” :

Déploiement du gMSA sur les machines SQL :

Une fois compte créé, il faut donc le déployer sur les machines SQL. On va se connecter en PowerShell sur les machines SQL et utiliser la commande “Install-ADServiceAccount”. Pour rappel, il faut avoir le composant Remote Management pour pouvoir exécuter cette commande. Si jamais vous ne l’avez pas, vous pouvez le déployer rapidement avec la commande PowerShell

Add-WindowsFeature RSAT-AD-PowerShell

Après on peut donc activer notre compte LAB1_gMSA :

Install-ADServiceAccount -Identity LAB1_gMSA

Après avoir réalisé ça sur nos deux machines, on peut les spécifier dans notre configuration SQL Server.

Configuration du gMSA dans SQL Server :

Dans la console “SQL Server Configuration Manager”, dans la section “SQL Server Services”, on doit aller dans l’onglet “Log On” des propriétés du service SQL Server (et de son agent éventuellement).

Comme un objet ordinateur, un gMSA s’écrit avec un $ à la fin.

Dans SQL Server, on n’a plus qu’à lui donner les privilèges suffisants :

CREATE LOGIN [LAB1\LAB1_gMSA$] FROM WINDOWS ; 
GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [LAB1\LAB1_gMSA$] ;
ALTER SERVER ROLE [sysadmin] ADD MEMBER [LAB1\LAB1_gMSA$]
GO

Si on regarde dans les logs, on verra désormais les lignes suivantes :

The service account is 'LAB1\LAB1_gMSA$'. This is an informational message; no user action is required.
[...]
The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/LAB1SQL1.lab1.local ] for the SQL Server service.
The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/LAB1SQL1.lab1.local:1433 ] for the SQL Server service.

Ca y est ! vous avez configuré le gMSA de votre SQL Server. Mais vous pouvez (devriez ?) utiliser également ça pour vos applications qui utilisent un compte de service spécifique.
Continuez votre lecture sur le blog :

• Elastic Job Agent : l’Agent SQL Server pour le PaaS Azure (Capdata team) [AzureSQL Server]
• Sauvegardes SQL Server dans un Azure Blob Storage (Capdata team) [AzureSQL Server]
• Les nouveautés de SQL Server 2022 (Capdata team) [SQL Server]
• AWS : Backup Restore SQL Server RDS vers une EC2 ou On-Premise et vice versa ! (Emmanuel RAMI) [AWSSQL Server]
• Export d’une VM d’un ESX vers une EC2 AWS (Emmanuel RAMI) [AWS]

L’article Les Managed Service Account (MSA et gMSA) : se simplifier la vie pour gérer ses comptes de service SQL Server est apparu en premier sur Capdata TECH BLOG.

Comme nous l’avions vu dans le premier épisode de cette série sur le chiffrement dans SQL Server, la solution la plus rigoureuse pour le chiffrement des données reste Always Encrypted. Celle-ci peut s’appliquer en complément de TDE qui lui va s’occuper de protéger l’intégralité des données et notamment ces backups. Always Encrypted, lui, travaille sur par colonne par colonne et partage les éléments cryptographique avec le composant applicatif qui veut lire ou écrire les données, de manière à que SQL Server ne voit pas les données en clair (ou presque : nous parlerons des Secure Enclaves plus loin)

Les objets cryptographiques derrière Always Encrypted :

La technologie derrière Always Encrypted repose sur des clés de chiffrements.

La première est la Column Master Key (ou CMK). C’est une clé RSA de 2048 bits qui sert à chiffré les clés de chiffrement de données. C’est cette clé que l’on devra sécuriser et que l’on doit administrer avec précaution. C’est aussi celle-ci que le composant applicatif devra être capable de lire pour chiffrer et déchiffrer les données. Cette clé peut-être stockée soit dans le magasin de certificat Windows, soit le magasin certificat Java si on développe avec le driver JDBC, soit dans un Azure Key Vault. Aucun autre support de gestion de cette clé n’est disponible pour le moment. Si notre composant applicatif est sous Linux on peut cependant utiliser un Azure Key Vault (par exemple, avec le driver PHP : https://learn.microsoft.com/fr-fr/sql/connect/php/using-always-encrypted-php-drivers?view=sql-server-ver16 ) ou le driver JDBC (https://learn.microsoft.com/fr-fr/sql/connect/jdbc/using-always-encrypted-with-the-jdbc-driver?view=sql-server-ver16 ).

Dans cet article, nous nous concentrerons sur l’Azure Key Vault et le magasin de certificat Windows.

La deuxième est la Column Encryption Key (CEK). Elle sert à chiffrer les données dans la colonne de notre base. Le chiffrement des données se fait par un algorithme publique et reconnu : AEAD_AES_256_CBC_HMAC_SHA_256 , qui est documenté par l’IETF. Cela veut dire qu’à partir que si le stockage de la clé est sécurisé, on peut avoir confiance en ce chiffrement. Dans le cas où la CMK est stockée dans l’Azure Key Vault, Microsoft (et donc le gouvernement américain, même si la zone Azure est en France, dans le cadre du Cloud Act ) pourrait donc déchiffrer nos données, en théorie.

La CEK va donc chiffrer les données par colonne et va affecter la taille de celles-ci. Avec l’algorithme AEAD_AES_256_CBC_HMAC_SHA_256, on obtient donc le tableau des tailles suivantes par type :

On comprend donc que le chiffrement implique forcément de consommer plus d’espace : un integer consomme 4 octets, mais une fois chiffré il en consomme 65.

Dans la documentation disponible ici, on a la formule suivante qui nous permet de déterminer la taille en octet d’un contenu :

1 + 32 + 16 + (FLOOR(DATALENGTH(cell_data)/16) + 1) * 16  

Ainsi par exemple avec une chaine de caractère de 76 caractères telle que “Par exemple du texte là qui peut être assez long finalement si on est bavard”, dans SQL Server je fais le calcul suivant :

On a doc 129 octets pour ce varchar(76). Si c’était du nvarchar(76), cela prendrait 209 octets chiffrés au lieu de 152. L’impact est donc non-négligeable dès que l’on arrive sur des grosses volumétries.

Usage et limitation :

Le chiffrement par Always Encrypted doit être considéré uniquement pour des colonnes stockant des informations très sensibles. Si les informations très sensibles n’ont pas été très bien identifiées par le métier, Microsoft propose de l’outillage et de la méthodologie afin de les identifier. cela peut se faire soit dans le Portail Azure, soit dans SSMS (https://learn.microsoft.com/en-us/sql/relational-databases/security/sql-data-discovery-and-classification?view=sql-server-ver16&tabs=t-sql ).

Une fois les colonnes identifiées, il faut également comprendre que leur chiffrement va impacteur leur usage. Dans le meilleur des cas (avec un chiffrement déterministe), on ne pourra faire que des recherches d’égalité dans nos requêtes ( SELECT =  / IN  / GROUP BY / DISTINCT). On ne peut pas non plus comparer les valeurs non-chiffrées et chiffrées directement. Il faut impérativement utiliser la paramétrisation.

La liste des limitations étant longue, il vaut mieux se reporter à la documentation pour vérifier si l’usage envisagé est compatible :  https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-ver16&source=docs#limitations

Séparation des rôles lors de la mise en œuvre :

Le but du chiffrement Always Encrypted est déporter les activités de chiffrements et de déchiffrements au driver SQL. Cela implique donc une responsabilité sur la détention de la paire de clés (CEK) : qui doit les posséder ? les mettre en oeuvre ? Microsoft répond à cela en considérant qu’il y a deux fonctions différentes :

Le DBA qui gère les métadonnées des clés dans la base de données : c’est à dire qu’il va voir des informations dans certaines DMV relatives à la gestion de ces clés, y compris l’emplacement de ces clés (magasin de certificat ou bien URL Azure Key Vault), mais il ne verra pas la CMK directement.

Le “Security Administrator” aura pour rôle de générer la CMK et de les placer dans le magasin de certificat ou l’Azure Key Vault, mais il n’aura pas forcément besoin d’un accès direct à la base de données. Il aura aussi potentiellement à appliquer une rotation des clés si cela fait partie de la stratégie d’entreprise ou bien si une risque de vol de la CMK a eu lieu.

Cette séparation n’est pas indispensable, mais elle représente l’aboutissement du besoin de cacher les données à des utilisateurs non-autorisés, surtout si le rôle de DBA est délégué à un prestataire ou à un opérateur de cloud par exemple.

Mise en oeuvre d’Always Encrypted :

Always Encrypted peut être mis en œuvre par deux outils  :

• SQL Server Management Studio
• PowerShell

SSMS encapsule beaucoup les tâches et ne laisse pas beaucoup de marge de manœuvre pour le nommage des clés en particulier. L’utilisation de PowerShell est donc recommandée.

Par ailleurs, il y a deux cadres d’utilisation à considérer à notre époque : le composant applicatif est hébergé sur un serveur On-Prem (et dispose donc d’un magasin de certificats ) ou bien il est hébergé dans le Cloud et voudra accéder à l’Azure Key Vault.

Dans le cadre Azure (avec le Key Vault) :

Deux modules PowerShell sont nécessaire pour travailler sur ces tâches : SqlServer et Az.

Install-Module SqlServer -allowclobber
Install-Module Az

On peut ensuite créer la CMK. Si on veut travailler avec Azure et son Key vault, on peut utiliser le script ci-dessous :

$subscriptionId = "xxxxxxxxxxxxxxxxxxxxxxxxx" #Id de la souscription Azure
$TenantId = "xxxxxxxxxxxxxxxxxxxxxxxxx" #Id du Tenant Azure Active Directory (Entra ID désormais)
$resourceGroup = "Lab1_RG" #Resource Group de l'Azure Key Vault
$keyVaultName = "VDE-KeyVault" #Nom de l'Azure Key Vault
$keyVaultKeyName = "AdventureWorks-CEK"
Import-Module "Az"
Connect-AzAccount -Tenant $TenantId

# Pour se placer dans le contexte de la souscription souhaitée
Set-AzContext -SubscriptionId $subscriptionId

# Récupération du nom du key vault name
Get-AzKeyVault-VaultName $keyVaultName-ResourceGroupName $resourceGroup

# Création de la clé
$keyVaultKey = Add-AzKeyVaultKey -VaultName $keyVaultName -Name $keyVaultKeyName -Destination "Software"
$keyVaultKey

On peut désormais voir la clé dans le Key Vault Azure :

On peut voit le détail de la clé, son URL (Key Identifier) et ses permissions ensuite :

L’URL ici visible sera à renseigner dans nos applications cloud. On autorisera l’applicatif à utiliser le rôle  “Key Vault Crypto Service Encryption User”.

Il nous reste à l’enregistrer dans notre base de données. On peut le faire en PowerShell ou bien en SQL. C’est ici que l’on peut dessiner la frontière entre le rôle de “dba” et celle de “security administrator”. Le “security administrator” peut juste donner l’URL de la clé au dba pour qu’il l’intègre à la base  :

USE [AdventureWorks]
GO
CREATE COLUMN MASTER KEY [AdventureWorks-CMK-SQL-Object]
WITH
(
KEY_STORE_PROVIDER_NAME = N'AZURE_KEY_VAULT',
KEY_PATH = N'https://vde-keyvault.vault.azure.net/keys/AdventureWorks-CMK/9112fb34e3a84051a6ee490bb110a259'
)
GO

Ou bien en PowerShell (attention c’est plus long, mais on part du principe que cela fait suite au script PowerShell précédent ) :

Import-Module SqlServer

$connectionString = "Data Source=LAB1SQL1;Initial Catalog=AdventureWorks;Integrated Security=True;Connect Timeout=30;Trust Server Certificate=True;Application Name=`"Script PowerShell de déploiement AlwaysEncrypted`""
$database = Get-SqlDatabase -ConnectionString $connectionString
$database# Récupération des paramètres de la CMK stockée dans le Key Vault :
$cmkSettings = New-SqlAzureKeyVaultColumnMasterKeySettings -KeyURL $keyVaultKey.Key.Kid
# Déclaration de la CMK dans SQL Server
$cmkName = "AdventureWorks-CMK-SQL-Object"
New-SqlColumnMasterKey -Name $cmkName -InputObject $database -ColumnMasterKeySettings $cmkSettings

On peut ensuite vérifier que notre CMK est bien déclarée dans SQL Server avec la requête suivante :

On peut ensuite crée la CEK qui nous servira à chiffrer les données dans les colonnes :

$cekName = "AdventureWorks-Person-emailaddress-CEK"
New-SqlColumnEncryptionKey -Name $cekName -InputObject $database -ColumnMasterKey $cmkName

Là aussi, on peut vérifier la création de la CEK en SQL :

Une fois la CEK présente, on peut l’utiliser pour chiffrer nos colonnes. Imaginons que l’on souhaite chiffrer la colonne EmailAddress de la table Person.EmailAddress : il faudra choisir entre deux type de chiffrement déterministe (Deterministic) ou aléatoire (Randomized). Déterministe signifie que deux chaines de caractères identiques renverront la même valeur une fois chiffrée, là où aléatoire renverra deux valeurs différentes. L’avantage de déterministe est ainsi de pouvoir faire des indexations et des jointures, contrairement au chiffrement aléatoire. Par contre, pour un attaquant, le fait d’avoir du déterministe (des valeurs qui se répètent dans la table) est une information déjà intéressante.

# Création des instructions de chiffrements : elles peuvent être multiples.
$ces = @()
$ces += New-SqlColumnEncryptionSettings -ColumnName "Person.EmailAddress.Employees.SSN" -EncryptionType "Deterministic" -EncryptionKey $cekName
Set-SqlColumnEncryption -InputObject $database -ColumnEncryptionSettings $ces -LogFileDirectory .

Désormais, la lecture des données sans les privilèges d’accès à la clé dans le Key Vault présentera les données ainsi :

SSMS permet d’accéder à des données chiffrées avec Always Encrypted, même si la clé est dans un Azure Key Vault (on aura une pop-up d’authentification, comme avec la commande PowerShell AzConnect), pour cela il suffit juste de cliquer sur “options” dans la fenêtre d’ouverture de session et d’aller cocher “enable Always Encrypted (column encryption)” :

Après cela, les données sont lisibles.

Dans le cadre On-Prem (avec le magasin de certificat Windows) :

Imaginons désormais que notre composant applicatif soit hébergé sur un serveur Windows et ne souhaite pas utiliser l’Azure Key Vault. La seule solution aujourd’hui possible est le magasin de certificat Windows. On peut créer un certificat auto-signer

$cert = New-SelfSignedCertificate -Subject "VDE-CMK-WindowsStore" -CertStoreLocation Cert:CurrentUser\My -KeyExportPolicy Exportable -Type DocumentEncryptionCert -KeyUsage DataEncipherment -KeySpec KeyExchange

On pourra voir le certificat dans la branche “personal” de CurrentUser (Start/run/mmc.exe , on ajoute le composant “certificats” pour l’utilisateur courant).

Attention à bien générer le certificat sur une machine autre que celle SQL Server, afin de bien séparer les rôles : la machine SQL Server ne doit pas disposer de la CMK.

Après, de la même manière qu’avec l’Azure Key Vault, on peut créer la CEK :

$cekName = "AdventureWorks-Person-emailaddress2-CEK"
$cmkName = "AdventureWorks-CMK-SQL-Object2"
$connectionString = "Data Source=LAB1SQL1;Initial Catalog=AdventureWorks;Integrated Security=True;Connect Timeout=30;Trust Server Certificate=True;Application Name=`"Script PowerShell de déploiement AlwaysEncrypted`""</div><div>$database = Get-SqlDatabase -ConnectionString $connectionString
New-SqlColumnEncryptionKey -Name $cekName -InputObject $database -ColumnMasterKey $cmkName

$ces = @()
$ces += New-SqlColumnEncryptionSettings -ColumnName "Person.EmailAddress2.EmailAddress" -EncryptionType deterministic -EncryptionKey $cekName
Set-SqlColumnEncryption  -InputObject $database -ColumnEncryptionSettings $ces -LogFileDirectory .

$ces = @()
$ces += New-SqlColumnEncryptionSettings -ColumnName "Person.EmailAddress2.EmailAddress" -EncryptionType PlainText
Set-SqlColumnEncryption -InputObject $database -ColumnEncryptionSettings $ces -LogFileDirectory .

Comme on l’a expliqué lors de ces deux épisodes, le bon fonctionnement applicatif repose sur deux points :

• La chaine de connexion spécifie “Column Encryption Setting=enabled“
• Le compte d’exécution de l’applicatif dispose d’un accès à la Column Master Key.

Dans le cas de l’utilisation d’un applicatif déployé nativement dans le cloud, les stratégies dites RBAC (Role-Based Access Control) sont à privilégier. La documentation Microsoft donne détaille davantage que cet article les éléments à considérer.

Si notre applicatif est plus classique, et est déployé sur un serveur Windows, on s’intéressera à son compte d’exécution pour utiliser le magasin de certificat approprié. Dans le cas de Linux, cela pourra être avec le driver JDBC et le Java KeyStore (https://learn.microsoft.com/en-us/sql/connect/jdbc/using-always-encrypted-with-the-jdbc-driver?view=sql-server-ver16 )

Les dernières versions de SQL Server (que ça soit dans Azure en PaaS ou bien On-Prem / IaaS) proposent cependant ce qu’ils appellent les Secure Enclaves, pour lever certaines limitations de développement d’Always Encrypted ! Peut-être l’objet d’un nouvel épisode ?

 Continuez votre lecture sur le blog :

• Le chiffrement et SQL Server – Episode 1 : Transparent Data Encryption (TDE) vs Always Encrypted (Capdata team) [SQL Server]
• Le chiffrement et SQL Server – Episode 2 : Mise en oeuvre de TDE (Capdata team) [SQL Server]
• Le chiffrement Oracle : Transparent Data Encryption sur Oracle 19c (Emmanuel RAMI) [Oracle]
• Le chiffrement Oracle : native network encryption (Emmanuel RAMI) [Oracle]
• Sauvegardes SQL Server dans un Azure Blob Storage (Capdata team) [AzureSQL Server]

L’article Le chiffrement et SQL Server – Episode 3 : Always Encrypted est apparu en premier sur Capdata TECH BLOG.

Dans un premier épisode (https://blog.capdata.fr/index.php/le-chiffrement-et-sql-server-episode-1-transparent-data-encryption-tde-vs-always-encrypted ) nous avons eu l’occasion de voir les deux grandes approches du chiffrement dans SQL Server, sous un angle théorique.

Maintenant que l’on a couvert les concepts derrière le chiffrement “at rest” de SQL Server, voici comment le mettre en œuvre concrètement.

Concepts de Transparent Data Encryption

On notera que cela a été maquetté en SQL Server 2022, mais que mis à part la sauvegarde (cf. Episode 1), les concepts sont les mêmes.

Créer les clés de chiffrement

Le fonctionnement est donc le suivant :

La clé de chiffrement par Windows à SQL Server va permettre de créer la Database Master Key de la base Master. Celle-ci servira à signer le certificat de la base Master. Ce certificat stockera les clés asymétriques utilisés pour chiffrer les bases de données utilisateurs.

Windows fourni une Service Master Key au premier démarrage de SQL Server après son installation. Celle-ci n’est visible et exploitable que par le compte de service SQL Server. Elle peut être identifiée ainsi :

use master
go
select * from sys.symmetric_keys ;

Elle porte le nom de ##MS_ServiceMasterKey##. Par mesure de sécurité, on va sauvegarder cette SMK :

BACKUP SERVICE MASTER KEY TO FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\Service_Master_Key.smk' ENCRYPTION BY PASSWORD = 'HugEEEEEP@$$word4SMK!' ;
GO

On peut donc désormais créer le certificat dans la base master . Par défaut, celui-ci a une durée de vie d’un an. Cela peut potentiellement alourdir la maintenance, car il faudra déchiffrer la base, créer un nouveau certificat et re-chiffrer la base. Dans l’exemple ci-dessous, nous partons sur une durée de vie… “lointaine” :

Use master
go
CREATE CERTIFICATE MyServerMasterCertForTDE WITH SUBJECT = 'DEK Certificate used for TDE keys' ,EXPIRY_DATE = '2100-12-31';
GO

On peut la vérifier avec la commande suivante :

select * from sys.certificates where SUBJECT = 'DEK Certificate used for TDE keys';

De la même manière que l’on a sauvegardé la Service Master Key, on va sauvegarder ce certificat : il nous servira si l’on souhaite restaurer la base sur un autre serveur ou bien pour des solutions de haute disponibilité :

BACKUP CERTIFICATE MyServerMasterCertForTDE TO FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\Master_Certificate_for_TDE.crt' 
WITH PRIVATE KEY (FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\Master_Certificate_for_TDE_PrivateKey' , 
ENCRYPTION BY PASSWORD = 'HugEEEEEP@$$word4Cert!');

Maintenant que l’on a ce certificat, on peut enfin créer les clés de chiffrements TDE pour la base métier que l’on souhaite chiffrer :

Use AdventureWorks
GO
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE MyServerMasterCertForTDE ; 
GO

Egalement, on peut vérifier la présence de notre paire de clés avec :

Use AdventureWorks
GO
select * from sys.key_encryptions

Deux lignes doivent apparaitre avec le key_id 101.

Il est possible que l’avertissement suivant apparaisse :

Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key
associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server,
you must have backups of both the certificate and the private key or you will not be able to open the database.

C’est parce que vous avez oublié de réaliser une sauvegarde du certificat. Cela peut être vérifié aisément :

Use AdventureWorks
GO
SELECT pvt_key_last_backup_date,
Db_name(dek.database_id) AS encrypteddatabase,
c.name AS Certificate_Name
FROM master.sys.certificates c
INNER JOIN sys.dm_database_encryption_keys dek ON c.thumbprint = dek.encryptor_thumbprint;

On voit donc le certificat associé à la paire de clé ainsi que la date de la dernière sauvegarde.

Activation de TDE pour la base

Maintenant que les pré-requis cryptographiques sont présents, on peut activer enfin TDE :

Use AdventureWorks
GO
ALTER DATABASE AdventureWorks SET ENCRYPTION ON ;
GO

Cela se fera en fond de tâche et n’empêchera pas l’utilisation de la base. La durée de l’opération dépendera de la volumétrie, de la version de SQL Server et des CPU présents (cf. Episode 1).

On peu cependant mettre en suspend si jamais cela consommait trop de ressource, à partir de SQL Server 2019 avec les commandes suivantes  :

ALTER DATABASE AdventureWorks SET ENCRYPTION SUSPEND ;
ALTER DATABASE AdventureWorks SET ENCRYPTION RESUME ;

Si jamais vous ne savez plus où vous en êtes du chiffrement de vos bases, vous pouvez également interroger vérifier ainsi :

select
db.name,
EncryptionStatus = CASE encryption_state
WHEN 0 THEN 'Pas de DEK, pas chiffrée' -- oui, alors dans les faits, si c'est le cas il n'y pas de ligne dans la table dek. Mais dans la doc...
WHEN 1 THEN 'DEK présente, pas chiffrée'
WHEN 2 THEN 'Chiffrement en cours'
WHEN 3 THEN 'Chiffrée'
WHEN 4 THEN 'Changement de clé en cours'
WHEN 5 THEN 'Déchiffrement en cours'
WHEN 6 THEN 'Changement de certificat ou clé en cours'
ELSE 'Statut inconnu'
END
from sys.dm_database_encryption_keys dek
LEFT OUTER JOIN sys.databases db ON dek.database_id = db.database_id

Cela nous renvoie ainsi le tableau suivant :

On voit ainsi que notre base AdventureWorks est bien chiffrée (et non pas “chiffrement en cours”). On voit également que la TempDb est chiffrée ! Eh oui : si jamais il y avait des opérations avec des tables temporaires, il faut bien entendu que les données soient chiffrée sur celle-ci. Cela veut donc dire un impact en terme de de performances pour les autres bases métier qui seraient sur le même serveur, même si elles ne sont pas chiffrées !

Intégration du chiffrement TDE dans un groupe de disponibilité Always-On

Maintenant, imaginons que l’on veuille mettre cette base de données dans un groupe de disponibilité AlwaysOn : comment faire ? Il suffit simplement de restaurer le certificat et sa clé privé sur le(s) réplicat(s) secondaire(s) pour pouvoir accéder aux données. C’est également la même procédure si on veut juste restaurer la base sur un autre serveur.

Dans un premier temps, on va s’assurer que le serveur qui nous servira de réplica secondaire a bien une Master Key  :

Use master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'HugEEEEEP@$$word!'
GO

On copie la paire de fichiers sauvegardés avec le certificat plus tôt sur le serveur destination (par exemple dans le répertoire de backup, mais attention à ne pas les y laisser trainer trop longtemps) :

Use master
go
CREATE CERTIFICATE MyServerMasterCertForTDE FROM FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\Master_Certificate_for_TDE.crt'
WITH PRIVATE KEY (FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\Master_Certificate_for_TDE_PrivateKey',
DECRYPTION BY PASSWORD = 'HugEEEEEP@$$word4Cert!') ;
GO

A partir de maintenant, une base de données chiffrée avec une paire de clé issue de ce certificat sera lisible sur ce serveur !

On peut donc par exemple l’intégrer à un groupe de disponibilité déjà présent. Sur le réplica principal (LAB1SQL1 et LAB1SQL2 est le secondaire):

USE [master]
GO
ALTER AVAILABILITY GROUP [LAB1AG]
MODIFY REPLICA ON N'LAB1SQL2' WITH (SEEDING_MODE = AUTOMATIC)
GO

ALTER AVAILABILITY GROUP [LAB1AG]
ADD DATABASE [AdventureWorks];
GO

puis sur le réplica secondaire :

ALTER AVAILABILITY GROUP [LAB1AG] GRANT CREATE ANY DATABASE;
GO

Avec l’Automatic Seeding, la base sera crée et dupliquée chiffrée, sans même passer par l’étape de backup.

Remarques sur les différences de taille de base

Dans l’épisode 1, on parlait de l’impact du chiffrement sur la taille des backups. Ici, dans notre exemple avec SQL Server 2022 et la base AdventureWorks, la base de données sans TDE fait 54 688 Ko, tandis que la version avec TDE fait 52 292 Ko : autant dire que c’est négligeable et que l’écart est lié à la fragmentation après le déchiffrement.

Une fois de plus et comme expliqué dans l’épisode 1, pensez à mettre les sauvegardes de vos clés et certificats dans un endroit sécurisé !! En l’absence de votre certificat, vous serez incapable de restaurer vos données !

Dans le prochain épisode, nous verrons concrètement comment déployer une stratégie de chiffrement avec Always Encrypted : stay tuned !
Continuez votre lecture sur le blog :

• Le chiffrement et SQL Server – Episode 1 : Transparent Data Encryption (TDE) vs Always Encrypted (Capdata team) [SQL Server]
• Le chiffrement et SQL Server – Episode 3 : Always Encrypted (Capdata team) [AzureSQL Server]
• Le chiffrement Oracle : native network encryption (Emmanuel RAMI) [Oracle]
• Retrouver la requête à l’origine d’une erreur 8623 “The query processor ran out of internal resources and could not produce a query plan” (David Baffaleuf) [SQL Server]
• AWS : Backup Restore SQL Server RDS vers une EC2 ou On-Premise et vice versa ! (Emmanuel RAMI) [AWSSQL Server]

L’article Le chiffrement et SQL Server – Episode 2 : Mise en oeuvre de TDE est apparu en premier sur Capdata TECH BLOG.

La gestion efficace de l’espace disque dans PostgreSQL est une préoccupation constante pour les administrateurs de bases de données. C’est là qu’intervient le processus de “vacuum”, une fonctionnalité puissante et complexe qui peut jouer un rôle déterminant dans la performance et la stabilité de votre base de données PostgreSQL. Cependant, comme toute arme à double tranchant, le vacuum apporte à la fois des avantages considérables et des défis potentiels.

Le principal reproche que l’on peut adresser au système de vacuum (et à l’auto-vacuum par extension) est sa consommation importante de ressources. C’est l’une des raisons majeures évoquées par les grandes entreprises qui optent pour un autre SGBD, comme Uber, qui a migré vers MySQL pour remplacer PostgreSQL.

OrioleDB nous propose une approche novatrice de la gestion de la fragmentation (bloat) et de l’espace disque de vos tables PostgreSQL. La principale promesse de ce nouveau moteur développé par la société Oriole est d’éliminer le besoin de vacuum régulier et de longues périodes de maintenance pour nos bases de données.

Cette innovation s’articule autour de journaux d’annulation au niveau de la ligne et du bloc, ainsi que de la fusion automatique des pages. Les journaux d’annulation au niveau des lignes et des blocs offrent un contrôle plus précis, permettant une gestion plus efficace des modifications de données. La fonction de fusion automatique des pages travaille en arrière-plan pour regrouper les données fragmentées, améliorant ainsi encore davantage l’efficacité du système. Le moteur est actuellement disponible en version bêta sur le site d’OrioleDB.

Dans cet article, je vais vous montrer comment installer OrioleDB et réaliser un test comparatif entre une instance classique et une instance OrioleDB en termes d’espace disque occupé. Je vais utiliser notre agent AllDB de supervision des bases de données pour générer des graphiques de la consommation d’espace disque.

Installation d’OrioleDB :

Pour pouvoir tester le nouveau moteur, il nous faut l’installer. Pour cela, deux solutions s’offrent à nous. On peut choisir de le compiler nous-mêmes à partir du code source fourni par Oriole, ou on peut choisir d’utiliser le conteneur Docker mis à notre disposition.

Pour des raisons de rapidité de mise en place, j’ai choisi d’utiliser le conteneur fourni.

Pour commencer, j’installe Docker sur ma machine.

root@ip-172-44-2-190:~$ sudo apt-get update
(...)
Reading package lists... Done
root@ip-172-44-2-190:~$ sudo apt-get install ca-certificates curl gnupg
Reading package lists... Done
(...)
No VM guests are running outdated hypervisor (qemu) binaries on this host.
root@ip-172-44-2-190:~$ sudo install -m 0755 -d /etc/apt/keyrings
root@ip-172-44-2-190:~$ curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo gpg --dearmor -o /etc/apt/keyrings/docker.gpg
root@ip-172-44-2-190:~$ sudo chmod a+r /etc/apt/keyrings/docker.gpg
root@ip-172-44-2-190:~$ echo \
"deb [arch="$(dpkg --print-architecture)" signed-by=/etc/apt/keyrings/docker.gpg] https://download.docker.com/linux/ubuntu \
"$(. /etc/os-release && echo "$VERSION_CODENAME")" stable" | \
sudo tee /etc/apt/sources.list.d/docker.list > /dev/null
sudo apt-get update
Hit:1 http://eu-west-3.ec2.archive.ubuntu.com/ubuntu jammy InRelease
Hit:2 http://eu-west-3.ec2.archive.ubuntu.com/ubuntu jammy-updates InRelease
(...)
No VM guests are running outdated hypervisor (qemu) binaries on this host.

Pour tester que mon installation fonctionne correctement, je peux démarrer l’image hello-world par défaut de Docker.

root@ip-172-44-2-190:~$ sudo docker run hello-world
Unable to find image 'hello-world:latest' locally
latest: Pulling from library/hello-world
719385e32844: Pull complete
Digest: sha256:4f53e2564790c8e7856ec08e384732aa38dc43c52f02952483e3f003afbf23db
Status: Downloaded newer image for hello-world:latest

Hello from Docker!
This message shows that your installation appears to be working correctly.

To generate this message, Docker took the following steps:
1. The Docker client contacted the Docker daemon.
2. The Docker daemon pulled the "hello-world" image from the Docker Hub.
(amd64)
3. The Docker daemon created a new container from that image which runs the
executable that produces the output you are currently reading.
4. The Docker daemon streamed that output to the Docker client, which sent it
to your terminal.

To try something more ambitious, you can run an Ubuntu container with:
$ docker run -it ubuntu bash

Share images, automate workflows, and more with a free Docker ID:
https://hub.docker.com/

For more examples and ideas, visit:
https://docs.docker.com/get-started/

Docker est correctement installé, nous pouvons à présent aller récupérer l’image Oriole depuis les dépôts (repositories). J’ai choisi de récupérer l’image “latest” de la version 14, avec laquelle je vais effectuer mes tests.

root@ip-172-44-2-190:~# docker pull orioledb/orioledb:latest-pg14
latest-pg14: Pulling from orioledb/orioledb
7264a8db6415: Pull complete
4e0888d8a332: Pull complete
4b0510894ae7: Pull complete
dc5da57566a1: Pull complete
9611602787a1: Pull complete
8f68f9452236: Pull complete
5c92ed25d025: Pull complete
11bb162cfe7a: Pull complete
6f375d990892: Pull complete
6ac166496e68: Pull complete
Digest: sha256:4d408916e929ff0bcea11fee91ca5e69b0929be1afa58b5dbbcddcce340089ef
Status: Downloaded newer image for orioledb/orioledb:latest-pg14
docker.io/orioledb/orioledb:latest-pg14

On vérifie que notre image est bien présente, et on l’éxecute :

root@ip-172-44-2-190:~# docker image ls --all
REPOSITORY TAG IMAGE ID CREATED SIZE
orioledb/orioledb latest-pg14 cd7f629f705e 10 days ago 270MB
hello-world latest 9c7a54a9a43c 4 months ago 13.3kB 

root@ip-172-44-2-190:~# docker run -e POSTGRES_PASSWORD=password cd7f629f705e
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... sh: locale: not found
2023-09-21 09:51:08.177 UTC [30] WARNING: no usable system locales were found
ok
syncing data to disk ... ok

Success. You can now start the database server using:

pg_ctl -D /var/lib/postgresql/data -l logfile start

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
waiting for server to start....2023-09-21 09:51:09.294 UTC [36] LOG: OrioleDB public beta 2 started
2023-09-21 09:51:09.295 UTC [36] LOG: starting PostgreSQL 14.9 OrioleDB public beta 2 PGTAG=patches14_16 alpine:3.18+clang-15 build:2023-09-10T16:41:50+00:00 on x86_64-pc-linux-musl, compiled by Alpine clang version 15.0.7, 64-bit
2023-09-21 09:51:09.300 UTC [36] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-09-21 09:51:09.316 UTC [37] LOG: database system was shut down at 2023-09-21 09:51:08 UTC
2023-09-21 09:51:09.316 UTC [38] LOG: orioledb background writer started
2023-09-21 09:51:09.323 UTC [36] LOG: database system is ready to accept connections
done
server started

/usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*

waiting for server to shut down...2023-09-21 09:51:09.350 UTC [36] LOG: received fast shutdown request
.2023-09-21 09:51:09.353 UTC [36] LOG: aborting any active transactions
2023-09-21 09:51:09.354 UTC [38] LOG: orioledb bgwriter is shut down
2023-09-21 09:51:09.357 UTC [36] LOG: background worker "logical replication launcher" (PID 44) exited with exit code 1
2023-09-21 09:51:09.357 UTC [39] LOG: shutting down
2023-09-21 09:51:09.360 UTC [39] LOG: orioledb checkpoint 1 started
2023-09-21 09:51:09.601 UTC [39] LOG: orioledb checkpoint 1 complete
2023-09-21 09:51:09.631 UTC [36] LOG: database system is shut down
done
server stopped

PostgreSQL init process complete; ready for start up.

2023-09-21 09:51:09.712 UTC [1] LOG: OrioleDB public beta 2 started
2023-09-21 09:51:09.764 UTC [1] LOG: starting PostgreSQL 14.9 OrioleDB public beta 2 PGTAG=patches14_16 alpine:3.18+clang-15 build:2023-09-10T16:41:50+00:00 on x86_64-pc-linux-musl, compiled by Alpine clang version 15.0.7, 64-bit
2023-09-21 09:51:09.764 UTC [1] LOG: listening on IPv4 address "0.0.0.0", port 5432
2023-09-21 09:51:09.764 UTC [1] LOG: listening on IPv6 address "::", port 5432
2023-09-21 09:51:09.770 UTC [1] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-09-21 09:51:09.778 UTC [49] LOG: database system was shut down at 2023-09-21 09:51:09 UTC
2023-09-21 09:51:09.778 UTC [50] LOG: orioledb background writer started
2023-09-21 09:51:09.785 UTC [1] LOG: database system is ready to accept connections
2023-09-21 09:54:23.633 UTC [68] FATAL: role "root" does not exist
2023-09-21 09:56:09.884 UTC [51] LOG: orioledb checkpoint 2 started
2023-09-21 09:56:10.100 UTC [51] LOG: orioledb checkpoint 2 complete 

Notre serveur est correctement démarré. Nous pouvons maintenant nous y connecter à partir d’un autre terminal et préparer le cas de test. Pour ce faire, nous allons créer une nouvelle base de données sur laquelle nous effectuerons un test de charge avec pg_bench.

root@ip-172-44-2-190:~# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
11c0860b7abc cd7f629f705e "docker-entrypoint.s…" 2 minutes ago Up 2 minutes 5432/tcp funny_hellman
root@ip-172-44-2-190:~# docker exec -it 11c0860b7abc bash
11c0860b7abc:/# su - postgres
11c0860b7abc:~$ psql
psql (14.9 OrioleDB public beta 2 PGTAG=patches14_16 alpine:3.18+clang-15 build:2023-09-10T16:41:50+00:00)
Type "help" for help.

postgres=# create database pg_bench;
CREATE DATABASE
postgres=# \c pg_bench
You are now connected to database "pg_bench" as user "postgres".
pg_bench=# CREATE TABLE test (
pg_bench(# id integer primary key,
pg_bench(# value1 float8 not null,
pg_bench(# value2 float8 not null,
pg_bench(# value3 float8 not null,
pg_bench(# value4 float8 not null,
pg_bench(# ts timestamp not null
pg_bench(# );
CREATE TABLE
pg_bench=# CREATE INDEX test_value1_idx ON test (value1);
CREATE INDEX
pg_bench=# CREATE INDEX test_value2_idx ON test (value2);
CREATE INDEX
pg_bench=# CREATE INDEX test_value3_idx ON test (value3);
CREATE INDEX
pg_bench=# CREATE INDEX test_value4_idx ON test (value4);
CREATE INDEX
pg_bench=# CREATE INDEX test_ts_idx ON test (ts);
CREATE INDEX 

Notre environnement de test est prêt. Afin de pouvoir comparer les performances avec une instance PostgreSQL plus traditionnelle, j’ai également installé une version legacy en local sur la machine que j’utilise. J’ai créé la même table et les mêmes index que dans mon conteneur Docker. Pour réaliser ce test, j’ai repris le script pg_bench de test fourni par Oriole.

11c0860b7abc:~$ cat test.sql
\set id random(1, 10000000)
INSERT INTO test VALUES(:id, random(), random(), random(), random(), now() - random() * random() * 1800 * interval '1 second')
ON CONFLICT (id) DO UPDATE SET ts = now();

Ce petit script de test permet de lancer des insertions successives de données dans notre table de test. Il est conçu pour maximiser les chances de fragmentation à mesure que nous insérons des données dans notre table, car les doublons de clé primaire entraînent la mise à jour d’anciennes lignes. Cela devrait donc nous permettre de constater la différence d’espace disque entre une version standard de PostgreSQL et la version du moteur développée par Oriole.

Résultats des test :

Mes tests sont réalisés sur une machine Amazon EC2 de type “large”, équipée de 7 gigaoctets de RAM et de 2 unités de traitement central (CPU).

Voici la capture d’écran de l’occupation de l’espace disque pour le premier test, celui concernant l’instance OrioleDB :

On peut observer que la progression de la taille de l’instance est régulière et très modérée. Pendant le test, j’ai surveillé l’utilisation de la RAM de la machine, qui atteignait un pic à 95 % et demeurait relativement stable pendant toute la durée du test. Conformément à la promesse du moteur, aucun processus d’autovacuum n’a été constaté pendant le processus.

En comparaison, le graphique de la base de données non Oriole présente une nette différence :

On observe notamment de nombreux pics et chutes fréquents dus aux vacuums organisés régulièrement. Le tracé n’est pas linéaire.

De plus, en surveillant les valeurs d’utilisation CPU, à la différence d’Oriole où les valeurs étaient constantes car il était toujours en train d’ajouter des données, les moments de vacuum de la base provoquent d’importantes montées de charge, suivies de chutes lorsque ces processus sont terminés sur notre instance de base.

Conclusion :

Sur le blog d’OrioleDB, les graphiques affichés sont le résultat de l’injection de 80 gigaoctets de données dans leur base de données à l’aide de pg_bench. Cependant, lorsque l’on examine les spécifications de la machine qu’ils ont utilisée pour leurs tests, on se rend compte qu’elle n’est pas à la portée de toutes les bourses. La machine utilisée comprend en effet 72 CPU et dispose d’un total de 144 Go de RAM, une configuration que très peu de personnes, principalement en raison du coût élevé de ce type de matériel, pourraient envisager pour leur infrastructure.

Oriole fonctionne, comme nous l’avons vu sur les graphiques que j’ai commentés précédemment. Cependant, il s’agit d’un outil conçu pour les architectures de très grande envergure et les besoins importants en termes d’entrées/sorties de données. Les gains ne sont pas aussi significatifs sur des machines de plus petite taille, qui correspondent davantage à la réalité de nombreuses entreprises actuelles.Continuez votre lecture sur le blog :

• PostgreSQL : optimiser vos opérations vacuum et analyze ! (Emmanuel RAMI) [PostgreSQL]
• Pyrseas et Postgresql : Comparer facilement des schémas de base de données (Sarah FAVEERE) [PostgreSQL]
• La montée de version en zero-downtime : merci la réplication ! (Sarah FAVEERE) [PostgreSQL]
• Containeriser PostgreSQL avec Docker ! (Emmanuel RAMI) [ContainerPostgreSQL]
• Cybertec Migrator : une alternative à ora2pg ? (Sarah FAVEERE) [OraclePostgreSQL]

L’article OrioleDB : la promesse d’un No-Vacuum est apparu en premier sur Capdata TECH BLOG.

Le chiffrement sous SQL Server n’est pas nouveau et il existe sous plusieurs formes, selon les versions. Les deux principales implémentations sont TDE (Transparent Data Encryption, qui est un chiffrement “at rest”) et Always Encrypted (chiffrement par colonne).

La première implémentation du chiffrement est arrivé en 2008 est TDE. C’est une solution assez simple à mettre en oeuvre et qui a le mérite de proposer un élément de réponse aux considérations RGPD.

Always Encrypted est une solution plus complexe à déployer qui nécessitera une évaluation des données à risque, table par table, colonne par colonne. L’architecture applicative devra également être considérée.

SQL Server TDE – chiffrement des données “at rest” :

TDE est apparu avec SQL Server 2008 et uniquement en édition Enterprise, ce qui était un frein à son déploiement pour beaucoup d’entreprise. Depuis SQL Server 2019, cette fonctionnalité est disponible dès l’édition Standard. Dans Azure avec SQL Database, tous les niveaux de service propose le chiffrement TDE et il est même activé par défaut.

L’idée avec TDE est d’appliquer un chiffrement, base de données par base de données, sur les fichiers de données et de log transactionnel. Si un backup de la base est réalisé, celui-ci ne sera qu’une retranscription des fichiers de la base de données et donc sera également chiffré.

Le chiffrement est basé soit sur un certificat ou une paire de clés asymétriques généré par SQL Server, soit par le module EKM. Dans les deux cas, une attention très importante devra être apportée à la sécurisation des clés de chiffrements. En effet, imaginons la destruction du serveur SQL pour une raison quelconque. Si on veut restaurer une sauvegarde d’une base de données sur un autre serveur, il faudra posséder la clé de chiffrement et la déployer sur ce nouveau serveur. En l’absence de cette clé, l’utilisation du backup sera impossible.

Par ailleurs, dans le cas d’une configuration en mirroring ou en AlwaysOn, il faudra copier la clé de chiffrement sur tous les réplicas afin que SQL Server puisse manipuler les données chiffrées.

En conséquence, on comprend bien que TDE a pour objectif de protéger les donnée contre un vol des fichiers de SQL Server : .mdf, .ndf, .ldf et les backups de ces bases de données.

L’accès dans le moteur de SQL Server étant “transparent” comme son nom l’indique, si l’attaque se fait à travers l’applicatif ou le moteur de SQL Server (parce qu’un compte sur le serveur ou dans le réseau a des privilèges suffisants de connexion à la base), cela n’apportera pas de sécurité supplémentaire.

En contrepartie de cette fonctionnalité, SQL Server va consommer un peu plus de ressource CPU pour manipuler ces données. Si vous comptez déployer TDE sur un serveur qui connait déjà une forte charge CPU : attention, donc !

Avant SQL Server 2016, la compression des backups est également problématique. En effet, la compression apporte un caractère aléatoire aux données dans les pages et donc diminue les répétitions de valeurs qui permettent à la compression d’apporter du gain. Ainsi donc, la compression des backups n’apportera quasiment aucun gain d’espace disque tout en consommant des cycles de CPU. Pour peu que vous ayez pris l’habitude de sauvegarder votre backup à un endroit avec un espace assez limité, l’activation de TDE va saturer ce volume et faire échouer vos backups.

A partir de SQL Server 2016, si on ajoute l’option MAXTRANSFERSIZE = 65536 (ou supérieur) à la commande de backup, l’algorithme de backup et chiffrement sera différent, permettant de gagner de l’espace disque.

A partir de SQL Server 2019 CU5, il n’y a plus besoin d’ajouter le paramètre MAXTRANSFERSIZE, du moment qu’il y a l’option COMPRESS d’activé, cela mettra la valeur à 128K et permettra automatiquement de gagner l’espace lié à la compression.

L’activation de TDE est relativement souple : elle va se faire en fond de tâche avec une consommation modérée de ressources, surtout depuis SQL 2016 où ce travail est dévolu au processeur par un accélérateur dédié (Intel AES-NI). Mais si votre serveur est déjà assez chargé niveau CPU, peut-être que cela peut valoir le coup de créer une base de test avec une copie d’une table et lancez le TDE uniquement sur cette base pour en voir l’impact.

Enfin, pensez bien à sauvegarder votre certificat TDE ou votre clé asymétrique de déchiffrement et mettez la à un voir deux endroits sécurisés. Peut-être une clé USB dans une coffre-fort ? Un NAS avec une solution de chiffrement tierce ? Dans tous les cas, il faut bien comprendre que si vous perdez cette clé et que vous rencontrez un problème sur votre serveur chiffré par TDE , vos données sont en grave péril.

SQL Server Always Encrypted – le chiffrement de bout en bout, par colonne :

La solution Always Encrypted est plus récente que TDE et est arrivé avec SQL Server 2016. Il n’est pas nécessaire d’être en édition Enterprise : c’est accessible dès l’édition Standard.

Contrairement à TDE qui va chiffrer l’intégralité de la base de données, Always Encrypted est une option qui s’active colonne par colonne, table par table. Le chiffrement/déchiffrement n’est pas non plus “interne” au serveur SQL, mais géré par l’application. Le but est de proposer que l’administrateur de l’infrastructure (l’administrateur système ou de base de données) ne soit pas en mesure de voir ces données. Seul l’applicatif sera capable de déchiffrer les données.

En effet : la philosophie de cette technologie est vraiment dans son nom “Always Encrypted” : toujours chiffré.

Dans ce schéma, on voit TDE entre la base et le stockage & backup : cela ne veut pas dire qu’AlwaysOn nécessite TDE. Mais l’addition de ces deux technologies permet un chiffrement exhaustif.

On voit également que le chiffrement se fait entre la base de données et l’appli. Cela veut dire qu’il faut penser au driver SQL qui sera utilisé pour se connecter à la base. Si à sa sortie Always Encrypted exigeait un driver ADO .Net , on peut désormais avoir recours à plus d’options. On peut ainsi utiliser Always Encrypted avec des drivers pour :

• .NET Core & .NET Framework
• JDBC
• ODBC
• PHP

Les pages de documentations pour l’utilisation de ces drivers dans le cadre Always Encrypted est disponible à partir d’ici : https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-client-development?view=sql-server-ver16

Always Encrypted va nécessiter la génération de deux type de clés : les Column Encryption Keys (qui vont servir à chiffrer les données des colonnes) et les Column Master Keys (qui protège les Column Encryption Keys). Comme avec TDE on peut utiliser le magasin de certificat de Windows, Azure Key Vault, ou bien un HSM pour les stocker. Cependant, le but est de sortir cette Column Master Key du serveur SQL pour bien séparer les rôles : SQL Server stock de la donnée chiffrée, et le driver compatible Always Encrypted se charge de chiffrer/déchiffrer les données.

Une Column Encryption Key peut chiffrer une ou plusieurs colonnes. Une base de données peut gérer plusieurs Column Encryptions Keys pour gérer des cas métiers avec différents profils d’utilisateurs par exemple. L’algorithme utilisé est un AES-256.

Il y aura donc deux tâches qui devront être considéré spécifiquement : le provisionnements des clés dans un key store approprié (avec le Magasin de Certificats Windows ou Azure Key Vault dans la plupart des cas), et la rotation des clés (parce qu’elles ont été compromises ou bien parce que le certificat est arrivé à expiration après une certaine date). On peut réaliser ces tâches avec SSMS ou en PowerShell.

La bonne pratique est de générer ces clés sur une autre machine, comme par exemple le serveur d’application qui a de toute façon vocation à les avoir pour chiffrer/déchiffrer les données.

SQL Server lui ne possédera que les méta-données de ces clés, qui lui serviront à identifier les colonnes comme recevant ce chiffrement.

Une fois les clés générées, on peut procéder au chiffrement des colonnes. Cependant, il y a un choix à faire : le chiffrement peut-être déterministe ou bien aléatoire.

Un chiffrement déterministe renvoie la même valeur chiffrée pour la même donnée en entrée. Cela peut permettre de deviner certaines informations, mais cela permet aussi de réaliser des opérations comme une recherche d’égalité ( “ma_colonne = ma valeur” et donc jointures), des agrégats et des indexations.

Un chiffrement aléatoire ne renvoie pas les mêmes valeurs pour des données identiques en entrées. C’est plus sécurisé mais toutes ces opérations deviennent impossibles.

Quand cette déclaration est faite dans le modèle de données, et pour peu que l’on est accès à la Column Master Key ainsi que bien sûr un accès à la base, on peut lire et écrire des données chiffrées. Il faut également spécifier dans la chaine de connexion qu’Always Encrypted est activé (en ADO. Net : Column Encryption Setting=enabled , en JDBC : encrypt=true …).

Il existe quelques limitations à l’usage d’Always Encrypted. On notera parmi celles-ci les suivantes :

• Impossible d’utiliser des colonnes de type xml, timestamp, rowversion, image, ntext, text, sql_variant, hierarchyid, geography, geometry, alias et les user-defined types.
• Les chaines de caractères doivent avoir un collation de type _BIN2 pour faire du déterministe.
• Le Full Text est bien entendu impossible.
• Pas de contrainte de valeur par défaut.
• Pas de Change Data Capture.
• Pas de Dynamic Data Masking.
• La réplication logique est impossible (AlwaysOn est possible).
• Les requêtes distribuées sont impossibles.
• Les requêtes cross-databases en joignant sur les colonnes (même avec chiffrement déterministe) sont impossibles.

Plus d’information : https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-ver16

TDE vs Always Encrypted – synthèse des différences :

On peut voir une approche complémentaire de ces deux solutions technologique. Une première phase étant TDE qui est une protection “basique”. Always Encrypted vient ajouter une vraie notion de chiffrement applicative mais qui va nécessiter un travail de réflexion sur les colonnes et les tables à chiffrer. Et sans parler du fait que, si il y a une interface utilisateur devant l’application, il faudrait également que ce flux soit chiffré avec SSL/TLS.

On peut cependant faire la synthèse suivante :

Nous reviendrons prochainement sur la mise en oeuvre technique de TDE et d’Always Encrypted ! Et n’hésitez pas à poser vos questions dans les commentaires.Continuez votre lecture sur le blog :

• Le chiffrement et SQL Server – Episode 3 : Always Encrypted (Capdata team) [AzureSQL Server]
• Le chiffrement et SQL Server – Episode 2 : Mise en oeuvre de TDE (Capdata team) [SQL Server]
• Le chiffrement Oracle : Transparent Data Encryption sur Oracle 19c (Emmanuel RAMI) [Oracle]
• Quelles solutions de chiffrement de données pour MySQL / MariaDB (David Baffaleuf) [MySQL]
• Le chiffrement Oracle : native network encryption (Emmanuel RAMI) [Oracle]

L’article Le chiffrement et SQL Server – Episode 1 : Transparent Data Encryption (TDE) vs Always Encrypted est apparu en premier sur Capdata TECH BLOG.

PostgreSQL est largement utilisé pour sa fiabilité, sa robustesse et ses performances élevées. Cependant, même dans un système aussi puissant que PostgreSQL, des erreurs peuvent survenir. L’une de ces erreurs courantes est “Fatal : Too Many Connections.” Dans cet article, nous allons explorer en détail cette erreur, comprendre pourquoi elle se produit et découvrir comment la résoudre.

Explication de l’erreur “Fatal : Too Many Connections”

L’erreur “Fatal : Too Many Connections” se produit lorsque le nombre de connexions simultanées à la base de données atteint la limite définie dans la configuration de PostgreSQL. Chaque fois qu’une application ou un utilisateur se connecte à PostgreSQL, une nouvelle connexion est établie, et PostgreSQL alloue des ressources pour gérer cette connexion. Si le nombre de connexions actives dépasse la limite prédéfinie, PostgreSQL génère cette erreur “Fatal : Too Many Connections.”

Plusieurs facteurs peuvent contribuer à cette erreur, notamment :

Limite de Connexion : PostgreSQL a une limite de connexion maximale définie dans son fichier de configuration (postgresql.conf). Par défaut, cette limite est souvent assez basse pour éviter de surcharger le système, mais elle peut être ajustée en fonction des besoins de votre application.

Fuites de Connexion : Des fuites de connexion peuvent survenir lorsque des connexions sont ouvertes mais jamais fermées. Cela peut se produire en raison de bogues dans le code de l’application ou d’une gestion incorrecte des connexions.

Trafic Élevé : Si votre application connaît un pic soudain de trafic et que le nombre de connexions simultanées dépasse la limite, cette erreur peut se produire.

Sessions Inactives : Des sessions inactives ou bloquées peuvent également contribuer à la saturation des connexions.

Résoudre l’erreur “Fatal : Too Many Connections”

Maintenant que nous avons une compréhension de pourquoi cette erreur se produit, voyons comment la résoudre.

1. Augmenter la limite de connexions

La première étape consiste à augmenter la limite de connexions dans le fichier de configuration postgresql.conf. Recherchez la ligne suivante et modifiez-la selon vos besoins :

 max_connections = 100 

Assurez-vous de redémarrer PostgreSQL après avoir apporté cette modification.

2. Vérifier les fuites de connexion

Il est essentiel de rechercher et de corriger les fuites de connexion dans votre application. Assurez-vous que chaque connexion ouverte est correctement fermée après son utilisation. Utilisez des outils de profilage et de débogage pour identifier les zones de code qui pourraient causer des fuites.

3. Optimiser les requêtes

Si votre application génère un grand nombre de connexions en raison d’un trafic élevé, il peut être nécessaire d’optimiser vos requêtes SQL pour réduire la charge sur la base de données. Assurez-vous que vos requêtes sont efficaces et utilisez des indexes pour accélérer les opérations de recherche.

4. Surveillance et nettoyage

Mettez en place une surveillance continue de l’état de votre base de données. Identifiez les sessions inactives ou bloquées et mettez en œuvre des mécanismes pour les libérer automatiquement après un certain temps.

5. Équilibrage de charge

Si votre application est soumise à une charge importante, envisagez d’utiliser un équilibrage de charge pour répartir le trafic sur plusieurs serveurs PostgreSQL. Cela peut aider à réduire la pression sur chaque serveur individuel.

En conclusion, l’erreur “Fatal : Too Many Connections” dans PostgreSQL peut être gérée en ajustant la limite de connexions, en traquant et en corrigeant les fuites de connexion, en optimisant les requêtes, en surveillant activement la base de données et en envisageant l’équilibrage de charge lorsque cela est nécessaire. Une compréhension approfondie de la façon dont les connexions fonctionnent dans PostgreSQL est essentielle pour gérer efficacement cette erreur et maintenir la stabilité de votre application.Continuez votre lecture sur le blog :

• [ERROR] Error reading packet from server: Lost connection to MySQL server during query (David Baffaleuf) [Codes erreurMySQL]
• ORA-01555 et Query Duration=0 (Benjamin VESAN) [Oracle]
• Mythe: SQL Server associe un thread à chaque connexion (David Baffaleuf) [SQL Server]
• Linux Out-Of-Memory Killer (OOM-Killer) pour un serveur base de données PostgreSQL (Emmanuel RAMI) [Non classé]
• Texte SQL tronqué dans les vues performance_schema en 5.6 et 5.7, il faut migrer ! (David Baffaleuf) [MySQL]

L’article Fatal : too many connections est apparu en premier sur Capdata TECH BLOG.

Salut à toutes et tous !

Au sein de notre pôle d’administration à distance AllDB, nous traitons depuis presque 20 ans des incidents de base de données. Notre base d’incidents renferme un peu plus d’1 million de problèmes divers et variés sur 5 à 6 moteurs de base de données différents. Chaque incident s’étant produit une à plusieurs fois, pour vous donner un ordre d’idée rien que sur la partie MySQL / MariaDB qui est la moins représentée dans le volume d’incidents générés, nous avons recensé 1.2 milliard d’occurrences de codes erreurs depuis 2005, sur un échantillon de versions allant de la 3.23 à la 8.0 aujourd’hui.

Ce qu’on voulait vous proposer c’est une petite vulgarisation des codes erreurs principaux que nous avons rencontré au fil des années, de ce qu’ils signifient et comment corriger ces erreurs. Cette série va commencer avec MySQL/MariaDB mais se poursuivra avec les autres moteurs SQL Server, Oracle, PostgreSQL, etc…

Comprendre l’erreur Lost connection to MySQL server during query

Cette erreur générique indique simplement que le serveur MySQL ne perçoit plus de signe du programme à l’autre bout du canal d’une connexion cliente. Le problème peut survenir à toutes les étapes du cycle de vie d’une connexion : à son établissement , au milieu de l’exécution d’une requête, lorsque la connexion est inactive depuis un certain temps, lorsque l’instance est saturée et ne prend plus de connexions, ou que le serveur est injoignable (stoppé : MySQL Server has gone away), ou enfin lors d’un transfert de données massives entre le client et le serveur.

Elle sera souvent accompagnée d’un autre message qui précise l’origine du problème, par exemple ici il s’agit de la connexion entre un slave de réplication et le master:

121010 17:32:18 [Note] Start binlog_dump to slave_server(2), pos(mysql-bin.000020, 4137470)
121010 17:34:02 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)
121010 17:34:02 [Note] Slave I_O thread: Failed reading log event, reconnecting to retry, log  mysql-bin.000012  at position 107

Dans ce cas il se peut que le réseau sont coupé entre les 2 machines ou services, ou que le master soit lui-même stoppé. Cela vient du fonctionnement de type pull de la réplication où le slave IO streame les positions ou GTIDs depuis les binlogs du master vers des relaylogs locaux. Il est connecté en permanence via une connexion classique et est donc sujets aux mêmes problèmes que les connections clientes.

Résoudre l’erreur Lost connection to MySQL server during query

Il faut commencer par identifier la cause, qui on l’a vu peut être multiple:

1. Le serveur MySQL est-il démarré et en capacité de répondre à une demande de connexion ? utiliser mysqlping par exemple pour valider que le serveur est disponible
2. Le réseau est-il coupé entre le client et le serveur : firewall, panne réseau, etc… Utiliser un client telnet par exemple avec le nom de l’hôte et le port (par défaut TCP3306) pour valider que le flux est ouvert.
3. Si vous étiez en train de transférer des données (mysqldump, mysqlpump ou LOAD DATA INFILE) il se peut qu’une partie des données dépasse le payload réseau client ou serveur, vérifier vos valeurs de max_allowed_packet des 2 côtés.
4. Côté métrologie, vérifier la valeur de ‘Aborted Connects’ à intervalles réguliers pour évaluer s’il s’agit d’un problème isolé ou d’un blocage global.

   MariaDB [(none)]> show global status like 'Aborted_connects' ;
   +------------------+-------+
   | Variable_name    | Value |
   +------------------+-------+
   | Aborted_connects | 2245  |
   +------------------+-------+
   1 row in set (0.000 sec)
   

Pour retrouver toute la série sur les codes erreurs, c’est par ici : https://blog.capdata.fr/index.php/category/codes-erreur/ !
Continuez votre lecture sur le blog :

• Réplication MySQL : Resynchronisation d’un Slave MySQL (Capdata team) [MySQL]
• Que faire des “[Warning] Aborted connection” avec MySQL ? (Benjamin VESAN) [MySQL]
• Déplacer le répertoire de données (datadir) MySQL vers un nouvel emplacement sur Debian (Capdata team) [MySQL]
• Migrer d’un cluster Galera MariaDB 10.3 vers MariaDB 10.5 avec la réplication logique (David Baffaleuf) [ContainerMySQLNon classé]
• Nouveautés MySQL 8.0 : Variables persistés (Capdata team) [MySQL]

L’article [ERROR] Error reading packet from server: Lost connection to MySQL server during query est apparu en premier sur Capdata TECH BLOG.