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 des environnements à haute charge. Chaque transaction nécessite une surveillance attentive. Une étude récente montre que 60 % des violations de bases de données surviennent en raison d’une surveillance insuffisante (une statistique de Verizon DBIR, 2023). Explorons comment mettre en œuvre une surveillance complète de l’activité des bases de données PostgreSQL et audit des données.

Compréhension de 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 du moment :

Surveiller la durée des sessions utilisateur 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 changements de données aide à détecter les modifications non autorisées. Mettez en œuvre ces mécanismes de suivi :

Créer une fonction de déclenchement 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 directe de l’audit, ils peuvent impacter les performances dans les systèmes à haute 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 modifications liées à la sécurité avec ces requêtes :

1. Surveiller les changements de rôles

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

Cela peut renvoyer la matrice d’accès comme ceci 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 une surveillance des sauvegardes avec ces approches :

-- Suivre le statut 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 le statut de la réplication
SELECT * FROM pg_stat_replication;

Surveillance de l’Activité des Requêtes

Surveiller les performances et les modèles 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 modèles 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

Suivez les événements système critiques :

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

Voici le résultat :

Vous pouvez trouver 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 comment vos tables sont efficacement accédées. En regardant la fréquence des scans séquentiels par rapport aux scans d'index, vous pouvez repérer les problèmes de performances, comme des tables scannées sans utiliser les index disponibles, ce qui peut indiquer un besoin de meilleur indexation ou d'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 élève la surveillance de l'activité des bases de données en fournissant des fonctionnalités de sécurité robustes de manière cohérente à travers des dizaines de bases de données pris en charge. Il offre cinq modes de déploiement flexibles, permettant de concilier latence de la base de données, ensembles de fonctionnalités, et 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 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, découverte de données sensibles, des rapports de conformité automatisés, et masquage des données. Notre interface web conviviale inclut un assistant piloté par l'IA, permettant un accès rapide aux guides et référentiels pour une utilisation fluide 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 la base de données. Une surveillance régulière aide à maintenir la sécurité, les performances et la fiabilité de vos systèmes de bases de données.

Solution de sécurité PostgreSQL de 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émonstration en ligne et découvrez comment nous pouvons améliorer votre infrastructure de sécurité des bases de données.