pgAudit
Journalisation d'audit open source pour PostgreSQL

Introduction

L'extension d'audit PostgreSQL (pgAudit) fournit une journalisation d'audit détaillée de session et/ou d'objet via le mécanisme standard de journalisation de PostgreSQL.

L'objectif de pgAudit est de fournir aux utilisateurs de PostgreSQL la capacité de produire des journaux d'audit souvent requis pour se conformer aux certifications gouvernementales, financières ou ISO.

Un audit est une inspection officielle des comptes d'un individu ou d'une organisation, généralement par un organisme indépendant. Les informations recueillies par pgAudit sont correctement appelées une piste d'audit ou un journal d'audit. Le terme journal d'audit est utilisé dans cette documentation.

Pourquoi pgAudit ?

La journalisation de base des instructions peut être fournie par le mécanisme de journalisation standard avec log_statement = all. Cela est acceptable pour la surveillance et d'autres usages, mais ne fournit pas le niveau de détail généralement requis pour un audit. Il ne suffit pas d'avoir une liste de toutes les opérations effectuées sur la base de données. Il doit également être possible de trouver des instructions particulières qui intéressent un auditeur. Le mécanisme de journalisation standard montre ce que l'utilisateur a demandé, tandis que pgAudit se concentre sur les détails de ce qui s'est passé pendant que la base de données satisfaisait la demande.

Par exemple, un auditeur peut vouloir vérifier qu'une table particulière a été créée dans une fenêtre de maintenance documentée. Cela peut sembler une tâche simple pour grep, mais que faire si vous êtes confronté à quelque chose comme cet exemple (volontairement obscurci) :

DO $$
BEGIN
    EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';
END $$;

La journalisation standard vous donnera ceci :

LOG:  statement: DO $$
BEGIN
    EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';
END $$;

Il semble que trouver la table d'intérêt puisse nécessiter une certaine connaissance du code dans les cas où les tables sont créées dynamiquement. Ce n'est pas idéal car il serait préférable de simplement rechercher le nom de la table. C'est là que pgAudit intervient. Pour la même entrée, il produira cette sortie dans le journal :

AUDIT: SESSION,33,1,FUNCTION,DO,,,"DO $$
BEGIN
    EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';
END $$;"
AUDIT: SESSION,33,2,DDL,CREATE TABLE,TABLE,public.important_table,CREATE TABLE important_table (id INT)

Non seulement le bloc DO est journalisé, mais la sous-instruction 2 contient le texte complet du CREATE TABLE avec le type d'instruction, le type d'objet et le nom complet qualifié pour faciliter les recherches.

Lors de la journalisation des instructions SELECT et DML, pgAudit peut être configuré pour journaliser une entrée distincte pour chaque relation référencée dans une instruction. Aucune analyse syntaxique n'est requise pour trouver toutes les instructions qui touchent une table particulière. En fait, l'objectif est que le texte de l'instruction soit fourni principalement pour des investigations approfondies et ne devrait pas être requis pour un audit.

Considérations d'utilisation

Selon les paramètres, il est possible que pgAudit génère un volume énorme de journalisation. Soyez prudent pour déterminer exactement ce qui doit être journalisé pour l'audit dans votre environnement afin d'éviter de journaliser trop.

Par exemple, dans un environnement OLAP, il ne serait probablement pas judicieux de journaliser les insertions dans une grande table de faits. La taille du fichier journal sera probablement plusieurs fois la taille réelle des données des insertions car le fichier journal est exprimé en texte. Comme les journaux sont généralement stockés avec le système d'exploitation, cela peut conduire à un épuisement très rapide de l'espace disque. Dans les cas où il n'est pas possible de limiter la journalisation d'audit à certaines tables, assurez-vous d'évaluer l'impact sur les performances pendant les tests et allouez beaucoup d'espace sur le volume des journaux. Cela peut également être vrai pour les environnements OLTP. Même si le volume d'insertions n'est pas aussi élevé, l'impact sur les performances de la journalisation d'audit peut néanmoins affecter sensiblement la latence.

