Storico delle Attività dei Dati di PostgreSQL: Migliori Pratiche per il Monitoraggio e la Sicurezza
PostgreSQL, un database relazionale open-source ampiamente usato, 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.
Le guideremo anche attraverso la configurazione dell’auditing delle query SQL per una tabella di esempio e presenteremo DataSunrise, una robusta suite di sicurezza che centralizza e semplifica il monitoraggio delle attività dei dati.
Perché il Monitoraggio dello Storico delle Attività dei Dati è Importante
Il monitoraggio dello storico delle attività dei dati è essenziale per diversi motivi. Garantisce che Lei possa rilevare attività sospette, soddisfare i requisiti di conformità e ottimizzare le prestazioni del database. Tenendo d’occhio chi ha accesso ai Suoi dati, quando e quali modifiche sono state apportate, può proteggere il Suo 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. Ciascuno di questi strumenti offre funzionalità uniche e comprendere le loro differenze la aiuterà a scegliere l’approccio giusto per le sue esigenze.
pgaudit: Auditing Completo delle SQL
pgaudit è un’estensione di PostgreSQL progettata per fornire un auditing dettagliato delle istruzioni SQL. Registra tutte le attività SQL, incluse le istruzioni DML (Data Manipulation Language) e DDL (Data Definition Language). Questo strumento è particolarmente utile per scopi di conformità, poiché consente agli amministratori di tenere un record dettagliato delle attività del database.
Configurazione di pgaudit per l’Auditing delle Query SQL
La seguente configurazione è per il Server Ubuntu 24.04 e PostgreSQL versione 16 installata dal repository Ubuntu come mostrato di seguito.
Per configurare pgaudit per l’auditing delle query SQL sulla tabella mock_data, segua questi passaggi:
Installare l’estensione pgaudit:
# sudo apt-get update # sudo apt-get install postgresql-16-pgaudit
Impostare l’estensione in postgresql.conf:
nano /etc/postgresql/16/main/postgresql.conf
modificando la riga: shared_preload_libraries = ‘pgaudit’.
E riavviare il demone del Database PostgreSQL.
sudo systemctl restart postgresql
Abilitare l’estensione nel database utilizzando l’interfaccia lite del comando psql:
CREATE EXTENSION pgaudit;
Configurare le impostazioni di pgaudit (anche in psql):
ALTER SYSTEM SET pgaudit.log = 'write'; Ricaricare la configurazione di PostgreSQL: SELECT pg_reload_conf();
Eseguire una 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, consentendole di tracciare chi ha eseguito le azioni e quando. Utilizzi 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 circa le prestazioni e l’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, può interrogare la vista pg_stat_database:
SELECT datname, numbackends, xact_commit, xact_rollback FROM pg_stat_database;
Per accedere alle query in esecuzione attive:
SELECT pid, usename, datname, state, query, query_start FROM pg_stat_activity WHERE state = 'active';
Questa vista fornisce anche preziose intuizioni sulle prestazioni del database, ma non offre le capacità di auditing dettagliate necessarie per tracciare attività specifiche dei dati.
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 tempo di esecuzione, numero di chiamate e tempo medio per chiamata. Questo strumento è particolarmente utile per identificare query lente e ottimizzare le prestazioni del database.
Installazione e Utilizzo di pg_stat_statements
Installare l’estensione pg_stat_statements:
sudo apt-get update sudo apt-get install postgresql-contrib
Configurare 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
Riavviare PostgreSQL:
sudo systemctl restart postgresql
Accedere a psql e creare un’estensione:
CREATE EXTENSION pg_stat_statements;
Interrogare 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, aiutandola a identificare i colli di bottiglia delle prestazioni.
È possibile reimpostare le statistiche:
SELECT pg_stat_statements_reset();
Differenze tra pgaudit, Statistiche Cumulative e pg_stat_statements
Ciascuno di questi strumenti serve a scopi diversi nel monitoraggio dello storico delle attività dei dati di PostgreSQL:
pgaudit: Si concentra sull’auditing e sulla 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 panoramica di alto livello.
pg_stat_statements: Traccia le prestazioni delle esecuzioni di query, aiutando a identificare e ottimizzare le query lente.
Combinando questi strumenti, è possibile ottenere un monitoraggio e un auditing completi del Suo database PostgreSQL, garantendo sia sicurezza che prestazioni.
Per verificare se le estensioni sono presenti nel database, utilizzi i seguenti comandi:
SELECT * FROM pg_extension;
SHOW shared_preload_libraries;
L’output potrebbe apparire come segue:
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 impegnativo. È qui che entra in gioco DataSunrise. DataSunrise è una potente suite di sicurezza che offre controllo centralizzato su tutte le attività di monitoraggio e audizione delle attività dei dati.
Creazione di un’istanza DataSunrise per PostgreSQL
Presumendo che DataSunrise sia già installato, segua questi passaggi per creare un’istanza e monitorare lo storico delle attività dei dati:
- Acceda alla console di DataSunrise.
- Navigare alla sezione ‘Configurazione – Database’ e creare una nuova istanza PostgreSQL.
- Configurare le impostazioni di connessione, inclusi host del database, porta e credenziali.
- Navigare alla sezione Audit – Regole. Qui crei e specifichi le tabelle, colonne e azioni che desidera auditare, come SELECT, INSERT, UPDATE e DELETE.
- Salvi la regola e il monitoraggio inizierà automaticamente. Quando accede al database tramite il proxy dell’istanza, DataSunrise intercetta le query e registra tutti i dati specificati nei Trailers Transazionali e Sessione. Può visualizzare questi log nelle sezioni ‘Audit – Trailers Transazionali’ e ‘Audit – Trailers Sessione’ dell’interfaccia Audit.
DataSunrise ora catturerà e memorizzerà tutto lo storico delle attività dei dati per l’istanza PostgreSQL specificata, fornendo una visione centralizzata e uniforme di tutte le azioni eseguite sul database.
Vantaggi dell’Uso di DataSunrise
DataSunrise offre diversi vantaggi per la gestione dello storico delle attività dei dati di PostgreSQL:
- Controllo Centralizzato: Gestisca e monitori tutti i Suoi database da un’unica interfaccia, riducendo il sovraccarico amministrativo.
- Audit Completio: Tracci tutte le attività SQL su più database, garantendo la conformità con i requisiti normativi.
- Configurazione Flessibile: Personalizzi le regole di auditing in base alle Sue esigenze specifiche, inclusa la filtrazione per utente, azione o tabella.
- Alert in Tempo Reale: Riceva notifiche di attività sospette, consentendole di agire immediatamente.
Con DataSunrise, ottiene un potente strumento per proteggere i Suoi database PostgreSQL e garantire che tutte le attività dei dati siano tracciate e registrate in modo efficiente.
Conclusione
Il monitoraggio dello storico delle attività dei dati in PostgreSQL è cruciale per mantenere sicurezza, conformità e prestazioni. Strumenti come pgaudit, statistiche cumulative e pg_stat_statements forniscono preziose intuizioni sulle attività del Suo database. Per un monitoraggio più avanzato e centralizzato, DataSunrise offre una soluzione completa che semplifica il tracciamento delle attività dei dati su più istanze di PostgreSQL. Combinando questi strumenti, può ottenere un ambiente di database sicuro e ben ottimizzato.
DataSunrise fornisce strumenti flessibili e facili da usare per la sicurezza del database, inclusi auditing, mascheramento e altre funzionalità avanzate. Visiti il nostro sito web di DataSunrise per programmare una demo online e per esplorare come le nostre soluzioni possono aiutarla a gestire lo storico delle attività dei dati di PostgreSQL in modo più efficace.
