Durcissement de la sécurité PostgreSQL avec des exemples pratiques

Introduction

Le durcissement de la sécurité PostgreSQL avec des exemples pratiques doit aider les opérateurs à passer d'un problème observé à un résultat vérifié. Cet article s'adresse aux développeurs, consultants DevOps et équipes techniques de startups. Il relie le durcissement de PostgreSQL, le contrôle d'accès, la gestion des secrets et les permissions à des commandes, des sorties attendues, des signaux d'échec et des décisions de récupération.

L'objectif est la sécurité opérationnelle : observer avant de modifier, limiter le rayon d'impact, utiliser des variables plutôt que des secrets, vérifier le résultat et documenter comment revenir en arrière si l'état attendu n'est pas atteint. Chaque étape comprend une commande concrète, sa sortie attendue et un signal d'échec, afin de construire une base de sécurité reproductible.

Inventaire de la version et de l'environnement

Commencez par identifier la version installée et la topologie de déploiement, car les paramètres de sécurité varient selon les versions de PostgreSQL. Capturez l'état observable actuel avec une commande en lecture seule, puis définissez le résultat attendu et le signal d'échec avant toute modification. Séparez l'observation de l'intervention : enregistrez les horodatages, protégez les informations d'identification et ne modifiez qu'un seul élément ciblé lorsque son rayon d'impact et son chemin de récupération sont compris.

Déterminer la version et l'environnement de PostgreSQL

Exécutez cette commande pour obtenir la version exacte du serveur et le répertoire de configuration :

postgres --version && pg_config --bindir && pg_config --sysconfdir

Sortie attendue sur un système typique basé sur Debian avec PostgreSQL 15 :

postgres (PostgreSQL) 15.3
/usr/lib/postgresql/15/bin
/etc/postgresql/15/main

Signal d'échec : si la commande renvoie « command not found » ou une version inférieure à votre base de référence (par exemple, inférieure à 14), arrêtez-vous et alignez-vous sur la version minimale supportée par votre organisation avant d'appliquer des paramètres destinés aux versions plus récentes.

Ensuite, identifiez l'état du cluster et l'emplacement du répertoire de données. Ceci est en lecture seule et sans danger :

pg_lsclusters

Exemple de sortie :

Ver Cluster Port Status Owner    Data directory               Log file
15  main    5432 online postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log

Si le cluster est arrêté ou introuvable, ne procédez à aucune modification de configuration tant que le cluster n'est pas en ligne et que son chemin de récupération n'a pas été testé.

Chemin de configuration sûre

Pour la sécurité PostgreSQL, le « Chemin de configuration sûre » signifie modifier un seul paramètre ciblé à la fois, avec une sauvegarde et une restauration testée. Vérifiez toujours la valeur actuelle d'abord à l'aide d'une requête en lecture seule, puis appliquez le plus petit changement justifié.

1. Imposer un stockage sécurisé des mots de passe

PostgreSQL stocke les hachages de mots de passe dans pg_authid. Par défaut, il utilise MD5 ou SCRAM-SHA-256 selon password_encryption. Pour vérifier le paramètre actuel :

SHOW password_encryption;

Sortie attendue pour un défaut moderne et sécurisé :

 password_encryption
---------------------
 scram-sha-256
(1 row)

Si la sortie est md5 ou on (un alias pour md5 dans les anciennes versions), modifiez-la :

ALTER SYSTEM SET password_encryption = 'scram-sha-256';
SELECT pg_reload_conf();

Vérification :

SHOW password_encryption;

Sortie attendue :

scram-sha-256

Signal d'échec : si la commande ALTER SYSTEM échoue en raison d'une syntaxe ou d'une permission, vérifiez l'orthographe exacte et confirmez que vous êtes connecté en tant que superutilisateur. Le paramètre prend effet immédiatement pour les nouveaux mots de passe ; les mots de passe existants restent inchangés jusqu'à modification. Pour mettre à jour le mot de passe d'un utilisateur existant afin d'utiliser SCRAM :

ALTER USER app_user WITH PASSWORD 'new_strong_password_placeholder';

Récupération : si le nouveau paramètre provoque des échecs d'authentification pour les clients existants, revenez en arrière avec :

ALTER SYSTEM SET password_encryption = 'md5';
SELECT pg_reload_conf();

2. Restreindre l'authentification des clients avec pg_hba.conf

Modifiez le fichier pg_hba.conf situé dans le répertoire de configuration. Faites d'abord une sauvegarde :

sudo cp /etc/postgresql/15/main/pg_hba.conf /etc/postgresql/15/main/pg_hba.conf.bak
sudo nano /etc/postgresql/15/main/pg_hba.conf

Remplacez les lignes permissives comme :

host    all             all             0.0.0.0/0               md5

par une règle restrictive :

# Autoriser uniquement l'adresse IP du serveur d'application avec scram-sha-256
host    app_db          app_user        192.0.2.10/32            scram-sha-256
# Rejeter toutes les autres connexions distantes
host    all             all             0.0.0.0/0               reject

