Lorsque l’on gère une base de données contenant des informations sensibles, il est essentiel de garder une trace des modifications effectuées. PostgreSQL, grâce à ses capacités avancées avec PL/pgSQL, permet de mettre en place un système de suivi des modifications (insertions, mises à jour, et suppressions) sur des tables critiques. Dans cet article, nous allons voir comment créer une table de suivi des modifications et un trigger associé pour capturer chaque opération.

Étape 1 : Créer une séquence pour générer des identifiants uniques

Pour chaque entrée de suivi, nous aurons besoin d’un identifiant unique. Nous pouvons utiliser une séquence dans PostgreSQL pour gérer cet aspect.

CREATE SEQUENCE public.sequence_de_suivi_base_id
INCREMENT 1
START 1
MINVALUE 1
MAXVALUE 1000000;

ALTER SEQUENCE public.sequence_de_suivi_base_id OWNER TO postgres;

Cette séquence servira de compteur pour attribuer un ID unique à chaque modification enregistrée dans la table de suivi.

Étape 2 : Créer la table de suivi des modifications

Nous allons créer une table dédiée, suivi_base, qui conservera les informations sur les modifications, y compris le nom de la table modifiée, l’utilisateur responsable, le type d’action (I pour INSERT, D pour DELETE, U pour UPDATE), et les données avant et après la modification.

CREATE TABLE public.suivi_base (
    id integer NOT NULL DEFAULT nextval('"sequence_de_suivi_base_id"'::regclass),
    schema character varying(15) NOT NULL,
    nomtable character varying(50) NOT NULL,
    utilisateur character varying(25),
    dateheure timestamp NOT NULL DEFAULT localtimestamp,
    action character varying(1) NOT NULL CHECK (action IN ('I','D','U')),
    dataorigine text,
    datanouvelle text,
    detailmaj text,
    idobjet integer,
    CONSTRAINT "pk_suivi_base" PRIMARY KEY (id)
) TABLESPACE pg_default;

ALTER TABLE public.suivi_base OWNER to postgres;

Des index peuvent être ajoutés sur certaines colonnes pour améliorer les performances de recherche dans cette table :

CREATE INDEX index_suivi_base_nomtable ON public.suivi_base(((schema||'.'||nomtable)::TEXT));
CREATE INDEX index_suivi_base_dateheure ON public.suivi_base(dateheure);
CREATE INDEX index_suivi_base_action ON public.suivi_base(action);
CREATE INDEX index_suivi_base_idobjet ON public.suivi_base(idobjet);

Étape 3 : Créer une fonction de suivi pour insérer des données dans la table de suivi

La fonction fonction_suivi_base_maj sert à insérer une nouvelle entrée dans la table suivi_base à chaque fois qu’une modification est effectuée dans une table surveillée.

CREATE OR REPLACE FUNCTION public.fonction_suivi_base_maj()
RETURNS TRIGGER AS $body$
DECLARE
    variable_ancienne_valeur TEXT;
    variable_nouvelle_valeur TEXT;
    identifiant INTEGER;
BEGIN
    IF (TG_OP = 'UPDATE') THEN
        variable_ancienne_valeur := ROW(OLD.*);
        variable_nouvelle_valeur := ROW(NEW.*);
        identifiant := OLD.id;
        INSERT INTO public.suivi_base (schema, nomtable, utilisateur, action, dataorigine, datanouvelle, detailmaj, idobjet)
        VALUES (TG_TABLE_SCHEMA::TEXT, TG_TABLE_NAME::TEXT, session_user::TEXT, substring(TG_OP,1,1), variable_ancienne_valeur, variable_nouvelle_valeur, current_query(), identifiant);
        RETURN NEW;

    ELSIF (TG_OP = 'DELETE') THEN
        variable_ancienne_valeur := ROW(OLD.*);
        identifiant := OLD.id;
        INSERT INTO public.suivi_base (schema, nomtable, utilisateur, action, dataorigine, detailmaj, idobjet)
        VALUES (TG_TABLE_SCHEMA::TEXT, TG_TABLE_NAME::TEXT, session_user::TEXT, substring(TG_OP,1,1), variable_ancienne_valeur, current_query(), identifiant);
        RETURN OLD;

    ELSIF (TG_OP = 'INSERT') THEN
        variable_nouvelle_valeur := ROW(NEW.*);
        identifiant := NEW.id;
        INSERT INTO public.suivi_base (schema, nomtable, utilisateur, action, datanouvelle, detailmaj, idobjet)
        VALUES (TG_TABLE_SCHEMA::TEXT, TG_TABLE_NAME::TEXT, session_user::TEXT, substring(TG_OP,1,1), variable_nouvelle_valeur, current_query(), identifiant);
        RETURN NEW;

    ELSE
        RAISE WARNING '[public.fonction_suivi_base_maj] - Other action occurred: %, at %', TG_OP, now();
        RETURN NULL;
    END IF;
END;
$body$
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, public;

Cette fonction capture chaque type d’action (INSERT, UPDATE, DELETE) en stockant les anciennes et nouvelles valeurs selon l’opération réalisée.

Étape 4 : Créer le trigger pour automatiser le suivi des modifications

Un trigger est nécessaire pour appeler la fonction fonction_suivi_base_maj à chaque modification de la table à surveiller. Par exemple, pour la table commune :

CREATE TRIGGER trigger_suivi_de_la_base
AFTER INSERT OR UPDATE OR DELETE ON commune
FOR EACH ROW EXECUTE PROCEDURE public.fonction_suivi_base_maj();

Grâce à ce trigger, toutes les modifications de la table commune seront automatiquement enregistrées dans suivi_base.

Conclusion

Le suivi des modifications est essentiel pour toute base de données contenant des informations sensibles ou nécessitant une piste d’audit. Avec PostgreSQL, l’utilisation des fonctions PL/pgSQL et des triggers permet d’implémenter un tel système de manière efficace et personnalisée. Cette méthode garantit une transparence totale sur les actions effectuées, facilitant ainsi la gestion et la sécurisation des données.

Source

Cet article est inspiré par l’approche présentée sur impulsmap.fr, qui propose une solution robuste et détaillée pour le suivi des modifications dans PostgreSQL et PostGIS.