Erreurs de configuration PostgreSQL : guide pratique pour des changements sécuritaires

Introduction

Les erreurs de configuration de PostgreSQL peuvent provoquer des pannes, dégrader les performances ou créer des failles de sécurité difficiles à retracer. Ce guide vous propose des procédures pratiques, adaptées à la version, pour observer, modifier, valider et restaurer en toute sécurité les paramètres de PostgreSQL. Il s'adresse aux développeurs, aux ingénieurs DevOps et aux équipes de start-up qui gèrent PostgreSQL en production ou en préproduction.

Chaque section associe une erreur courante à une commande concrète, à la sortie attendue, au signal d'échec et au chemin de récupération. L'objectif est la sécurité opérationnelle : ne jamais modifier un paramètre sans savoir comment vérifier le résultat et comment l'annuler en cas de problème.

Vous apprendrez à :

• Inventorier votre version et votre topologie PostgreSQL avant toute intervention.
• Utiliser des commandes en lecture seule pour capturer l'état actuel.
• Effectuer le plus petit changement possible avec un rayon d'impact clair.
• Valider le changement à l'aide de contrôles documentés.
• Revenir rapidement en arrière en cas d'échec de validation.
• Élaborer une liste de contrôle opérationnelle reproductible qui réduit les erreurs humaines.

Tous les exemples utilisent des espaces réservés tels que <votre_base_de_donnees>, <votre_utilisateur> et <votre_table> pour que les commandes puissent être exécutées en toute sécurité dans votre environnement. N'exécutez jamais une commande issue d'un article de blog sans avoir vérifié au préalable ses prérequis et sa sortie attendue.

Inventaire de la version et de l'environnement

Avant de modifier un paramètre PostgreSQL, identifiez la version installée, la topologie de déploiement et les prérequis pertinents. Une erreur commise sur PostgreSQL 12 peut être inoffensive sur PostgreSQL 15, et inversement. Connaître la version et l'environnement réduit le rayon d'impact et détermine la syntaxe correcte pour la validation et la récupération.

1.1 Identifier la version de PostgreSQL et les détails du serveur

Exécutez la commande en lecture seule suivante depuis l'hôte PostgreSQL ou un client ayant accès :

psql -U <votre_utilisateur> -d <votre_base_de_donnees> -c "SELECT version();"

La sortie attendue ressemble à :

PostgreSQL 15.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 12.2.0, 64-bit

Pour une vue plus structurée, interrogez pg_settings :

SELECT name, setting, unit, context, vartype, source
FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'max_connections');

Exemple de résultat :

      name      | setting | unit |  context   | vartype |       source
----------------+---------+------+------------+---------+--------------------
 max_connections| 100     |      | postmaster | integer | configuration file
 shared_buffers | 16384   | 8kB  | postmaster | integer | configuration file
 work_mem       | 4096    | kB   | user       | integer | default

Cela vous indique la valeur actuelle, l'unité effective et la provenance du paramètre (fichier de configuration, ligne de commande ou valeur par défaut compilée). Ne sautez pas cette étape : si source n'est pas configuration file, la modification de postgresql.conf n'affectera pas le serveur en cours d'exécution tant que le mécanisme approprié n'aura pas été utilisé.

1.2 Capturer la topologie de déploiement

Déterminez si PostgreSQL s'exécute sur du matériel nu, une machine virtuelle, un conteneur ou un service géré :

• Matériel nu ou machine virtuelle : vérifiez le système d'exploitation et le gestionnaire de paquets.
• Docker : exécutez docker inspect <nom_du_conteneur> pour voir les volumes montés et les variables d'environnement.
• Géré (par exemple, Amazon RDS, Google Cloud SQL) : de nombreux paramètres sont gérés via l'API ou la console du fournisseur, pas via postgresql.conf.

Exemple pour Docker :

docker ps --filter "name=postgres" --format "{{.Names}}\t{{.Image}}\t{{.Ports}}"

La sortie pourrait être :

my-postgres  postgres:15.3  0.0.0.0:5432->5432/tcp

1.3 Vérifier les prérequis et les dépendances

Listez les extensions qui peuvent affecter le comportement de la configuration :

SELECT extname, extversion FROM pg_extension ORDER BY extname;

Si vous prévoyez de modifier les paramètres de mémoire, vérifiez la RAM système disponible et l'utilisation mémoire actuelle de PostgreSQL :

free -h

Exemple :

              total        used        free      shared  buff/cache   available
Mem:           15Gi       2.1Gi       8.4Gi       234Mi       4.9Gi        12Gi

