Padroneggia strumenti e best practice DevOps - DevOps Knowledge Hub Padroneggia strumenti e best practice DevOps - DevOps Knowledge Hub

Risoluzione dei Problemi Comuni di Connessione a PostgreSQL

Risolvi gli errori di connessione a PostgreSQL controllando lo stato del servizio, gli indirizzi di ascolto, pg_hba.conf, le credenziali, il DNS, SSL e i log.

Risoluzione dei Problemi Comuni di Connessione a PostgreSQL

Gli errori di connessione a PostgreSQL sembrano simili finché non rallenti e leggi il messaggio esatto. Connection refused di solito significa che il client ha raggiunto un host, ma nulla ha accettato la connessione TCP su quell'indirizzo e porta. Connection timed out spesso significa che il pacchetto non ha mai ricevuto una risposta utile. password authentication failed significa che il server è stato raggiunto e ha rifiutato le credenziali. no pg_hba.conf entry significa che PostgreSQL non ha trovato una regola di accesso corrispondente.

L'ordine è importante. Inizia dall'esterno e procedi verso l'interno: host, porta, servizio, listener, firewall, pg_hba.conf, utente, database, password, SSL, quindi pooling dell'applicazione. Indovinare le password quando il servizio non è in ascolto è una perdita di tempo. Modificare pg_hba.conf quando il DNS punta all'host sbagliato è una perdita di tempo.

Comprensione delle Basi della Connessione a PostgreSQL

