PostgreSQL

PostgreSQL è un sistema di gestione di database relazionali open-source, considerato tra i più avanzati e affidabili disponibili oggi. Nato nel 1986 come progetto accademico all’Università di Berkeley, nel corso degli anni si è evoluto fino a diventare la scelta prediletta per chi necessita di un database robusto e conforme agli standard SQL.

Questo database è progettato per gestire carichi di lavoro complessi, grandi volumi di dati e accessi concorrenti da parte di più utenti e applicazioni. PostgreSQL supporta tipi di dato avanzati come JSON, array e dati geospaziali, rendendolo adatto a scenari molto diversi tra loro: dalle applicazioni web ai sistemi di analisi dati, dalla gestione di contenuti alla raccolta di metriche per il monitoraggio.

Installazione su Fedora 43

Per lo scopo di questa guida, abbiamo utilizzato una macchina con Fedora 43 installata (disponibile al momento della scrittura della guida).

Installazione dei pacchetti

Per poter iniziare l’installazione sarà sufficiente utilizzare il gestore di pacchetti dnf, che si occuperà di scaricare e configurare tutti i componenti necessari. Per farlo possiamo utilizzare il comando sotto.

Il comando permette quindi di andare ad installare due pacchetti, rispettivamente:

• postgresql-server che contiene il motore del database vero e proprio
• postgresql che fornisce il client da riga di comando psql e gli strumenti di amministrazione.

sudo dnf install postgresql-server postgresql

Installazione Directory Dati

Una volta completata l’installazione è necessario inizializzare la directory dati del database. Questo passaggio crea la struttura di file e cartelle in cui PostgreSQL memorizzerà tutti i dati. Questo processo di inizializzazione può avvenire utilizzando il comando sotto.

sudo postgresql-setup --initdb

Abilitazione del Servizio

Una volta finalizzato il processo di installazione, sarà necessario abilitare il servizio in modo che si avvii automaticamente ad ogni riavvio del sistema e avviarlo immediatamente.

sudo systemctl enable --now postgresql

Una volta fatto ed attivato, possiamo verificare che il servizio sia attivo e funzionante controllandone lo stato.

sudo systemctl status postgresql

Primo Accesso al Database

Una volta finalizzata l’installazione sarà necessario andare a sistemare le credenziali di accesso al database, in quanto al momento non è stata inserita una password iniziale per proteggere l’accesso dell’utente “postgres” all’interno del database.

Quindi una volta che siamo entrati nella macchina via SSH o tramite accesso diretto possiamo eseguire il comando che ci permette di accedere alla console del database, che avviene attraverso l’utente “postgres” che è stato creato in automatico durante il processo di installazione.

sudo -u postgres psql

Una volta entrati nel database, sarà necessario impostare una password per l’utente postgres, in modo da poter utilizzare questo account per la gestione e l’amministrazione del database.

ALTER USER postgres WITH PASSWORD 'Sup3r_P4ss0rd';

Il comando ALTER USER ha effetto immediato: la nuova password sarà richiesta a partire dalla prossima connessione al database, senza bisogno di riavviare o ricaricare il servizio PostgreSQL.

Gestire gli Accessi al Database

La configurazione predefinita di PostgreSQL permette connessioni esclusivamente dalla macchina locale. Per consentire l’accesso da altri dispositivi della rete, ad esempio per collegare un’applicazione ospitata su un server diverso, o semplicemente poter collegare un IDE (es. DBeaver) al database, sarà necessario modificare due file di configurazione, che su Fedora si trovano nella directory /var/lib/pgsql/data/.

Interfacce di Rete

Il primo file da modificare è postgresql.conf, che definisce su quali interfacce di rete il server PostgreSQL resta in ascolto. Di default PostgreSQL ascolta solo su localhost, impedendo qualsiasi connessione dall’esterno.

sudo nvim /var/lib/pgsql/data/postgresql.conf

Per accettare connessioni da tutte le interfacce di rete è necessario impostare il parametro listen_addresses con il valore '*'.

listen_addresses = '*'

In alcuni casi il server potrebbe avere più interfacce di rete, ad esempio una rete locale e una VPN come Tailscale. Se si volesse limitare l’ascolto a una specifica interfaccia, è possibile indicare direttamente il suo indirizzo IP. In questo modo PostgreSQL accetterà connessioni esclusivamente dall’interfaccia indicata, ignorando tutte le altre. Nell’esempio sotto possiamo andare a definire anche più interfacce di rete da utilizzare.

listen_addresses = '100.125.124.54, 127.0.0.1'

Permessi sui Client

Il secondo file è pg_hba.conf, che controlla quali client della rete possono autenticarsi e con quale metodo. Per permettere le connessioni dalla propria rete locale è necessario aggiungere una riga che specifichi la sottorete autorizzata e il metodo di autenticazione da utilizzare.

