Dépannage réseau PostgreSQL avec exemples pratiques : guide d’implémentation étape par étape

Intro

Les pannes de connectivité PostgreSQL ressemblent souvent à des bogues applicatifs jusqu’à ce que vous prouviez qu’il s’agit de problèmes réseau. Ce guide pratique montre comment isoler les problèmes réseau PostgreSQL avec un petit ensemble d’étapes sûres, exécutables sous Linux, macOS et Windows. Vous recueillerez les bons faits, ferez des changements réversibles, vérifierez chaque étape avec des commandes concrètes et rétablirez le service de façon fiable si quelque chose tourne mal.

Des exemples construits sont utilisés tout au long. Remplacez les noms d’hôtes, adresses IP, ports et utilisateurs par les valeurs de votre environnement.

Ce que vous allez apprendre :

Comment inventorier les versions, la topologie et les chemins de fichiers critiques
Comment modifier en toute sécurité les réglages réseau de PostgreSQL
Comment tester DNS, routage, ports et pare‑feux sans tâtonner
Comment lire les erreurs de connexion PostgreSQL et confirmer la correction
Comment gérer les échecs courants, revenir en arrière et rétablir le service

Inventaire des versions et de l’environnement

Avant de changer quoi que ce soit, consignez votre état initial afin que les tests soient reproductibles.

Prérequis :

Accès shell sur le serveur PostgreSQL
Une machine cliente pour des tests à distance
Droits de lecture de la configuration et des journaux PostgreSQL
Privilèges élevés pour les vérifications de pare‑feu si nécessaire

Recueillir les faits :

Versions de PostgreSQL et du client

Sur le serveur :

postgres --version
psql --version

Sur chaque hôte client :

psql --version

Confirmer où PostgreSQL lit sa configuration (depuis une session psql fonctionnelle ou via socket local) :

psql -d postgres -c 'show config_file;'
psql -d postgres -c 'show hba_file;'
psql -d postgres -c 'show data_directory;'

Interfaces réseau et adresses

Linux :

ip addr
ip route
hostname -I

macOS :

ifconfig
route -n get default
ipconfig getifaddr en0   # ou votre interface

Windows (PowerShell) :

Get-NetIPAddress
Get-NetRoute -DestinationPrefix 0.0.0.0/0

État du service (exemples variables selon distribution/installation)

Linux systemd :

systemctl status postgresql || systemctl status postgresql-15

macOS Homebrew :

brew services list | grep -i postgres

Windows (le nom du service varie) :

Get-Service | Where-Object {$_.Name -like 'postgres*'}

Environnement client (influence la façon dont psql se connecte)

Linux/macOS :

printenv | grep -E '^PGHOST|^PGPORT|^PGUSER|^PGDATABASE'

Windows PowerShell :

Get-ChildItem Env: PG*

Carte de topologie minimale (exemple) :

Serveur : db1.internal.example.com, 10.10.20.15, port 5432
Hôte applicatif : app1.internal.example.com, 10.10.20.50
Bastion externe : bastion.example.com, 203.0.113.10

Voie de configuration sûre

Modifiez une chose à la fois et confirmez que cela fonctionne avant de poursuivre. Sauvegardez les fichiers de configuration et préparez un plan de retour arrière.

Sauvegarde avant modification (chemins d’exemple) :

# Sur le serveur PostgreSQL
cp /var/lib/pgsql/15/data/postgresql.conf /var/lib/pgsql/15/data/postgresql.conf.bak.$(date +%Y%m%d%H%M%S)
cp /var/lib/pgsql/15/data/pg_hba.conf /var/lib/pgsql/15/data/pg_hba.conf.bak.$(date +%Y%m%d%H%M%S)

Rechargement vs redémarrage :

Le rechargement applique de nombreux changements de configuration sans déconnecter les sessions :

psql -d postgres -c 'select pg_reload_conf();'

Un redémarrage est requis lorsque vous modifiez listen_addresses, port ou ssl.

Séquence de changements recommandée (étroite, mesurable et testable localement) :

Prouvez d’abord la connectivité locale.

psql -h 127.0.0.1 -p 5432 -U postgres -d postgres -c 'select 1;'

Attendu : une seule ligne avec 1.

Activez l’écoute sur l’adresse IP spécifique du serveur (évitez 0.0.0.0 sauf justification). Dans postgresql.conf :

listen_addresses = '127.0.0.1,10.10.20.15'
port = 5432

Redémarrez PostgreSQL, puis vérifiez les sockets d’écoute (Linux) :

ss -ltnp | grep 5432

Ajoutez les entrées pg_hba.conf les plus restrictives nécessaires pour votre sous‑réseau ou hôte client (exemple autorisant l’utilisateur app depuis 10.10.20.0/24 via SCRAM) :