Pour limiter le nombre de relations journalisées pour l'audit des instructions SELECT et DML, envisagez d'utiliser la journalisation d'audit d'objets (voir Audit d'objets). La journalisation d'audit d'objets permet de sélectionner les relations à journaliser, ce qui permet de réduire le volume global des journaux. Cependant, lorsque de nouvelles relations sont ajoutées, elles doivent être explicitement ajoutées à la journalisation d'audit d'objets. Une solution programmatique où des tables spécifiées sont exclues de la journalisation et toutes les autres sont incluses peut être une bonne option dans ce cas.

Compatibilité des versions de PostgreSQL

pgAudit prend en charge PostgreSQL 14 ou supérieur.

Afin de prendre en charge les nouvelles fonctionnalités introduites dans chaque version de PostgreSQL, pgAudit maintient une branche séparée pour chaque version majeure de PostgreSQL (actuellement PostgreSQL 14 - 19) qui sera maintenue d'une manière similaire au projet PostgreSQL.

En dehors des corrections de bogues, aucun développement supplémentaire n'est autorisé pour les branches stables. Les nouveaux développements, s'il y en a, seront strictement pour la prochaine version majeure non publiée de PostgreSQL.

Les versions de pgAudit correspondent aux versions majeures de PostgreSQL comme suit :

• pgAudit v19.X est destiné à prendre en charge PostgreSQL 19.

• pgAudit v18.X est destiné à prendre en charge PostgreSQL 18.

• pgAudit v17.X est destiné à prendre en charge PostgreSQL 17.

• pgAudit v16.X est destiné à prendre en charge PostgreSQL 16.

• pgAudit v1.7.X est destiné à prendre en charge PostgreSQL 15.

• pgAudit v1.6.X est destiné à prendre en charge PostgreSQL 14.

Compilation et installation

pgAudit peut être compilé contre une copie installée de PostgreSQL avec les paquets de développement en utilisant PGXS. Les instructions suivantes devraient fonctionner sur la plupart des systèmes d'exploitation de type Unix.

Clonez l'extension pgAudit :

git clone https://github.com/pgaudit/pgaudit.git

Déplacez-vous dans le répertoire pgAudit :

cd pgaudit

Extrayez la branche REL_19_STABLE (notez que la branche stable peut ne pas exister pour les versions non publiées de PostgreSQL) :

git checkout REL_19_STABLE

Compilez et installez pgAudit :

make install USE_PGXS=1 PG_CONFIG=/usr/pgsql-19/bin/pg_config

Les instructions pour les tests et le développement se trouvent dans test.

Paramètres

Les paramètres ne peuvent être modifiés que par un superutilisateur. Autoriser les utilisateurs normaux à modifier leurs paramètres irait à l'encontre de l'objectif d'un journal d'audit.

Les paramètres peuvent être spécifiés globalement (dans postgresql.conf ou en utilisant ALTER SYSTEM ... SET), au niveau de la base de données (en utilisant ALTER DATABASE ... SET), ou au niveau du rôle (en utilisant ALTER ROLE ... SET). Notez que les paramètres ne sont pas hérités par l'héritage normal des rôles et que SET ROLE ne modifiera pas les paramètres pgAudit d'un utilisateur. Il s'agit d'une limitation du système de rôles et non inhérente à pgAudit.

L'extension pgAudit doit être chargée dans shared_preload_libraries. Sinon, une erreur sera levée au moment du chargement et aucune journalisation d'audit n'aura lieu.

De plus, CREATE EXTENSION pgaudit doit être appelé avant que pgaudit.log ne soit défini pour garantir le bon fonctionnement de pgaudit. L'extension installe des déclencheurs d'événements qui ajoutent une journalisation supplémentaire pour le DDL. pgAudit fonctionnera sans l'extension installée, mais les instructions DDL n'auront pas d'informations sur le type d'objet et le nom.

Si l'extension pgaudit est supprimée et doit être recréée, pgaudit.log doit d'abord être désactivé, sinon une erreur sera levée.

pgaudit.log

Spécifie quelles classes d'instructions seront journalisées par la journalisation d'audit de session. Les valeurs possibles sont :

• READ : SELECT et COPY lorsque la source est une relation ou une requête.

• WRITE : INSERT, UPDATE, DELETE, TRUNCATE et COPY lorsque la destination est une relation.

• FUNCTION : Appels de fonctions et blocs DO.

• ROLE : Instructions relatives aux rôles et privilèges : GRANT, REVOKE, CREATE/ALTER/DROP ROLE.

• DDL : Tout DDL qui n'est pas inclus dans la classe ROLE.

• MISC : Commandes diverses, p. ex. DISCARD, FETCH, CHECKPOINT, VACUUM, SET.

• MISC_SET : Commandes SET diverses, p. ex. SET ROLE.

• ALL : Inclut tout ce qui précède.

Plusieurs classes peuvent être fournies en utilisant une liste séparée par des virgules et des classes peuvent être soustraites en préfixant la classe avec un signe - (voir Journalisation d'audit de session).

La valeur par défaut est none.

pgaudit.log_catalog

Spécifie que la journalisation de session doit être activée dans le cas où toutes les relations d'une instruction sont dans pg_catalog. La désactivation de ce paramètre réduira le bruit dans le journal provenant d'outils comme psql et PgAdmin qui interrogent fortement le catalogue.

La valeur par défaut est on.

pgaudit.log_client

Spécifie si les messages de journal seront visibles par un processus client tel que psql. Ce paramètre doit généralement rester désactivé mais peut être utile pour le débogage ou à d'autres fins.

Notez que pgaudit.log_level n'est activé que lorsque pgaudit.log_client est on.

La valeur par défaut est off.

pgaudit.log_level

Spécifie le niveau de journal qui sera utilisé pour les entrées de journal (voir Niveaux de sévérité des messages pour les niveaux valides) mais notez que ERROR, FATAL et PANIC ne sont pas autorisés). Ce paramètre est utilisé pour les tests de régression et peut également être utile aux utilisateurs finaux pour les tests ou à d'autres fins.

Notez que pgaudit.log_level n'est activé que lorsque pgaudit.log_client est on ; sinon, la valeur par défaut sera utilisée.

La valeur par défaut est log.

pgaudit.log_parameter

Spécifie que la journalisation d'audit doit inclure les paramètres qui ont été passés avec l'instruction. Lorsque des paramètres sont présents, ils seront inclus au format CSV après le texte de l'instruction.

La valeur par défaut est off.

pgaudit.log_parameter_max_size

Spécifie que les valeurs de paramètres plus longues que ce paramètre (en octets) ne doivent pas être journalisées, mais remplacées par <long param suppressed>. Ceci est défini en octets, pas en caractères, donc ne tient pas compte des caractères multi-octets dans l'encodage d'un paramètre texte. Ce paramètre n'a aucun effet si log_parameter est off. Si ce paramètre est 0 (la valeur par défaut), tous les paramètres sont journalisés quelle que soit leur longueur.

La valeur par défaut est 0.

pgaudit.log_relation

Spécifie si la journalisation d'audit de session doit créer une entrée de journal distincte pour chaque relation (TABLE, VIEW, etc.) référencée dans une instruction SELECT ou DML. C'est un raccourci utile pour une journalisation exhaustive sans utiliser la journalisation d'audit d'objets.

La valeur par défaut est off.

pgaudit.log_rows

Spécifie que la journalisation d'audit doit inclure le nombre de lignes récupérées ou affectées par une instruction. Lorsqu'il est activé, le champ lignes sera inclus après le champ paramètre.

La valeur par défaut est off.

pgaudit.log_statement

Spécifie si la journalisation inclura le texte de l'instruction et les paramètres (si activés). Selon les exigences, un journal d'audit peut ne pas en avoir besoin et cela rend les journaux moins verbeux.

La valeur par défaut est on.

pgaudit.log_statement_once

Spécifie si la journalisation inclura le texte de l'instruction et les paramètres avec la première entrée de journal pour une combinaison instruction/sous-instruction ou avec chaque entrée. L'activation de ce paramètre entraînera une journalisation moins verbeuse mais peut rendre plus difficile la détermination de l'instruction qui a généré une entrée de journal, bien que la paire instruction/sous-instruction avec l'identifiant de processus devrait suffire pour identifier le texte de l'instruction journalisé avec une entrée précédente.

La valeur par défaut est off.

pgaudit.role

Spécifie le rôle principal à utiliser pour la journalisation d'audit d'objets. Plusieurs rôles d'audit peuvent être définis en les accordant au rôle principal. Cela permet à plusieurs groupes d'être en charge de différents aspects de la journalisation d'audit.

Il n'y a pas de valeur par défaut.

Journalisation d'audit de session

La journalisation d'audit de session fournit des journaux détaillés de toutes les instructions exécutées par un utilisateur dans le backend.

Configuration

La journalisation de session est activée avec le paramètre pgaudit.log.

Activez la journalisation de session pour tout DML et DDL et journalisez toutes les relations dans les instructions DML :

set pgaudit.log = 'write, ddl';
set pgaudit.log_relation = on;

Activez la journalisation de session pour toutes les commandes sauf MISC et élevez les messages du journal d'audit en NOTICE :

set pgaudit.log = 'all, -misc';
set pgaudit.log_level = notice;

Exemple

Dans cet exemple, la journalisation d'audit de session est utilisée pour journaliser les instructions DDL et SELECT. Notez que l'instruction insert n'est pas journalisée car la classe WRITE n'est pas activée.

SQL :

set pgaudit.log = 'read, ddl';

create table account
(
    id int,
    name text,
    password text,
    description text
);

insert into account (id, name, password, description)
             values (1, 'user1', 'HASH1', 'blah, blah');

select *
    from account;

Sortie du journal :

AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.account,create table account
(
    id int,
    name text,
    password text,
    description text
);,<not logged>
AUDIT: SESSION,2,1,READ,SELECT,,,select *
    from account,,<not logged>

Journalisation d'audit d'objets

La journalisation d'audit d'objets journalise les instructions qui affectent une relation particulière. Seules les commandes SELECT, INSERT, UPDATE et DELETE sont prises en charge. TRUNCATE n'est pas inclus dans la journalisation d'audit d'objets.

La journalisation d'audit d'objets est destinée à être un remplacement plus fin que pgaudit.log = 'read, write'. En tant que telle, il peut ne pas être judicieux de les utiliser conjointement, mais un scénario possible serait d'utiliser la journalisation de session pour capturer chaque instruction, puis de la compléter avec la journalisation d'objets pour obtenir plus de détails sur des relations spécifiques.

Configuration

La journalisation d'audit au niveau des objets est implémentée via le système de rôles. Le paramètre pgaudit.role définit le rôle qui sera utilisé pour la journalisation d'audit. Une relation (TABLE, VIEW, etc.) sera journalisée pour l'audit lorsque le rôle d'audit a les permissions pour la commande exécutée ou hérite des permissions d'un autre rôle. Cela vous permet d'avoir effectivement plusieurs rôles d'audit même s'il n'y a qu'un seul rôle principal dans n'importe quel contexte.

Définissez pgaudit.role sur auditor et accordez les privilèges SELECT et DELETE sur la table account. Toutes les instructions SELECT ou DELETE sur la table account seront désormais journalisées :

set pgaudit.role = 'auditor';

grant select, delete
   on public.account
   to auditor;

Exemple

Dans cet exemple, la journalisation d'audit d'objets est utilisée pour illustrer comment une approche granulaire peut être adoptée pour la journalisation des instructions SELECT et DML. Notez que la journalisation sur la table account est contrôlée par des permissions au niveau des colonnes, tandis que la journalisation sur la table account_role_map est au niveau de la table.

SQL :

set pgaudit.role = 'auditor';

create table account
(
    id int,
    name text,
    password text,
    description text
);

grant select (password)
   on public.account
   to auditor;

select id, name
  from account;

select password
  from account;

grant update (name, password)
   on public.account
   to auditor;

update account
   set description = 'yada, yada';

update account
   set password = 'HASH2';

create table account_role_map
(
    account_id int,
    role_id int
);

grant select
   on public.account_role_map
   to auditor;

select account.password,
       account_role_map.role_id
  from account
       inner join account_role_map
            on account.id = account_role_map.account_id

Sortie du journal :

AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.account,select password
  from account,<not logged>
AUDIT: OBJECT,2,1,WRITE,UPDATE,TABLE,public.account,update account
   set password = 'HASH2',<not logged>
AUDIT: OBJECT,3,1,READ,SELECT,TABLE,public.account,select account.password,
       account_role_map.role_id
  from account
       inner join account_role_map
            on account.id = account_role_map.account_id,<not logged>
AUDIT: OBJECT,3,1,READ,SELECT,TABLE,public.account_role_map,select account.password,
       account_role_map.role_id
  from account
       inner join account_role_map
            on account.id = account_role_map.account_id,<not logged>

Format

Les entrées d'audit sont écrites dans le mécanisme de journalisation standard et contiennent les colonnes suivantes au format séparé par des virgules. La sortie n'est au format CSV conforme que si la partie préfixe de ligne de journal de chaque entrée de journal est supprimée.

• AUDIT_TYPE - SESSION ou OBJECT.

• STATEMENT_ID - Identifiant d'instruction unique pour cette session. Chaque identifiant d'instruction représente un appel backend. Les identifiants d'instruction sont séquentiels même si certaines instructions ne sont pas journalisées. Il peut y avoir plusieurs entrées pour un identifiant d'instruction lorsque plus d'une relation est journalisée.

• SUBSTATEMENT_ID - Identifiant séquentiel pour chaque sous-instruction dans l'instruction principale. Par exemple, appeler une fonction depuis une requête. Les identifiants de sous-instruction sont continus même si certaines sous-instructions ne sont pas journalisées. Il peut y avoir plusieurs entrées pour un identifiant de sous-instruction lorsque plus d'une relation est journalisée.

• CLASS - p. ex. READ, ROLE (voir pgaudit.log).

• COMMAND - p. ex. ALTER TABLE, SELECT.

• OBJECT_TYPE - TABLE, INDEX, VIEW, etc. Disponible pour les instructions SELECT, DML et la plupart des DDL.

• OBJECT_NAME - Le nom d'objet complet qualifié (p. ex. public.account). Disponible pour les instructions SELECT, DML et la plupart des DDL.

• STATEMENT - Instruction exécutée sur le backend.

• PARAMETER - Si pgaudit.log_parameter est défini, ce champ contiendra les paramètres de l'instruction en CSV entre guillemets ou <none> s'il n'y a pas de paramètres. Sinon, le champ est <not logged>.

Utilisez log_line_prefix pour ajouter tous les autres champs nécessaires pour satisfaire vos exigences de journal d'audit. Un préfixe de ligne de journal typique pourrait être '%m %u %d [%p]: ' qui fournirait la date/heure, le nom d'utilisateur, le nom de la base de données et l'identifiant de processus pour chaque journal d'audit.

Mises en garde

La journalisation d'audit est fournie au mieux et n'est pas transactionnelle. pgAudit écrit les entrées d'audit via le mécanisme de journalisation standard de PostgreSQL, qui ne vide pas chaque entrée sur disque de manière synchrone avec la transaction qui l'a produite, et ne propage pas non plus les erreurs d'écriture à la session. Il n'y a aucune garantie qu'une transaction validée aura une entrée de journal d'audit correspondante. Si le serveur plante ou perd de l'alimentation, ou si la destination du journal devient indisponible (par exemple, le volume des journaux est plein) après qu'une transaction a été validée mais avant que ses entrées d'audit ne soient écrites durablement, ces entrées peuvent être perdues. Inversement, une instruction est journalisée lorsqu'elle s'exécute, donc une entrée peut être écrite même si sa transaction est ensuite annulée.

Les renommages d'objets sont journalisés sous le nom vers lequel ils ont été renommés. Par exemple, renommer une table produira le résultat suivant :

ALTER TABLE test RENAME TO test2;

AUDIT: SESSION,36,1,DDL,ALTER TABLE,TABLE,public.test2,ALTER TABLE test RENAME TO test2,<not logged>

Il est possible qu'une commande soit journalisée plus d'une fois. Par exemple, lorsqu'une table est créée avec une clé primaire spécifiée à la création, l'index de la clé primaire sera journalisé indépendamment et un autre journal d'audit sera fait pour l'index sous l'entrée de création. Les entrées multiples seront cependant contenues dans un seul identifiant d'instruction.

Autovacuum et Autoanalyze ne sont pas journalisés.

Les instructions exécutées après qu'une transaction entre dans un état annulé ne seront pas journalisées pour l'audit. Cependant, l'instruction qui a causé l'erreur et toutes les instructions ultérieures exécutées dans la transaction annulée seront journalisées comme des ERROR par le mécanisme de journalisation standard.

Il n'est pas possible de surveiller de manière fiable les superutilisateurs avec pgAudit. Une solution consiste à restreindre l'accès aux comptes superutilisateurs et à utiliser l'extension set_user pour élever les permissions lorsque cela est nécessaire.

Auteurs

L'extension d'audit PostgreSQL est basée sur le projet pgaudit de 2ndQuadrant écrit par Simon Riggs, Abhijit Menon-Sen et Ian Barwick et soumis comme extension au cœur de PostgreSQL. Des développements supplémentaires ont été effectués par David Steele de Crunchy Data.