IT

Linux e rete

Installa e proteggi PostgreSQL

Installa PostgreSQL 18 dal repository ufficiale su Debian o Ubuntu: ruoli e permessi, accesso remoto in TLS, backup automatici e test di ripristino.

9 min di lettura, aggiornato il 18 novembre 2026

In questa pagina

I tuoi valori

Inseriscili una volta: i comandi della guida si aggiornano.

Questa guida ti spiega come installare PostgreSQL sul tuo VPS o server dedicato dal repository ufficiale del progetto, creare ruoli con permessi limitati, aprire se necessario un accesso remoto cifrato e configurare backup che saprai ripristinare.

Alla fine, PostgreSQL ascolta solo in locale, ogni applicazione dispone del proprio ruolo e un backup giornaliero viene prodotto e poi verificato con un test di ripristino.

Ordinare un server

Per ospitare il tuo database, HostMyServers propone diverse offerte:

Prerequisiti

  • Un server con Debian 12 o 13, oppure Ubuntu 22.04 o 24.04
  • Un accesso SSH con un utente che dispone di sudo
  • Un sistema aggiornato (sudo apt update && sudo apt upgrade)
  • Solo per l'accesso remoto diretto: un nome di dominio che punta al server

Porte da aprire

Utilizzo Porta Da aprire?
Applicazioni sullo stesso server 5432/tcp in locale No
Accesso remoto tramite tunnel SSH (consigliato) 22/tcp, o la tua porta SSH Già aperta per l'amministrazione
Accesso remoto diretto in TLS 5432/tcp Sì, solo dall'indirizzo IP dei tuoi client

Installare PostgreSQL dal repository ufficiale

Debian e Ubuntu includono PostgreSQL, ma la versione resta bloccata per tutta la vita della distribuzione. Il repository PGDG (apt.postgresql.org) propone tutte le versioni mantenute.

Al momento della stesura, l'ultima versione maggiore è PostgreSQL 18 (18.7). Consulta la politica delle versioni per conoscere la versione attuale.

  1. Installa gli strumenti necessari e recupera la chiave del repository:

    Terminale
    sudo apt install -y curl ca-certificates gpg
    sudo install -d /usr/share/postgresql-common/pgdg
    sudo curl -fsSL -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc https://www.postgresql.org/media/keys/ACCC4CF8.asc
  2. Verifica l'impronta della chiave:

    Terminale
    gpg --show-key /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc

    Deve corrispondere a B97B0AFCAA1A47F044F244A07FCC7D46ACCC4CF8. In caso contrario, fermati qui.

  3. Dichiara il repository:

    Terminale
    . /etc/os-release
    sudo tee /etc/apt/sources.list.d/pgdg.sources > /dev/null <<EOF
    Types: deb
    URIs: https://apt.postgresql.org/pub/repos/apt
    Suites: ${VERSION_CODENAME}-pgdg
    Architectures: $(dpkg --print-architecture)
    Components: main
    Signed-By: /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc
    EOF
  4. Installa il server:

    Terminale
    sudo apt update
    sudo apt install -y postgresql-18

    Il pacchetto crea e avvia automaticamente un'istanza chiamata 18/main.

Verificare l'installazione

Terminale
pg_lsclusters
sudo systemctl status postgresql@18-main --no-pager
sudo -u postgres psql -c "SELECT version();"
sudo ss -tlnp | grep 5432

pg_lsclusters deve mostrare l'istanza 18 main con lo stato online. ss deve mostrare un ascolto su 127.0.0.1:5432 e [::1]:5432, e non su 0.0.0.0.

Elemento Percorso
Configurazione /etc/postgresql/18/main/
Impostazioni aggiuntive /etc/postgresql/18/main/conf.d/
Regole di accesso /etc/postgresql/18/main/pg_hba.conf
Dati /var/lib/postgresql/18/main/
Log /var/log/postgresql/postgresql-18-main.log

Capire l'autenticazione

Il file pg_hba.conf decide chi può connettersi, a quale database e con quale metodo. PostgreSQL legge le sue righe dall'alto verso il basso e applica la prima che corrisponde. Se quel metodo fallisce, le righe successive non vengono provate.

Mostra le regole attive:

Terminale
sudo -u postgres psql -c "SELECT line_number, type, database, user_name, address, auth_method, error FROM pg_hba_file_rules;"

