pgaudit est une extension puissante pour PostgreSQL conçue pour améliorer les capacités d’audit du SGBD.

Elle permet de tracer précisément les actions réalisées dans la base (requêtes, modifications de schéma, accès aux données), ce qui est essentiel pour répondre à des exigences de conformité (RGPD, SOX, PCI-DSS) et renforcer la sécurité des environnements critiques.

Contrairement à l’audit classique via les logs PostgreSQL ou les triggers, pgaudit offre une granularité et une centralisation des logs d’audit, facilitant la supervision et les analyses post-incident.

Installation (en bref)

L’extension pgaudit nécessite PostgreSQL 9.5 ou supérieur. Elle est souvent disponible dans les dépôts officiels ou via la compilation des sources. Après installation, il suffit de l’activer dans la configuration, ce qui sera détaillé ci-dessous.

Configuration de pgaudit

Pour activer et configurer pgaudit, vous devez modifier le fichier postgresql.conf et éventuellement le fichier pg_hba.conf. Voici les étapes clés :

a. Charger l’extension au démarrage

Ajoutez ou modifiez la ligne suivante dans postgresql.conf :

shared_preload_libraries = ‘pgaudit’

Redémarrez PostgreSQL après cette modification.

b. Créer l’extension dans la base

Connectez-vous à la base et exécutez :

CREATE EXTENSION pgaudit;

c. Configurer les paramètres d’audit

Ajoutez dans postgresql.conf les paramètres adaptés à vos besoins. Les principaux sont :

pgaudit.log = ‘all’ # Auditer toutes les catégories (READ, WRITE, FUNCTION, ROLE, DDL, MISC)
pgaudit.log_level = ‘log’ # Niveau de log (log, info, notice, warning, error)
pgaudit.log_parameter = on # Inclure les paramètres des requêtes dans les logs
pgaudit.role = ‘auditeur’ # Optionnel : limiter l’audit à un rôle spécifique

Rechargez la configuration avec :

SELECT pg_reload_conf();

d. Exemples de configuration ciblée

Pour n’auditer que les requêtes DDL et DML :

pgaudit.log = ‘write, ddl’

Cas d’usage : Auditer les actions critiques

pgaudit permet d’auditer différents types d’opérations :

• DDL : création, modification, suppression de tables, schémas, etc.

• DML : INSERT, UPDATE, DELETE.

• SELECT : lecture de données.

• Connexions : suivi des connexions et déconnexions (via les logs standard, pgaudit ne log pas directement les connexions).

Exemple d’audit de requêtes

• Pour auditer toutes les requêtes DDL et DML :

pgaudit.log = ‘ddl, write’

• Pour auditer uniquement les SELECT :

pgaudit.log = ‘read’

Exemple de test complet

   1. Préparation du schéma

Supposons deux utilisateurs : alice et bob, et une table simple :

CREATE TABLE comptes (
id SERIAL PRIMARY KEY,
nom VARCHAR(50),
solde NUMERIC
);

CREATE USER alice WITH PASSWORD ‘alicepass’;
CREATE USER bob WITH PASSWORD ‘bobpass’;

GRANT SELECT, INSERT, UPDATE ON comptes TO alice, bob;

   2. Requêtes à auditer

• Alice insère une ligne :

SET SESSION AUTHORIZATION alice;
INSERT INTO comptes (nom, solde) VALUES (‘Alice’, 1000);

• Bob consulte le solde :

SET SESSION AUTHORIZATION bob;
SELECT * FROM comptes WHERE nom = ‘Alice’;

• Alice met à jour le solde :

SET SESSION AUTHORIZATION alice;
UPDATE comptes SET solde = 1200 WHERE nom = ‘Alice’;

   3. Exemples d’entrées d’audit générées

Les logs générés par pgaudit ressembleront à :

AUDIT: SESSION,1,1,WRITE,INSERT,TABLE,public.comptes,INSERT INTO comptes (nom, solde) VALUES (‘Alice’, 1000);
AUDIT: SESSION,2,1,READ,SELECT,TABLE,public.comptes,SELECT * FROM comptes WHERE nom = ‘Alice’;
AUDIT: SESSION,1,1,WRITE,UPDATE,TABLE,public.comptes,UPDATE comptes SET solde = 1200 WHERE nom = ‘Alice’;

Chaque ligne détaille :

• Le type d’action (READ, WRITE, DDL…)

• L’objet concerné (table, schéma…)

• L’utilisateur, la session, etc.

Analyse des logs générés

Les logs pgaudit sont structurés pour faciliter l’analyse automatisée. Une ligne typique contient :

Pour parser ces logs, il est recommandé d’utiliser des outils comme pgBadger, ELK Stack ou des scripts personnalisés en Python.

Performance et bonnes pratiques

Impact sur les performances

• Surcharge : pgaudit ajoute un overhead, surtout si l’on audite toutes les requêtes. L’impact dépend du volume d’opérations et du niveau d’audit activé.

• Optimisation :

  • Limiter l’audit aux actions critiques (par exemple, DDL et modifications de données sensibles).

  • Utiliser la journalisation asynchrone (log_destination = ‘csvlog’) pour faciliter le traitement.

  • Surveiller la taille et la rotation des logs.

Bonnes pratiques

• N’activez pas l’audit complet (all) en production sans nécessité.

• Filtrez par utilisateur ou rôle si possible.

• Combinez pgaudit avec des solutions de centralisation de logs.

• Comparez avec d’autres solutions : les triggers permettent un audit applicatif mais sont plus complexes à maintenir , les logs standards de PostgreSQL sont moins structurés et plus difficiles à exploiter pour l’audit réglementaire.

Conclusion

pgaudit est un outil incontournable pour renforcer la traçabilité et la conformité des bases PostgreSQL. Il offre une granularité et une centralisation supérieures à l’audit natif, tout en restant plus simple à exploiter que des solutions à base de triggers. Son principal inconvénient reste l’impact potentiel sur les performances, qui doit être maîtrisé par une configuration adaptée. En production, il convient de cibler l’audit sur les opérations sensibles pour bénéficier d’un excellent compromis entre sécurité et efficacité.