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.

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: