Audyt dostępu i logowanie działań użytkowników

Wyobraź sobie, że twoja baza danych to taki klub z bardzo surowymi zasadami członkostwa. Wchodzić mogą tylko swoi, ale chcemy wiedzieć, kto przyszedł, kiedy, ile czasu spędził i co tam robił. Właśnie do tego służy audyt w PostgreSQL. Dzięki niemu możesz:

1. Śledzić działania użytkowników. Na przykład, kto i o której godzinie wykonał ważne zapytanie.

2. Wykrywać podejrzaną aktywność. To może być ktoś, kto próbuje czytać dane, do których nie powinien mieć dostępu.

3. Spełniać wymagania prawa i standardów. W wielu branżach (np. finansowej czy medycznej) trzeba prowadzić szczegółowy audyt działań użytkowników.

4. Rozumieć system od środka. Logowanie pomaga ogarnąć, jakie zapytania są najczęściej wykonywane, gdzie mogą być wąskie gardła i jak zoptymalizować wydajność.

Konfiguracja parametrów logowania

PostgreSQL pozwala ustawić audyt działań za pomocą parametrów konfiguracyjnych, takich jak log_statement i log_connections. Zobaczmy je bliżej.

1. log_statement: co zapisujemy?

Parametr log_statement określa, które zapytania SQL będą zapisywane do logów. To bardzo przydatne, żeby wiedzieć, co się dzieje w systemie.

Możliwe wartości log_statement:

• none — nie zapisuj nic (dobry wybór, jeśli lubisz życie na krawędzi).
• ddl — zapisuj tylko polecenia zmieniające strukturę bazy (np. CREATE TABLE).
• mod — zapisuj polecenia, które zmieniają dane (np. INSERT, UPDATE, DELETE).
• all — zapisuj wszystko jak leci.

Przykład konfiguracji: żeby zmienić ten parametr, trzeba edytować plik postgresql.conf:

# Konfigurujemy logowanie wszystkich zapytań SQL
log_statement = 'all'

Po tym zapisz zmiany i zrestartuj serwer PostgreSQL:

pg_ctl reload

Teraz twój klub będzie zapisywał w dzienniku każde działanie, od zamawiania drinków po taneczne ruchy.

2. log_connections: kto wchodzi do klubu?

Parametr log_connections odpowiada za zapisywanie w logach informacji o każdym nowym połączeniu z bazą. Jest też powiązany parametr log_disconnections, który rejestruje moment zamknięcia połączenia.

Przykład konfiguracji: Znowu edytujemy plik postgresql.conf:

# Logujemy połączenia
log_connections = on

# Logujemy rozłączenia
log_disconnections = on

Co nam to daje? Na przykład możesz zobaczyć w logach, że twój menedżer przez dwie godziny próbował się połączyć do bazy na złym haśle. Tak, o 3 w nocy.

Analiza logów: co można znaleźć?

Po ustawieniu parametrów log_statement i log_connections PostgreSQL zacznie pisać logi. Oto przykład, jak może wyglądać plik loga przy włączonym log_statement = 'mod':

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

Ciekawe momenty:

1. Kto się połączył: user=admin database=university — to nasz szef.
2. Co robił: wstawiał wiersz z imieniem Alice i aktualizował jej wiek.
3. Kiedy wyszedł: 12:48:45, po dwóch i pół minuty aktywności.

Praktyczne przykłady: jak używać audytu w praktyce

Zobaczmy kilka scenariuszy, gdzie audyt i logowanie mogą się przydać.

Scenariusz 1: śledzenie zmian danych

Masz podejrzenie, że ktoś zmienia wpisy w tabeli students. Tak możesz ustawić audyt:

1. Ustaw log_statement = 'mod', żeby zapisywać wszystkie modyfikujące zapytania.
2. Analizuj logi:

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

Teraz zobaczysz, kto, kiedy i jakie zmiany zrobił.

Scenariusz 2: wykrywanie podejrzanych połączeń

Jeśli w logach pojawiają się częste połączenia z różnych adresów IP, coś może być nie tak. Do analizy użyj:

1. Logów połączeń (log_connections).
2. Filtrowania po adresach IP:

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

Scenariusz 3: analiza wydajności zapytań

Chcesz wiedzieć, czemu twój serwer zamula? Prosty sposób — włącz log_statement = 'all' na krótko (np. na godzinę), zbierz logi i zobacz, gdzie serwer spędza najwięcej czasu.

Najlepsze praktyki

Nie loguj wszystkiego jak leci. Ustaw log_statement tak, żeby zapisywały się tylko ważne akcje — DDL i zmiany danych.

Automatyzuj analizę logów. Używaj skryptów lub narzędzi do regularnego czytania i analizy logów, takich jak grep, awk albo nawet ELK Stack (Elasticsearch, Logstash, Kibana).

Zarządzaj rozmiarem logów. Skonfiguruj rotację logów przez parametry PostgreSQL lub narzędzia systemowe (np. logrotate w Linuksie).

Mam nadzieję, że teraz czujesz się jak prawdziwy szeryf swojej bazy danych. Logowanie i audyt to nie tylko ochrona, ale też świetny sposób, żeby lepiej ogarnąć, co się dzieje w twoim systemie. Zobaczymy, jak ta wiedza przyda się w prawdziwych projektach!