Zugriffs-Audit und Logging von Benutzeraktionen

Stell dir vor, deine Datenbank ist so ein Club mit richtig strengen Mitgliedsregeln. Rein dürfen nur die eigenen Leute, aber wir wollen wissen, wer gekommen ist, wann, wie lange er da war und was er gemacht hat. Genau dafür braucht man Audit in PostgreSQL. Damit kannst du:

1. Benutzeraktionen verfolgen. Zum Beispiel, wer zu welcher Zeit eine wichtige Abfrage ausgeführt hat.

2. Verdächtige Aktivitäten erkennen. Das kann jemand sein, der versucht, Daten zu lesen, auf die er keinen Zugriff haben sollte.

3. Gesetzliche Anforderungen und Standards einhalten. In vielen Branchen (zum Beispiel Finanzen oder Medizin) muss man ein detailliertes Audit der Benutzeraktionen führen.

4. Das System von innen verstehen. Logging hilft dabei herauszufinden, welche Abfragen am häufigsten laufen, wo es Engpässe gibt und wie man die Performance optimieren kann.

Logging-Parameter konfigurieren

PostgreSQL lässt dich das Audit von Aktionen mit Konfigurationsparametern wie log_statement und log_connections einstellen. Lass uns die mal genauer anschauen.

1. log_statement: Was wird geloggt?

Der Parameter log_statement legt fest, welche SQL-Queries ins Log geschrieben werden. Das ist super praktisch, um zu verstehen, was im System abgeht.

Mögliche Werte für log_statement:

• none — nichts loggen (gute Wahl, wenn du gern am Limit lebst).
• ddl — nur Befehle loggen, die die Struktur der Datenbank ändern (zum Beispiel CREATE TABLE).
• mod — Befehle loggen, die Daten verändern (zum Beispiel INSERT, UPDATE, DELETE).
• all — einfach alles loggen.

Beispiel für die Einstellung: Um diesen Parameter zu ändern, musst du die Datei postgresql.conf anpassen:

# Wir loggen alle SQL-Queries
log_statement = 'all'

Danach speicherst du die Änderungen und startest den PostgreSQL-Server neu:

pg_ctl reload

Jetzt schreibt dein Club jede Aktion ins Log, vom Drink-Bestellen bis zu den Dance-Moves.

2. log_connections: Wer kommt in den Club?

Der Parameter log_connections sorgt dafür, dass jede neue Verbindung zur Datenbank ins Log geschrieben wird. Außerdem gibt es den verwandten Parameter log_disconnections, der das Schließen der Verbindung festhält.

Beispiel für die Einstellung: Wieder die Datei postgresql.conf anpassen:

# Verbindungen loggen
log_connections = on

# Verbindungsabbrüche loggen
log_disconnections = on

Was bringt uns das? Zum Beispiel kannst du im Log sehen, dass dein Manager zwei Stunden lang versucht hat, sich mit dem falschen Passwort einzuloggen. Ja, um 3 Uhr nachts.

Log-Analyse: Was kann man finden?

Nachdem du die Parameter log_statement und log_connections eingestellt hast, fängt PostgreSQL an, Logs zu schreiben. So könnte ein Log-File aussehen, wenn log_statement = 'mod' aktiviert ist:

2023-11-01 12:45:01 UTC [12345] LOG:  connection authorized: user=admin database=university
2023-11-01 12:46:15 UTC [12345] STATEMENT:  INSERT INTO students (name, age) VALUES ('Alice', 22);
2023-11-01 12:47:30 UTC [12345] STATEMENT:  UPDATE students SET age = 23 WHERE name = 'Alice';
2023-11-01 12:48:45 UTC [12345] LOG:  disconnection: session time: 2:45 connection: 1/5

Interessante Punkte:

1. Wer hat sich verbunden: user=admin database=university — das ist unser Boss.
2. Was wurde gemacht: Eine Zeile mit dem Namen Alice eingefügt und ihr Alter aktualisiert.
3. Wann ist er gegangen: 12:48:45, nach zweieinhalb Minuten Aktivität.

Praktische Beispiele: Wie nutzt man Audit in der Praxis?

Schauen wir uns ein paar Szenarien an, wo Audit und Logging nützlich sein können.

Szenario 1: Datenänderungen verfolgen

Du vermutest, dass jemand Einträge in der Tabelle students ändert. So kannst du das Audit einrichten:

1. Setze log_statement = 'mod', um alle modifizierenden Queries zu loggen.
2. Analysiere die Logs:

cat /var/log/postgresql/postgresql.log | grep "UPDATE students"

Jetzt kannst du sehen, wer wann welche Änderungen gemacht hat.

Szenario 2: Verdächtige Verbindungen erkennen

Wenn im Log häufig Verbindungen von verschiedenen IP-Adressen auftauchen, ist das vielleicht verdächtig. Für die Analyse nutze:

1. Verbindungs-Logs (log_connections).
2. Filter nach IP-Adressen:

cat /var/log/postgresql/postgresql.log | grep "connection authorized"

Szenario 3: Analyse der Query-Performance

Willst du wissen, warum dein Server langsam ist? Ein einfacher Weg: Schalte log_statement = 'all' für eine kurze Zeit (zum Beispiel eine Stunde) ein, sammle die Logs und schau, wo der Server am meisten Zeit verbringt.

Best Practices

Logge nicht alles. Stell log_statement so ein, dass nur wichtige Aktionen geloggt werden – DDL und Datenänderungen.

Automatisiere die Log-Analyse. Nutze Skripte oder Tools für das regelmäßige Lesen und Auswerten der Logs, wie grep, awk oder auch den ELK Stack (Elasticsearch, Logstash, Kibana).

Verwalte die Log-Größe. Richte Log-Rotation über PostgreSQL-Parameter oder Betriebssystem-Tools ein (zum Beispiel logrotate unter Linux).

Ich hoffe, du fühlst dich jetzt wie der Sheriff deiner Datenbank. Logging und Audit sind nicht nur Schutz, sondern auch ein super Weg, besser zu verstehen, was in deinem System abgeht. Wir werden sehen, wie dieses Wissen in echten Projekten eingesetzt wird!