Homepage
Centro di Conoscenza
Requisiti per il Monitoraggio dell’Attività del Database PostgreSQL

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 accurato. Uno studio recente mostra che il 60% delle violazioni della sicurezza del database si verifica a causa di un monitoraggio insufficiente (statistica tratta da Verizon DBIR, 2023). Esaminiamo 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 strumenti robusti 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 del momento:

Monitora la durata delle sessioni degli utenti 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

Il monitoraggio delle modifiche ai dati aiuta a rilevare modifiche non autorizzate. Implementare 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 di audit, sostituisca “sensitive_table” con il nome reale della tabella che si desidera monitorare. Sebbene i trigger forniscano un approccio semplice all’audit, essi possono impattare le prestazioni in sistemi ad alto carico e richiedere manutenzione aggiuntiva. Consideri l’utilizzo di soluzioni dedicate per la registrazione dei log di 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 ai ruoli

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

Questo potrebbe restituire la matrice degli accessi 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. Controlla le impostazioni di sicurezza correnti.

SHOW all;

Operazioni di Backup e Ripristino

Implementi il monitoraggio dei backup con questi approcci:

-- Traccia 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 replica
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 in esecuzione da lungo tempo
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 pattern delle query
SELECT query, calls, total_time, rows, 
       mean_time
FROM pg_stat_statements 
ORDER BY total_time DESC;

Monitoraggio degli Eventi del Sistema

Traccia gli eventi critici del sistema:

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

Questo produce il seguente output:

Potrebbe riscontrare 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 aiuta a comprendere quanto efficientemente le tabelle vengono accessate. Analizzando la frequenza delle scansioni sequenziali rispetto alle scansioni tramite indici, è possibile individuare eventuali problemi di prestazioni, come tabelle esaminate senza l’utilizzo degli indici disponibili, segnalando la necessità di un’ottimizzazione dell’indicizzazione o 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 innalza il monitoraggio dell’attività del database fornendo caratteristiche di sicurezza robuste in modo coerente attraverso decine di database supportati. Offre cinque modalità di distribuzione flessibili, che consentono di bilanciare la latenza del database, l’insieme delle funzionalità e l’impatto sull’infrastruttura esistente.

Il cruscotto offre una visione completa delle informazioni chiave sui database sotto protezione e monitoraggio:

Tutti i database protetti sono elencati nella pagina Databases 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 luogo:

Oltre al monitoraggio, DataSunrise offre una sicurezza avanzata dei dati, la scoperta dei dati sensibili, la generazione automatica di report di conformità e il mascheramento dei dati. La nostra interfaccia web intuitiva include un assistente basato su AI, che consente un rapido accesso a guide e materiali di riferimento per un utilizzo senza interruzioni del prodotto.

Riepilogo e Conclusioni

Un monitoraggio efficace di PostgreSQL richiede un approccio completo che copra tutti gli aspetti delle operazioni del database. Un monitoraggio regolare contribuisce a mantenere la sicurezza, le prestazioni e l’affidabilità dei sistemi di database.

DataSunrise PostgreSQL Security Solution

DataSunrise fornisce strumenti specializzati per il monitoraggio e la sicurezza di PostgreSQL. La nostra soluzione offre un monitoraggio in tempo reale dell’attività, report di conformità e caratteristiche di sicurezza avanzate progettate specificamente per i database PostgreSQL.

Sperimenti la potenza degli strumenti di sicurezza PostgreSQL di DataSunrise. Visiti il nostro sito web per fissare una demo online e scoprire come possiamo migliorare l’infrastruttura di sicurezza del Suo database.