Audit de tables avec PostgreSQL, PostGIS et QGIS - 1/3

L’audit des tables d’une base de données permet de suivre les modifications effectuées sur celles-ci, que ce soit des INSERT, UPDATE et DELETE par exemple. Pour enregistrer ces changements, il faut passer par un trigger dans PostgreSQL dans lequel on va indiquer les opérations à détecter et ce qu’il faut faire selon celles-ci.

Ainsi, dans cet article nous allons voir comment enregistrer ces changements dans une table, et plus précisément dans des attributs old et new, ainsi que l’identifiant de la personne qui a effectué ceux-ci et à quel moment. De cette manière, s’il faut restaurer une valeur, ou une entité, ce sera plus simple de le faire en ciblant la personne et l’heure à laquelle elle a fait la modification.

Travaillant principalement avec des données spatiales, et vu le format sous lequel les valeurs vont être stockées dans un prochain article nous verrons comment faciliter la consultation et la restauration des données avec QGIS.

Sommaire

Afficher le sommaire

Création de la table d’audit
Création de la fonction d’audit
Et ensuite ?

Création de la table d’audit

Tout d’abord, nous allons créer une table qui va recevoir ces changements. Dans le cas présent, une seule table est créée pour suivre plusieurs tables, mais vous pouvez tout à fait réaliser une table d’audit par table à suivre en adaptant la requête :

CREATE TABLE t_history (
        hid SERIAL PRIMARY KEY,
        datetimez TIMESTAMPTZ DEFAULT current_timestamp,
        schemaname VARCHAR(50),
        tabname VARCHAR(50),
        operation VARCHAR(1), --I insert D delete U update
        username VARCHAR(50) DEFAULT session_user,
        new_val jsonb,
        old_val jsonb
);

Pour le stockage des valeurs, nous allons utiliser le format json, et plus précisément jsonb. Ce choix se fait par rapport à 2 aspects :

le json va permettre de stocker de manière indifférenciée l’ensemble des attributs d’une table ;
le format jsonb supporte l’indexation, ce qui sera utile pour rechercher toutes les modifications qui ont eu lieu dans une zone spécifique par exemple.

Concernant l’indexation, nous utiliserons les 2 suivantes :

BRIN : cet index est très performant pour indexer, et donc rechercher, des données qui ont une continuité. Étant donné que la modification de la table va s’effectuer chronologiquement, il est possible de mettre en place cet index. Il est donc impératif de ne pas toucher à l’attribut date, sinon l’index sera inutile.
GIST : étant donné que nous allons stocker de nombreuses sources de données, le mieux est d’utiliser cet index. Nous aurions pu envisager le SPGIST si nous avions un jeu de données pour lequel il n’y a pas de superposition de données, ce qui ne va pas être le cas.

Une démonstration de l’importante de l’indexation sera présentée dans un autre article.

Création de la fonction d’audit

Maintenant que nous avons notre table, nous allons créer la fonction qui va se charger de peupler celle-ci selon les opérations qui vont être détectées sur les tables à suivre.

CREATE FUNCTION change_trigger() RETURNS trigger AS $$
 BEGIN
  IF      TG_OP = 'INSERT'
    THEN
      INSERT INTO t_history (tabname, schemaname, operation, username, new_val)
            VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, SESSION_USER, ROW_TO_JSON(NEW));
      RETURN NEW;
  ELSIF   TG_OP = 'UPDATE'
    THEN
      INSERT INTO t_history (tabname, schemaname, operation, username, new_val, old_val)
              VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, SESSION_USER, ROW_TO_JSON(NEW), ROW_TO_JSON(OLD));
      RETURN NEW;
  ELSIF   TG_OP = 'DELETE'
    THEN
      INSERT INTO t_history (tabname, schemaname, operation, username, old_val)
            VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, SESSION_USER, ROW_TO_JSON(OLD));
      RETURN OLD;
  END IF;
 END;
$$ LANGUAGE 'plpgsql' SECURITY INVOKER;

Cette fonction se compose de la manière suivante :

TG_value, NEW, OLD : ce sont des variables PostgreSQL spécifiques aux fonctions Trigger visant à identifier les opérations en cours, mais aussi les enregistrements (documentation);
SESSION_USER : le choix ici d’utiliser SESSION_USER au lieu de CURRENT_USER, est que nous souhaitons cibler la personne qui a initialisé la connexion à la base de données, pas le rôle sous lequel elle intervient. Mais au fond, dans le cadre de notre base de données, ce sera toujours la même personne dans les 2 cas. Nous n’allons pas réaliser de fonctions qui vont intervenir sous un autre rôle que celui de la personne connectée pour peupler cette table.
ROW_TO_JSON : c’est la fonction de PostgreSQL permettant de transformer les données en json.
SECURITY INVOKER : cela permet d’indiquer que la fonction sera exécutée selon les droits de l’user qui va l’utiliser.

Ainsi, il ne reste plus qu’à activer cette fonction selon les opérations que nous voulons suivre, c’est à dire INSERT, UPDATE et DELETE.

CREATE TRIGGER history_tablename AFTER INSERT OR UPDATE OR DELETE ON table_de_suivi
FOR EACH ROW EXECUTE PROCEDURE change_trigger();

Le choix d’utiliser le mot clé AFTER au lieu de BEFORE s’explique par l’objectif de la table, c’est-à-dire lister tous les changements qui ont eu lieu dans une table. Ainsi, insérer les valeurs dans la table de log avant qu’elles soient dans la table de destination n’est pas pertinent.

Maintenant, à chaque fois qu’une opération INSERT, UPDATE et DELETE sera effectuée sur la table_de_suivi, la fonction change_trigger() sera déclenchée.

Étant donné que les logs sont écrits selon les droits de la personne qui est connectée, vous devez vous assurer qu’elle ai le droit INSERT dans la table t_history. Elle n’a pas besoin de plus, étant donné que les opérations UPDATE et DELETE sont détectées dans la table cible, en aucun cas elles seront appliquées dans la table de log. Puisque des INSERT sont effectués et que nous avons une clé primaire, veillez à bien donner les droits USAGE et SELECT de la SEQUENCE gérant l’identifiant aux personnes qui vont modifier les tables cibles.

GRANT USAGE, SELECT ON SEQUENCE t_history_id_seq TO user;

De là, lorsqu’un user effectuera des changements dans la table suivie, une entrée old_val/new_val sera ajoutée dans la table de log.

Et ensuite ?

Maintenant que nous avons cet audit, nous pouvons le consulter en faisant une requête SQL. Cependant, la mise en forme n’est pas la plus pratique, et surtout, nous souhaitons afficher facilement ces logs dans QGIS (et avec la géométrie de nos objets) afin de restaurer facilement des valeurs/entités au besoin. Pour cela, nous allons créer des vues qui vont convertir les attributs:valeurs stockées dans les champs old_val et new_val qui sont au format jsonb.