Startseite
Wissenszentrum
PostgreSQL Datenaktivitätshistorie

PostgreSQL Datenaktivitätshistorie

PostgreSQL, eine weit verbreitete Open-Source-Relationdatenbank, bietet mehrere Werkzeuge und Ansätze zur Überwachung und Verfolgung der Datenaktivitätshistorie. Dieser Artikel wird die Grundlagen der PostgreSQL Datenaktivitätshistorie erkunden und wichtige Methoden wie pgaudit, kumulative Statistiken und pg_stat_statements behandeln.

Wir werden Sie auch durch die Einrichtung von SQL-Abfrageauditing für eine Beispieltabelle führen und DataSunrise vorstellen, eine robuste Sicherheitslösung, die die Überwachung der Datenaktivität zentralisiert und vereinfacht.

Warum die Überwachung der Datenaktivitätshistorie wichtig ist

Die Überwachung der Datenaktivitätshistorie ist aus mehreren Gründen wichtig. Sie stellt sicher, dass verdächtige Aktivitäten erkannt werden können, hilft bei der Erfüllung von Compliance-Anforderungen und optimiert die Datenbankleistung. Durch die genaue Beobachtung, wer auf Ihre Daten zugegriffen hat, wann und welche Änderungen vorgenommen wurden, können Sie Ihre Datenbank vor unbefugtem Zugriff und potenziellen Datenverletzungen schützen. Darüber hinaus hilft die Verfolgung der Datenaktivität, Leistungsengpässe zu identifizieren und die Ausführung von Abfragen zu optimieren, was zu einer effizienteren und sichereren Datenbankumgebung führt.

Ansätze zur Verfolgung von Datenaktivitätshistorie in PostgreSQL

PostgreSQL bietet verschiedene eingebaute Werkzeuge und Erweiterungen zur Verfolgung und Überwachung der Datenaktivität. Drei der am häufigsten verwendeten Methoden sind pgaudit, kumulative Statistiken und pg_stat_statements. Jedes dieser Werkzeuge bietet einzigartige Funktionalitäten, und das Verständnis ihrer Unterschiede hilft Ihnen, den richtigen Ansatz für Ihre Bedürfnisse zu wählen.

pgaudit: Umfassendes SQL-Auditing

pgaudit ist eine PostgreSQL-Erweiterung, die entwickelt wurde, um detaillierte Audits von SQL-Anweisungen bereitzustellen. Es protokolliert alle SQL-Aktivitäten, einschließlich DML- (Data Manipulation Language) und DDL- (Data Definition Language) Anweisungen. Dieses Tool ist besonders nützlich für Compliance-Zwecke, da es Administratoren ermöglicht, eine detaillierte Aufzeichnung der Datenbankaktivitäten zu führen.

Einrichtung von pgaudit zum Auditing von SQL-Abfragen

Die folgende Einrichtung gilt für den Ubuntu 24.04 Server und PostgreSQL Version 16, die aus den Ubuntu-Repositories installiert wurde, wie unten gezeigt.

Um pgaudit für das Auditing von SQL-Abfragen auf der Tabelle mock_data einzurichten, befolgen Sie diese Schritte:

Installieren Sie die pgaudit-Erweiterung:

# sudo apt-get update
# sudo apt-get install postgresql-16-pgaudit

Setzen Sie die Erweiterung in postgresql.conf:

nano /etc/postgresql/16/main/postgresql.conf

indem Sie die Zeile bearbeiten: shared_preload_libraries = ‘pgaudit’.

Und starten Sie den PostgreSQL Datenbank-Daemon neu.

sudo systemctl restart postgresql

Aktivieren Sie die Erweiterung in der Datenbank mit der psql-Befehlszeilenschnittstelle:

CREATE EXTENSION pgaudit;

Konfigurieren Sie die pgaudit-Einstellungen (ebenfalls in psql):

ALTER SYSTEM SET pgaudit.log = 'write';
Laden Sie die PostgreSQL-Konfiguration neu:
SELECT pg_reload_conf();

Führen Sie eine SQL-Abfrage auf der Tabelle mock_data aus:

SELECT * FROM mock_data WHERE id = 1;
INSERT INTO mock_data (first_name, last_name, email, ip_address) VALUES ('John', 'Doe', '[email protected]', '192.168.1.1');

