PostgreSQL DDL Audit Logging

Diese Dokumentation beschreibt die Einrichtung eines einfachen DDL-Audit-Loggings in PostgreSQL. Ziel ist es, alle relevanten DDL-Kommandos wie CREATE, ALTER, DROP usw. nachvollziehbar zu protokollieren. Dazu werden Event Trigger und benutzerdefinierte Funktionen verwendet, die Informationen über ausgeführte Kommandos in einer zentralen Tabelle speichern.

Verwandter Artikel: Auswertung, pg_stat_statements und Betriebshinweise: PostgreSQL DDL-Audit Setup.

Zunächst wird die Tabelle `ddl_audit_log` erstellt, die alle relevanten Informationen wie Befehlstyp, Objektname, Benutzer und Zeitstempel enthält:

CREATE TABLE IF NOT EXISTS public.ddl_audit_log (
  id serial NOT NULL,
  command_tag text,
  object_type text,
  schema_name text,
  object_name text,
  executed_by text DEFAULT CURRENT_USER,
  executed_at TIMESTAMP WITH TIME zone DEFAULT CURRENT_TIMESTAMP,
  strg text,
  CONSTRAINT ddl_audit_log_pkey PRIMARY KEY (id)
);

Die Spalte `executed_at` verwendet `CURRENT_TIMESTAMP`, um den Zeitpunkt der Einfügung zu erfassen. Optional kann dieser Wert auch explizit in den Triggerfunktionen gesetzt werden, falls Inserts verzögert oder asynchron erfolgen.

Um DDL-Kommandos zu erfassen, werden zwei Event Trigger definiert. Der erste reagiert auf das Ende eines DDL-Kommandos, der zweite auf DROP-Operationen:

CREATE EVENT TRIGGER ddl_audit_trigger ON DDL_COMMAND_END
  EXECUTE PROCEDURE public.log_ddl_command();
 
CREATE EVENT TRIGGER ddl_drop_audit_trigger ON SQL_DROP
  EXECUTE PROCEDURE public.log_ddl_drop();

Diese Funktion verarbeitet alle DDL-Kommandos außer DROP. Sie nutzt `pg_event_trigger_ddl_commands()` und extrahiert relevante Informationen aus dem DDL-Kontext:

CREATE OR REPLACE FUNCTION public.log_ddl_command()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
DECLARE
  ddl_record RECORD;
BEGIN
  FOR ddl_record IN SELECT * FROM pg_event_trigger_ddl_commands()
  LOOP
    INSERT INTO public.ddl_audit_log (
      command_tag,
      object_type,
      schema_name,
      object_name,
      executed_by,
      strg
    )
    VALUES (
      ddl_record.command_tag,
      ddl_record.object_type,
      split_part(ddl_record.object_identity, '.', 1),
      split_part(ddl_record.object_identity, '.', 2),
      CURRENT_USER,
      current_query()
    );
  END LOOP;
END;
$$;

DROP-Kommandos werden separat behandelt, da sie über `pg_event_trigger_dropped_objects()` erfasst werden. Der Befehlstyp wird manuell auf `'DROP'` gesetzt:

CREATE OR REPLACE FUNCTION public.log_ddl_drop()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
DECLARE
  obj RECORD;
BEGIN
  FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects()
  LOOP
    INSERT INTO public.ddl_audit_log (
      command_tag,
      object_type,
      schema_name,
      object_name,
      executed_by
    )
    VALUES (
      'DROP',
      obj.object_type,
      split_part(obj.object_identity, '.', 1),
      split_part(obj.object_identity, '.', 2),
      CURRENT_USER
    );
  END LOOP;
END;
$$;

PostgreSQL bietet mehrere eingebaute Event-Zeitpunkte, die für erweitertes Logging genutzt werden können:

Es handelt sich um Event Trigger (nicht um Row-Level-Trigger auf Tabellen). Nur Rollen mit ausreichenden Rechten (typischerweise Superuser oder entsprechende Konfiguration) dürfen ALTER EVENT TRIGGER ausführen.

Die Namen können bei Abweichung von diesem Beispiel in pg_event_trigger nachgeschlagen werden:

SELECT evtname, evtenabled, evtevent
FROM pg_event_trigger
WHERE evtname LIKE 'ddl%';

evtenabled: O = aktiv (enabled), D = mit DISABLE abgeschaltet.

-- beide Audit-Trigger abschalten (z. B. Massenmigration, Wartung)
ALTER EVENT TRIGGER ddl_audit_trigger DISABLE;
ALTER EVENT TRIGGER ddl_drop_audit_trigger DISABLE;
 
-- nur einen Abschnitt abschalten (CREATE/ALTER oder DROP)
ALTER EVENT TRIGGER ddl_audit_trigger DISABLE;
-- oder nur DROP-Protokollierung:
ALTER EVENT TRIGGER ddl_drop_audit_trigger DISABLE;
 
-- wieder einschalten (Standardzustand)
ALTER EVENT TRIGGER ddl_audit_trigger ENABLE;
ALTER EVENT TRIGGER ddl_drop_audit_trigger ENABLE;

Nach DISABLE werden keine neuen DDL-Einträge mehr geschrieben; bestehende Zeilen in ddl_audit_log bleiben erhalten. Nach Abschluss der Arbeit wieder ENABLE setzen.

PostgreSQL, DDL, Audit, Event Trigger, pg_event_trigger_ddl_commands, Logging, DISABLE, ENABLE, Wartung