# TYPE  DATABASE  USER  ADDRESS          METHOD
host    all       app   10.10.20.0/24    scram-sha-256

Rechargez et testez depuis l’hôte applicatif. N’élargissez la portée que si une raison visible et vérifiée l’exige.

Vérifications et diagnostics

Utilisez les contrôles ci‑dessous pour isoler la couche défaillante. Arrêtez‑vous quand vous avez trouvé la cause.

Points de contact de référence (exemples) :

Port : généralement 5432. Vérifier avec : psql -p 5432, ss -ltnp, Test-NetConnection.
listen_addresses : souvent 127.0.0.1, <serverIP>. Vérifiez via show config_file; et l’inspection du fichier.
pg_hba.conf : doit autoriser le sous‑réseau/hôte client. Vérifiez via show hba_file; et l’ordre des règles.
DNS A/AAAA : db1.internal -> 10.10.20.15. Vérifiez avec dig, nslookup ou getent hosts.
Pare‑feu : autorisez TCP 5432 vers le serveur. Vérifiez avec ufw/firewalld/nftables/iptables/Windows Firewall.

Correspondances symptôme‑cause (démarrages rapides) :

Délai d’attente à la connexion : problème de routage ou de filtrage. Premier contrôle : nc -vz host 5432, traceroute/tracert.
Connexion refusée : pas d’écoute ou blocage local. Premier contrôle : ss -ltnp, état du pare‑feu.
FATAL : no pg_hba.conf entry : règle pg_hba manquante ou incorrecte. Premier contrôle : ordre des règles et CIDR correspondant.
FATAL : password authentication failed : mauvais identifiants ou rôle. Premier contrôle : tester avec un utilisateur de référence.
Erreurs SSL ou FATAL liées à ssl/hostssl : désalignement TLS. Premier contrôle : hostssl vs host, sslmode du client.
Mauvais serveur de base de données : DNS ou fichier hosts conduisent ailleurs. Premier contrôle : dig +short, getent hosts.

DNS et résolution de noms

Confirmez que le nom d’hôte utilisé par les clients résout l’adresse IP attendue.

Linux :

getent hosts db1.internal.example.com

Multiplateforme avec dig (à installer si besoin) :

dig +short db1.internal.example.com A

Windows (PowerShell) :

Resolve-DnsName db1.internal.example.com

Attendu : l’IP de votre serveur PostgreSQL. Si elle est erronée ou renvoie plusieurs IP, testez par IP le temps de corriger les enregistrements. S’il existe un DNS à vues multiples (split‑horizon), testez depuis chaque segment réseau.

Accessibilité et routage

Accessibilité ICMP (peut être bloquée ; un échec est informatif mais non définitif) :

ping -c 3 10.10.20.15     # Linux/macOS
Test-Connection 10.10.20.15 -Count 3   # Windows

Chemin de routage :

traceroute 10.10.20.15    # Linux/macOS (à installer si besoin)
tracert 10.10.20.15       # Windows

Quelle route sera utilisée (Linux) :

ip route get 10.10.20.15

Si la route cible la mauvaise interface ou passerelle, corrigez les routes ou utilisez l’IP d’interface appropriée dans listen_addresses.

PostgreSQL écoute‑t‑il ?

Vérifiez sur le serveur la présence d’un écouteur TCP sur l’interface et le port attendus.

Linux :

ss -ltnp | grep 5432
# ou
lsof -iTCP:5432 -sTCP:LISTEN

macOS :

lsof -n -iTCP:5432 -sTCP:LISTEN

Windows (PowerShell) :

Get-NetTCPConnection -LocalPort 5432 -State Listen

Attendu : des entrées liées à 127.0.0.1 et à l’IP du serveur. Si seule 127.0.0.1 apparaît, les clients distants subiront des délais d’attente ou des refus.

Vérifications de pare‑feu

Pare‑feu de l’hôte local :

Ubuntu/Debian avec UFW :

sudo ufw status verbose

firewalld (famille RHEL) :

sudo firewall-cmd --list-all
sudo firewall-cmd --list-ports

nftables (générique) :

sudo nft list ruleset | grep -i 5432 -n

iptables (héritage) :

sudo iptables -S | grep 5432

Windows Defender Firewall :

netsh advfirewall firewall show rule name=all | findstr 5432

Si l’entrée 5432 est bloquée en entrée sur le serveur, ajoutez une règle d’autorisation TCP 5432 limitée aux adresses sources nécessaires. Préférez une autorisation par sous‑réseau spécifique plutôt qu’une ouverture générale.

Connectivité du port depuis le client

Multiplateforme avec netcat (à installer si besoin) :

nc -vz db1.internal.example.com 5432

