Accueil
Centre de Connaissances
Exigences de surveillance de l’activité de la base de données PostgreSQL

Exigences de surveillance de l’activité de la base de données PostgreSQL

Introduction

PostgreSQL traite plus de 10 000 transactions par seconde dans les environnements à forte charge. Chaque transaction nécessite une surveillance minutieuse. Une étude récente montre que 60 % des violations de bases de données se produisent en raison d’une surveillance insuffisante (une statistique de Verizon DBIR, 2023). Explorons comment mettre en œuvre une surveillance complète de l’activité de la base de données PostgreSQL et l’audit des données.

Comprendre la surveillance des accès et de l’authentification

PostgreSQL fournit des outils robustes pour suivre l’accès des utilisateurs. Voici comment mettre en œuvre une surveillance essentielle de l’authentification :

-- Surveiller les sessions actives
SELECT pid, usename, application_name, client_addr, 
       backend_start, state, query
FROM pg_stat_activity;

pg_stat_activity contient toutes les sessions actives et inactives à l’instant :

Surveillez la durée des sessions utilisateurs comme suit :

SELECT usename, count(*), 
       avg(extract(epoch from now() - backend_start))::integer 
FROM pg_stat_activity 
GROUP BY usename;

Suivi des modifications de données

La surveillance des modifications de données aide à détecter les modifications non autorisées. Mettez en œuvre ces mécanismes de suivi :

Créer une fonction déclencheur d’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;

Appliquer le déclencheur aux tables sensibles

CREATE TRIGGER audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON sensitive_table
FOR EACH ROW EXECUTE FUNCTION audit_trigger_func();

Avant de mettre en œuvre le déclencheur d’audit, remplacez ‘sensitive_table’ par le nom réel de la table que vous souhaitez surveiller. Bien que les déclencheurs offrent une approche simple pour l’audit, ils peuvent impacter les performances sur des systèmes à forte charge et peuvent nécessiter une maintenance supplémentaire. Envisagez d’utiliser des solutions de journalisation d’audit dédiées comme pgAudit pour les environnements de production.

Surveillance de la configuration de sécurité

Suivez les changements liés à la sécurité avec ces requêtes :

1. Surveiller les modifications des rôles

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

Cela peut renvoyer la matrice d’accès comme suit pour notre PostgreSQL local :

2. Suivre les modifications de permissions.

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

3. Vérifier les paramètres de sécurité actuels.

SHOW all;

Opérations de sauvegarde et de récupération

Mettez en œuvre la surveillance des sauvegardes avec ces approches :

-- Suivre l'état du WAL (Write-Ahead Log)
SELECT * FROM pg_stat_wal;

-- Surveiller l'historique des sauvegardes
SELECT start_time, end_time, 
       success, database_name
FROM pg_backup_history;

-- Vérifier l'état de la réplication
SELECT * FROM pg_stat_replication;

Surveillance de l’activité des requêtes

Suivez les performances et les schémas des requêtes :

-- Activer le suivi des requêtes
CREATE EXTENSION pg_stat_statements;

-- Surveiller les requêtes de longue durée
SELECT pid, age(clock_timestamp(), query_start), 
       usename, query 
FROM pg_stat_activity 
WHERE state != 'idle' 
AND query_start < now() - interval '5 minutes';

-- Analyser les schémas de requêtes
SELECT query, calls, total_time, rows, 
       mean_time
FROM pg_stat_statements 
ORDER BY total_time DESC;

Surveillance des événements système

Surveillez les événements système critiques :

-- Surveiller les statistiques de la base de données (backend, résumé des transactions)
SELECT datname, numbackends, xact_commit, 
       xact_rollback, blks_read, blks_hit
FROM pg_stat_database;

Ce qui donne le résultat suivant :

Vous pouvez constater certaines transactions annulées (118 dans ce cas).

-- Vérifier les statistiques des tables
SELECT schemaname, relname, seq_scan, 
       seq_tup_read, idx_scan
FROM pg_stat_user_tables;

Cette requête vous aide à comprendre l’efficacité d’accès à vos tables. En examinant la fréquence des scans séquentiels par rapport aux scans par index, vous pouvez repérer des problèmes de performances, tels que des tables parcourues sans utiliser les index disponibles, ce qui peut signaler la nécessité d’une meilleure indexation ou d’une optimisation des requêtes.

-- Surveiller les conflits de verrouillage
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 pour la surveillance de l’activité de la base de données PostgreSQL

DataSunrise améliore la surveillance de l’activité de la base de données en offrant des fonctionnalités de sécurité robustes de manière cohérente sur des dizaines de bases de données supportées. Il offre cinq modes de déploiement flexibles, vous permettant d’équilibrer la latence de la base de données, les ensembles de fonctionnalités et l’impact sur l’infrastructure existante.

Le tableau de bord offre une vue d’ensemble complète des informations clés sur les bases de données sous protection et surveillance :

Toutes les bases de données protégées sont listées sur la page des bases de données avec leurs paramètres réseau :

Enfin, tous les événements surveillés sont présentés dans un format cohérent pour toutes les bases de données, affichés de manière pratique en un seul endroit :

Au-delà de la surveillance, DataSunrise offre une sécurité avancée des données, la découverte de données sensibles, des rapports de conformité automatisés et le masquage des données. Notre interface web conviviale inclut un assistant piloté par IA, permettant un accès rapide aux guides et aux documents de référence pour une utilisation sans faille du produit.

Résumé et conclusions

Une surveillance efficace de PostgreSQL nécessite une approche complète couvrant tous les aspects des opérations de base de données. Une surveillance régulière aide à maintenir la sécurité, les performances et la fiabilité de vos systèmes de base de données.

Solution de sécurité PostgreSQL DataSunrise

DataSunrise fournit des outils spécialisés pour la surveillance et la sécurité de PostgreSQL. Notre solution offre une surveillance en temps réel de l’activité, des rapports de conformité et des fonctionnalités de sécurité avancées conçues spécifiquement pour les bases de données PostgreSQL.

Découvrez la puissance des outils de sécurité PostgreSQL de DataSunrise. Visitez notre site web pour planifier une démo en ligne et découvrir comment nous pouvons améliorer votre infrastructure de sécurité de base de données.