1,456 25/03/2026 07/04/2026 8 min

In produzione il problema non è “fare un backup”. Il problema è scoprire troppo tardi che il backup non si ripristina, o che un GRANT sbagliato ha aperto troppo il database.

Questo articolo parte da uno scenario reale: PostgreSQL 15 con dati applicativi, backup su repository separato e un test di restore mensile. In più, vediamo come bloccare gli errori di permessi più comuni, soprattutto quelli che nascono da GRANT ALL ON SCHEMA public o da ruoli creati in fretta.

La configurazione è pensata per produzione. Include cifratura, separazione dei ruoli, retention ragionata e un controllo finale sul ripristino. Non è un tutorial di installazione base. È una checklist operativa per evitare un incidente costoso.

Prerequisiti

Prima di toccare il server, servono alcuni punti fermi.

  • PostgreSQL 15 già attivo su Linux.
  • Accesso root o sudo sul server database.
  • Un repository backup dedicato su disco separato o storage remoto.
  • pgBackRest installato sul server database e sul nodo di backup, se usi repository remoto.
  • Un ruolo applicativo separato dal ruolo amministratore.
  • Finestra di manutenzione per il primo test di restore.

Note: se il database è critico, il repository backup non deve stare sullo stesso volume dei dati. Un snapshot locale non sostituisce un backup ripristinabile.

Comandi utili per verificare il contesto:

psql --version
pgbackrest version
sudo -u postgres psql -c "SELECT version();"

# Output:

psql (PostgreSQL) 15.x
pgBackRest 2.x
PostgreSQL 15.x on x86_64-pc-linux-gnu

Step 1: separa i ruoli e blocca i privilegi eccessivi

Il primo rischio non è il disco pieno. È l’uso del superuser per l’applicazione. In produzione il principio è semplice: il servizio usa un ruolo limitato, l’amministratore usa un ruolo separato, e il backup usa un ruolo ancora diverso.

Per esempio, crea un ruolo applicativo senza privilegi globali e senza accesso inutile agli altri schemi.

CREATE ROLE app_user LOGIN PASSWORD 'CHANGE_ME_STRONG';
GRANT CONNECT ON DATABASE appdb TO app_user;
GRANT USAGE ON SCHEMA app TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

# Output:

CREATE ROLE
GRANT
GRANT
GRANT
ALTER DEFAULT PRIVILEGES

Warning: evita di concedere privilegi su public se non è una scelta precisa. In molti ambienti, il problema nasce proprio lì.

Se trovi permessi troppo larghi sullo schema pubblico, restringili subito.

REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE USAGE ON SCHEMA public FROM PUBLIC;

# Output:

REVOKE
REVOKE

Questo non rompe il database. Riduce la superficie d’attacco e impedisce oggetti creati per errore da ruoli non previsti.

Step 2: configura pgBackRest con repository separato e cifratura

pgBackRest è adatto quando vuoi backup consistenti, retention chiara e restore affidabile. In produzione è preferibile a un semplice dump per database grandi o con molti oggetti.

Un profilo minimale ma serio può usare compressione, repository dedicato e cifratura lato backup.

sudo mkdir -p /etc/pgbackrest /var/lib/pgbackrest
sudo chown -R postgres:postgres /var/lib/pgbackrest
sudo chmod 750 /var/lib/pgbackrest

# Output:

directory created
ownership updated
permissions set

File di configurazione base:

[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
repo1-retention-diff=7
compress-type=zst
start-fast=y
process-max=2
log-level-console=info
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=CHANGE_ME_LONG_RANDOM

[main]
pg1-path=/var/lib/postgresql/15/main

# Output:

configuration saved

Note: la retention va calibrata sul tuo RPO reale. Due full e sette differential sono un punto di partenza, non una regola universale.

Avvia l’inizializzazione del repository e verifica la stanza.

sudo -u postgres pgbackrest --stanza=main stanza-create
sudo -u postgres pgbackrest --stanza=main check

# Output:

stanza create command completed successfully
check command completed successfully

Step 3: programma backup automatici senza esporre credenziali nel codice

Il backup deve partire da solo. In produzione non basta una riga in cron senza controlli. Meglio un timer systemd, log leggibili e file di configurazione protetti.

Un esempio pratico con timer giornaliero:

[Unit]
Description=pgBackRest backup daily

[Service]
Type=oneshot
User=postgres
Group=postgres
ExecStart=/usr/bin/pgbackrest --stanza=main backup

# Output:

service file saved

Timer:

[Unit]
Description=Run pgBackRest backup daily

[Timer]
OnCalendar=03:15
Persistent=true

[Install]
WantedBy=timers.target

# Output:

timer file saved

Attiva il timer e controlla lo stato.

sudo systemctl daemon-reload
sudo systemctl enable --now pgbackrest-backup.timer
systemctl list-timers | grep pgbackrest

# Output:

pgbackrest-backup.timer enabled
NEXT                         LEFT    LAST                         PASSED
...

Warning: non inserire la passphrase del repository in uno script world-readable. Usa permessi stretti o un file di environment protetto.

Step 4: esegui un backup full e controlla i log come faresti in un incidente

Il primo backup non serve solo a “salvare dati”. Serve a verificare che il repository, i permessi e la configurazione siano coerenti.

Avvia un backup full manuale e osserva il risultato.

sudo -u postgres pgbackrest --stanza=main --type=full backup
sudo -u postgres pgbackrest info

# Output:

new backup label = 20260325-031500F
backup command completed successfully

Controlla anche i log dell’unità systemd, se hai usato un servizio dedicato.

journalctl -u pgbackrest-backup.service -n 50 --no-pager

# Output:

backup command completed successfully

Se il backup fallisce, la causa più frequente è banale: path sbagliato, permessi errati sul repository o passphrase non coerente.

Step 5: fai un restore di prova su istanza separata

Il restore test è la parte che molti saltano. È l’errore più costoso. Un backup non testato è solo un file grande.

La strategia giusta è ripristinare su una directory o su una VM di test. Non sovrascrivere subito il database di produzione.

Prima ferma PostgreSQL sul server di test.

sudo systemctl stop postgresql

# Output:

Stopped PostgreSQL Cluster 15-main.

Poi prepara la directory dati e lancia il restore.

sudo rm -rf /var/lib/postgresql/15/main/*
sudo -u postgres pgbackrest --stanza=main restore

# Output:

restore command completed successfully

Riavvia il servizio e verifica che il database risponda.

sudo systemctl start postgresql
sudo -u postgres psql -c "SELECT now();"

# Output:

now
-------------------------------
2026-03-25 03:25:12.123456+00

Note: il restore test deve includere anche almeno una query applicativa reale. Un semplice SELECT 1 non basta a validare schema e permessi.

Step 6: verifica indici e query lente dopo il ripristino

Dopo un restore, il database può essere coerente ma comunque lento. Il punto spesso è l’indice mancante o una statistica vecchia.

Controlla i punti caldi con EXPLAIN ANALYZE su una query rappresentativa.

EXPLAIN ANALYZE
SELECT id, email
FROM customers
WHERE tenant_id = 42 AND status = 'active'
ORDER BY updated_at DESC
LIMIT 20;

# Output:

Index Scan using idx_customers_tenant_status_updated_at on customers
...

Se vedi Seq Scan su tabelle grandi, l’indice non è quello giusto o non esiste più. In quel caso crea un indice mirato, non uno generico.

CREATE INDEX CONCURRENTLY idx_customers_tenant_status_updated_at
ON customers (tenant_id, status, updated_at DESC);

# Output:

CREATE INDEX

Warning: su tabelle grandi usa CONCURRENTLY solo se sai gestire tempi più lunghi. Evita lock inutili in produzione.

Step 7: imposta una routine di controllo per i permessi rotti

I permessi si rompono spesso dopo una migrazione, uno script manuale o un restore parziale. Il sintomo non è sempre un errore di autenticazione. A volte è una query che fallisce su una tabella appena creata.

Controlla chi possiede cosa e se i privilegi default sono coerenti.

SELECT nspname, nspowner::regrole
FROM pg_namespace
WHERE nspname IN ('public', 'app');

SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_schema = 'app'
ORDER BY grantee, privilege_type;

# Output:

 public | postgres
 app    | app_owner

Se trovi un proprietario sbagliato, correggi con precisione.

ALTER SCHEMA app OWNER TO app_owner;
ALTER TABLE app.orders OWNER TO app_owner;

# Output:

ALTER SCHEMA
ALTER TABLE

Per le nuove tabelle, i default privileges devono essere impostati dal ruolo corretto. Se li applichi al ruolo sbagliato, l’app continuerà a creare oggetti con permessi incoerenti.

Verifica finale

Prima di considerare chiusa la configurazione, esegui questi controlli.

  • Il backup full è completato e leggibile con pgBackRest info.
  • Il restore test parte su un’istanza separata e PostgreSQL si avvia senza errori.
  • Le query critiche usano gli indici attesi.
  • Il ruolo applicativo non ha privilegi superflui su public.
  • La retention è compatibile con spazio disco e policy di conservazione.
  • I log non mostrano errori di accesso al repository o alla passphrase.

Un controllo rapido utile:

sudo -u postgres pgbackrest info
sudo -u postgres psql -d appdb -c "\dn+"
sudo -u postgres psql -d appdb -c "\dp app.*"

# Output:

backup set found
List of schemas
Access privileges

Troubleshooting

Errore: ERROR: permission denied for schema public

Causa: lo schema è stato irrigidito correttamente, ma l’app continua a usare oggetti creati o cercati in public.

Fix: sposta l’app sullo schema dedicato e verifica il search_path.

ALTER ROLE app_user SET search_path = app, pg_catalog;
SHOW search_path;

# Output:

ALTER ROLE
 app, pg_catalog

Errore: pgBackRest [082]: ERROR: [056]: unable to open repo path

Causa: permessi errati o path repository non montato.

Fix: controlla mount, ownership e accesso del processo PostgreSQL.

mount | grep pgbackrest
sudo chown -R postgres:postgres /var/lib/pgbackrest
sudo chmod -R 750 /var/lib/pgbackrest

# Output:

repo mounted
ownership corrected
permissions corrected

Errore: could not open relation with OID ...

Causa: il restore ha ripristinato dati, ma manca un oggetto o un indice è danneggiato.

Fix: verifica l’oggetto, poi ricrea l’indice o ripeti il restore completo da un backup valido.

REINDEX TABLE CONCURRENTLY app.orders;

# Output:

REINDEX

Conclusione

Una strategia di backup seria non si misura dal numero di file salvati. Si misura dal tempo necessario a ripristinare un servizio utile, con permessi corretti e query stabili.

Il passo concreto successivo è semplice: pianifica oggi un restore test su un ambiente separato, usando un dataset reale e una query applicativa vera.

Se quel test fallisce, hai già trovato il problema prima dell’incidente. Ed è esattamente il momento giusto per trovarlo.