
Requisiti per il Monitoraggio dell’Attività del Database PostgreSQL

Introduzione
PostgreSQL elabora oltre 10.000 transazioni al secondo in ambienti ad alto carico. Ogni transazione richiede un monitoraggio attento. Un recente studio mostra che il 60% delle violazioni dei database si verifica a causa di un monitoraggio insufficiente (statistica tratta dal Verizon DBIR, 2023). Esploriamo come implementare un monitoraggio completo dell’attività del database PostgreSQL e l’audit dei dati.
Comprendere il Monitoraggio degli Accessi e dell’Autenticazione
PostgreSQL fornisce potenti strumenti per tracciare l’accesso degli utenti. Ecco come implementare il monitoraggio essenziale dell’autenticazione:
-- Monitora le sessioni attive SELECT pid, usename, application_name, client_addr, backend_start, state, query FROM pg_stat_activity;
Pg_stat_activity contiene tutte le sessioni attive e inattive al momento:

Monitora la durata delle sessioni utente come segue:
SELECT usename, count(*), avg(extract(epoch from now() - backend_start))::integer FROM pg_stat_activity GROUP BY usename;
Monitoraggio delle Modifiche dei Dati
Monitorare le modifiche ai dati aiuta a rilevare modifiche non autorizzate. Implementa questi meccanismi di tracciamento:
Crea una funzione trigger per l’audit
CREATE OR REPLACE FUNCTION audit_trigger_func() RETURNS trigger AS $$ BEGIN INSERT INTO audit_log( table_name, action, user_name, changed_fields, row_data ) VALUES ( TG_TABLE_NAME, TG_OP, current_user, row_to_json(NEW), row_to_json(OLD) ); RETURN NEW; END; $$ LANGUAGE plpgsql;
Applica il trigger alle tabelle sensibili
CREATE TRIGGER audit_trigger AFTER INSERT OR UPDATE OR DELETE ON sensitive_table FOR EACH ROW EXECUTE FUNCTION audit_trigger_func();
Prima di implementare il trigger per l’audit, sostituisci “sensitive_table” con il nome effettivo della tabella che desideri monitorare. Anche se i trigger offrono un approccio semplice all’audit, possono influire sulle prestazioni in sistemi ad alto carico e potrebbero richiedere manutenzione aggiuntiva. Considera l’utilizzo di soluzioni dedicate di registrazione dell’audit come pgAudit per ambienti di produzione.
Monitoraggio della Configurazione della Sicurezza
Traccia le modifiche relative alla sicurezza con queste query:
1. Monitora le modifiche dei ruoli
SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin FROM pg_roles;
Questo può restituire la matrice di accesso come segue per il nostro PostgreSQL locale:

2. Traccia le modifiche alle autorizzazioni.
SELECT grantor, grantee, table_schema, table_name, privilege_type FROM information_schema.role_table_grants;
3. Verifica le impostazioni di sicurezza correnti.
SHOW all;
Operazioni di Backup e Recupero
Implementa il monitoraggio dei backup con questi approcci:
-- Monitora lo stato del WAL (Write-Ahead Log) SELECT * FROM pg_stat_wal; -- Monitora la cronologia dei backup SELECT start_time, end_time, success, database_name FROM pg_backup_history; -- Controlla lo stato della replicazione SELECT * FROM pg_stat_replication;
Monitoraggio dell’Attività delle Query
Traccia le prestazioni e i modelli delle query:
-- Abilita il tracciamento delle query CREATE EXTENSION pg_stat_statements; -- Monitora le query a lunga esecuzione SELECT pid, age(clock_timestamp(), query_start), usename, query FROM pg_stat_activity WHERE state != 'idle' AND query_start < now() - interval '5 minutes'; -- Analizza i modelli delle query SELECT query, calls, total_time, rows, mean_time FROM pg_stat_statements ORDER BY total_time DESC;
Monitoraggio degli Eventi di Sistema
Traccia eventi di sistema critici:
-- Monitora le statistiche del database (back-end, riepilogo delle transazioni) SELECT datname, numbackends, xact_commit, xact_rollback, blks_read, blks_hit FROM pg_stat_database;
Questo produce come output quanto segue:

Potresti trovare alcune transazioni annullate (118 in questo caso).
-- Controlla le statistiche delle tabelle SELECT schemaname, relname, seq_scan, seq_tup_read, idx_scan FROM pg_stat_user_tables;

Questa query ti aiuta a comprendere quanto efficientemente le tue tabelle sono accessibili. Analizzando la frequenza delle scansioni sequenziali rispetto alle scansioni tramite indice, puoi individuare problemi di prestazioni, come ad esempio tabelle scansionate senza sfruttare gli indici disponibili, il che potrebbe segnalare la necessità di una migliore indicizzazione o ottimizzazione delle query.
-- Monitora i conflitti di lock SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user FROM pg_locks blocked_locks JOIN pg_locks blocking_locks ON blocked_locks.locktype = blocking_locks.locktype AND blocked_locks.locktype = 'transactionid' AND blocked_locks.transactionid = blocking_locks.transactionid JOIN pg_stat_activity blocked_activity ON blocked_locks.pid = blocked_activity.pid JOIN pg_stat_activity blocking_activity ON blocking_locks.pid = blocking_activity.pid WHERE blocked_locks.granted = FALSE AND blocking_locks.granted = TRUE;
DataSunrise per il Monitoraggio dell’Attività del Database PostgreSQL
DataSunrise eleva il monitoraggio dell’attività del database fornendo funzionalità di sicurezza robuste in modo coerente su decine di database supportati. Offre cinque modalità di distribuzione flessibili, permettendoti di bilanciare la latenza del database, le funzionalità e l’impatto sull’infrastruttura esistente.
Il cruscotto offre una vista completa delle informazioni chiave sui database protetti e monitorati:

Tutti i database protetti elencati nella pagina dei Database con i rispettivi parametri di rete:

Infine, tutti gli eventi monitorati sono presentati in un formato coerente per tutti i database, comodamente visualizzati in un unico luogo:

Oltre al monitoraggio, DataSunrise offre una sicurezza avanzata dei dati, la scoperta di dati sensibili, la generazione automatica di report di conformità e il data masking. La nostra interfaccia web facile da usare include un assistente basato su IA, che consente un accesso rapido a guide e materiali di riferimento per un utilizzo senza interruzioni del prodotto.
Sintesi e Conclusioni
Un monitoraggio efficace di PostgreSQL richiede un approccio completo che copra tutti gli aspetti delle operazioni del database. Un monitoraggio regolare aiuta a mantenere la sicurezza, le prestazioni e l’affidabilità dei tuoi sistemi di database.
Soluzione di Sicurezza PostgreSQL di DataSunrise
DataSunrise fornisce strumenti specializzati per il monitoraggio e la sicurezza di PostgreSQL. La nostra soluzione offre monitoraggio dell’attività in tempo reale, report di conformità e funzionalità di sicurezza avanzate progettate specificamente per i database PostgreSQL.
Sperimenta la potenza degli strumenti di sicurezza per PostgreSQL di DataSunrise. Visita il nostro sito web per programmare una demo online e scoprire come possiamo migliorare l’infrastruttura di sicurezza dei tuoi database.