PostgreSQL Datenaktivitätshistorie: Best Practices für Überwachung und Sicherheit
PostgreSQL, eine weit verbreitete Open-Source-Relationale-Datenbank, 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 führen Sie auch durch das Einrichten der SQL-Abfrageüberwachung für eine Beispieltabelle und stellen DataSunrise vor, eine robustes Sicherheitssuite, die die Datenaktivitätsüberwachung zentralisiert und vereinfacht.
Warum die Überwachung der Datenaktivitätshistorie wichtig ist
Die Überwachung der Datenaktivitätshistorie ist aus mehreren Gründen unerlässlich. Sie stellt sicher, dass Sie verdächtige Aktivitäten erkennen, Compliance-Anforderungen erfüllen und die Datenbankleistung optimieren können. Durch die genaue Überwachung, wer auf Ihre Daten zugegriffen hat, wann und welche Änderungen vorgenommen wurden, können Sie Ihre Datenbank vor unbefugtem Zugriff und möglichen Datenschutzverletzungen schützen. Darüber hinaus hilft das Verfolgen der Datenaktivität, Leistungsengpässe zu identifizieren und die Abfrageausführung zu optimieren, was zu einer effizienteren und sichereren Datenbankumgebung führt.
Ansätze zur Verfolgung der Datenaktivitätshistorie in PostgreSQL
PostgreSQL bietet verschiedene integrierte 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 Funktionen, und das Verständnis ihrer Unterschiede hilft Ihnen, den richtigen Ansatz für Ihre Bedürfnisse zu wählen.
pgaudit: Umfassende SQL-Prüfung
pgaudit ist eine PostgreSQL-Erweiterung, die entwickelt wurde, um eine detaillierte Prüfung von SQL-Anweisungen bereitzustellen. Es protokolliert alle SQL-Aktivitäten, einschließlich DML (Data Manipulation Language) und DDL (Data Definition Language) Anweisungen. Dieses Werkzeug 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 zur Überwachung von SQL-Abfragen
Die folgende Einrichtung ist für den Ubuntu 24.04 Server und PostgreSQL Version 16, die aus den Ubuntu Repositories installiert wurde, wie unten gezeigt.
Um pgaudit zur Überwachung von SQL-Abfragen in der mock_data-Tabelle einzurichten, gehen Sie wie folgt vor:
Installieren Sie die pgaudit-Erweiterung:
# sudo apt-get update # sudo apt-get install postgresql-16-pgaudit
Legen Sie die Erweiterung in postgresql.conf fest:
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 dem psql-Befehlszeilentool:
CREATE EXTENSION pgaudit;
Konfigurieren Sie die pgaudit-Einstellungen (ebenfalls in psql):
ALTER SYSTEM SET pgaudit.log = 'write'; Reload the PostgreSQL configuration: SELECT pg_reload_conf();
Führen Sie eine SQL-Abfrage auf der mock_data-Tabelle 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 anzeigen zu lassen:
cat /var/log/postgresql/postgresql-16-main.log | tail -n 20
Alle Zeilen mit der Bezeichnung ‘AUDIT’ stammen von der pgaudit-Erweiterung.
Kumulative Statistiken: Datenbankweite Metriken
Kumulative Statistiken in PostgreSQL bieten aggregierte Daten zur Leistung und Nutzung der Datenbank. Diese Statistiken umfassen Informationen über die Anzahl der ausgeführten Abfragen, gelesene Tupel und abgerufene Blöcke, neben anderen Metriken. Während kumulative Statistiken einen umfassenden Ü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 wertvolle Einblicke in die Datenbankleistung, jedoch nicht die detaillierten Prüfungsmöglichkeiten, die zum Verfolgen bestimmter Datenaktivitäten erforderlich sind.
pg_stat_statements: Überwachung der Abfrageleistung
pg_stat_statements ist eine weitere PostgreSQL-Erweiterung, 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 Durchschnittszeit pro Aufruf. Dieses Werkzeug ist besonders nützlich zur Identifizierung langsamer Abfragen und zur Optimierung der Datenbankleistung.
Installation und Verwendung 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;
Fragen Sie die Ansicht pg_stat_statements ab:
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 bietet eine Liste der zeitaufwändigsten Abfragen und hilft Ihnen, 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 die Prüfung und Protokollierung einzelner SQL-Anweisungen für Sicherheits- und Compliance-Zwecke.
Kumulative Statistiken: Bietet aggregierte Leistungsmetriken für die gesamte Datenbank, die nützlich für einen umfassenden Überblick sind.
pg_stat_statements: Verfolgt die Ausführungsleistung von Abfragen und hilft bei der Identifizierung und Optimierung langsamer Abfragen.
Durch die Kombination dieser Werkzeuge können Sie eine umfassende Überwachung und Prüfung Ihrer PostgreSQL-Datenbank erreichen und somit sowohl Sicherheit als auch Leistung sicherstellen.
Um zu überprüfen, ob die Erweiterungen in der Datenbank vorhanden sind, verwenden Sie die folgenden Befehle:
SELECT * FROM pg_extension;
SHOW shared_preload_libraries;
Die Ausgabe könnte wie folgt aussehen:
Nutzung von DataSunrise für zentralisierte Datenaktivitätsüberwachung
Während die in PostgreSQL integrierten Werkzeuge robuste Überwachungsfunktionen bieten, kann das Verwalten der Datenaktivitätshistorie über mehrere Datenbanken hinweg eine Herausforderung darstellen. Hier kommt DataSunrise ins Spiel. DataSunrise ist eine leistungsstarke Sicherheits-Suite, die zentrale Kontrolle über alle Aufgaben zur Überwachung und Prüfung der Datenaktivität bietet.
Erstellen einer DataSunrise-Instanz für PostgreSQL
Vorausgesetzt, DataSunrise ist bereits installiert, folgen Sie diesen Schritten, 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 Datenbank-Host, Port und Anmeldedaten.
- Navigieren Sie zu ‘Audit – Regeln’. Hier erstellen und spezifizieren Sie die Tabellen, Spalten und Aktionen, die Sie überwachen möchten, wie SELECT, INSERT, UPDATE und DELETE.
- Speichern Sie die Regel, und sie wird automatisch mit der Überwachung 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 Transaktionsverläufen und Sitzungsverläufen. Sie können diese Protokolle im Abschnitt ‘Audit – Transaktionsverläufe’ und ‘Audit – Sitzungsverläufe’ der Audit-Schnittstelle einsehen.
DataSunrise wird nun alle Datenaktivitätshistorien für die angegebene PostgreSQL-Instanz erfassen und speichern, wodurch eine zentrale und einheitliche Ansicht aller auf der Datenbank durchgeführten Aktionen bereitgestellt wird.
Vorteile der Nutzung von DataSunrise
DataSunrise bietet mehrere Vorteile bei der Verwaltung der PostgreSQL-Datenaktivitätshistorie:
- Zentralisierte Kontrolle: Verwalten und überwachen Sie alle Ihre Datenbanken von einer einzigen Schnittstelle aus, was den Verwaltungsaufwand reduziert.
- Umfassende Prüfung: Verfolgen Sie alle SQL-Aktivitäten über mehrere Datenbanken hinweg und gewährleisten Sie die Einhaltung gesetzlicher Anforderungen.
- Flexible Konfiguration: Passen Sie Prüfungsregeln an Ihre spezifischen Bedürfnisse an, einschließlich der Filterung nach Benutzer, Aktion oder Tabelle.
- Echtzeit-Benachrichtigungen: Erhalten Sie Benachrichtigungen bei verdächtigen Aktivitäten, sodass Sie sofort handeln können.
Mit DataSunrise erhalten Sie ein leistungsstarkes Werkzeug zur Sicherung Ihrer PostgreSQL-Datenbanken und zur effizienten Überwachung und Aufzeichnung aller Datenaktivitäten.
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 fortgeschrittene und zentralisierte Überwachung bietet DataSunrise eine umfassende Lösung, die die Datenaktivitätsverfolgung über mehrere PostgreSQL-Instanzen hinweg vereinfacht. Indem Sie diese Werkzeuge kombinieren, können Sie eine sichere und optimierte Datenbankumgebung erreichen.
DataSunrise bietet flexible, benutzerfreundliche Werkzeuge für die Datenbanksicherheit, einschließlich Prüfung, Maskierung und anderer fortgeschrittener Funktionen. Besuchen Sie unsere DataSunrise-Website um einen Online-Demo-Termin zu vereinbaren und herauszufinden, wie unsere Lösungen Ihnen helfen können, Ihre PostgreSQL-Datenaktivitätshistorie effektiver zu verwalten.
