Audit-trigger — vijf manieren waarop je logtabel liegt
Een PL/pgSQL-trigger die elke wijziging als JSONB wegschrijft. Hij crasht op DELETE, mist precies de gebeurtenissen die je wilde vastleggen, en zijn tijdstempels staan allemaal gelijk.
"We willen kunnen zien wie wat wanneer gewijzigd heeft." Vrijwel elk systeem krijgt vroeg of laat zo'n trigger. Deze ziet er compleet uit: hij slaat oude en nieuwe waarde op als JSONB, berekent welke velden veranderd zijn, en registreert de gebruiker.
Hij zit vol gaten. Eén ervan laat de trigger hard crashen; de andere zorgen ervoor dat de logtabel er gevuld uitziet terwijl hij het verkeerde verhaal vertelt. Dat tweede is erger — een crash merk je.
CREATE OR REPLACE FUNCTION audit.log_wijziging()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
v_oud jsonb;
v_nieuw jsonb;
v_gewijzigd text[];
BEGIN
IF TG_OP = 'DELETE' THEN
v_oud := to_jsonb(OLD);
v_nieuw := NULL;
ELSIF TG_OP = 'INSERT' THEN
v_oud := NULL;
v_nieuw := to_jsonb(NEW);
ELSE
v_oud := to_jsonb(OLD);
v_nieuw := to_jsonb(NEW);
SELECT array_agg(n.sleutel)
INTO v_gewijzigd
FROM jsonb_each(v_nieuw) AS n(sleutel, waarde)
WHERE v_oud -> n.sleutel IS DISTINCT FROM n.waarde;
IF v_gewijzigd IS NULL THEN
RETURN NEW;
END IF;
END IF;
INSERT INTO audit.wijzigingen
(tabel, record_id, actie, oude_waarde, nieuwe_waarde,
gewijzigde_velden, gebruiker, tijdstip)
VALUES (TG_TABLE_NAME,
COALESCE(NEW.id, OLD.id),
TG_OP,
v_oud,
v_nieuw,
v_gewijzigd,
current_setting('app.gebruiker_id', true),
now());
RETURN COALESCE(NEW, OLD);
END;
$$;
CREATE TRIGGER klanten_audit
AFTER INSERT OR UPDATE OR DELETE ON crm.klanten
FOR EACH ROW EXECUTE FUNCTION audit.log_wijziging();
TG_OP, OLD en NEW zijn automatisch beschikbaar in een triggerfunctie. Wat bevat elk van de drie
bij een INSERT, een UPDATE en een DELETE?
Er staat COALESCE(NEW.id, OLD.id). De bedoeling is duidelijk: pak NEW.id, en als die er niet is,
OLD.id. Werkt dat bij een DELETE?
De trigger is AFTER. Wat doen RETURN NEW en RETURN COALESCE(NEW, OLD) dan eigenlijk?
De functie is SECURITY DEFINER. Wat betekent dat hier, en wat ontbreekt er?
De trigger vergelijkt de velden via jsonb_each(v_nieuw). Welk soort wijziging ontsnapt daaraan?
current_setting('app.gebruiker_id', true) — wat doet die tweede parameter, en wat is het gevolg?
now() staat in de tijdstempel. Waarom zien alle auditrijen van één bulkwijziging er daardoor
identiek uit?
De grootste beperking staat niet in de functie zelf. Welke gebeurtenissen komen er nóóit in deze audittabel terecht?