PL/pgSQL-Funktionen & Revisionssichere Audit-Trigger: Compliance im Datenbankkern

Erstellt: 08.11.2026  ·  Zuletzt aktualisiert: 08.11.2026  ·  Nejat Hakan

Management Summary: Audit-Protokollierungen auf reiner Applikationsebene scheitern regelmäßig an regulatorischen Compliance-Vorgaben: Direkte administrative Datenbankzugriffe via psql, Migrationstools, fehlerhafte Backend-Exceptions oder unautorisierte Skripte umgehen die Protokollierung der Web-Anwendung vollkommen. Wer geschäftliche Invarianten und Audit-Trails direkt in der Datenbank-Engine verankert, stellt sicher, dass jede Datenänderung unumgänglich innerhalb derselben atomaren ACID-Transaktionsgrenze protokolliert wird. Dieser Leitfaden beschreibt die PL/pgSQL-Laufzeitumgebung, Sicherheitsmodelle (SECURITY DEFINER) und den Aufbau eines generischen, manipulationssicheren Audit-Logs mittels JSONB-Diffs.

• PL/pgSQL-Architektur

  ---

  Prozedurale Geschäftslogik speichernah im RAM neben den Datenblöcken ausführen; Netzwerk-Latenzen eliminieren und Transaktionssicherheit garantieren.

• SECURITY DEFINER Härtung

  ---

  Privilege-Escalation-Exploits ausschließen: search_path strikt auf pg_catalog, pg_temp festnageln, um manipulierte Schemata abzuwehren.

• BEFORE vs. AFTER Trigger

  ---

  Eingehende Werte mit BEFORE-Triggern bereinigen und prüfen; unveränderliche Audit-Ereignisse mit AFTER-Triggern atomar festhalten.

• Generischer JSONB-Audit-Trail

  ---

  Präzise Attribut-Deltas tabellenübergreifend erfassen: to_jsonb(OLD) vs. to_jsonb(NEW) inklusive Benutzer- und Sitzungs-Metadaten archivieren.

---

1. Das prozedurale Ausführungsmodell in PostgreSQL

Während Standard-SQL rein deklarativ arbeitet, erfordern komplexe betriebliche Abläufe (wie mehrstufige Buchungsprüfungen, Zustandsübergänge oder Compliance-Protokolle) prozedurale Kontrollstrukturen wie Schleifen, Bedingungen und Exception-Handling.

%%{init: {'flowchart': {'wrappingWidth': 350}}}%%
flowchart TB
    DML["Client-DML-Operation<br/>(INSERT, UPDATE, DELETE)"]
    TRG_B["1. BEFORE-Trigger<br/>Validiert & modifiziert NEW"]
    HEAP["2. Heap-Schreibvorgang<br/>Änderung in 8 KiB Page"]
    TRG_A["3. AFTER-Trigger<br/>Feuert Audit-Funktion ab"]
    AUDIT["4. Audit-Tabelle<br/>Schreibt JSONB-Diff"]
    COMMIT["5. Transaktions-Commit<br/>Änderung & Log gesichert"]

    DML --> TRG_B
    TRG_B --> HEAP
    HEAP --> TRG_A
    TRG_A --> AUDIT
    AUDIT --> COMMIT

Funktionen vs. Stored Procedures

In modernem PostgreSQL erfüllen Funktionen und Prozeduren unterschiedliche architektonische Aufgaben:

Eigenschaft	Benutzerdefinierte Funktion (FUNCTION)	Stored Procedure (PROCEDURE)
Aufrufsyntax	SELECT my_func(arg);	CALL my_proc(arg);
Rückgabewert	Muss einen Wert, eine Tabelle oder VOID liefern	Liefert keinen direkten Rückgabewert (nutzt INOUT)
Transaktionskontrolle	Kann nicht committen/rollen (läuft in äußerer Transaktion)	Kann committen/rollen (COMMIT, ROLLBACK im Rumpf)
Anwendungsbereich	Datentransformationen, Trigger-Handler, Berechnungen	Große Datenmigrationen, ETL-Batch-Jobs, Partitionierungs-Wartung

---

2. Absicherung von SECURITY DEFINER-Funktionen

Standardmäßig laufen Funktionen mit den Rechten des aufrufenden Benutzers (SECURITY INVOKER). Eine als SECURITY DEFINER deklarierte Funktion läuft dagegen mit den Rechten des Benutzers, der die Funktion erstellt hat (vergleichbar mit dem setuid-Bit unter Linux).

Die search_path Sicherheitslücke

Erstellt ein Angreifer eine manipulierte Tabelle oder einen Operator in einem temporären Schema und ruft anschließend eine SECURITY DEFINER-Funktion auf, führt diese Funktion unter Umständen den Code des Angreifers mit Superuser-Rechten aus. Setze daher immer zwingend den search_path bei jeder SECURITY DEFINER-Funktion!

