Résolution des erreurs de connexion PostgreSQL courantes

Les erreurs de connexion PostgreSQL se ressemblent jusqu'à ce que vous ralentissiez et lisiez le message exact. Connection refused signifie généralement que le client a atteint un hôte, mais rien n'a accepté la connexion TCP sur cette adresse et ce port. Connection timed out signifie souvent que le paquet n'a jamais reçu de réponse utile. password authentication failed signifie que le serveur a été atteint et a rejeté les identifiants. no pg_hba.conf entry signifie que PostgreSQL n'a trouvé aucune règle d'accès correspondante.

Cet ordre est important. Commencez de l'extérieur et allez vers l'intérieur : hôte, port, service, écouteur, pare-feu, pg_hba.conf, utilisateur, base de données, mot de passe, SSL, puis pooling d'application. Deviner les mots de passe alors que le service n'écoute pas fait perdre du temps. Modifier pg_hba.conf alors que le DNS pointe vers le mauvais hôte fait également perdre du temps.

Comprendre les bases de la connexion PostgreSQL

Avant de plonger dans les erreurs spécifiques, il est crucial de comprendre comment PostgreSQL gère les connexions. PostgreSQL fonctionne sur un modèle client-serveur. Un client (par exemple, l'outil en ligne de commande psql, une application web ou un client de bureau) tente de se connecter à un processus serveur PostgreSQL. Ce processus écoute généralement les connexions entrantes sur une interface réseau et un port spécifiques (par défaut 5432).

Deux fichiers de configuration principaux régissent la manière dont les connexions sont acceptées et authentifiées :

postgresql.conf : Contrôle le comportement général du serveur, y compris les interfaces réseau à écouter (listen_addresses) et le port (port).
pg_hba.conf : (Authentification basée sur l'hôte) Dicte qui peut se connecter depuis où à quelle base de données, en utilisant quelle méthode d'authentification. Ce fichier est essentiel pour la sécurité et le contrôle d'accès.

Comprendre les rôles de ces fichiers et l'interaction client-serveur est fondamental pour un dépannage efficace.

Erreurs de connexion courantes et solutions

Examinons les erreurs de connexion les plus fréquentes que vous pourriez rencontrer et leurs solutions respectives.

Erreur 1 : `FATAL: database "..." does not exist`

Cette erreur signifie que la base de données spécifiée par le client n'existe pas sur le serveur PostgreSQL.

Explication : L'application cliente ou la commande psql tente de se connecter à une base de données qui n'a pas été créée ou dont le nom est mal orthographié.

Solution :

Vérifiez le nom de la base de données : Assurez-vous que le nom de la base de données dans votre chaîne de connexion ou votre commande psql est correct.
Listez les bases de données existantes : Connectez-vous à une base de données par défaut (comme postgres ou template1) et listez toutes les bases de données disponibles en utilisant \l (ou \list).

# Essayez de vous connecter à la base de données par défaut 'postgres'
psql -U votre_utilisateur -h votre_hote -d postgres

# Une fois connecté, listez toutes les bases de données
\l

# Exemple de création d'une base de données manquante
CREATE DATABASE ma_base_app;

Erreur 2 : `FATAL: role "..." does not exist`

Cela indique que le nom d'utilisateur (rôle) spécifié pour la connexion n'existe pas.

Explication : Similaire à l'erreur de base de données, le compte utilisateur tentant de se connecter est inexistant ou mal orthographié.

Solution :

Vérifiez le nom d'utilisateur : Vérifiez le nom d'utilisateur dans votre chaîne de connexion.
Listez les rôles existants : Connectez-vous avec un compte superutilisateur (par exemple, l'utilisateur postgres) et listez tous les rôles en utilisant \du.

# Connectez-vous en tant que superutilisateur 'postgres' par défaut
psql -U postgres -h votre_hote -d postgres

# Listez tous les rôles (utilisateurs)
\du

# Exemple de création d'un rôle manquant
CREATE ROLE mon_utilisateur_app WITH LOGIN PASSWORD 'mon_mot_de_passe_fort';

Erreur 3 : `FATAL: password authentication failed for user "..."`

C'est une erreur courante, indiquant qu'un mot de passe incorrect a été fourni pour l'utilisateur spécifié.

Explication : Le mot de passe fourni par le client ne correspond pas à celui stocké pour l'utilisateur PostgreSQL (rôle).

Solution :

Vérifiez la configuration de l'application : Examinez la chaîne de connexion de votre application ou les variables d'environnement pour vous assurer que le mot de passe est correct.
Réinitialisez le mot de passe (si vous avez un accès superutilisateur) :
```
# Connectez-vous en tant que superutilisateur postgres
psql -U postgres -h votre_hote -d postgres

# Changez le mot de passe pour l'utilisateur problématique
ALTER USER mon_utilisateur_app WITH PASSWORD 'nouveau_mot_de_passe_fort';
```
Astuce : Assurez-vous que l'entrée pg_hba.conf pour l'utilisateur spécifie une méthode d'authentification basée sur un mot de passe (par exemple, md5, scram-sha-256) plutôt que trust ou ident si vous avez l'intention d'utiliser des mots de passe.

Erreur 4 : `FATAL: no pg_hba.conf entry for host "...", user "...", database "...", SSL off/on`

Cette erreur est un problème de configuration pg_hba.conf, ce qui signifie que le serveur a explicitement refusé la connexion en fonction de ses règles d'accès.

Explication : Le fichier pg_hba.conf ne contient pas de règle correspondant aux paramètres de la connexion entrante (adresse IP du client, utilisateur, base de données et méthode d'authentification).

Solution :

Localisez pg_hba.conf : L'emplacement varie selon le système d'exploitation et la méthode d'installation (par exemple, /etc/postgresql/14/main/pg_hba.conf sur Debian/Ubuntu, ou spécifié par SHOW hba_file; dans psql).
Modifiez pg_hba.conf : Ajoutez ou modifiez une entrée pour autoriser la connexion. Une entrée courante pour autoriser les connexions depuis n'importe où avec une authentification par mot de passe ressemble à ceci :
```
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    ma_base_app     mon_utilisateur_app     203.0.113.25/32         scram-sha-256
```
- TYPE : host pour les connexions TCP/IP.
- DATABASE : all (ou un nom de base de données spécifique).
- USER : all (ou un nom d'utilisateur spécifique).
- ADDRESS : La plage d'adresses IP du client (par exemple, 192.168.1.0/24, 127.0.0.1/32 pour local, ou une seule adresse IP client publique).
- METHOD : La méthode d'authentification. Préférez scram-sha-256 pour l'authentification par mot de passe sur les déploiements PostgreSQL modernes lorsque les clients le supportent.
Avertissement : Évitez host all all 0.0.0.0/0 ... sauf s'il existe un contrôle réseau très délibéré en amont. Une base de données, un rôle et un CIDR étroits facilitent la détection des erreurs.
Rechargez PostgreSQL : Après avoir modifié pg_hba.conf, vous devez recharger la configuration PostgreSQL pour que les modifications prennent effet.
```
# Sur les systèmes basés sur systemd
```

sudo systemctl reload postgresql

# Ou en utilisant pg_ctl (nécessite de spécifier le répertoire de données)
# pg_ctl reload -D /var/lib/postgresql/14/main
```

Erreur 5 : `could not connect to server: Connection refused (0x0000274D/10061)`

Ceci est une erreur générique indiquant que le client n'a pas pu établir de connexion avec le serveur PostgreSQL. Le serveur a activement rejeté la tentative de connexion, souvent parce que rien n'écoute sur l'IP/le port cible.

Explication : Cela pointe généralement vers l'un des éléments suivants :

Le service PostgreSQL n'est pas en cours d'exécution.
PostgreSQL n'écoute pas sur l'interface réseau ou le port attendu.
Un pare-feu bloque la connexion.

Solutions :

PostgreSQL est-il en cours d'exécution ?
- Vérifiez l'état du service :
```
sudo systemctl status postgresql
# Ou, pour les systèmes plus anciens/autres configurations :
# sudo service postgresql status
```
  S'il n'est pas en cours d'exécution, démarrez-le :
```
sudo systemctl start postgresql
```
- Vérifiez les journaux : Consultez les journaux PostgreSQL (par exemple, /var/log/postgresql/) pour les erreurs de démarrage.
Écoute-t-il sur la bonne adresse/le bon port ?
- Vérifiez postgresql.conf : Assurez-vous que listen_addresses est configuré correctement. Pour les connexions depuis d'autres hôtes, il doit être * ou l'adresse IP spécifique de l'interface réseau du serveur, pas seulement localhost (127.0.0.1).
```
# Dans postgresql.conf
listen_addresses = '*'    # Écouter sur toutes les interfaces réseau disponibles
port = 5432               # Port par défaut
```
  Après avoir modifié listen_addresses, vous devez redémarrer PostgreSQL (un rechargement ne suffit pas).
```
sudo systemctl restart postgresql
```
- Vérifiez le port d'écoute : Utilisez netstat ou ss pour vérifier si PostgreSQL écoute réellement sur le port 5432 (ou votre port configuré).
```
sudo ss -ltnp | grep 5432
# Exemple de sortie attendue :
# tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      12345/postgres
```
  Si vous ne voyez pas 0.0.0.0:5432 ou votre_ip_serveur:5432, PostgreSQL écoute probablement uniquement sur 127.0.0.1:5432 ou pas du tout.
Un pare-feu bloque-t-il la connexion ?
- Pare-feu côté serveur : Vérifiez ufw (Ubuntu/Debian), firewalld (CentOS/RHEL) ou iptables pour vous assurer que le port 5432 est ouvert pour les connexions entrantes depuis l'adresse IP du client.
```
# Exemple pour UFW
sudo ufw allow 5432/tcp
sudo ufw enable
sudo ufw status

# Exemple pour firewalld
sudo firewall-cmd --permanent --add-port=5432/tcp
sudo firewall-cmd --reload
sudo firewall-cmd --list-ports
```
- Pare-feu côté client : Moins courant, mais assurez-vous que le pare-feu du client ne bloque pas les connexions sortantes vers le serveur sur le port 5432.

Erreur 6 : `timeout expired` ou `connection timed out`

Cette erreur suggère que le client a tenté de se connecter mais n'a pas reçu de réponse du serveur dans un délai spécifié.

Explication : Contrairement à Connection refused, qui est un rejet actif, un délai d'attente implique que la tentative de connexion n'a jamais atteint le serveur ou que le serveur n'a pas répondu. Cela pointe souvent vers des problèmes de connectivité réseau ou un serveur gravement surchargé.

Solutions :

Connectivité réseau :
- Pingez le serveur : ping adresse_ip_serveur. Si le ping échoue, il y a un problème réseau fondamental (câble, routeur, serveur hors ligne).
- Traceroute/MTR : traceroute adresse_ip_serveur (Linux/macOS) ou tracert adresse_ip_serveur (Windows) peut aider à identifier où la connexion échoue le long du chemin réseau.
listen_addresses du serveur et pare-feux : Revenez aux solutions pour l'erreur 5, car des listen_addresses mal configurés ou des pare-feux peuvent également provoquer des délais d'attente si le serveur n'est pas joignable.
Charge du serveur : Si le serveur est sous une charge extrême (CPU élevé, faible mémoire, E/S disque excessives), il peut être trop occupé pour accepter de nouvelles connexions rapidement, entraînant des délais d'attente. Vérifiez l'utilisation des ressources système.

Erreur 7 : SSL requis, SSL désactivé ou échec de vérification du certificat

PostgreSQL peut accepter des connexions cryptées et non cryptées, en fonction des paramètres du serveur et des règles pg_hba.conf. Un client peut échouer avec des messages concernant SSL désactivé, SSL requis ou l'échec de la vérification du certificat.

Vérifiez trois endroits :

# Voir si le serveur a SSL activé
psql -U postgres -d postgres -c "SHOW ssl;"

# Essayez une connexion cryptée depuis le client
psql "host=db.example.com port=5432 dbname=ma_base_app user=mon_utilisateur_app sslmode=require"

# Si la validation du certificat est attendue, utilisez verify-full et un certificat racine de confiance
psql "host=db.example.com dbname=ma_base_app user=mon_utilisateur_app sslmode=verify-full sslrootcert=/chemin/vers/root.crt"

sslmode=require crypte la connexion mais ne vérifie pas l'identité du serveur de la même manière que verify-full. Pour le développement interne, cela peut suffire. Pour le trafic de production sur des réseaux non fiables, utilisez la vérification du certificat et assurez-vous que le nom d'hôte dans la chaîne de connexion correspond au certificat.

Vérifiez également si pg_hba.conf utilise hostssl ou hostnossl. Une règle hostssl ne correspondra pas à une connexion non-SSL, et une règle hostnossl ne correspondra pas à une connexion SSL.

Erreur 8 : Trop de clients déjà

Si PostgreSQL renvoie FATAL: sorry, too many clients already, le chemin de connexion fonctionne. Le serveur refuse de nouvelles sessions car max_connections a été atteint, ou parce qu'il ne reste que des emplacements réservés aux superutilisateurs.

Tout d'abord, voyez ce qui est connecté :

SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state
ORDER BY count(*) DESC;

Ensuite, recherchez des modèles. Des centaines de sessions idle pointent souvent vers un pool d'application configuré trop grand, une fuite de processus de travail, ou plusieurs réplicas d'application ouvrant chacun leur propre pool. Augmenter max_connections peut faire gagner du temps, mais cela augmente également la pression sur la mémoire car chaque backend a des frais généraux et peut utiliser work_mem. Dans la plupart des applications web, PgBouncer en mode de pooling de transactions est une meilleure solution à long terme que de laisser chaque processus d'application détenir de nombreuses sessions PostgreSQL directes.

Un flux de triage rapide

Quand quelqu'un dit "la base de données est en panne", j'utilise un chemin court avant de toucher à la configuration :

# 1. Le nom se résout-il vers l'hôte que j'attends ?
getent hosts db.example.com

# 2. Le port TCP est-il accessible depuis ce client ?
nc -vz db.example.com 5432

# 3. psql peut-il se connecter avec le même hôte, port, utilisateur et base de données ?
psql "host=db.example.com port=5432 dbname=ma_base_app user=mon_utilisateur_app connect_timeout=5"

# 4. Que consigne PostgreSQL pour la tentative échouée ?
sudo tail -n 100 /var/log/postgresql/postgresql-*.log

Si nc échoue, restez dans le territoire du réseau et de l'écouteur. Si nc réussit mais que psql échoue avec FATAL, PostgreSQL est accessible et la réponse se trouve généralement dans l'authentification, le nom de la base de données, le nom du rôle, le mode SSL ou pg_hba.conf.

Étapes générales de dépannage

Face à un problème de connexion persistant, suivez ces étapes générales pour un diagnostic systématique :

Vérifiez les journaux PostgreSQL : Les fichiers journaux sont vos meilleurs amis. Ils contiennent des informations détaillées sur les problèmes de démarrage, les erreurs et les tentatives de connexion refusées. L'emplacement est généralement spécifié par log_directory dans postgresql.conf (par exemple, /var/log/postgresql/ sur Debian/Ubuntu, ou pg_log dans le répertoire de données).
```
# Exemple de vérification des journaux récents
sudo tail -f /var/log/postgresql/postgresql-14-main.log
```
Vérifiez les fichiers de configuration : Revérifiez postgresql.conf et pg_hba.conf pour les erreurs de syntaxe, les fautes de frappe ou les valeurs incorrectes. Même un seul caractère mal placé peut empêcher le serveur de démarrer ou d'accepter des connexions.
Redémarrez PostgreSQL (en dernier recours pour les modifications de configuration) : Bien que reload soit souvent suffisant pour pg_hba.conf et certains paramètres de postgresql.conf, certains changements critiques (comme listen_addresses) nécessitent un redémarrage complet.
```
sudo systemctl restart postgresql
```
Testez localement sur le serveur : Si la connexion depuis une machine distante échoue, essayez de vous connecter directement sur le serveur lui-même. Cela aide à déterminer si le problème est côté serveur ou lié au réseau.
```
# Connectez-vous en utilisant un socket de domaine Unix (si disponible)
psql -U votre_utilisateur -d votre_base_de_donnees

# Ou connectez-vous via TCP/IP à localhost
psql -U votre_utilisateur -h 127.0.0.1 -p 5432 -d votre_base_de_donnees
```
Si la connexion locale fonctionne mais pas la connexion distante, le problème est probablement listen_addresses, pg_hba.conf ou un pare-feu.
Vérifiez la configuration du client : Assurez-vous que la chaîne de connexion de votre application (par exemple, les variables d'environnement PGHOST, PGPORT, PGUSER, PGPASSWORD, PGDATABASE, ou une chaîne de connexion libpq) est correctement configurée pour correspondre aux paramètres du serveur.

Conseils et bonnes pratiques

Principe du moindre privilège : Évitez d'utiliser le superutilisateur postgres pour les connexions d'application de routine. Créez des rôles spécifiques avec seulement les privilèges nécessaires.
Mots de passe forts : Utilisez toujours des mots de passe forts et uniques pour vos rôles de base de données.
Restreignez pg_hba.conf : Au lieu de 0.0.0.0/0, spécifiez des adresses IP client exactes ou des plages CIDR étroites dans pg_hba.conf pour renforcer la sécurité.
Surveillez les journaux régulièrement : Établissez une routine pour consulter les journaux PostgreSQL. De nombreux problèmes peuvent être détectés tôt en observant les entrées du journal.
Documentez votre configuration : Conservez des enregistrements clairs de vos paramètres postgresql.conf et pg_hba.conf, en particulier pour les environnements de production.

La correction la plus rapide vient généralement de la correspondance de l'erreur avec la couche qui l'a produite. Les outils réseau vous indiquent si l'hôte et le port sont accessibles. Les journaux PostgreSQL vous indiquent pourquoi le serveur a rejeté une session. pg_hba.conf vous indique si la connexion est autorisée. La configuration de l'application vous indique si les mêmes valeurs sont réellement utilisées en production.

Résolution des erreurs de connexion PostgreSQL courantes

Comprendre les bases de la connexion PostgreSQL

Erreurs de connexion courantes et solutions

Erreur 1 : FATAL: database "..." does not exist

Erreur 2 : FATAL: role "..." does not exist

Erreur 3 : FATAL: password authentication failed for user "..."

Erreur 4 : FATAL: no pg_hba.conf entry for host "...", user "...", database "...", SSL off/on

Erreur 5 : could not connect to server: Connection refused (0x0000274D/10061)

Erreur 6 : timeout expired ou connection timed out

Erreur 7 : SSL requis, SSL désactivé ou échec de vérification du certificat

Erreur 8 : Trop de clients déjà

Un flux de triage rapide

Étapes générales de dépannage

Conseils et bonnes pratiques

Erreur 1 : `FATAL: database "..." does not exist`

Erreur 2 : `FATAL: role "..." does not exist`

Erreur 3 : `FATAL: password authentication failed for user "..."`

Erreur 4 : `FATAL: no pg_hba.conf entry for host "...", user "...", database "...", SSL off/on`

Erreur 5 : `could not connect to server: Connection refused (0x0000274D/10061)`

Erreur 6 : `timeout expired` ou `connection timed out`