Requisiti per il Monitoraggio delle Attività del Database PostgreSQL

Introduzione

PostgreSQL elabora oltre 10.000 transazioni al secondo in ambienti ad alto carico. Ogni transazione richiede un’attenta supervisione. Uno studio recente mostra che il 60% delle violazioni di database si verifica a causa di un monitoraggio insufficiente (statistica tratta da Verizon DBIR, 2023). Esaminiamo come implementare un monitoraggio completo delle attività del database PostgreSQL e l’auditing dei dati.

Comprendere il Monitoraggio dell’Accesso e dell’Autenticazione

PostgreSQL fornisce strumenti robusti per tracciare l’accesso degli utenti. Ecco come implementare il monitoraggio essenziale dell’autenticazione:

-- Monitorare 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:

Monitorare 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;

Tracciamento delle Modifiche ai Dati

Monitorare le modifiche ai dati aiuta a rilevare modifiche non autorizzate. Implementare questi meccanismi di tracciamento:

Creare una funzione di trigger di 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;

Applicare 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 di audit, sostituire ‘sensitive_table’ con il nome effettivo della tabella che si desidera monitorare. Sebbene i trigger forniscano un approccio diretto per l’auditing, possono influenzare le prestazioni in sistemi ad alto carico e potrebbero richiedere manutenzione aggiuntiva. Considerare l’uso di soluzioni di audit logging dedicate come pgAudit per ambienti di produzione.

Monitoraggio della Configurazione della Sicurezza

Tracciare le modifiche relative alla sicurezza con queste query:

1. Monitorare le modifiche ai ruoli

SELECT rolname, rolsuper, rolcreaterole, 
       rolcreatedb, rolcanlogin
FROM pg_roles;

Questo può restituire la matrice di accesso come questa per il nostro PostgreSQL locale:

2. Tracciare le modifiche ai permessi.

SELECT grantor, grantee, table_schema, 
       table_name, privilege_type
FROM information_schema.role_table_grants;

3. Controllare le impostazioni di sicurezza correnti.

SHOW all;

Operazioni di Backup e Ripristino

Implementare il monitoraggio dei backup con questi approcci:

-- Tracciare lo stato del WAL (Write-Ahead Log)
SELECT * FROM pg_stat_wal;

-- Monitorare la cronologia dei backup
SELECT start_time, end_time, 
       success, database_name
FROM pg_backup_history;

-- Controllare lo stato della replica
SELECT * FROM pg_stat_replication;

Monitoraggio dell’Attività delle Query

Tracciare le prestazioni delle query e i pattern:

-- Abilitare il tracciamento delle query
CREATE EXTENSION pg_stat_statements;

-- Monitorare le query di lunga durata
SELECT pid, age(clock_timestamp(), query_start), 
       usename, query 
FROM pg_stat_activity 
WHERE state != 'idle' 
AND query_start < now() - interval '5 minutes';

-- Analizzare i pattern delle query
SELECT query, calls, total_time, rows, 
       mean_time
FROM pg_stat_statements 
ORDER BY total_time DESC;

Monitoraggio degli Eventi di Sistema

Tracciare eventi critici di sistema:

-- Monitorare le statistiche del database (backend, riepilogo delle transazioni)
SELECT datname, numbackends, xact_commit, 
       xact_rollback, blks_read, blks_hit
FROM pg_stat_database;

Questo produce l’output come segue:

Potrebbe trovare alcune transazioni annullate (118 in questo caso).

-- Controllare le statistiche delle tabelle
SELECT schemaname, relname, seq_scan, 
       seq_tup_read, idx_scan
FROM pg_stat_user_tables;

Questa query aiuta a comprendere quanto efficientemente le tue tabelle vengono accessibili. Guardando la frequenza delle scansioni sequenziali rispetto alle scansioni degli indici, è possibile individuare problemi di prestazioni, come le tabelle scansionate senza utilizzare gli indici disponibili, il che può segnalare la necessità di un migliore indicizzazione o ottimizzazione delle query.

-- Monitorare i conflitti di blocco
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 delle Attività del Database PostgreSQL

DataSunrise migliora il monitoraggio delle attività del database fornendo funzionalità di sicurezza robuste in modo coerente in dozzine di database supportati. Offre cinque modalità di distribuzione flessibili, che consentono di bilanciare la latenza del database, i set di funzionalità e l’impatto sull’infrastruttura esistente.

La dashboard offre una visione completa delle informazioni chiave sui database protetti e monitorati:

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

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

Oltre al monitoraggio, DataSunrise offre sicurezza avanzata dei dati, scoperta dei dati sensibili, reportistica automatizzata di conformità e mascheramento dei dati. La nostra interfaccia web user-friendly include un assistente AI-driven, per un accesso rapido a guide e materiali di riferimento per un utilizzo senza soluzione di continuità del prodotto.

Sintesi e Conclusioni

Un monitoraggio efficace di PostgreSQL richiede un approccio completo che copra tutti gli aspetti delle operazioni del database. Il monitoraggio regolare aiuta a mantenere la sicurezza, le prestazioni e l’affidabilità dei 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 in tempo reale delle attività, reportistica di conformità e funzionalità di sicurezza avanzate progettate specificamente per i database PostgreSQL.

Scopri la potenza degli strumenti di sicurezza PostgreSQL di DataSunrise. Visita il nostro sito Web per programmare una demo online e scopri come possiamo migliorare la tua infrastruttura di sicurezza dei database.