Enregistrez toujours ces observations avec des horodatages. Utilisez un outil comme script ou copiez la sortie dans un journal d'exploitation. Cet instantané constitue votre base de référence pour le retour en arrière et le dépannage.

Chemin de configuration sécuritaire

Un changement de configuration sécuritaire suit une séquence stricte : observer, planifier, modifier, valider, puis revenir en arrière si nécessaire. La plus grande erreur est d'effectuer plusieurs modifications à la fois ou de modifier un serveur en production sans plan de récupération. Cette section présente un flux de travail sécuritaire en utilisant work_mem comme exemple.

2.1 Observer l'état actuel

Interrogez le paramètre actuel et son contexte :

SELECT name, setting, unit, context, reset_val
FROM pg_settings
WHERE name = 'work_mem';

Sortie attendue :

  name   | setting | unit | context | reset_val
---------+---------+------+---------+-----------
 work_mem| 4096    | kB   | user    | 4096

Ici, context = user signifie que le paramètre peut être modifié par session sans redémarrer le serveur. reset_val est la valeur utilisée lors de l'exécution de RESET. Ceci est crucial pour le retour en arrière.

2.2 Déterminer le plus petit changement justifié

Supposons que votre application exécute de grands tris et que vous souhaitiez augmenter work_mem de 4 Mo à 16 Mo pour une session spécifique uniquement. Cela évite un changement global qui pourrait épuiser la mémoire si de nombreuses sessions s'exécutent simultanément. Le rayon d'impact est une session, pas l'ensemble du serveur.

2.3 Effectuer le changement de manière réversible

Privilégiez les changements au niveau de la session lorsque cela est possible pour les paramètres ajustables :

SET work_mem = '16MB';

Confirmez ensuite :

SHOW work_mem;

Sortie attendue :

 work_mem
----------
 16MB

Si vous avez besoin d'un changement qui persiste entre les sessions mais pas au redémarrage du serveur, utilisez ALTER DATABASE ou ALTER ROLE :

ALTER DATABASE <votre_base_de_donnees> SET work_mem = '16MB';

Vérifiez avec :

SELECT datname, datconfig FROM pg_database WHERE datname = '<votre_base_de_donnees>';

Exemple de résultat :

   datname    |        datconfig
--------------+-------------------------
 votre_base_de_donnees| {work_mem=16MB}

Si le changement doit être global, modifiez postgresql.conf ou utilisez ALTER SYSTEM, qui écrit dans postgresql.auto.conf :

ALTER SYSTEM SET work_mem = '16MB';

Vérifiez le contenu du fichier auto.conf (en dehors de psql) :

cat /var/lib/postgresql/15/main/postgresql.auto.conf

Exemple :

# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
work_mem = '16MB'

Contrairement aux changements au niveau de la session, les changements globaux nécessitent un rechargement du serveur (pg_ctl reload ou SELECT pg_reload_conf();) pour prendre effet pour les nouvelles sessions, et certains paramètres nécessitent un redémarrage.

2.4 Énoncer explicitement le rayon d'impact

Avant de modifier, demandez-vous :

• Cela affecte-t-il une session, une base de données, un rôle ou toutes les connexions ?
• Nécessite-t-il un rechargement ou un redémarrage complet ?
• Pourrait-il provoquer une pression mémoire, des pics d'E/S disque ou des échecs de connexion ?

Notez cela dans votre ticket de changement. Pour l'exemple de work_mem : « Augmentation limitée à une session à 16 Mo ; aucun redémarrage nécessaire ; risque d'utilisation mémoire plus élevée pour une session uniquement. »

Vérification et diagnostics

Après avoir effectué un changement, vérifiez qu'il a pris effet comme prévu et qu'il n'introduit pas de nouveaux problèmes. Utilisez à la fois des requêtes SQL et des outils de surveillance système.

3.1 Vérifier le paramètre effectif

Pour les changements au niveau de la session :

SHOW work_mem;