pgaudit wird diese Abfragen mit ihren jeweiligen Zeitstempeln protokollieren, sodass Sie nachverfolgen können, wer die Aktionen durchgeführt hat und wann. Verwenden Sie den folgenden Befehl, um das Ende der Protokolldatei anzuzeigen:

cat /var/log/postgresql/postgresql-16-main.log | tail -n 20

Alle Zeilen, die mit “AUDIT” gekennzeichnet sind, stammen aus der pgaudit-Erweiterung.

Kumulative Statistiken: Datenbankweite Metriken

Kumulative Statistiken in PostgreSQL bieten aggregierte Daten über die Leistung und Nutzung der Datenbank. Diese Statistiken umfassen Informationen über die Anzahl der ausgeführten Abfragen, gelesene Tupel und abgerufene Blöcke, unter anderen Metriken. Während kumulative Statistiken einen breiten Überblick über die Datenbankaktivität bieten, fehlt ihnen die Granularität von pgaudit, da sie keine einzelnen SQL-Anweisungen oder deren Zeitstempel verfolgen.

Um auf kumulative Statistiken zuzugreifen, können Sie die Ansicht pg_stat_database abfragen:

SELECT datname, numbackends, xact_commit, xact_rollback
FROM pg_stat_database;

Um auf die aktiven laufenden Abfragen zuzugreifen:

SELECT pid, usename, datname, state, query, query_start
FROM pg_stat_activity
WHERE state = 'active';

Diese Ansicht bietet ebenfalls wertvolle Einblicke in die Datenbankleistung, bietet aber nicht die detaillierten Audit-Fähigkeiten, die für die Verfolgung spezifischer Datenaktivitäten notwendig sind.

pg_stat_statements: Abfrageleistungsüberwachung

pg_stat_statements ist eine weitere PostgreSQL-Erweiterung, die die Ausführungsstatistiken aller SQL-Anweisungen verfolgt. Im Gegensatz zu kumulativen Statistiken konzentriert sich pg_stat_statements auf die Abfrageleistung und bietet Details wie Ausführungszeit, Anzahl der Aufrufe und durchschnittliche Zeit pro Aufruf. Dieses Tool ist besonders nützlich, um langsame Abfragen zu identifizieren und die Datenbankleistung zu optimieren.

Installation und Nutzung von pg_stat_statements

Installieren Sie die pg_stat_statements-Erweiterung:

sudo apt-get update
sudo apt-get install postgresql-contrib

Konfigurieren Sie die Erweiterung:

sudo nano /etc/postgresql/<version>/main/postgresql.conf

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 1000
track_activity_query_size = 2048

Starten Sie PostgreSQL neu:

sudo systemctl restart postgresql

Melden Sie sich in psql an und erstellen Sie eine Erweiterung:

CREATE EXTENSION pg_stat_statements;

Abfragen der Ansicht pg_stat_statements:

SELECT query, calls, total_exec_time, rows, 100.0 * shared_blks_hit /
    nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
WHERE query ILIKE '%mock_data%'
ORDER BY total_exec_time DESC
LIMIT 5;

Dies wird eine Liste der zeitaufwändigsten Abfragen liefern und Ihnen helfen, Leistungsengpässe zu identifizieren.

Sie können die Statistiken zurücksetzen:

SELECT pg_stat_statements_reset();

Unterschiede zwischen pgaudit, kumulativen Statistiken und pg_stat_statements

Jedes dieser Werkzeuge dient einem anderen Zweck bei der Überwachung der PostgreSQL Datenaktivitätshistorie:

pgaudit: Konzentriert sich auf das Auditing und die Protokollierung einzelner SQL-Anweisungen zu Sicherheits- und Compliance-Zwecken.

Kumulative Statistiken: Bietet aggregierte Leistungskennzahlen für die gesamte Datenbank und ist nützlich für einen Überblick auf hoher Ebene.

pg_stat_statements: Verfolgt die Ausführungsleistung von Abfragen und hilft dabei, langsame Abfragen zu identifizieren und zu optimieren.

Durch die Kombination dieser Werkzeuge können Sie eine umfassende Überwachung und Auditing Ihrer PostgreSQL-Datenbank erreichen und sowohl Sicherheit als auch Leistung gewährleisten.

Um zu überprüfen, ob Erweiterungen in der Datenbank vorhanden sind, verwenden Sie die folgenden Befehle:

SELECT * FROM pg_extension;

SHOW shared_preload_libraries;

Die Ausgabe kann wie folgt aussehen:

