Historique des Activités de Données PostgreSQL
PostgreSQL, une base de données relationnelle open-source largement utilisée, propose plusieurs outils et approches pour surveiller et suivre l’historique des activités de données. Cet article explorera les notions de base de l’historique des activités de données PostgreSQL, couvrant des méthodes clés telles que pgaudit, les statistiques cumulatives et pg_stat_statements.
Nous vous guiderons également dans la configuration de l’audit des requêtes SQL pour une table d’exemple et vous présenterons DataSunrise, une suite de sécurité robuste qui centralise et simplifie la surveillance des activités de données.
Pourquoi le Suivi de l’Historique des Activités de Données est Important
Surveiller l’historique des activités de données est essentiel pour plusieurs raisons. Cela permet de détecter les activités suspectes, de respecter les exigences de conformité et d’optimiser les performances de la base de données. En surveillant attentivement qui a accédé à vos données, quand et quelles modifications ont été apportées, vous pouvez protéger votre base de données contre les accès non autorisés et les potentielles violations de données. De plus, le suivi des activités de données aide à identifier les goulots d’étranglement de performance et optimisez l’exécution des requêtes, menant à un environnement de base de données plus efficace et sécurisé.
Approches pour Suivre l’Historique des Activités de Données dans PostgreSQL
PostgreSQL propose divers outils et extensions intégrés pour suivre et surveiller les activités de données. Trois des méthodes les plus couramment utilisées sont pgaudit, les statistiques cumulatives et pg_stat_statements. Chacun de ces outils offre des fonctionnalités uniques, et comprendre leurs différences vous aidera à choisir l’approche qui convient à vos besoins.
pgaudit : Audit SQL Complet
pgaudit est une extension PostgreSQL conçue pour fournir un audit détaillé des instructions SQL. Il journalise toutes les activités SQL, y compris les instructions DML (Data Manipulation Language) et DDL (Data Definition Language). Cet outil est particulièrement utile à des fins de conformité, car il permet aux administrateurs de conserver un enregistrement détaillé des activités de la base de données.
Configurer pgaudit pour Auditer les Requêtes SQL
La configuration suivante est pour le serveur Ubuntu 24.04 et PostgreSQL version 16 installé à partir des dépôts Ubuntu comme montré ci-dessous.
Pour configurer pgaudit pour l’audit des requêtes SQL sur la table mock_data, suivez ces étapes :
Installez l’extension pgaudit :
# sudo apt-get update # sudo apt-get install postgresql-16-pgaudit
Définissez l’extension dans postgresql.conf :
nano /etc/postgresql/16/main/postgresql.conf
en modifiant la ligne : shared_preload_libraries = ‘pgaudit’.
Et redémarrez le démon PostgreSQL.
sudo systemctl restart postgresql
Activez l’extension dans la base de données en utilisant l’interface en ligne de commande psql :
CREATE EXTENSION pgaudit;
Configurez les paramètres de pgaudit (également dans psql) :
ALTER SYSTEM SET pgaudit.log = 'write'; Reload the PostgreSQL configuration: SELECT pg_reload_conf();
Exécutez la requête SQL sur la table mock_data :
SELECT * FROM mock_data WHERE id = 1;
INSERT INTO mock_data (first_name, last_name, email, ip_address) VALUES ('John', 'Doe', '[email protected]', '192.168.1.1');pgaudit journalisera ces requêtes avec leurs horodatages respectifs, vous permettant de suivre qui a effectué les actions et quand. Utilisez la commande suivante pour afficher la fin du fichier journal :
cat /var/log/postgresql/postgresql-16-main.log | tail -n 20
Toutes les lignes étiquetées ‘AUDIT’ proviennent de l’extension pgaudit.
Statistiques Cumulatives : Mesures Globales de la Base de Données
Les statistiques cumulatives dans PostgreSQL fournissent des données agrégées sur les performances et l’utilisation de la base de données. Ces statistiques incluent des informations sur le nombre de requêtes exécutées, les tuples lus et les blocs récupérés, parmi d’autres mesures. Bien que les statistiques cumulatives offrent une vue d’ensemble de l’activité de la base de données, elles manquent de granularité par rapport à pgaudit, car elles ne suivent pas les requêtes SQL individuelles ou leurs horodatages.
Pour accéder aux statistiques cumulatives, vous pouvez interroger la vue pg_stat_database :
SELECT datname, numbackends, xact_commit, xact_rollback FROM pg_stat_database;
Pour accéder aux requêtes en cours d’exécution :
SELECT pid, usename, datname, state, query, query_start FROM pg_stat_activity WHERE state = 'active';
Cette vue fournit également des informations précieuses sur les performances de la base de données, mais elle n’offre pas les capacités d’audit détaillées nécessaires pour suivre des activités de données spécifiques.
pg_stat_statements : Suivi des Performances des Requêtes
pg_stat_statements est une autre extension PostgreSQL qui suit les statistiques d’exécution de toutes les instructions SQL. Contrairement aux statistiques cumulatives, pg_stat_statements se concentre sur les performances des requêtes, fournissant des détails tels que le temps d’exécution, le nombre d’appels et le temps moyen par appel. Cet outil est particulièrement utile pour identifier les requêtes lentes et optimiser les performances de la base de données.
Installation et Utilisation de pg_stat_statements
Installez l’extension pg_stat_statements :
sudo apt-get update sudo apt-get install postgresql-contrib
Configurez l’extension :
sudo nano /etc/postgresql/<version>/main/postgresql.conf
shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.track = all pg_stat_statements.max = 1000 track_activity_query_size = 2048
Redémarrez PostgreSQL :
sudo systemctl restart postgresql
Connectez-vous à psql et créez une extension :
CREATE EXTENSION pg_stat_statements;
Interrogez la vue pg_stat_statements :
SELECT query, calls, total_exec_time, rows, 100.0 * shared_blks_hit /
nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
WHERE query ILIKE '%mock_data%'
ORDER BY total_exec_time DESC
LIMIT 5;Cela fournira une liste des requêtes les plus chronophages, vous aidant à identifier les goulots d’étranglement de performance.
Vous pouvez réinitialiser les statistiques :
SELECT pg_stat_statements_reset();
Différences entre pgaudit, les Statistiques Cumulatives et pg_stat_statements
Chacun de ces outils a un objectif différent dans la surveillance de l’historique des activités de données PostgreSQL :
pgaudit : Se concentre sur l’audit et la journalisation des instructions SQL individuelles à des fins de sécurité et de conformité.
Statistiques Cumulatives : Fournit des mesures de performance agrégées pour l’ensemble de la base de données, utiles pour une vue d’ensemble.
pg_stat_statements : Suit les performances d’exécution des requêtes, aidant à identifier et optimiser les requêtes lentes.
En combinant ces outils, vous pouvez réaliser une surveillance et un audit complets de votre base de données PostgreSQL, garantissant à la fois la sécurité et les performances.
Pour vérifier si les extensions sont présentes dans la base de données, utilisez les commandes suivantes :
SELECT * FROM pg_extension;
SHOW shared_preload_libraries;
La sortie peut ressembler à ceci :
Utilisation de DataSunrise pour la Surveillance Centralisée des Activités de Données
Bien que les outils intégrés de PostgreSQL fournissent des capacités de surveillance robustes, la gestion de l’historique des activités de données à travers plusieurs bases de données peut être un défi. C’est là que DataSunrise intervient. DataSunrise est une puissante suite de sécurité qui offre un contrôle centralisé sur toutes les tâches de surveillance et d’audit des activités de données.
Créer une Instance DataSunrise pour PostgreSQL
En supposant que DataSunrise soit déjà installé, suivez ces étapes pour créer une instance et surveiller l’historique des activités de données :
- Connectez-vous à la console DataSunrise.
- Naviguez vers la section ‘Configuration – Bases de Données’ et créez une nouvelle instance PostgreSQL.
- Configurez les paramètres de connexion, y compris l’hôte de la base de données, le port et les informations d’identification.
- Naviguez vers ‘Audit – Règles’. Ici, créez et spécifiez les tables, colonnes et actions que vous souhaitez auditer, telles que SELECT, INSERT, UPDATE et DELETE.
- Enregistrez la règle, et elle commencera à surveiller automatiquement. Lorsque vous accédez à la base de données via le proxy de l’instance, DataSunrise intercepte les requêtes et journalise toutes les données spécifiées dans les Traînées Transactionnelles et les Traînées de Session. Vous pouvez consulter ces journaux dans les sections ‘Audit – Traînées Transactionnelles’ et ‘Audit – Traînées de Session’ de l’interface d’Audit.
DataSunrise capturera et stockera maintenant tout l’historique des activités de données pour l’instance PostgreSQL spécifiée, fournissant une vue centralisée et uniforme de toutes les actions effectuées sur la base de données.
Avantages de l’Utilisation de DataSunrise
DataSunrise offre plusieurs avantages pour la gestion de l’historique des activités de données PostgreSQL :
- Contrôle Centralisé : Gérez et surveillez toutes vos bases de données depuis une interface unique, réduisant ainsi la charge administrative.
- Audit Exhaustif : Suivez toutes les activités SQL sur plusieurs bases de données, assurant la conformité avec les exigences réglementaires.
- Configuration Flexible : Personnalisez les règles d’audit en fonction de vos besoins spécifiques, y compris le filtrage par utilisateur, action ou table.
- Alertes en Temps Réel : Recevez des notifications d’activités suspectes, vous permettant de prendre des mesures immédiates.
Avec DataSunrise, vous obtenez un outil puissant pour sécuriser vos bases de données PostgreSQL et vous assurer que toutes les activités de données sont suivies et enregistrées efficacement.
Conclusion
Surveiller l’historique des activités de données dans PostgreSQL est essentiel pour maintenir la sécurité, la conformité et les performances. Des outils comme pgaudit, les statistiques cumulatives et pg_stat_statements fournissent des informations précieuses sur les activités de votre base de données. Pour une surveillance plus avancée et centralisée, DataSunrise offre une solution complète qui simplifie le suivi des activités de données à travers plusieurs instances PostgreSQL. En combinant ces outils, vous pouvez atteindre un environnement de base de données sécurisé et bien optimisé.
DataSunrise propose des outils flexibles et conviviaux pour la sécurité des bases de données, y compris l’audit, le masquage et d’autres fonctionnalités avancées. Visitez notre site web DataSunrise pour planifier une démo en ligne et découvrir comment nos solutions peuvent vous aider à gérer plus efficacement l’historique des activités de données de PostgreSQL.