Après avoir enregistré, rechargez la configuration :

sudo systemctl reload postgresql@15-main

Vérification :

sudo -u postgres psql -c "SELECT * FROM pg_hba_file_rules;"

La sortie attendue inclut votre nouvelle règle avec la colonne error nulle. Si la règle contient une erreur, error affichera une description.

Signal d'échec : si pg_reload_conf() ou systemctl reload échoue, consultez le journal PostgreSQL pour détecter les erreurs de syntaxe. Récupération : restaurez à partir de la sauvegarde :

sudo cp /etc/postgresql/15/main/pg_hba.conf.bak /etc/postgresql/15/main/pg_hba.conf
sudo systemctl reload postgresql@15-main

3. Désactiver les privilèges par défaut inutiles

Révoquez le privilège CONNECT par défaut de PUBLIC sur toutes les bases de données pour empêcher les tentatives de connexion non autorisées :

REVOKE CONNECT ON DATABASE template1 FROM PUBLIC;
REVOKE CONNECT ON DATABASE postgres FROM PUBLIC;
REVOKE CONNECT ON DATABASE app_db FROM PUBLIC;

Ensuite, accordez l'accès uniquement à des rôles spécifiques :

GRANT CONNECT ON DATABASE app_db TO app_user;

Vérification :

SELECT datname, datacl FROM pg_database WHERE datname = 'app_db';

La sortie attendue montre datacl contenant app_user=c/postgres et aucune entrée =c/ pour PUBLIC. Si PUBLIC apparaît toujours, la révocation n'a pas été appliquée.

Récupération : pour restaurer les valeurs par défaut, exécutez :

GRANT CONNECT ON DATABASE app_db TO PUBLIC;

Vérification et diagnostics

La sécurité PostgreSQL nécessite une vérification continue, pas seulement une configuration initiale. Utilisez des requêtes en lecture seule et des vues intégrées pour détecter les écarts par rapport à votre base de référence.

Vérifier les méthodes d'authentification non sécurisées

Exécutez cette requête pour lister toutes les méthodes d'authentification de pg_hba_file_rules :

SELECT type, database, user_name, address, auth_method
FROM pg_hba_file_rules
WHERE auth_method IN ('trust', 'password', 'md5');

Sortie attendue si toutes les règles sont sécurisées :

 type | database | user_name | address | auth_method
------+----------+-----------+---------+-------------
(0 rows)

Si des lignes sont renvoyées, chacune représente une règle qui autorise une méthode d'authentification faible et doit être mise à jour vers scram-sha-256 ou cert.

Auditer les rôles superutilisateur

Listez tous les rôles avec des privilèges de superutilisateur :

SELECT rolname FROM pg_roles WHERE rolsuper = true;

La sortie attendue dans un environnement durci ne montre que le rôle du système d'exploitation postgres ou un compte de secours désigné. Si vous voyez des rôles inattendus comme app_user avec superutilisateur, révoquez-le :

ALTER ROLE app_user WITH NOSUPERUSER;

Vérifier les rôles sans mot de passe

Identifiez les rôles qui peuvent se connecter sans mot de passe (remarque : cette requête fonctionne dans PostgreSQL 14+) :

SELECT rolname FROM pg_authid WHERE rolpassword IS NULL AND rolcanlogin = true;

Si des rôles sont renvoyés, attribuez un mot de passe ou désactivez la connexion :

ALTER ROLE reporting_user WITH PASSWORD 'strong_placeholder_password';
-- ou
ALTER ROLE reporting_user WITH NOLOGIN;

Modes de défaillance et récupération

Même avec une planification minutieuse, des erreurs de configuration surviennent. Anticipez les défaillances courantes et préparez des étapes de récupération qui restaurent le service sans affaiblir la sécurité.

Défaillance : verrouillage après modification de pg_hba.conf

Si vous restreignez accidentellement trop l'accès, vous risquez de ne plus pouvoir vous connecter à distance ni même localement en tant que postgres.

Récupération immédiate :

1. Connectez-vous via le socket local en utilisant l'utilisateur système :

sudo -u postgres psql

1. Si le socket local est également bloqué, modifiez pg_hba.conf directement en tant que root :

sudo nano /etc/postgresql/15/main/pg_hba.conf

1. Ajoutez temporairement une règle pour autoriser l'authentification locale par pair pour l'utilisateur postgres :

local   all             postgres                                peer

1. Rechargez :

sudo systemctl reload postgresql@15-main

1. Ensuite, corrigez les règles d'origine et rechargez à nouveau.

Prévention : conservez toujours une sauvegarde de pg_hba.conf avant de le modifier et testez dans un environnement de préproduction si possible.

Défaillance : le changement de chiffrement du mot de passe casse une application existante

Si vous passez à scram-sha-256 et qu'un ancien client ne prend en charge que MD5, l'authentification échouera avec des messages comme :

