„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:

root@pg14:/# apt install postgresql-14-pgaudit
Reading package lists... Done
Building dependency tree
…
0 upgraded, 1 newly installed, 0 to remove and 0 not upgraded.
 

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. 

postgres@pg14:~/14/pgdata$ cat postgresql.conf | grep shared_preload
shared_preload_libraries = 'pgaudit'	# (change requires restart)
 

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

postgres@pg14:~/14/pgdata$ pgbench -i sampledb
dropping old tables...
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
100000 of 100000 tuples (100%) done (elapsed 0.21 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 0.48 s (drop tables 0.01 s, create tables 0.02 s, client-side generate 0.30 s, vacuum 0.08 s, primary keys 0.08 s).
 

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: 

postgres@pg14:~/14/pgdata$ psql -d sampledb
psql (14.2 (Ubuntu 14.2-1.pgdg20.04+1))
Type "help" for help.

sampledb=# create extension pgaudit;
CREATE EXTENSION

sampledb=# select extname, extversion from pg_extension;
 extname | extversion
---------+------------
 plpgsql | 1.0
 pgaudit | 1.6.1
(2 rows)
 

• INSERT
• UPDATE
• DELETE
• TRUNCATE
• COPY

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

sampledb=# set pgaudit.log = 'write';
sampledb=# \c sampledb SampleRW
You are now connected to database "sampledb" as user "SampleRW".
sampledb=> insert into pgbench_accounts values ( 100001, 1, 0);
INSERT 0 1
 

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

postgres@pg14:~$ tail -f /var/lib/postgresql/logfile &
2022-02-25 09:40:11.447 CET [3004] LOG:  AUDIT: SESSION,2,1,WRITE,INSERT,,,"insert into pgbench_accounts values ( 100001, 1, 0);",<not logged>
 

2022-02-25 10:25:42.240 CET SampleRW [3310]LOG:  AUDIT: SESSION,1,1,WRITE,INSERT,,,"insert into pgbench_accounts values ( 100001, 1, 0);",<not logged> 

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.

#1
root@pg14:/pgaudit# apt install postgresql-14-pgauditlogtofile

#2
postgres@pg14:~/14/log$ cat /var/lib/postgresql/14/pgdata/postgresql.conf | grep preload_lib
shared_preload_libraries = 'pgaudit,pgauditlogtofile'	# (change requires restart)

#3 
You are now connected to database "sampledb" as user "postgres".
sampledb=# create extension pgauditlogtofile;

postgres=# select name, setting from pg_settings where name like 'pgaudit%';
            name            |          setting
----------------------------+----------------------------
 pgaudit.log                | write
 pgaudit.log_catalog        | on
 pgaudit.log_client         | off
 pgaudit.log_directory      | /var/lib/postgresql/14/log
 pgaudit.log_filename       | audit-%Y%m%d_%H%M.log
 pgaudit.log_level          | log
 pgaudit.log_parameter      | off
 pgaudit.log_relation       | off
 pgaudit.log_rotation_age   | 1440
 pgaudit.log_rows           | off
 pgaudit.log_statement      | on
 pgaudit.log_statement_once | off
 pgaudit.role               |
(13 rows)
 

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.

sampledb=# alter system set pgaudit.log = 'NONE';
ALTER SYSTEM
sampledb=# alter system set pgaudit.log_catalog = off;
ALTER SYSTEM
 

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: 

sampledb=# create role auditor;
CREATE ROLE
GRANT
sampledb=# set pgaudit.role='auditor';
SET
 

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:

sampledb=> select * from pgbench_accounts where aid <= 1 /* TEST 1 */;
 aid | bid | abalance |                                        filler
-----+-----+----------+--------------------------------------------------------------------------------------
   1 |   1 |        0 |
(1 row)

sampledb=> select aid, bid from pgbench_accounts where aid <= 1 /* TEST 2 */;
 aid | bid
-----+-----
   1 |   1
(1 row)

sampledb=> select abalance from pgbench_accounts where aid <= 1 /* TEST 3 */;
 abalance
----------
        0
(1 row)
 

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. 

2022-02-25 11:07:15.837 CET,SampleRW,sampledb,3523,[local],6218a9d3.dc3,6,SELECT,2022-02-25 11:05:07 CET,3/974,0,00000,OBJECT,6,1,READ,SELECT,TABLE,public.pgbench_accounts,select * from pgbench_accounts where aid <= 1 /* TEST 1 */;,<not logged>,,,,,,,,,psql

2022-02-25 11:07:39.660 CET,SampleRW,sampledb,3523,[local],6218a9d3.dc3,7,SELECT,2022-02-25 11:05:07 CET,3/976,0,00000,OBJECT,7,1,READ,SELECT,TABLE,public.pgbench_accounts,select abalance from pgbench_accounts where aid <= 1 /* TEST 3 */;,<not logged>,,,,,,,,,psql
 

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

sampledb=> insert into pgbench_accounts (aid, bid) values ( 100009 , 1) /* Test 4 */;
INSERT 0 1 

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. 

sampledb=# grant insert  on public.pgbench_accounts to auditor;
GRANT

sampledb=> insert into pgbench_accounts (aid, bid) values ( 100010 , 1) /* Test 5 */;
INSERT 0 1

postgres@pg14:~/14/log$ tail -f audit-20220225_0000.log
…
2022-02-25 11:25:01.651 CET,SampleRO,sampledb,3585,[local],6218ae79.e01,1,SELECT,2022-02-25 11:24:57 CET,3/987,0,00000,OBJECT,1,1,READ,SELECT,TABLE,public.pgbench_accounts,select abalance from pgbench_accounts where aid <= 1 /* TEST 3 */;,<not logged>,,,,,,,,,psql
2022-02-25 11:30:35.042 CET,SampleRW,sampledb,3597,[local],6218af44.e0d,1,INSERT,2022-02-25 11:28:20 CET,3/996,0,00000,OBJECT,1,1,WRITE,INSERT,TABLE,public.pgbench_accounts,"insert into pgbench_accounts (aid, bid) values ( 100010 , 1) /* Test 5 */;",<not logged>,,,,,,,,,psql
 

Fazit: Nun höre und lerne.

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

Seminarempfehlung