-- Best Practice: Sichere Funktionsdeklaration
CREATE OR REPLACE FUNCTION fn_secure_operation()
RETURNS VOID
LANGUAGE plpgsql
SECURITY DEFINER
-- search_path strikt festnageln, um Schema-Injections zu verhindern
SET search_path = pg_catalog, pg_temp
AS $$
BEGIN
    -- Privilegierte Operationen laufen hier manipulationssicher ab
END;
$$;

---

3. Trigger-Mechanik: Lebenszyklus & Kontext-Variablen

Ein Trigger führt eine zugehörige PL/pgSQL-Funktion automatisch aus, sobald ein definiertes Ereignis auf einer Tabelle eintritt: - BEFORE-Trigger: Feuern, bevor die Zeilenänderung in den Heap geschrieben wird. Können den Datensatz NEW verändern oder mit einer Exception die gesamte Transaktion abbrechen. - AFTER-Trigger: Feuern, nachdem die Zeilenänderung geschrieben wurde. Ideal für Audit-Einträge oder das Nachführen denormalisierter Zähler. - INSTEAD OF-Trigger: Feuern auf Views, die nicht automatisch beschreibbar sind, und übersetzen Schreibvorgänge in Befehle auf den Basistabellen.

Wichtige Kontext-Variablen in Trigger-Funktionen

Variable	Datentyp	Beschreibung
NEW	RECORD	Neuer Zustand der Zeile bei INSERT oder UPDATE (NULL bei DELETE).
OLD	RECORD	Vorheriger Zustand der Zeile bei UPDATE oder DELETE (NULL bei INSERT).
TG_OP	TEXT	Auslöser-Operation: 'INSERT', 'UPDATE' oder 'DELETE'.
TG_TABLE_NAME	TEXT	Name der Tabelle, auf der der Trigger ausgelöst wurde.
TG_WHEN	TEXT	Zeitpunkt: 'BEFORE', 'AFTER' oder 'INSTEAD OF'.

---

4. Hands-on-Labor: Revisionssicherer generischer JSONB-Audit-Trail

Richte ein unternehmensweites, generisches Audit-System ein, das Änderungen über alle Tabellen hinweg automatisch als JSONB-Deltas festhält:

-- Datei: /opt/db-ops/tamper_evident_audit_lab.sql
BEGIN;

-- 1. Zentrale Audit-Tabelle anlegen
CREATE TABLE security_audit_log (
    audit_id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    table_name      TEXT NOT NULL,
    operation       VARCHAR(8) NOT NULL,
    db_user         TEXT NOT NULL DEFAULT session_user,
    client_addr     INET DEFAULT inet_client_addr(),
    changed_at      TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
    old_data        JSONB,
    new_data        JSONB
);

-- 2. Berechtigungen sperren: Applikationen dürfen Logs niemals ändern oder löschen!
REVOKE UPDATE, DELETE, TRUNCATE ON security_audit_log FROM PUBLIC;

-- 3. Generische PL/pgSQL Trigger-Funktion definieren
CREATE OR REPLACE FUNCTION fn_generic_audit_trail()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, pg_temp
AS $$
BEGIN
    IF (TG_OP = 'DELETE') THEN
        INSERT INTO public.security_audit_log (
            table_name, operation, old_data, new_data
        ) VALUES (
            TG_TABLE_NAME, TG_OP, to_jsonb(OLD), NULL
        );
        RETURN OLD;

    ELSIF (TG_OP = 'UPDATE') THEN
        -- Nur protokollieren, wenn sich tatsächlich Spaltenwerte geändert haben
        IF to_jsonb(OLD) IS DISTINCT FROM to_jsonb(NEW) THEN
            INSERT INTO public.security_audit_log (
                table_name, operation, old_data, new_data
            ) VALUES (
                TG_TABLE_NAME, TG_OP, to_jsonb(OLD), to_jsonb(NEW)
            );
        END IF;
        RETURN NEW;

    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO public.security_audit_log (
            table_name, operation, old_data, new_data
        ) VALUES (
            TG_TABLE_NAME, TG_OP, NULL, to_jsonb(NEW)
        );
        RETURN NEW;
    END IF;

    RETURN NULL;
END;
$$;

-- 4. Trigger an die Produkttabelle koppeln
CREATE TRIGGER trg_audit_products
AFTER INSERT OR UPDATE OR DELETE ON products
FOR EACH ROW EXECUTE FUNCTION fn_generic_audit_trail();

COMMIT;

Verifikation der Unveränderbarkeit in psql

Führe ein Update aus und prüfe das resultierende JSONB-Delta im Audit-Log:

-- Produkt-Preis und Bestand aktualisieren
UPDATE products 
SET price_cents = 19900, stock_qty = 35 
WHERE sku = 'SRV-NVME-2TB';

-- Revisions-Trail abfragen:
SELECT 
    audit_id, 
    table_name, 
    operation, 
    db_user, 
    changed_at,
    old_data->>'price_cents' AS old_price,
    new_data->>'price_cents' AS new_price
FROM security_audit_log
ORDER BY audit_id DESC
LIMIT 1;