
Storico delle Attività dei Dati di PostgreSQL

PostgreSQL, un database relazionale open-source ampiamente utilizzato, offre diversi strumenti e approcci per monitorare e tracciare lo storico delle attività dei dati. Questo articolo esplorerà le basi dello storico delle attività dei dati di PostgreSQL, coprendo metodi chiave come pgaudit, statistiche cumulative e pg_stat_statements.
Vi guideremo anche nella configurazione dell’audit delle query SQL per una tabella di esempio e introdurremo DataSunrise, una robusta suite di sicurezza che centralizza e semplifica il monitoraggio delle attività dei dati.
Perché è Importante Monitorare lo Storico delle Attività dei Dati
Monitorare lo storico delle attività dei dati è essenziale per diversi motivi. Vi assicura di poter rilevare attività sospette, soddisfare i requisiti di conformità e ottimizzare le prestazioni del database. Tenendo sotto controllo chi ha accesso ai vostri dati, quando e quali modifiche sono state fatte, potete proteggere il vostro database da accessi non autorizzati e potenziali violazioni dei dati. Inoltre, tracciare l’attività dei dati aiuta a identificare i colli di bottiglia delle prestazioni e ottimizzare l’esecuzione delle query, portando a un ambiente di database più efficiente e sicuro.
Approcci per Tracciare lo Storico delle Attività dei Dati in PostgreSQL
PostgreSQL fornisce vari strumenti e estensioni integrati per tracciare e monitorare l’attività dei dati. Tre dei metodi più comunemente usati sono pgaudit, statistiche cumulative e pg_stat_statements. Ognuno di questi strumenti offre funzionalità uniche, e comprendere le loro differenze vi aiuterà a scegliere l’approccio giusto per le vostre esigenze.
pgaudit: Audit Completo delle Query SQL
pgaudit è un’estensione di PostgreSQL progettata per fornire un audit dettagliato delle query SQL. Registra tutte le attività SQL, incluse le dichiarazioni DML (Data Manipulation Language) e DDL (Data Definition Language). Questo strumento è particolarmente utile per scopi di conformità, poiché consente agli amministratori di mantenere un registro dettagliato delle attività del database.
Configurazione di pgaudit per l’Audit delle Query SQL
La seguente configurazione è per il Server Ubuntu 24.04 e PostgreSQL versione 16 installato dai repository di Ubuntu come mostrato di seguito.
Per configurare pgaudit per l’audit delle query SQL sulla tabella mock_data, seguite questi passaggi:
Installate l’estensione pgaudit:
# sudo apt-get update # sudo apt-get install postgresql-16-pgaudit
Impostate l’estensione in postgresql.conf:
nano /etc/postgresql/16/main/postgresql.conf
modificando la riga: shared_preload_libraries = ‘pgaudit’.
Riavviate il servizio del Database PostgreSQL.
sudo systemctl restart postgresql
Abilitate l’estensione nel database utilizzando l’interfaccia a riga di comando psql:
CREATE EXTENSION pgaudit;
Configurate le impostazioni pgaudit (sempre in psql):
ALTER SYSTEM SET pgaudit.log = 'write';
Ricaricare la configurazione di PostgreSQL:
SELECT pg_reload_conf();
Eseguire query SQL sulla tabella mock_data:
SELECT * FROM mock_data WHERE id = 1; INSERT INTO mock_data (first_name, last_name, email, ip_address) VALUES ('John', 'Doe', '[email protected]', '192.168.1.1');
pgaudit registrerà queste query con i rispettivi timestamp, permettendovi di tracciare chi ha eseguito le azioni e quando. Usate il seguente comando per visualizzare la fine del file di log:
cat /var/log/postgresql/postgresql-16-main.log | tail -n 20

Tutte le righe etichettate come ‘AUDIT’ provengono dall’estensione pgaudit.
Statistiche Cumulative: Metriche a Livello di Database
Le statistiche cumulative in PostgreSQL forniscono dati aggregati sulle prestazioni e sull’uso del database. Queste statistiche includono informazioni sul numero di query eseguite, tuple lette e blocchi recuperati, tra altre metriche. Mentre le statistiche cumulative offrono una panoramica dell’attività del database, mancano della granularità di pgaudit, poiché non tracciano le singole istruzioni SQL o i loro timestamp.
Per accedere alle statistiche cumulative, potete interrogare la vista pg_stat_database:
SELECT datname, numbackends, xact_commit, xact_rollback FROM pg_stat_database;

Per accedere alle query attive in esecuzione:
SELECT pid, usename, datname, state, query, query_start FROM pg_stat_activity WHERE state = 'active';