Attendu : succeeded/open. La raison d’un échec (timeout/refused) oriente les étapes suivantes.

Windows PowerShell :

Test-NetConnection -ComputerName db1.internal.example.com -Port 5432 -InformationLevel Detailed

Attendu : TcpTestSucceeded: True. Si False, notez NoRoute, TimedOut ou Refused.

Telnet peut aussi indiquer ouvert vs fermé/refusé :

telnet db1.internal.example.com 5432

Authentification PostgreSQL et HBA

Depuis le client, tentez une requête minimale :

psql 'host=db1.internal.example.com port=5432 user=app dbname=postgres' -c 'select version();'

Messages fréquents et leur signification :

FATAL : no pg_hba.conf entry for host X, user Y, database Z
Cause : règle pg_hba manquante ou non concordante. Corrigez la règle et rechargez.
FATAL : password authentication failed for user « app »
Cause : mauvais mot de passe ou utilisateur inexistant.
psql : could not connect to server: Connection timed out
Cause : problème de routage ou de pare‑feu.
psql : could not connect to server: Connection refused
Cause : aucun écouteur sur cette IP:port ou blocage local.

L’ordre des règles compte : la première correspondance gagne. Placez les autorisations spécifiques au‑dessus des règles plus larges.

Exemple construit autorisant le SSL depuis un sous‑réseau et l’accès local par socket :

# Autoriser les connexions locales par socket pour l’admin
local   all         postgres                peer
# Autoriser les connexions SSL depuis le sous-réseau applicatif avec SCRAM
hostssl all         app     10.10.20.0/24   scram-sha-256
# Optionnel : lecture seule depuis un hôte unique
hostssl reporting   ro_user 10.10.20.55/32  scram-sha-256

Rechargez après toute modification :

psql -d postgres -c 'select pg_reload_conf();'

Alignement du mode TLS/SSL

Si les clients exigent TLS mais que le serveur n’est pas configuré, vous verrez des erreurs liées au SSL. Testez explicitement :

psql 'host=db1.internal.example.com user=app dbname=postgres sslmode=require' -c 'show ssl;'

Attendu quand TLS est actif : ssl = on.

Pour activer TLS sur le serveur (exemple) :

# postgresql.conf
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file  = 'server.key'

Utilisez des entrées hostssl dans pg_hba.conf pour les clients concernés. Un redémarrage est requis après l’activation de ssl.

Journaux et inspection de paquets

Les journaux PostgreSQL indiquent souvent la raison des refus. Localisez les journaux via :

psql -d postgres -c 'show log_destination;'
psql -d postgres -c 'show logging_collector;'
psql -d postgres -c 'show log_directory;'

Inspectez le journal le plus récent pour des erreurs d’authentification ou de connexion.

Capture de paquets (avancé ; limitez la portée/la durée et évitez de capturer des identifiants) :

sudo tcpdump -ni eth0 host 10.10.20.50 and port 5432 -c 20 -w /tmp/pg_5432.cap

Attendu : poignée de main SYN/SYN‑ACK/ACK pour un établissement TCP réussi. Une suite de SYN sans réponse indique un filtrage sur le chemin.

Modes de panne et récupération

Cette section associe des problèmes courants à des actions de rétablissement et de retour arrière concrètes.

Le serveur n’écoute que sur 127.0.0.1
Symptôme : connexion distante refusée ou expirée ; ss n’affiche que 127.0.0.1:5432.
Correctif : définissez listen_addresses pour inclure l’IP du serveur, redémarrez et vérifiez.
Vérifier :
ss -ltnp | grep 5432
nc -vz 10.10.20.15 5432
Retour arrière : restaurez la sauvegarde de postgresql.conf et redémarrez si nécessaire.

Règle pg_hba.conf erronée ou manquante
Symptôme : FATAL : no pg_hba.conf entry…
Correctif : ajoutez une règle host ou hostssl explicite pour l’utilisateur et le sous‑réseau client ; placez‑la avant les règles plus larges.
Vérifier : rechargez et reconnectez‑vous.
Retour arrière : revenez à la sauvegarde de pg_hba.conf et rechargez.

Pare‑feu local bloque l’entrée 5432
Symptôme : nc ou Test-NetConnection indique un délai ; le serveur écoute correctement.
Correctif (exemple UFW) : sudo ufw allow from 10.10.20.0/24 to any port 5432 proto tcp.
Vérifier : l’état du pare‑feu reflète la règle ; le client se connecte.
Retour arrière : sudo ufw delete allow from 10.10.20.0/24 to any port 5432 proto tcp.

