DataSunrise Consegue la Certificazione AWS DevOps Competency per AWS DevSecOps e Monitoraggio, Logging e Performance

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 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.

Successivo

Audit dei Dati per Snowflake

Audit dei Dati per Snowflake

Scopri di più

Ha bisogno del nostro team di supporto?

I nostri esperti saranno lieti di rispondere alle Sue domande.

Informazioni generali:
[email protected]
Servizio clienti e supporto tecnico:
support.datasunrise.com
Richieste di collaborazione e alleanza:
[email protected]