Tabellen mit Triggern auditieren

Warum Auditierung?

Die Audittabelle

CREATE TABLE audit_log (
  id BIGSERIAL PRIMARY KEY,
  ts TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  user_name TEXT NOT NULL DEFAULT CURRENT_USER,
  table_name TEXT NOT NULL,
  action TEXT NOT NULL,             -- INSERT, UPDATE, DELETE
  row_id TEXT,
  old_data JSONB,
  new_data JSONB
);

Die Audit-Triggerfunktion

CREATE OR REPLACE FUNCTION audit_row()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log (table_name, action, row_id, old_data, new_data)
  VALUES (
    TG_TABLE_NAME,
    TG_OP,
    COALESCE(NEW.id::TEXT, OLD.id::TEXT),
    CASE WHEN TG_OP IN ('UPDATE','DELETE') THEN to_jsonb(OLD) END,
    CASE WHEN TG_OP IN ('INSERT','UPDATE') THEN to_jsonb(NEW) END
  );
  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

An Tabellen anhängen

CREATE TRIGGER trg_audit_users
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION audit_row();

to_jsonb(NEW)

Den Akteur erfassen

-- App sets:
SET LOCAL app.user_id = '42';

-- Trigger reads:
INSERT INTO audit_log (... actor_id ...) VALUES (..., current_setting('app.user_id', true)::BIGINT);

Bestimmte Spalten auditieren

CREATE TRIGGER trg_audit_role_change
AFTER UPDATE OF role ON users
FOR EACH ROW WHEN (OLD.role IS DISTINCT FROM NEW.role)
EXECUTE FUNCTION audit_role_change();

Audittabellen pro Tabelle

Nur Änderungen auditieren

INSERT INTO audit_log (table_name, action, changes)
VALUES (
  TG_TABLE_NAME, TG_OP,
  (SELECT jsonb_object_agg(key, value)
   FROM jsonb_each(to_jsonb(NEW))
   WHERE NEW.* IS DISTINCT FROM OLD.* AND to_jsonb(NEW)->key IS DISTINCT FROM to_jsonb(OLD)->key)
);

Performance der Audittabelle

CREATE TABLE audit_log (...) PARTITION BY RANGE (ts);
CREATE TABLE audit_log_2024_q1 PARTITION OF audit_log
  FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

Abwägung bei der Performance

Wann Sie KEINE Datenbank-Trigger verwenden sollten

Rückblick

Kurzer Test