Historique de l’Activité des Données PostgreSQL : Meilleures Pratiques pour la Surveillance et la Sécurité
PostgreSQL, une base de données relationnelle open-source largement utilisée, offre plusieurs outils et approches pour surveiller et suivre l’historique de l’activité des données. Cet article explorera les bases de l’historique de l’activité des données dans PostgreSQL, en couvrant des méthodes clés telles que pgaudit, les statistiques cumulatives et pg_stat_statements.
Nous vous guiderons également à travers la configuration de l’audit des requêtes SQL pour une table d’exemple et nous vous présenterons DataSunrise, une suite de sécurité robuste qui centralise et simplifie la surveillance de l’activité des données.
Pourquoi la Surveillance de l’Historique des Activités des Données est Importante
Surveiller l’historique des activités des données est essentiel pour plusieurs raisons. Cela garantit que vous pouvez détecter les activités suspectes, répondre aux exigences de conformité et optimiser les performances de la base de données. En gardant un œil sur qui a accédé à vos données, quand et quels changements ont été effectués, 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 de l’activité des données aide à identifier les goulots d’étranglement de performance et à optimiser 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 des Données dans PostgreSQL
PostgreSQL propose divers outils et extensions intégrés pour suivre et surveiller l’activité des 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 la mieux adaptée à vos besoins.
pgaudit : Audit SQL Complet
pgaudit est une extension PostgreSQL conçue pour fournir un audit détaillé des déclarations SQL. Il enregistre 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 l’Audit des Requêtes SQL
La configuration suivante est pour le serveur Ubuntu 24.04 et PostgreSQL version 16 installé à partir des répertoires 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 éditant la ligne : shared_preload_libraries = ‘pgaudit’.
Et redémarrez le démon de la base de données PostgreSQL.
sudo systemctl restart postgresql
Activez l’extension dans la base de données à l’aide de l’interface de commande psql :
CREATE EXTENSION pgaudit;
Configurez les paramètres de pgaudit (également dans psql) :
ALTER SYSTEM SET pgaudit.log = 'write'; Rechargez la configuration de PostgreSQL : 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 enregistrera 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 à l’Échelle 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, entre autres mesures. Bien que les statistiques cumulatives offrent une vue d’ensemble de l’activité de la base de données, elles manquent de la granularité de pgaudit, car elles ne suivent pas les instructions 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é nécessaires pour suivre les activités de données spécifiques.
pg_stat_statements : Surveillance 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 via psql et créez l’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, Statistiques Cumulatives et pg_stat_statements
Chacun de ces outils a un objectif différent dans la surveillance de l’historique des activités des 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 obtenir une surveillance et un audit complets de votre base de données PostgreSQL, assurant à la fois sécurité et performance.
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;
Le résultat peut ressembler à ceci :
Exploiter DataSunrise pour la Surveillance Centralisée de l’Activité des Données
Bien que les outils intégrés de PostgreSQL offrent des capacités de surveillance robustes, gérer l’historique des activités des données à travers plusieurs bases de données peut être un défi. C’est ici que DataSunrise intervient. DataSunrise est une suite de sécurité puissante qui offre un contrôle centralisé sur toutes les tâches de surveillance et d’audit de l’activité des données.
Créer une Instance DataSunrise pour PostgreSQL
En supposant que DataSunrise est déjà installé, suivez ces étapes pour créer une instance et surveiller l’historique des activités des données :
- Connectez-vous à la console DataSunrise.
- Accédez à la section ‘Configuration – Databases’ 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 identifiants.
- Accédez à Audit – Rules. 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 enregistre toutes les données spécifiées dans les Transactional Trails et les Session Trails. Vous pouvez voir ces logs dans les sections ‘Audit – Transactional Trails’ et ‘Audit – Session Trails’ de l’interface d’audit.
DataSunrise capturera et stockera désormais 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 des données PostgreSQL :
- Contrôle Centralisé : Gérez et surveillez toutes vos bases de données depuis une interface unique, réduisant la surcharge administrative.
- Audit Complet : Suivez toutes les activités SQL à travers 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 disposez d’un outil puissant pour sécuriser vos bases de données PostgreSQL et assurer que toutes les activités des données soient suivies et enregistrées efficacement.
Conclusion
Surveiller l’historique des activités des données dans PostgreSQL est crucial pour maintenir la sécurité, la conformité et la performance. Des outils comme pgaudit, les statistiques cumulatives et les 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 des 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 fournit 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émonstration en ligne et explorer comment nos solutions peuvent vous aider à gérer plus efficacement l’historique des activités des données PostgreSQL.