DNS pointe vers le mauvais hôte
Symptôme : psql se connecte mais version() ou les données sont inattendues.
Correctif : corrigez le DNS ; connectez‑vous temporairement par IP ou via le fichier hosts en attendant la propagation.
Vérifier : dig +short renvoie l’IP voulue ; psql se connecte au serveur attendu.
Retour arrière : supprimez les entrées temporaires du fichier hosts une fois corrigé.

Incohérence TLS
Symptôme : le client utilise sslmode=require alors que le serveur a ssl=off, ou hostssl dans HBA avec ssl=off.
Correctif : activez ssl sur le serveur et utilisez des règles hostssl, ou alignez les clients sur la politique (disable/allow/require).
Vérifier : show ssl; renvoie on ; la connexion réussit.
Retour arrière : revenez à l’ancienne config et redémarrez ; ajustez HBA vers des règles non‑SSL si nécessaire.

Collision de port sur 5432
Symptôme : PostgreSQL ne démarre pas ; lsof montre un autre processus sur 5432.
Correctif : arrêtez le service en conflit ou déplacez PostgreSQL sur un nouveau port, p. ex. port = 5433.
Vérifier : ss montre 5433 à l’écoute ; clients mis à jour avec -p 5433.
Retour arrière : revenez à 5432 une fois le conflit résolu.

Hairpin NAT non pris en charge
Symptôme : les clients internes au LAN ne peuvent pas se connecter via l’IP publique, alors que les clients externes oui.
Correctif : utilisez l’IP interne pour les clients internes ou configurez le hairpin NAT sur le routeur.
Vérifier : les clients internes se connectent via l’IP LAN.

MTU de chemin ou pertes intermittentes
Symptôme : petites requêtes OK ; les plus grosses se bloquent.
Correctif (Linux/macOS) :

ping -M do -s 1472 10.10.20.15

Réduisez le MTU sur l’interface ou corrigez le chemin réseau ; coordonnez‑vous avec les équipes réseau.
Vérifier : les requêtes volumineuses aboutissent ; la perte de paquets disparaît.

Résultats attendus et vérification de bout en bout

Après corrections, confirmez le comportement de bout en bout :

Local au serveur via loopback et IP d’interface :

psql -h 127.0.0.1 -U postgres -d postgres -c 'select 1;'
psql -h 10.10.20.15 -U postgres -d postgres -c 'select 1;'

Les deux doivent retourner 1 rapidement.

TCP ouvert depuis un client distant :

nc -vz db1.internal.example.com 5432

Doit indiquer open.

Requête authentifiée depuis un client distant avec sslmode explicite selon la politique :

psql 'postgresql://[email protected]:5432/postgres?sslmode=require' \
  -c 'select current_user, inet_server_addr(), inet_server_port();'

Attendu : current_user vaut app ; inet_server_addr() correspond à l’IP du serveur visé ; inet_server_port() au port configuré.

Si vous avez changé listen_addresses, port ou ssl, redémarrez et vérifiez avec :

pg_isready -h db1.internal.example.com -p 5432 -t 5

Attendu : accepting connections.

Liste de contrôle opérationnelle

Utilisez cette liste compacte lors des incidents et pour les changements de routine.

Consigner le contexte : noms d’hôte serveur/clients, IP, versions PostgreSQL
Confirmer que DNS résout l’IP attendue depuis chaque réseau client
Prouver la connectivité locale au serveur via 127.0.0.1 et l’IP d’interface
Vérifier que le serveur écoute sur l’interface et le port prévus (ss/lsof)
Tester l’atteignabilité TCP côté client (nc ou Test‑NetConnection)
Inspecter et corriger les règles pg_hba.conf et leur ordre ; recharger
Aligner TLS : ssl=on côté serveur si hostssl est utilisé ; clients réglés avec sslmode approprié
Examiner les pare‑feux hôtes et n’autoriser que les sources requises vers TCP 5432
Vérifier de bout en bout avec psql : exécuter une requête minimale et confirmer l’identité du serveur
En cas de changement de config : sauvegarder, modifier un seul élément, recharger/redémarrer si requis, vérifier et documenter
En cas de problème : restaurer depuis les sauvegardes, redémarrer si besoin, et retester du local vers le distant dans l’ordre

Conclusion

Les problèmes de connectivité PostgreSQL deviennent simples quand vous isolez la panne couche par couche et vérifiez chaque étape. Commencez local, élargissez progressivement et modifiez la configuration par incréments petits et réversibles. Maintenez un DNS non ambigu, limitez les pare‑feux au nécessaire, ordonnez précisément les règles pg_hba et alignez les attentes TLS. Avec l’inventaire, les diagnostics, les actions de récupération et la liste de contrôle de ce guide, vous résoudrez rapidement et en sécurité la plupart des problèmes réseau PostgreSQL et disposerez d’éléments probants si des changements réseau plus profonds s’imposent.