Pour les changements globaux, ouvrez une nouvelle session (important : les sessions existantes conservent l'ancienne valeur) et exécutez la même commande SHOW.

Vérifiez également la colonne source :

SELECT name, setting, source, sourcefile, sourceline
FROM pg_settings
WHERE name = 'work_mem';

Exemple de sortie pour un changement effectué via ALTER SYSTEM :

  name   | setting |   source   |          sourcefile           | sourceline
---------+---------+------------+-------------------------------+------------
 work_mem| 16384   | configuration file | /var/lib/postgresql/15/main/postgresql.auto.conf | 3

Si source est toujours default ou session, votre changement ne s'est pas appliqué globalement. Vérifiez si vous avez modifié le bon fichier et si le serveur a été rechargé.

3.2 Tester le comportement réel

Pour les paramètres de mémoire, exécutez une requête qui exerce l'opération concernée. Par exemple, testez un tri qui utilise work_mem :

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM <grande_table> ORDER BY <colonne_aleatoire>;

Regardez la ligne Sort Method dans la sortie :

Avant l'augmentation (4 Mo), vous pourriez voir :

Sort Method: external merge  Disk: 8496kB

Après être passé à 16 Mo, vous pourriez voir :

Sort Method: quicksort  Memory: 12584kB

Cela confirme que le paramètre a eu l'effet souhaité. Si le tri déborde toujours sur le disque, votre work_mem est peut-être encore trop petit ou le planificateur n'utilise pas le plan attendu.

3.3 Vérifier les effets secondaires indésirables

Surveillez les métriques système autour de la fenêtre de changement :

• Connexions : SELECT count(*) FROM pg_stat_activity;
• Verrous : SELECT * FROM pg_locks WHERE NOT granted;
• Activité des points de contrôle : SELECT * FROM pg_stat_bgwriter;

Sous Linux, utilisez vmstat 1 ou iostat -x 1 pour surveiller la mémoire et les E/S. Si vous avez augmenté shared_buffers, surveillez une baisse de la mémoire libre ou un pic d'utilisation du swap.

Créez un script de diagnostic qui capture les métriques les plus pertinentes avant et après le changement :

#!/bin/bash
# Capture un instantané des paramètres et de l'activité de PostgreSQL
psql -U <votre_utilisateur> -d <votre_base_de_donnees> -c "SELECT name, setting FROM pg_settings WHERE name IN ('shared_buffers','work_mem','max_connections');" > avant_parametres.txt
psql -U <votre_utilisateur> -d <votre_base_de_donnees> -c "SELECT count(*) FROM pg_stat_activity;" > avant_activite.txt
# ... effectuer le changement ...
psql -U <votre_utilisateur> -d <votre_base_de_donnees> -c "SELECT name, setting FROM pg_settings WHERE name IN ('shared_buffers','work_mem','max_connections');" > apres_parametres.txt
psql -U <votre_utilisateur> -d <votre_base_de_donnees> -c "SELECT count(*) FROM pg_stat_activity;" > apres_activite.txt
diff avant_parametres.txt apres_parametres.txt
diff avant_activite.txt apres_activite.txt

Cela vous donne une comparaison claire avant/après.

Modes d'échec et récupération

Même avec une planification minutieuse, les changements de configuration peuvent échouer ou causer des problèmes. Cette section décrit les modes d'échec courants et les procédures de récupération étape par étape.

4.1 Le serveur ne démarre pas après un changement de configuration

Symptômes : pg_ctl start échoue, ou le journal affiche des erreurs comme « invalid value for parameter » ou « could not write lock file ».

Récupération immédiate :

1. Consultez le journal PostgreSQL (généralement dans /var/log/postgresql/postgresql-15-main.log ou spécifié par logging_collector).
2. Identifiez le paramètre erroné. Si vous avez modifié postgresql.conf, commentez la ligne incorrecte et redémarrez.
3. Si vous avez utilisé ALTER SYSTEM, supprimez manuellement l'entrée erronée de postgresql.auto.conf (après avoir arrêté le serveur) ou utilisez ALTER SYSTEM RESET <parametre>; si le serveur est suffisamment fonctionnel pour accepter des connexions.
4. Si le serveur est complètement arrêté et que vous ne pouvez pas modifier les fichiers, démarrez avec une configuration minimale : pg_ctl start -o "-c config_file=/chemin/vers/postgresql_propre.conf".

Exemple de journal d'erreur :

LOG:  invalid value for parameter "shared_buffers": "999999999"
FATAL:  configuration file "/etc/postgresql/15/main/postgresql.conf" contains errors

4.2 Le changement provoque une régression des performances

Symptômes : requêtes plus lentes, CPU élevé ou pics d'E/S disque après avoir modifié un paramètre.

Récupération : rétablissez le paramètre à sa valeur précédente. Si vous l'avez modifié globalement, utilisez ALTER SYSTEM RESET ou modifiez le fichier et rechargez. Pour les changements de session, reconnectez-vous simplement.

Exemple de retour à la valeur par défaut :

ALTER SYSTEM RESET work_mem;
SELECT pg_reload_conf();

Vérifiez :

SHOW work_mem;  -- devrait afficher la valeur par défaut ou reset_val de pg_settings

Si vous n'avez pas enregistré la valeur précédente, consultez reset_val dans pg_settings. C'est la valeur par défaut compilée ou la valeur de postgresql.conf avant toute substitution de session.

4.3 Le changement entraîne des problèmes de connexion

Si vous avez modifié max_connections et l'avez réglé trop haut, le serveur peut consommer trop de mémoire ou ne pas démarrer. S'il est trop bas, les nouvelles connexions sont refusées avec « sorry, too many clients already ».

Pour récupérer, réduisez ou augmentez max_connections à une valeur sûre. Ce paramètre nécessite un redémarrage du serveur.

Avant de redémarrer, assurez-vous qu'aucune transaction critique n'est active. Utilisez pg_ctl -m smart stop pour attendre que tous les clients se déconnectent, ou pg_ctl -m fast stop pour un arrêt plus propre.

Exemple de retour en arrière pour max_connections :

ALTER SYSTEM SET max_connections = '100';

Puis redémarrez :

pg_ctl restart -m fast

4.4 Récupération après un changement destructeur accidentel

Si vous avez accidentellement supprimé une table ou corrompu des données à la suite d'un changement de configuration (peu probable, mais possible si vous avez exécuté une commande pendant le dépannage), vous devez vous appuyer sur les sauvegardes. Effectuez une récupération à un point dans le temps (PITR) si l'archivage WAL est activé.

Étapes de base de la PITR :

1. Arrêtez le serveur.
2. Restaurez la sauvegarde de base dans le répertoire de données.
3. Créez un fichier recovery.signal dans le répertoire de données.
4. Définissez restore_command dans postgresql.conf pour récupérer les fichiers WAL depuis l'archive.
5. Réglez recovery_target_time juste avant l'erreur.
6. Démarrez le serveur et surveillez la récupération.

Il s'agit d'une procédure de récupération avancée. Testez toujours régulièrement votre processus de sauvegarde et de restauration.

Liste de contrôle opérationnelle

Utilisez cette liste de contrôle avant et après tout changement de configuration PostgreSQL. Remplissez les valeurs concrètes de votre environnement. Un exemple rempli à titre illustratif est présenté.

Liste de contrôle avant changement

Étapes d'exécution du changement

1. Ouvrez deux sessions de terminal : une pour le changement, une pour la surveillance.
2. Dans la session de surveillance, démarrez vmstat 1 ou une boucle sur pg_stat_activity.
3. Effectuez le changement en utilisant la méthode la moins invasive (session, base de données, rôle, global).
4. Vérifiez immédiatement avec SHOW ou SELECT depuis pg_settings.
5. Exécutez une requête ou une charge de travail représentative pour confirmer le comportement.
6. Vérifiez les nouvelles erreurs dans le journal PostgreSQL.

Vérification après changement

Si un contrôle échoue, exécutez immédiatement le plan de retour en arrière et enquêtez avant de réessayer.

Exemples de commandes de retour en arrière

Niveau session :

RESET work_mem;

Niveau base de données :

ALTER DATABASE <votre_base_de_donnees> RESET work_mem;

Global (ALTER SYSTEM) :

ALTER SYSTEM RESET work_mem;
SELECT pg_reload_conf();

Retour en arrière par modification manuelle du fichier : commentez la ligne dans postgresql.conf, puis rechargez : pg_ctl reload -D /var/lib/postgresql/15/main.

Conclusion

Les erreurs de configuration de PostgreSQL sont évitables lorsque vous suivez un processus discipliné : inventorier l'environnement, observer les paramètres actuels, effectuer des changements minimaux et réversibles, vérifier l'effet avec des requêtes et des métriques concrètes, et disposer d'un chemin de récupération testé. Copier une commande d'un forum sans connaître la version, le contexte et la méthode de retour en arrière n'est pas une procédure opérationnelle.

Comme prochaine étape, choisissez un paramètre à faible risque dans votre instance PostgreSQL — work_mem est un bon candidat — et suivez l'intégralité du flux de travail de ce guide : enregistrez l'état actuel, modifiez-le dans une session, vérifiez avec SHOW et un test EXPLAIN, puis revenez en arrière avec RESET. Documentez les commandes et sorties exactes dans le journal d'exploitation de votre équipe.

Prenez l'habitude de capturer les états avant et après pour chaque changement, d'utiliser des espaces réservés plutôt que de vrais secrets dans vos notes, et de définir la vérification de récupération avant qu'un incident ne force la décision. Avec ces pratiques, vous transformez la configuration de PostgreSQL d'une source d'inquiétude en une tâche d'ingénierie gérable et observable.