Prima di modificare questo file è necessario conoscere l’indirizzo IP della macchina, in modo da poter risalire alla sottorete da autorizzare. Per farlo è sufficiente utilizzare il comando ip address, che mostrerà tutte le interfacce di rete disponibili con i relativi indirizzi. Una volta individuato l’indirizzo IP dell’interfaccia collegata alla rete locale, sarà possibile ricavare la sottorete corrispondente da inserire nella configurazione.

sudo nvim /var/lib/pgsql/data/pg_hba.conf

La sintassi della riga che andremo ad inserire nel file di configurazione è composta da 5 parti che sono spiegate nella tabella sottostante.

Una volta aperto il file, sarà possibile aggiungere una riga come la seguente per autorizzare le connessioni provenienti dalla rete locale.

host    all    all    80.81.82.0/24    scram-sha-256

Applicazione delle Configurazioni

Dopo aver salvato le modifiche è necessario riavviare il servizio per applicarle. Una volta fatto, il database sarà accessibile dall’esterno secondo le regole che abbiamo definito.

sudo systemctl restart postgresql

Infine, se il firewall è attivo, è necessario aprire la porta utilizzata da PostgreSQL per consentire il traffico in ingresso.

sudo firewall-cmd --add-service=postgresql --permanent
sudo firewall-cmd --reload

Gestione del Database

Una volta che PostgreSQL è attivo e funzionante, è buona pratica creare utenti e database dedicati per ogni servizio che andremo ad ospitare. Evitare di utilizzare l’utente postgres per le applicazioni è importante dal punto di vista della sicurezza: in questo modo si ottiene una compartimentazione dei dati e, nel caso in cui un utente venisse compromesso, l’impatto rimarrebbe limitato al singolo servizio senza coinvolgere l’intero sistema.

Tutti i comandi seguenti vanno eseguiti dalla console PostgreSQL, accessibile tramite il comando psql.

Creazione di un nuovo utente

Per ogni servizio è consigliato creare un utente dedicato con una password robusta. Questo garantisce che ogni applicazione acceda esclusivamente ai propri dati.

CREATE USER userxyz WITH PASSWORD '5up3rM3g4_P455w0rd';

Creazione di un database

Una volta creato l’utente è possibile creare il database assegnandolo direttamente come proprietario. In questo modo l’utente avrà automaticamente tutti i privilegi necessari sul database stesso.

CREATE DATABASE service_01 OWNER userxyz;

Configurazione dei permessi

Anche se userxyz è il proprietario del database, è buona pratica assegnare esplicitamente i permessi di connessione, schema e tabelle. Questo rende la configurazione riutilizzabile anche per utenti che non sono proprietari del database e garantisce che alcuni servizi self-hosted, che richiedono permessi espliciti, funzionino correttamente.

GRANT CONNECT ON DATABASE service_01 TO userxyz;

Una volta concessa la connessione è possibile assegnare all’utente anche i permessi per la gestione delle tabelle. Per farlo è necessario prima connettersi al database.

\c service_01

A questo punto possiamo assegnare i privilegi sullo schema e sulle tabelle. I comandi ALTER DEFAULT PRIVILEGES garantiscono che i permessi vengano applicati automaticamente anche alle tabelle e sequenze che verranno create in futuro dal servizio, evitando di dover ripetere manualmente l’assegnazione ogni volta.

GRANT USAGE ON SCHEMA public TO userxyz;
GRANT CREATE ON SCHEMA public TO userxyz;

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO userxyz;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO userxyz;

GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO userxyz;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO userxyz;

Consigli Finali

• Evitare di utilizzare l’utente postgres per le applicazioni: creare sempre un utente dedicato per ogni servizio, in modo da limitare i danni in caso di compromissione.
• Sostituire il metodo di autenticazione md5 con scram-sha-256 dove possibile, in quanto offre una protezione più robusta delle credenziali durante il processo di autenticazione.
• Restringere gli intervalli IP nella configurazione di pg_hba.conf ai soli indirizzi che necessitano realmente di accedere al database, evitando di lasciare 0.0.0.0/0 in ambienti di produzione.
• Verificare periodicamente che i backup vengano eseguiti correttamente e testare il ripristino di un dump su un ambiente di prova.
• Mantenere PostgreSQL aggiornato all’ultima minor release disponibile. Le minor release includono spesso correzioni di sicurezza importanti.
• Valutare l’abilitazione di SSL (ssl = on) per cifrare le connessioni tra client e server, specialmente se il database è raggiungibile da reti non fidate.
• Monitorare le dimensioni della directory dati e della directory di backup per evitare di esaurire lo spazio disco, configurando eventualmente degli alert tramite strumenti come Grafana o Prometheus.