Questa vista fornisce anche utili intuizioni sulle prestazioni del database, ma non offre le capacità di audit dettagliato necessarie per tracciare attività dati specifiche.
pg_stat_statements: Monitoraggio delle Prestazioni delle Query
pg_stat_statements è un’altra estensione di PostgreSQL che traccia le statistiche di esecuzione di tutte le istruzioni SQL. A differenza delle statistiche cumulative, pg_stat_statements si concentra sulle prestazioni delle query, fornendo dettagli come il tempo di esecuzione, il numero di chiamate e il tempo medio per chiamata. Questo strumento è particolarmente utile per identificare le query lente e ottimizzare le prestazioni del database.
Installazione e Uso di pg_stat_statements
Installate l’estensione pg_stat_statements:
sudo apt-get update sudo apt-get install postgresql-contrib
Configurate l’estensione:
sudo nano /etc/postgresql/<version>/main/postgresql.conf
shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.track = all pg_stat_statements.max = 1000 track_activity_query_size = 2048
Riavviate PostgreSQL:
sudo systemctl restart postgresql
Accedete a psql e create un’estensione:
CREATE EXTENSION pg_stat_statements;
Interrogate la vista pg_stat_statements:
SELECT query, calls, total_exec_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent FROM pg_stat_statements WHERE query ILIKE '%mock_data%' ORDER BY total_exec_time DESC LIMIT 5;
Questo fornirà un elenco delle query più dispendiose in termini di tempo, aiutandovi a identificare i colli di bottiglia delle prestazioni.

Potete resettare le statistiche:
SELECT pg_stat_statements_reset();
Differenze tra pgaudit, Statistiche Cumulative e pg_stat_statements
Ognuno di questi strumenti serve a un diverso scopo nel monitoraggio dello storico delle attività dei dati di PostgreSQL:
pgaudit: Si concentra sull’audit e la registrazione delle singole istruzioni SQL per scopi di sicurezza e conformità.
Statistiche Cumulative: Fornisce metriche di prestazioni aggregate per l’intero database, utili per una visione d’insieme.
pg_stat_statements: Traccia le prestazioni delle query, aiutandovi a identificare e ottimizzare le query lente.
Combinando questi strumenti, potete ottenere un monitoraggio completo e un audit del vostro database PostgreSQL, assicurando sia sicurezza che prestazioni.
Per verificare se le estensioni sono presenti nel database, utilizzate i seguenti comandi:
SELECT * FROM pg_extension;
SHOW shared_preload_libraries;
L’output potrebbe sembrare così:

Sfruttare DataSunrise per Il Monitoraggio Centralizzato delle Attività dei Dati
Mentre gli strumenti integrati di PostgreSQL forniscono robuste capacità di monitoraggio, gestire lo storico delle attività dei dati su più database può essere una sfida. Qui entra in gioco DataSunrise. DataSunrise è una potente suite di sicurezza che offre il controllo centralizzato su tutte le attività di monitoraggio e auditing dei dati.
Creare un’Istanza DataSunrise per PostgreSQL
Supponendo che DataSunrise sia già installato, seguite questi passaggi per creare un’istanza e monitorare lo storico delle attività dei dati:
- Accedete alla console di DataSunrise.
- Navigare alla sezione ‘Configuration – Databases’ e creare una nuova istanza PostgreSQL.
- Configurate le impostazioni di connessione, incluso l’host del database, la porta e le credenziali.
- Navigate nella sezione Audit – Rules. Qui create e specificate le tabelle, le colonne e le azioni che desiderate auditare, come SELECT, INSERT, UPDATE e DELETE.

- Salvate la regola, e inizierà a monitorare automaticamente. Quando accedete al database tramite il proxy dell’istanza, DataSunrise intercetta le query e registra tutti i dati specificati nei Transactional Trails e Session Trails. Potete visualizzare questi log nelle sezioni ‘Audit – Transactional Trails’ e ‘Audit – Session Trails’ dell’interfaccia di Audit.
DataSunrise catturerà e memorizzerà ora tutto lo storico delle attività dei dati per l’istanza PostgreSQL specificata, fornendo una vista centralizzata e uniforme di tutte le azioni eseguite sul database.

Vantaggi nell’Uso di DataSunrise
DataSunrise offre diversi vantaggi per la gestione dello storico delle attività dei dati di PostgreSQL:
- Controllo Centralizzato: Gestite e monitorate tutti i vostri database da un’unica interfaccia, riducendo il carico amministrativo.
- Audit Completo: Tracciate tutte le attività SQL su più database, assicurando la conformità ai requisiti normativi.
- Configurazione Flessibile: Personalizzate le regole di audit in base alle vostre esigenze specifiche, includendo filtri per utente, azione o tabella.
- Avvisi in Tempo Reale: Ricevete notifiche di attività sospette, permettendovi di agire immediatamente.
Con DataSunrise, otterrete uno strumento potente per mettere in sicurezza i vostri database PostgreSQL e garantire che tutte le attività sui dati siano tracciate e registrate in modo efficiente.
Conclusione
Monitorare lo storico delle attività dei dati in PostgreSQL è fondamentale per mantenere la sicurezza, la conformità e le prestazioni. Strumenti come pgaudit, statistiche cumulative e pg_stat_statements forniscono informazioni preziose sulle attività del vostro database. Per un monitoraggio più avanzato e centralizzato, DataSunrise offre una soluzione completa che semplifica il tracciamento delle attività dei dati attraverso più istanze di PostgreSQL. Combinando questi strumenti, potete ottenere un ambiente di database sicuro e ben ottimizzato.
DataSunrise fornisce strumenti flessibili e user-friendly per la sicurezza del database, incluso l’audit, il mascheramento e altre funzionalità avanzate. Visitate il nostro sito Web di DataSunrise per programmare una demo online ed esplorare come le nostre soluzioni possono aiutarvi a gestire lo storico delle attività dei dati di PostgreSQL in modo più efficace.