Prima di addentrarci negli errori specifici, è fondamentale capire come PostgreSQL gestisce le connessioni. PostgreSQL opera su un modello client-server. Un client (ad esempio, lo strumento a riga di comando psql, un'applicazione web o un client desktop) tenta di connettersi a un processo server PostgreSQL. Questo processo di solito ascolta le connessioni in arrivo su un'interfaccia di rete e una porta specifiche (con default 5432).

Due file di configurazione principali regolano come le connessioni vengono accettate e autenticate:

  • postgresql.conf: Controlla il comportamento generale del server, incluse le interfacce di rete su cui ascoltare (listen_addresses) e la porta (port).
  • pg_hba.conf: (Host-Based Authentication) Stabilisce chi può connettersi da dove a quale database, utilizzando quale metodo di autenticazione. Questo file è fondamentale per la sicurezza e il controllo degli accessi.

Comprendere i ruoli di questi file e l'interazione client-server è fondamentale per una risoluzione efficace dei problemi.

Errori di Connessione Comuni e Soluzioni

Analizziamo gli errori di connessione più frequenti che potresti incontrare e le rispettive soluzioni.

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

Questo errore significa che il database specificato dal client non esiste sul server PostgreSQL.

Spiegazione: L'applicazione client o il comando psql sta tentando di connettersi a un database che non è stato creato o il cui nome è scritto in modo errato.

Soluzione:

  1. Verifica il nome del database: Assicurati che il nome del database nella stringa di connessione o nel comando psql sia corretto.
  2. Elenca i database esistenti: Connettiti a un database predefinito (come postgres o template1) ed elenca tutti i database disponibili usando \l (o \list).
# Prova a connetterti al database predefinito 'postgres'
psql -U tuo_utente -h tuo_host -d postgres

# Una volta connesso, elenca tutti i database
\l

# Esempio di creazione di un database mancante
CREATE DATABASE my_app_db;

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

Questo indica che il nome utente (ruolo) specificato per la connessione non esiste.

Spiegazione: Simile all'errore del database, l'account utente che tenta di connettersi è inesistente o scritto in modo errato.

Soluzione:

  1. Verifica il nome utente: Controlla il nome utente nella stringa di connessione.
  2. Elenca i ruoli esistenti: Connettiti con un account superutente (ad esempio, utente postgres) ed elenca tutti i ruoli usando \du.
# Connettiti come superutente predefinito 'postgres'
psql -U postgres -h tuo_host -d postgres

# Elenca tutti i ruoli (utenti)
\du

# Esempio di creazione di un ruolo mancante
CREATE ROLE my_app_user WITH LOGIN PASSWORD 'my_strong_password';

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

Questo è un errore comune, che indica che è stata fornita una password errata per l'utente specificato.

Spiegazione: La password fornita dal client non corrisponde a quella memorizzata per l'utente PostgreSQL (ruolo).

Soluzione:

  1. Controlla la configurazione dell'applicazione: Rivedi la stringa di connessione o le variabili d'ambiente della tua applicazione per assicurarti che la password sia corretta.

  2. Reimposta la password (se hai accesso come superutente):

    # Connettiti come superutente postgres
psql -U postgres -h tuo_host -d postgres

# Cambia la password per l'utente problematico
ALTER USER my_app_user WITH PASSWORD 'nuova_password_forte';

    Suggerimento: Assicurati che la voce pg_hba.conf per l'utente specifichi un metodo di autenticazione basato su password (ad esempio, md5, scram-sha-256) piuttosto che trust o ident se intendi usare le password.

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

Questo errore è un problema di configurazione di pg_hba.conf, il che significa che il server ha negato esplicitamente la connessione in base alle sue regole di accesso.

Spiegazione: Il file pg_hba.conf non contiene una regola che corrisponda ai parametri della connessione in arrivo (IP client, utente, database e metodo di autenticazione).

Soluzione:

  1. Individua pg_hba.conf: La posizione varia in base al sistema operativo e al metodo di installazione (ad esempio, /etc/postgresql/14/main/pg_hba.conf su Debian/Ubuntu, o specificato da SHOW hba_file; in psql).

  2. Modifica pg_hba.conf: Aggiungi o modifica una voce per consentire la connessione. Una voce comune per consentire connessioni da qualsiasi luogo con autenticazione tramite password è simile a questa:

    # TYPE  DATABASE        USER            ADDRESS                 METHOD
host    my_app_db       my_app_user     203.0.113.25/32         scram-sha-256
    • TYPE: host per connessioni TCP/IP.
    • DATABASE: all (o un nome di database specifico).
    • USER: all (o un nome utente specifico).
    • ADDRESS: L'intervallo di indirizzi IP del client (ad esempio, 192.168.1.0/24, 127.0.0.1/32 per locale, o un singolo IP client pubblico).
    • METHOD: Il metodo di autenticazione. Preferisci scram-sha-256 per l'autenticazione tramite password su distribuzioni PostgreSQL moderne quando i client lo supportano.

    Attenzione: Evita host all all 0.0.0.0/0 ... a meno che non ci sia un controllo di rete molto deliberato davanti. Un database, un ruolo e un CIDR ristretti rendono più facile individuare gli errori.

  3. Ricarica PostgreSQL: Dopo aver modificato pg_hba.conf, devi ricaricare la configurazione di PostgreSQL affinché le modifiche abbiano effetto.

    # Su sistemi basati su systemd

sudo systemctl reload postgresql

# Oppure usando pg_ctl (richiede di specificare la directory dei dati)
# pg_ctl reload -D /var/lib/postgresql/14/main
```

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

Questo è un errore generico che indica che il client non è riuscito a stabilire una connessione al server PostgreSQL. Il server ha rifiutato attivamente il tentativo di connessione, spesso perché nulla è in ascolto sull'IP/porta di destinazione.

Spiegazione: Questo di solito punta a uno dei seguenti:

  • Il servizio PostgreSQL non è in esecuzione.
  • PostgreSQL non è in ascolto sull'interfaccia di rete o sulla porta prevista.
  • Un firewall sta bloccando la connessione.

Soluzioni:

  1. PostgreSQL è in esecuzione?

    • Controlla lo stato del servizio:
      sudo systemctl status postgresql
# Oppure, per sistemi più vecchi/altre configurazioni:
# sudo service postgresql status
      Se non è in esecuzione, avvialo:
      sudo systemctl start postgresql
    • Controlla i log: Rivedi i log di PostgreSQL (ad esempio, /var/log/postgresql/) per errori di avvio.

  2. È in ascolto sull'indirizzo/porta giusti?

    • Controlla postgresql.conf: Assicurati che listen_addresses sia configurato correttamente. Per connessioni da altri host, dovrebbe essere * o l'indirizzo IP specifico dell'interfaccia di rete del server, non solo localhost (127.0.0.1).
      # In postgresql.conf
listen_addresses = '*'    # Ascolta su tutte le interfacce di rete disponibili
port = 5432               # Porta predefinita
      Dopo aver modificato listen_addresses, devi riavviare PostgreSQL (un ricaricamento non è sufficiente).
      sudo systemctl restart postgresql
    • Verifica la porta in ascolto: Usa netstat o ss per verificare se PostgreSQL è effettivamente in ascolto sulla porta 5432 (o sulla porta configurata).
      sudo ss -ltnp | grep 5432
# Esempio di output previsto:
# tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      12345/postgres
      Se non vedi 0.0.0.0:5432 o ip_del_tuo_server:5432, PostgreSQL probabilmente è in ascolto solo su 127.0.0.1:5432 o per niente.

  3. Un firewall sta bloccando la connessione?

    • Firewall lato server: Controlla ufw (Ubuntu/Debian), firewalld (CentOS/RHEL) o iptables per assicurarti che la porta 5432 sia aperta per le connessioni in entrata dall'indirizzo IP del client.
      # Esempio per UFW
sudo ufw allow 5432/tcp
sudo ufw enable
sudo ufw status

# Esempio per firewalld
sudo firewall-cmd --permanent --add-port=5432/tcp
sudo firewall-cmd --reload
sudo firewall-cmd --list-ports
    • Firewall lato client: Meno comune, ma assicurati che il firewall del client non stia bloccando le connessioni in uscita verso il server sulla porta 5432.

Errore 6: timeout expired o connection timed out

Questo errore suggerisce che il client ha tentato di connettersi ma non ha ricevuto risposta dal server entro un intervallo di tempo specificato.

Spiegazione: A differenza di Connection refused, che è un rifiuto attivo, un timeout implica che il tentativo di connessione non ha mai raggiunto il server o che il server non ha risposto. Questo spesso punta a problemi di connettività di rete o a un server gravemente sovraccarico.

Soluzioni:

  1. Connettività di rete:
    • Ping del server: ping indirizzo_ip_server. Se il ping fallisce, c'è un problema di rete fondamentale (cavo, router, server offline).
    • Traceroute/MTR: traceroute indirizzo_ip_server (Linux/macOS) o tracert indirizzo_ip_server (Windows) può aiutare a identificare dove la connessione fallisce lungo il percorso di rete.
  2. listen_addresses e Firewall del server: Rivedi le soluzioni per l'Errore 5, poiché listen_addresses o firewall configurati in modo errato possono anche causare timeout se il server non è raggiungibile.
  3. Carico del server: Se il server è sotto carico estremo (CPU alta, memoria bassa, I/O del disco eccessivo), potrebbe essere troppo occupato per accettare nuove connessioni tempestivamente, portando a timeout. Controlla l'utilizzo delle risorse di sistema.

Errore 7: SSL Required, SSL Disabled, o Certificate Verification Failed

PostgreSQL può accettare connessioni crittografate e non crittografate, a seconda delle impostazioni del server e delle regole di pg_hba.conf. Un client potrebbe fallire con messaggi relativi a SSL disattivato, SSL richiesto o verifica del certificato fallita.

Controlla tre punti:

# Verifica se il server ha SSL abilitato
psql -U postgres -d postgres -c "SHOW ssl;"

# Prova una connessione crittografata dal client
psql "host=db.example.com port=5432 dbname=my_app_db user=my_app_user sslmode=require"

# Se è prevista la convalida del certificato, usa verify-full e un certificato radice attendibile
psql "host=db.example.com dbname=my_app_db user=my_app_user sslmode=verify-full sslrootcert=/path/to/root.crt"

sslmode=require crittografa la connessione ma non verifica l'identità del server allo stesso modo di verify-full. Per lo sviluppo interno potrebbe essere sufficiente. Per il traffico di produzione attraverso reti non attendibili, usa la verifica del certificato e assicurati che il nome host nella stringa di connessione corrisponda al certificato.

Controlla anche se pg_hba.conf usa hostssl o hostnossl. Una regola hostssl non corrisponderà a una connessione non SSL, e una regola hostnossl non corrisponderà a una connessione SSL.

Errore 8: Too Many Clients Already

Se PostgreSQL restituisce FATAL: sorry, too many clients already, il percorso di connessione funziona. Il server sta rifiutando nuove sessioni perché max_connections è stato raggiunto, o perché rimangono solo slot riservati per superutenti.

Prima, guarda cosa è connesso:

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

Quindi cerca degli schemi. Centinaia di sessioni idle spesso indicano un pool di applicazioni configurato troppo grande, una perdita di processi worker o diverse repliche dell'applicazione che aprono ciascuna il proprio pool. Aumentare max_connections può guadagnare tempo, ma aumenta anche la pressione sulla memoria perché ogni backend ha un sovraccarico e può usare work_mem. Nella maggior parte delle applicazioni web, PgBouncer in modalità di pooling delle transazioni è una soluzione a lungo termine migliore rispetto a lasciare che ogni processo dell'app mantenga molte sessioni PostgreSQL dirette.

Un Flusso di Triage Rapido

Quando qualcuno dice "il database è giù", uso un percorso breve prima di toccare la configurazione:

# 1. Il nome si risolve nell'host che mi aspetto?
getent hosts db.example.com

# 2. La porta TCP è raggiungibile da questo client?
nc -vz db.example.com 5432

# 3. psql può connettersi con lo stesso host, porta, utente e database?
psql "host=db.example.com port=5432 dbname=my_app_db user=my_app_user connect_timeout=5"

# 4. Cosa registra PostgreSQL per il tentativo fallito?
sudo tail -n 100 /var/log/postgresql/postgresql-*.log

Se nc fallisce, resta nel territorio della rete e del listener. Se nc riesce ma psql fallisce con FATAL, PostgreSQL è raggiungibile e la risposta è solitamente nell'autenticazione, nel nome del database, nel nome del ruolo, nella modalità SSL o in pg_hba.conf.

Passaggi Generali per la Risoluzione dei Problemi

Quando affronti un problema di connessione persistente, segui questi passaggi generali per una diagnosi sistematica:

  1. Controlla i Log di PostgreSQL: I file di log sono i tuoi migliori amici. Contengono informazioni dettagliate su problemi di avvio, errori e tentativi di connessione negati. La posizione è solitamente specificata da log_directory in postgresql.conf (ad esempio, /var/log/postgresql/ su Debian/Ubuntu, o pg_log all'interno della directory dei dati).

    # Esempio di controllo dei log recenti
sudo tail -f /var/log/postgresql/postgresql-14-main.log

  2. Verifica i File di Configurazione: Ricontrolla postgresql.conf e pg_hba.conf per errori di sintassi, errori di battitura o valori errati. Anche un singolo carattere fuori posto può impedire al server di avviarsi o accettare connessioni.

  3. Riavvia PostgreSQL (come ultima risorsa per le modifiche alla configurazione): Mentre reload è spesso sufficiente per pg_hba.conf e alcuni parametri di postgresql.conf, alcune modifiche critiche (come listen_addresses) richiedono un riavvio completo.

    sudo systemctl restart postgresql

  4. Testa Localmente sul Server: Se la connessione da una macchina remota fallisce, prova a connetterti direttamente sul server stesso. Questo aiuta a determinare se il problema è lato server o di rete.

    # Connettiti usando un socket di dominio Unix (se disponibile)
psql -U tuo_utente -d tuo_database

# Oppure connettiti tramite TCP/IP a localhost
psql -U tuo_utente -h 127.0.0.1 -p 5432 -d tuo_database

    Se la connessione locale funziona ma quella remota no, il problema è probabilmente listen_addresses, pg_hba.conf o un firewall.

  5. Controlla la Configurazione del Client: Assicurati che la stringa di connessione della tua applicazione (ad esempio, variabili d'ambiente PGHOST, PGPORT, PGUSER, PGPASSWORD, PGDATABASE o una stringa di connessione libpq) sia configurata correttamente per corrispondere alle impostazioni del server.

Suggerimenti e Buone Pratiche

  • Principio del Minimo Privilegio: Evita di usare il superutente postgres per le connessioni di routine dell'applicazione. Crea ruoli specifici con solo i privilegi necessari.
  • Password Forti: Usa sempre password forti e uniche per i tuoi ruoli del database.
  • Limita pg_hba.conf: Invece di 0.0.0.0/0, specifica indirizzi IP client esatti o intervalli CIDR ristretti in pg_hba.conf per migliorare la sicurezza.
  • Monitora i Log Regolarmente: Stabilisci una routine per la revisione dei log di PostgreSQL. Molti problemi possono essere individuati precocemente osservando le voci di log.
  • Documenta la Tua Configurazione: Tieni registri chiari delle impostazioni di postgresql.conf e pg_hba.conf, specialmente per gli ambienti di produzione.

La soluzione più rapida di solito deriva dall'abbinare l'errore al livello che lo ha prodotto. Gli strumenti di rete ti dicono se l'host e la porta sono raggiungibili. I log di PostgreSQL ti dicono perché il server ha rifiutato una sessione. pg_hba.conf ti dice se la connessione è consentita. La configurazione dell'applicazione ti dice se gli stessi valori vengono effettivamente utilizzati in produzione.