Nutzung von DataSunrise für zentralisiertes Datenaktivitätsmonitoring

Während die eingebauten Werkzeuge von PostgreSQL robuste Überwachungsfunktionen bieten, kann die Verwaltung der Datenaktivitätshistorie über mehrere Datenbanken hinweg eine Herausforderung darstellen. Hier kommt DataSunrise ins Spiel. DataSunrise ist eine leistungsstarke Sicherheitslösung, die die zentrale Kontrolle über alle Überwachungs- und Auditing-Aufgaben der Datenaktivität ermöglicht.

Erstellen einer DataSunrise-Instanz für PostgreSQL

Angenommen, DataSunrise ist bereits installiert, befolgen Sie diese Schritte, um eine Instanz zu erstellen und die Datenaktivitätshistorie zu überwachen:

Melden Sie sich an der DataSunrise-Konsole an.
Navigieren Sie zum Abschnitt ‘Konfiguration – Datenbanken’ und erstellen Sie eine neue PostgreSQL-Instanz.
Konfigurieren Sie die Verbindungseinstellungen, einschließlich Datenbankhost, Port und Anmeldedaten.
Navigieren Sie zu Audit – Regeln. Erstellen Sie hier Regeln und spezifizieren Sie die Tabellen, Spalten und Aktionen, die Sie auditieren möchten, wie SELECT, INSERT, UPDATE und DELETE.

Speichern Sie die Regel, und sie wird automatisch zu überwachen beginnen. Wenn Sie über den Proxy der Instanz auf die Datenbank zugreifen, fängt DataSunrise die Abfragen ab und protokolliert alle angegebenen Daten in den Transaktionspfaden und Sitzungsprotokollen. Sie können diese Protokolle im Abschnitt ‘Audit – Transaktionspfade’ und ‘Audit – Sitzungsprotokolle’ der Audit-Benutzeroberfläche einsehen.

DataSunrise erfasst nun alle Datenaktivitätshistorien für die angegebene PostgreSQL-Instanz und bietet eine zentralisierte und einheitliche Ansicht aller Aktionen, die auf der Datenbank durchgeführt werden.

Vorteile der Nutzung von DataSunrise

DataSunrise bietet mehrere Vorteile für die Verwaltung der Datenaktivitätshistorie von PostgreSQL:

Zentralisierte Kontrolle: Verwalten und überwachen Sie alle Ihre Datenbanken von einer einzigen Oberfläche aus, wodurch der Verwaltungsaufwand reduziert wird.
Umfassendes Auditing: Verfolgen Sie alle SQL-Aktivitäten über mehrere Datenbanken hinweg, um die Einhaltung von Vorschriften zu gewährleisten.
Flexible Konfiguration: Passen Sie Auditing-Regeln basierend auf Ihren spezifischen Bedürfnissen an, einschließlich Filterung nach Benutzer, Aktion oder Tabelle.
Echtzeit-Benachrichtigungen: Erhalten Sie Benachrichtigungen über verdächtige Aktivitäten, sodass Sie sofort Maßnahmen ergreifen können.

Mit DataSunrise erhalten Sie ein leistungsstarkes Werkzeug zur Sicherung Ihrer PostgreSQL-Datenbanken und zur Gewährleistung, dass alle Datenaktivitäten effizient verfolgt und protokolliert werden.

Fazit

Die Überwachung der Datenaktivitätshistorie in PostgreSQL ist entscheidend für die Aufrechterhaltung von Sicherheit, Compliance und Leistung. Werkzeuge wie pgaudit, kumulative Statistiken und pg_stat_statements bieten wertvolle Einblicke in die Aktivitäten Ihrer Datenbank. Für eine fortschrittlichere und zentralisierte Überwachung bietet DataSunrise eine umfassende Lösung, die die Verfolgung der Datenaktivität über mehrere PostgreSQL-Instanzen hinweg vereinfacht. Durch die Kombination dieser Werkzeuge können Sie eine sichere und gut optimierte Datenbankumgebung erreichen.

DataSunrise bietet flexible, benutzerfreundliche Werkzeuge für die Datenbanksicherheit, einschließlich Auditing, Maskierung und andere fortschrittliche Funktionen. Besuchen Sie unsere DataSunrise-Website, um eine Online-Demo zu vereinbaren und zu erfahren, wie unsere Lösungen Ihnen helfen können, die Datenaktivitätshistorie Ihrer PostgreSQL-Datenbank effektiver zu verwalten.