FATAL:  password authentication failed for user "app_user"
DETAIL:  User's password is not a valid SCRAM verifier.

Options de récupération :

• Mettez à jour la bibliothèque cliente vers une version qui prend en charge SCRAM (recommandé).
• Réglez temporairement password_encryption = 'md5' et réinitialisez le mot de passe de l'utilisateur, puis planifiez une migration.
• Pour un utilisateur spécifique, vous pouvez définir manuellement un mot de passe MD5 (non recommandé à long terme) :

ALTER USER app_user WITH PASSWORD 'md5';

Restaurez ensuite SCRAM plus tard.

Défaillance : le rechargement échoue en raison d'une erreur de syntaxe

Si vous introduisez une erreur de syntaxe dans postgresql.conf ou pg_hba.conf, le rechargement échouera et PostgreSQL peut continuer à fonctionner avec l'ancienne configuration ou refuser de démarrer.

Consultez le journal :

sudo tail -n 50 /var/log/postgresql/postgresql-15-main.log

Recherchez des lignes comme :

FATAL:  could not load pg_hba.conf

Corrigez l'erreur de syntaxe dans le fichier, puis rechargez à nouveau. Si PostgreSQL refuse de démarrer après un redémarrage, vous pouvez le démarrer en mode mono-utilisateur pour contourner les problèmes de configuration (mais c'est une étape d'urgence).

Liste de contrôle des opérations

Utilisez cette liste de contrôle avant et après l'application de toute modification de sécurité PostgreSQL. Elle résume le flux de travail de l'observation à la vérification.

Liste de contrôle pré-modification

• [ ] Identifiez la version de PostgreSQL avec postgres --version et confirmez qu'elle est prise en charge.
• [ ] Déterminez le nom du cluster et le répertoire de données avec pg_lsclusters.
• [ ] Sauvegardez les fichiers de configuration pertinents (postgresql.conf, pg_hba.conf, pg_ident.conf).
• [ ] Enregistrez la valeur actuelle du paramètre à l'aide d'une requête en lecture seule, par exemple SHOW password_encryption;.
• [ ] Définissez la nouvelle valeur attendue et le signal d'échec.
• [ ] Identifiez qui peut effectuer la modification et qui l'approuve.

Mise en œuvre du changement

• [ ] Appliquez le plus petit changement : un paramètre, une règle ou une autorisation.
• [ ] Utilisez ALTER SYSTEM pour les modifications de postgresql.conf, ALTER ROLE pour les modifications de rôle et modifiez pg_hba.conf pour les règles basées sur l'hôte.
• [ ] Rechargez la configuration avec SELECT pg_reload_conf(); ou systemctl reload.
• [ ] Ne redémarrez pas le service sauf si nécessaire ; le rechargement est moins perturbateur.

Vérification post-modification

• [ ] Exécutez la requête ou la commande de vérification de l'exemple.
• [ ] Comparez la sortie réelle à la sortie attendue.
• [ ] Consultez les journaux PostgreSQL pour détecter les erreurs ou les avertissements :

sudo tail -n 100 /var/log/postgresql/postgresql-15-main.log

• [ ] Tentez de vous connecter en tant que rôle concerné depuis un hôte autorisé et un hôte refusé pour confirmer le contrôle d'accès.
• [ ] Documentez le changement, l'horodatage et les étapes de restauration dans votre runbook d'exploitation.

Plan de restauration

• [ ] Pour les modifications ALTER SYSTEM, conservez la valeur précédente et la commande ALTER SYSTEM SET ... exacte pour revenir en arrière.
• [ ] Pour pg_hba.conf, conservez le fichier de sauvegarde et connaissez la commande de restauration.
• [ ] Pour les modifications de rôle, ayez la commande ALTER ROLE inverse prête.
• [ ] Testez la restauration dans un environnement hors production si possible.

Conclusion

Le durcissement de la sécurité PostgreSQL avec des exemples pratiques n'est utile que si chaque recommandation est spécifique à une version, observable et réversible lorsque la technologie le permet. Copier une commande sans vérifier les prérequis et la sortie attendue n'est pas une procédure d'exploitation.

Comme prochaine étape, choisissez une vérification à faible risque de cet article : vérifiez password_encryption, examinez pg_hba_file_rules ou auditez les rôles superutilisateur. Enregistrez l'état actuel, exécutez la vérification documentée, comparez le résultat avec le signal attendu et passez en revue les dépendances telles que les pilotes clients et les procédures de sauvegarde. Appliquez ensuite un changement de durcissement et vérifiez-le.

Un flux de travail technique fiable rend les défaillances visibles, protège les valeurs sensibles, limite les changements à la ressource prévue et définit la vérification de la récupération avant qu'un incident ne force la décision. En suivant les modèles présentés ici, vous pouvez construire une base de sécurité PostgreSQL à la fois pratique et durable.