PostgreSQL DDL-Audit Setup

Dieses Setup dokumentiert alle strukturverändernden Aktionen (DDL) in PostgreSQL, wie z. B. CREATE TABLE, ALTER FUNCTION, DROP INDEX usw. Die Änderungen werden automatisch in einer zentralen Audit-Tabelle gespeichert und sind über ein Dashboard oder View auswertbar.

Verwandter Artikel: Tabellen, Trigger-Funktionen, Event Trigger und Wartung (DISABLE/ENABLE) sind in PostgreSQL DDL Audit Logging beschrieben (Referenzimplementierung).

* Nachvollziehbare Dokumentation aller DDL-Änderungen * Speicherung in einer zentralen Tabelle im Schema public * Unterstützung für CREATE, ALTER, DROP und verwandte Befehle * Trennung von Schema und Objektname für bessere Auswertbarkeit * Minimale Belastung für produktive Systeme

* Audit-Tabelle: Speicherung der DDL-Ereignisse * Event Trigger: Reaktion auf DDL-Befehle * Trigger-Prozeduren: Extrahieren der Metadaten aus Systemfunktionen * Optional: pg_stat_statements: Ergänzende Erfassung von DDL-Texten

CREATE TABLE public.ddl_audit_log (
id SERIAL PRIMARY KEY,
command_tag TEXT,             -- z. B. CREATE TABLE, DROP FUNCTION
object_type TEXT,             -- z. B. TABLE, FUNCTION, INDEX
schema_name TEXT,             -- z. B. public
object_name TEXT,             -- z. B. users
executed_by TEXT DEFAULT CURRENT_USER,
executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

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
)
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
);
END LOOP;
END;
$$;

CREATE EVENT TRIGGER ddl_audit_trigger
ON ddl_command_end
EXECUTE FUNCTION public.log_ddl_command();

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;
$$;

CREATE EVENT TRIGGER ddl_drop_audit_trigger
ON sql_drop
EXECUTE FUNCTION public.log_ddl_drop();

Befehle und Erläuterung: PostgreSQL DDL Audit Logging — Abschnitt Wartung: Trigger aktivieren/deaktivieren.

\-- Beispielaktion
CREATE TABLE public.audit_test (
id SERIAL PRIMARY KEY,
name TEXT
);
 
DROP TABLE public.audit_test;
 
\-- Auswertung
SELECT * FROM public.ddl_audit_log ORDER BY executed_at DESC;

CREATE VIEW public.ddl_audit_summary AS
SELECT
executed_at::DATE AS datum,
schema_name,
object_name,
command_tag,
object_type,
executed_by
FROM public.ddl_audit_log
ORDER BY executed_at DESC;

Die PostgreSQL-Erweiterung pg_stat_statements kann ergänzend verwendet werden, um vollständige DDL-Befehle (inkl. Spaltennamen) zu erfassen, die über Event Trigger nicht verfügbar sind.

SELECT query, calls, userid, dbid, queryid, total_time
FROM pg_stat_statements
WHERE query LIKE 'ALTER TABLE%'
AND query \~\* 'ADD COLUMN|DROP COLUMN|RENAME COLUMN'
ORDER BY total_time DESC;

oder eben selektiv mit lesbarem User und Datenbanknamen:

SELECT a.rolname,b.datname,c.query
FROM public.pg_stat_statements c, pg_roles a, pg_database b
WHERE a.oid = c.userid AND b.oid = c.dbid
AND c.query LIKE 'ALTER%';

Hinweis:

* Die Erweiterung speichert normierte SQL-Befehle * Die Statistik wird nicht sofort aktualisiert * Für produktive Systeme mit hoher Last kann die Kombination mit Event Triggern zu Performance-Einbußen führen

* Die Spalte ddl wurde entfernt, da der Typ pg_ddl_command nicht direkt gespeichert werden kann. * Spaltenänderungen (z. B. ADD COLUMN) enthalten keine Spaltennamen über Event Trigger. * Die Lösung ist leichtgewichtig und für produktive Systeme geeignet. * DDL-Audit vorübergehend aus: PostgreSQL DDL Audit Logging, Abschnitt Wartung: Trigger aktivieren/deaktivieren (ALTER EVENT TRIGGER … DISABLE / ENABLE). * Event Trigger und Tabellen-Trigger sind verschiedene Mechanismen — siehe folgender Unterabschnitt.

Zum vorübergehenden Abschalten dieses DDL-Audits werden nur Event Trigger mit ALTER EVENT TRIGGER … gesteuert. ALTER TABLE … DISABLE TRIGGER … betrifft Tabellen-Trigger auf einer konkreten Tabelle und hat mit diesem DDL-Protokoll nichts zu tun.

* Filterung nach Objektarten oder Benutzer * Integration mit Git zur Versionskontrolle * Visualisierung der Änderungen über Dashboards * Benachrichtigung bei kritischen Änderungen (z. B. DROP TABLE)

Für mich ist das Standard Vorgehen meist unbrauchbar, da pg_stat_statements wie auch pgaudit nicht das Objekt selbst aufzeichnet. Ich schreibe meine eigene Triggerfunction — dokumentiert in PostgreSQL DDL Audit Logging.

PostgreSQL, DDL, Audit, Event Trigger, pg_stat_statements, Dashboard