Su un'installazione nuova, devi trovare due metodi:

  • peer per le connessioni locali tramite socket: il nome dell'utente di sistema deve corrispondere al nome del ruolo. Per questo sudo -u postgres psql funziona senza password.
  • scram-sha-256 per le connessioni TCP da 127.0.0.1 e ::1: viene richiesta una password.

Creare ruoli e un database

Non usare il superutente postgres nelle tue applicazioni. L'esempio seguente crea un database appdb, un ruolo proprietario app_owner e un ruolo di sola lettura app_reader.

  1. Crea i ruoli. Ogni comando chiede una password, che in questo modo non resta nella cronologia della shell:

    Terminale
    sudo -u postgres createuser --pwprompt app_owner
    sudo -u postgres createuser --pwprompt app_reader
  2. Crea il database:

    Terminale
    sudo -u postgres createdb --owner=app_owner appdb
  3. Limita l'accesso a questo database:

    Terminale
    sudo -u postgres psql -d appdb
    SQL
    REVOKE ALL ON DATABASE appdb FROM PUBLIC;
    GRANT CONNECT ON DATABASE appdb TO app_reader;
    GRANT USAGE ON SCHEMA public TO app_reader;
    GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_reader;
    ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public GRANT SELECT ON TABLES TO app_reader;
    \q

    L'ultima istruzione dà a app_reader anche il permesso di lettura sulle future tabelle create da app_owner.

  4. Verifica la connessione con password:

    Terminale
    psql -h 127.0.0.1 -U app_owner -d appdb -c "CREATE TABLE restore_check (id int PRIMARY KEY, note text);"
    psql -h 127.0.0.1 -U app_owner -d appdb -c "INSERT INTO restore_check VALUES (1, 'sauvegarde OK');"
    psql -h 127.0.0.1 -U app_reader -d appdb -c "SELECT * FROM restore_check;"

    Una scrittura con app_reader deve fallire con permission denied for table restore_check. La tabella restore_check servirà più avanti per controllare i tuoi ripristini.

Accedere al database da remoto

Opzione consigliata: tunnel SSH

Il tunnel non richiede alcuna modifica a PostgreSQL né al firewall. Dal tuo computer:

Terminale
ssh -N -L 5433:127.0.0.1:5432 TUO_USER@IP_DEL_TUO_SERVER

Lascia attivo questo comando, poi collega il tuo client alla porta locale 5433:

Terminale
psql -h 127.0.0.1 -p 5433 -U app_owner -d appdb

Opzione avanzata: connessione diretta in TLS

Usa questa opzione se un servizio esterno non può passare da SSH. Richiede un nome di dominio, ad esempio db.tuo-dominio.com, e un certificato valido: consulta la nostra guida Installare un certificato SSL con Let's Encrypt per ottenerlo con Certbot.

  1. Crea uno script che copia il certificato per PostgreSQL e verrà eseguito a ogni rinnovo:

    Terminale
    sudo nano /etc/letsencrypt/renewal-hooks/deploy/postgresql.sh
    Terminale
    #!/bin/sh
    set -e
    DOMAIN="db.tuo-dominio.com"
    [ "$RENEWED_LINEAGE" = "/etc/letsencrypt/live/$DOMAIN" ] || exit 0
    install -o postgres -g postgres -m 644 "$RENEWED_LINEAGE/fullchain.pem" /etc/postgresql/18/main/server.crt
    install -o postgres -g postgres -m 600 "$RENEWED_LINEAGE/privkey.pem" /etc/postgresql/18/main/server.key
    systemctl reload postgresql@18-main
  2. Rendilo eseguibile e lancialo una prima volta:

    Terminale
    sudo chmod 755 /etc/letsencrypt/renewal-hooks/deploy/postgresql.sh
    sudo RENEWED_LINEAGE=/etc/letsencrypt/live/db.tuo-dominio.com /etc/letsencrypt/renewal-hooks/deploy/postgresql.sh
  3. Crea il file di configurazione:

    Terminale
    sudo nano /etc/postgresql/18/main/conf.d/90-remote-access.conf
    Configurazione
    listen_addresses = '*'
    ssl = on
    ssl_cert_file = '/etc/postgresql/18/main/server.crt'
    ssl_key_file = '/etc/postgresql/18/main/server.key'
    ssl_min_protocol_version = 'TLSv1.2'
  4. Aggiungi alla fine di /etc/postgresql/18/main/pg_hba.conf una regola limitata a un database, un ruolo e un indirizzo:

    Testo
    hostssl appdb app_owner TUO_IP/32 scram-sha-256

    hostssl rifiuta qualsiasi connessione non cifrata. Per un indirizzo IPv6, usa il suffisso /128.

  5. Autorizza solo il tuo indirizzo nel firewall. Con UFW:

    Terminale
    sudo ufw allow from TUO_IP to any port 5432 proto tcp
  6. Riavvia PostgreSQL, perché listen_addresses non viene applicato con un semplice ricaricamento:

    Terminale
    sudo systemctl restart postgresql@18-main
    sudo -u postgres psql -c "SELECT line_number, error FROM pg_hba_file_rules WHERE error IS NOT NULL;"

    Il secondo comando non deve restituire alcuna riga.

  7. Connettiti dal tuo computer con un client psql in versione 16 o successiva:

    Terminale
    psql "host=db.tuo-dominio.com port=5432 dbname=appdb user=app_owner sslmode=verify-full sslrootcert=system"

    Controlla poi che la sessione sia cifrata:

    SQL
    SELECT ssl, version FROM pg_stat_ssl WHERE pid = pg_backend_pid();

Automatizzare i backup

pg_dump esporta un database senza fermarlo, ma non salva né i ruoli né le loro password. Lo script seguente esporta quindi anche questi elementi globali con pg_dumpall --globals-only.

  1. Crea la cartella di destinazione:

    Terminale
    sudo install -d -o postgres -g postgres -m 700 /var/backups/postgresql
  2. Crea lo script:

    Terminale
    sudo nano /usr/local/sbin/postgresql-backup.sh
    Terminale
    #!/bin/bash
    set -euo pipefail
    umask 077
    
    DEST="/var/backups/postgresql"
    RETENTION_DAYS=14
    STAMP="$(date +%F_%H%M%S)"
    TARGET="$DEST/$STAMP"
    
    mkdir -p "$TARGET"
    pg_dumpall --globals-only -f "$TARGET/globals.sql"
    
    psql -AtX -d postgres -c "SELECT datname FROM pg_database WHERE datallowconn AND NOT datistemplate" |
    while IFS= read -r db; do
      pg_dump -Fc -d "$db" -f "$TARGET/$db.dump"
    done
    
    find "$DEST" -mindepth 1 -maxdepth 1 -type d -mtime +"$RETENTION_DAYS" -exec rm -rf {} +
  3. Crea il servizio:

    Terminale
    sudo chmod 755 /usr/local/sbin/postgresql-backup.sh
    sudo nano /etc/systemd/system/postgresql-backup.service
    Configurazione
    [Unit]
    Description=PostgreSQL backup
    After=postgresql.service
    
    [Service]
    Type=oneshot
    User=postgres
    Group=postgres
    ExecStart=/usr/local/sbin/postgresql-backup.sh
  4. Crea il timer:

    Terminale
    sudo nano /etc/systemd/system/postgresql-backup.timer
    Configurazione
    [Unit]
    Description=Daily PostgreSQL backup
    
    [Timer]
    OnCalendar=*-*-* 02:30:00
    RandomizedDelaySec=10min
    Persistent=true
    
    [Install]
    WantedBy=timers.target
  5. Attivalo e lancia un primo backup:

    Terminale
    sudo systemctl daemon-reload
    sudo systemctl enable --now postgresql-backup.timer
    sudo systemctl start postgresql-backup.service
    sudo systemctl status postgresql-backup.service --no-pager
    sudo -u postgres ls -lR /var/backups/postgresql

Testare il ripristino

Un backup ha valore solo se sei già riuscito a ripristinarlo. Questo test usa un database temporaneo e non tocca il database di produzione.

Terminale
STAMP="$(sudo -u postgres ls -1 /var/backups/postgresql | tail -n 1)"
sudo -u postgres createdb restore_test
sudo -u postgres pg_restore --no-owner --no-privileges --exit-on-error -d restore_test "/var/backups/postgresql/$STAMP/appdb.dump"
sudo -u postgres psql -d restore_test -c "SELECT * FROM restore_check;"
sudo -u postgres dropdb restore_test

Devi ritrovare la riga sauvegarde OK. Ripeti questo controllo regolarmente e confronta anche alcune tabelle importanti per la tua applicazione.

Ripristinare dopo un incidente

Su un server nuovo in cui PostgreSQL è installato, oppure dopo aver fermato le applicazioni ed eliminato il database danneggiato:

Terminale
STAMP="2026-11-18_023000"
sudo -u postgres psql -X -f "/var/backups/postgresql/$STAMP/globals.sql" postgres
sudo -u postgres dropdb --if-exists appdb
sudo -u postgres pg_restore --create --exit-on-error -d postgres "/var/backups/postgresql/$STAMP/appdb.dump"
sudo -u postgres psql -d appdb -c "ANALYZE;"

Sostituisci il valore di STAMP con la cartella desiderata. L'errore role "postgres" already exists durante il ripristino dei ruoli è normale.

Aggiornare PostgreSQL

Le versioni minori (ad esempio da 18.6 a 18.7) correggono bug e vulnerabilità. Si installano con il sistema e riavviano il servizio:

Terminale
sudo apt update
sudo apt upgrade

Una nuova versione maggiore si installa accanto alla precedente e richiede una migrazione. Lancia prima un backup, poi adatta i numeri di versione:

Terminale
OLD=18
NEW=19
sudo systemctl start postgresql-backup.service
sudo apt install -y "postgresql-$NEW"
pg_lsclusters
sudo pg_dropcluster --stop "$NEW" main
sudo pg_upgradecluster -v "$NEW" "$OLD" main
pg_lsclusters

pg_dropcluster elimina qui l'istanza vuota creata dal nuovo pacchetto: verifica con pg_lsclusters che non contenga nulla di utile. pg_upgradecluster colloca poi la nuova versione sulla porta originale e conserva la vecchia, fermata. Una volta controllate le tue applicazioni, rimuovila:

Terminale
sudo pg_dropcluster "$OLD" main
sudo apt purge "postgresql-$OLD" "postgresql-client-$OLD"

Ricordati di adattare il numero di versione nello script di rinnovo del certificato, se lo usi.

In caso di problemi

FATAL: no pg_hba.conf entry for host

Nessuna regola corrisponde all'indirizzo, al ruolo, al database o alla cifratura. Il messaggio indica i valori ricevuti dal server. Verifica che corrispondano alla tua riga hostssl, compreso l'indirizzo IP pubblico reale del tuo client, poi ricarica la configurazione:

Terminale
sudo systemctl reload postgresql@18-main

FATAL: password authentication failed for user

La password è errata oppure il ruolo non ne ha una. Ridefiniscila da una sessione di amministrazione:

Terminale
sudo -u postgres psql -c "\password app_owner"

FATAL: Peer authentication failed for user

Ti connetti tramite il socket locale con un ruolo diverso dal tuo utente di sistema. Aggiungi -h 127.0.0.1 per usare la password, oppure lancia il comando con l'utente giusto tramite sudo -u.

Connection refused o timeout

Terminale
pg_lsclusters
sudo ss -tlnp | grep 5432
sudo ufw status
sudo tail -n 50 /var/log/postgresql/postgresql-18-main.log

Connection refused indica in genere che PostgreSQL è fermo o non ascolta su quell'indirizzo. Un timeout fa pensare piuttosto a un firewall.

permission denied for table o for schema public

Il ruolo non ha i permessi necessari. Riesegui i GRANT essendo connesso al database giusto. Per le tabelle esistenti create da un altro ruolo, ALTER DEFAULT PRIVILEGES non basta: riguarda solo le tabelle future.

Il servizio non si avvia più dopo una modifica

Il log indica il file e la riga in causa:

Terminale
sudo journalctl -u postgresql@18-main -n 50 --no-pager
sudo tail -n 50 /var/log/postgresql/postgresql-18-main.log

Con un certificato personalizzato, verifica anche che server.key appartenga a postgres con i permessi 600.

could not change directory to "/root"

Questo messaggio compare quando lanci sudo -u postgres da una cartella che postgres non può leggere. Non ha conseguenze: spostati in /tmp per farlo sparire.

Riferimenti