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.

Creazione file per NixOS

Qui di seguito è riportato il codice .nix che permette la configurazione completa e one-shot di un database PostgreSQL su NixOS. Nello specifico sono presenti la creazione dei gruppi e degli utenti, la configurazione del database stesso con la dichiarazione dei database da creare e infine è stata inserita anche una parte relativa alla creazione di backup automatici.

{ config, pkgs, ... }:

{
  # Configurazione Gruppi & Utenti
  users.groups.postgres = {
    name = "postgres";
    members = [ "utente_01" "postgres" ];
  };

  users.users.postgres = {
    isSystemUser = true;
    extraGroups = [ "networkmanager" "wheel" "postgres" ];
    password = "053cc51a94b775baf34f0414af3457df";
    createHome = false;
    enable = true;
    openssh.authorizedKeys.keys = [ "ssh-ed25519 asdiyb438d34dhn3284df3287dsgvb2u3" ];
  };

  # Configurazione del Database
  networking.firewall.allowedTCPPorts = [ 5432 ];

  services.postgresql = {
    enable = true;
    package = pkgs.postgresql;
    enableTCPIP = true;
    dataDir = "/var/lib/postgresql/data";
    initdbArgs = [ "--auth-local=trust" "--auth-host=md5" ];
    ensureDatabases = [ "mydatabase" ];
    settings = {
      ssl = false;
    };
    authentication = ''
      local all postgres trust
      host all all 0.0.0.0/0 md5
      host all all ::/0 md5
    '';
  };

  # Configurazione del BackUp
  services.postgresqlBackup = {
    enable = true;
    startAt = "*-*-* 03:00:00";
    location = "/var/backup/postgresql";
    databases = [ "mydatabase" ];
    compression = "zstd";
  };

  systemd.services.postgresql-backup-cleanup = {
    description = "Pulizia backup PostgreSQL più vecchi di 7 giorni";
    serviceConfig = {
      Type = "oneshot";
      ExecStart = "${pkgs.findutils}/bin/find /var/backup/postgresql -name '*.sql.zstd' -mtime +7 -delete";
    };
  };

  systemd.timers.postgresql-backup-cleanup = {
    wantedBy = [ "timers.target" ];
    timerConfig = {
      OnCalendar = "*-*-* 04:00:00";
      Persistent = true;
    };
  };

}

Configurazione Utenti & Gruppo

Il modulo services.postgresql di NixOS crea automaticamente l’utente di sistema postgres necessario al funzionamento del servizio. Definire manualmente il blocco users.users.postgres non è quindi strettamente necessario, ma farlo ci permette di personalizzare aspetti importanti come il metodo di autenticazione, i gruppi di appartenenza e l’accesso remoto via SSH.

• La password dell’utente può essere generata in modo sicuro tramite OpenSSL, evitando di utilizzare password deboli o prevedibili (vedere la guida su come creare password randomiche con OpenSSL ).
• Per semplificare il processo di connessione al server è possibile configurare una chiave SSH per l’utente postgres, così da evitare di dover inserire la password ad ogni accesso (la procedura per la generazione delle chiavi SSH è consultabile nella guida dedicata ).
• L’opzione createHome = false è impostata intenzionalmente: l’utente postgres non ha bisogno di una home directory personale, poiché tutti i dati vengono gestiti all’interno della dataDir del servizio PostgreSQL.
• Il gruppo postgres include anche utente_01 tra i suoi membri, permettendo a questo utente di interagire direttamente con i file e i socket del database senza necessità di permessi aggiuntivi.

Configurazione Servizio

Il blocco services.postgresql rappresenta il cuore della configurazione. Tramite questo modulo NixOS si occupa di installare, inizializzare e avviare PostgreSQL come servizio systemd, gestendo in automatico tutto il ciclo di vita del database.

• Con package = pkgs.postgresql viene utilizzata la versione stabile inclusa nel canale NixOS corrente (attualmente la 17.x). Se si desidera una versione specifica è possibile indicarla esplicitamente, ad esempio pkgs.postgresql_15 o pkgs.postgresql_16. La lista completa delle versioni disponibili è consultabile su NixOS Search .
• L’opzione enableTCPIP = true permette al database di accettare connessioni di rete da altri host e non solo tramite socket Unix locale. Senza questa opzione PostgreSQL sarebbe raggiungibile esclusivamente dallo stesso server tramite Unix domain socket o connessioni TCP verso localhost.
• Tramite ensureDatabases è possibile dichiarare una lista di database che NixOS creerà automaticamente al primo avvio del servizio. In questo modo non sarà necessario crearli manualmente dopo l’installazione.
• Il blocco authentication definisce le regole di accesso al database. La configurazione proposta utilizza trust per le connessioni locali dell’utente postgres e md5 per tutte le connessioni remote. Questa impostazione va considerata come punto di partenza e adattata in base alle proprie politiche di sicurezza: in ambienti di produzione è consigliabile restringere gli intervalli IP autorizzati.

Configurazione del BackUp

NixOS mette a disposizione il modulo services.postgresqlBackup che si integra direttamente con il servizio PostgreSQL e gestisce in autonomia la creazione di dump periodici tramite un timer systemd. Questo significa che non è necessario configurare manualmente cron job o script esterni: basta dichiarare le opzioni desiderate e il sistema si occupa del resto.

• In questo esempio, con startAt = "*-*-* 03:00:00" il backup viene eseguito ogni giorno alle 3 di notte. Sarà necessario capire quali siano i periodi migliori per minimizzare l’impatto sulle prestazioni del server.
• L’opzione databases accetta una lista di database da includere nel backup. In questo modo è possibile selezionare solo quelli realmente necessari (es. non considerando dump di database temporanei o di test che possono occupare spazio non necessario).
• La compressione zstd offre un buon compromesso tra velocità di esecuzione e dimensione dei file generati, riducendo sensibilmente lo spazio occupato dai backup rispetto ai dump non compressi (ma sono disponibili anche altri formati).
• I file di backup vengono salvati nella directory indicata da location ed è buona pratica verificare sia di avere spazio sufficiente sia di configurare una copia periodica dei dump verso un supporto esterno (es. un bucket S3).

Il sistema nativo di backup non fornisce un’opzione per eliminare i backup precedenti ad un numero di giorni da noi definito, quindi è stato aggiunto uno script che permette di andare ad eliminare i backup che sono più vecchi di 7 giorni (anche qui da modificare secondo le vostre necessità).

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.

psql

Quindi 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.

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 nel blocco authentication 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 controllando il contenuto della directory /var/backup/postgresql e testando il ripristino di un dump su un ambiente di prova.
• Mantenere PostgreSQL aggiornato seguendo gli aggiornamenti del canale NixOS. Le minor release includono spesso correzioni di sicurezza importanti.
• Valutare l’abilitazione di SSL (ssl = true) per cifrare le connessioni tra client e server, specialmente se il database è raggiungibile da reti non fidate.
• Monitorare le dimensioni della dataDir e della directory di backup per evitare di esaurire lo spazio disco, configurando eventualmente degli alert tramite strumenti come Grafana o Prometheus.