Von Matthias Jung auf Mittwoch, 25. Mai 2022
Kategorie: PostgreSQL

„Hör gut zu!“. PostgreSQL Auditing mit PGAudit

An vielen Stellen müssen Zugriffe auf Daten bzw. Datenbanken auditiert werden. Die Open Source Datenbank PostgreSQL bietet für diesen Zweck die Erweiterung (Extension) PGAudit. Sie ermöglicht es Session- und / oder Objekt-basiert solche Informationen zu erheben. Im Folgenden möchten wir Ihnen die Installation und Funktionsweise dieser Erweiterung kurz vorstellen.

„Achtung Aufnahme!"

Bevor mit der Erhebung von Auditdaten begonnen werden kann, muss die Erweiterung installiert und konfiguriert werden. Auf einem Ubuntu-Server haben wir das aktuelle PostgreSQL-Repository (Version 14) eingebunden und den Cluster bereits installiert und gestartet. Lediglich die PGAudit-Erweiterung ist noch nicht vorhanden. Die Nachinstallation der Extension ist an sich jedoch recht einfach:

Nachdem die Extension nun verfügbar ist, muss sie in den Cluster eingebunden und dieser neu gestartet werden. Dazu muss PGAudit als zu ladende Bibliothek über die zentrale Konfigurationsdatei „postgresql.conf" spezifiziert werden. 

Im Vorfeld wurde bereits eine Test-Datenbank mit „pgbench" erstellt, anhand derer die folgenden Schritte verdeutlicht werden sollen: 

Zusätzlich wurden im Nachgang zwei User angelegt, die nur lesende Rechte (SampleRO) und sowie schreibende Rechte (SampleRW) auf diese vier Tabellen haben. 

„Aufnahme läuft?!"

Um das Auditing innerhalb unserer Datenbank nutzen zu können, muss die Extension dort erzeugt (besser eingebunden) werden: 

Im ersten Schritt aktivieren wir das Auditing Session-spezifisch und so zwar so, dass alle schreibenden Aktionen protokolliert werden sollen. Der Wert „write" beinhaltet, die folgenden SQL-Kommandos:

Eine vollständige Übersicht dieses und anderer PGAudit Parameter ist in der entsprechenden Dokumentation zu finden:
https://github.com/pgaudit/pgaudit/blob/master/README.md

Dieses Kommando (INSERT) führt konsequenterweise zu einem Eintrag im Logfile (Parameter: log_filename) von PostgreSQL 

Der Log-Eintrag setzt sich dabei (in der Reihenfolge) aus den folgenden Komponenten zusammen:

  1. AUDIT_TYPE "SESSION" oder "OBJECT"
  2. STATEMENT_ID Jedes Statement verfügt über eine eindeutige ID
  3. SUBSTATEMENT_ID ID des verschalteten Kommandos (z.B. bei Funktionen, Prozeduren)
  4. CLASS z.B. „WRITE" (wie in unserem Beispiel)
  5. COMMAND z.B. „INSERT" als Element der Klasse (CLASS) „WRITE"
  6. OBJECT_TYPE z.B. „TABLE" oder "VIEW" (AUDIT_TYPE = OBJECT)
  7. OBJECT_NAME Der Name des Objektes (AUDIT_TYPE = OBJECT)
  8. STATEMENT Das ausgeführte Kommando
  9. PARAMETER Übergebenes Parameter (z.B. bei Prozeduren) können optional als CSV-Liste mitprotokolliert werden

Auffällig ist, dass der verursachende User (Session) nicht augenfällig im Protokoll erscheint. Dies kann über den Parameter „log_line_prefix" optimiert werden. Wir setzten ihn in der Konfiguration wie folgt:

log_line_prefix = '%m %u [%p] '

Neben dem Zeitstempel (%m) wird der Benutzername (%u) und seine Prozess-ID (%p) nun in das Logfile geschrieben. Der obenstehende Log-Eintrag verändert sich wie folgt:

Um noch mehr Übersicht zu haben, möchten wir die Audit-Informationen in eine separate Datei schreiben. Dazu installieren (1) wir eine weitere Extension „pgauditlogtofile", laden diese über die „postgresql.conf" (2) und konfigurieren (3) sie in der Datenbank.

Zuhören ist eine Kunst! 

Okay, generell sind wir in der Lage zu auditieren und ein paar Konfigurationen vorzunehmen (z.B. über pgaudit.log). In der Praxis wollen wir aber sehr zielgerichtet „hinhören". In unserem Datenmodell gibt es ggfs. ein paar Tabellen mit vertraulichen Daten, die in wenigen Spalten verwahrt werden. Und genau hier lohnt es sich für uns, ganz genau zu protokollieren. Aus diesem Grund schalten wir das Session-spezifische Auditing ab und konzentrieren uns auf bestimmte Objekte.

Nachdem wir das Auditing für die Sessions generell deaktiviert haben, richten wir nun die Objekt-basierte Auditierung ein. Dazu definieren wir uns eine Rolle, welche unsere Audit-Regeln sammeln, bzw. umsetzen soll. Diese Rolle wird dann als Regelwert der Erweiterung PGAudit übergeben: 

Im ersten Schritt möchten wir nur eine einzige Spalte („abalance") in der Tabelle „pgbench_accounts" auf Zugriffe „SELECTs" überwachen. Dazu formulieren wir unsere Audit-Regel als „GRANT"-Anweisung.

sampledb=# grant select (abalance) on public.pgbench_accounts to auditor;

Nun führen wir mit einem unserer User (SampleRO) ein paar lesende Zugriffe auf die überwachte Tabelle durch:

Das Ergebnis ist erwartungsgemäß. Statement „1" und „3" haben auf die Spalte „abalance" zugegriffen. Exakt diesen beiden Statements sind daher auch im Audit-Protokoll zu finden. 

Der User "SampleRW" verfügt über Schreibrechte auf alle Tabellen. Wir starten daher einen weiteren Test mit einem „INSERT". 

Im Logfile ist kein Eintrag zu finden. Das macht Sinn, da es ja noch keine Regel zur Überwachung von „INSERTs" auf dieser Tabelle gibt. Legen wird diese nun an und wiederholen den Test, ändert sich natürlich das Ergebnis. 

Fazit: Nun höre und lerne.

 Die Einrichtung eines Auditings ist in PostgreSQL kein Hexenwerk. Die Erweiterungen lassen sich meist schnell und problemlos konfigurieren. Lediglich die Konfiguration ist im ersten Augenblick nicht immer gleich verständlich. Die Parameter haben nicht immer die treffendsten Namen und können an der einen oder anderen Stelle leicht zur Verwirrung beitragen. Hier hilft es, sich einfach ein paar Testszenarien auszudenken und auszuprobieren. So kommt schnell Licht ins Dunkel.

Sie haben Fragen rund um den Betrieb von PostgreSQL? Dann sprechen Sie uns an. 

Seminarempfehlung

Kommentare hinterlassen