Wykrywalne ścieżki audytu w PostgreSQL z łańcuchowaniem hashów

Dlaczego zwykłe logi audytu łatwo podważyć

Ślad audytu to zapis, do którego wracasz, gdy coś wygląda źle: dziwny zwrot pieniędzy, zmiana uprawnień, której nikt nie pamięta, albo rekord klienta, który „zniknął”. Jeśli ślad audytu można edytować, przestaje być dowodem i staje się kolejnym kawałkiem danych, który ktoś może przepisać.

Wiele „dzienników audytu” to po prostu zwykłe tabele. Jeśli wiersze można aktualizować lub usuwać, historia też może zostać zmieniona lub usunięta.

Kluczowa różnica: zablokowanie edycji to nie to samo co wykrywalność edycji. Możesz ograniczyć zmiany przez uprawnienia, ale każdy z wystarczającym dostępem (lub skradzionymi danymi administratora) nadal może zmienić historię. Wykrywalność manipulacji akceptuje tę rzeczywistość. Możesz nie zapobiec każdej zmianie, ale możesz sprawić, że zmiany zostawią oczywisty odcisk palca.

Zwykłe logi audytu są podważane z przewidywalnych powodów. Uprzywilejowani użytkownicy mogą „naprawić” log po fakcie. Skompromitowane konto aplikacji może zapisać wiarygodne wpisy wyglądające jak normalny ruch. Znaczniki czasu można wypełnić wstecz, żeby ukryć późną zmianę. Albo ktoś usuwa tylko najbardziej szkodliwe wiersze.

„Wykrywalne” oznacza, że projektujesz ślad audytu tak, by nawet drobna edycja (zmiana jednego pola, usunięcie jednego wiersza, zmiana kolejności zdarzeń) była później wykrywalna. Nie obiecujesz magii. Obiecujesz, że gdy ktoś zapyta „Skąd wiemy, że ten log jest prawdziwy?”, będziesz mógł uruchomić sprawdzenia pokazujące, czy log był modyfikowany.

Zdecyduj, co musisz udowodnić

Wykrywalny ślad audytu jest przydatny tylko wtedy, gdy odpowiada na pytania, które mogą się pojawić później: kto zrobił co, kiedy to zrobił i co się zmieniło.

Zacznij od zdarzeń ważnych dla Twojego biznesu. Zmiany danych (create, update, delete) to podstawa, ale dochodzenia często zależą też od bezpieczeństwa i dostępu: logowania, resetów haseł, zmian uprawnień i blokad kont. Jeśli obsługujesz płatności, zwroty, kredyty lub wypłaty, traktuj ruch pieniędzy jako zdarzenia pierwszorzędne, a nie efekt uboczny zaktualizowanego wiersza.

Następnie zdecyduj, co czyni zdarzenie wiarygodnym. Audytorzy zwykle oczekują: aktora (użytkownik lub serwis), znacznika czasu po stronie serwera, podjętej akcji oraz obiektu, którego akcja dotyczyła. Dla aktualizacji przechowuj wartości przed i po (lub przynajmniej wrażliwe pola), plus identyfikator żądania lub identyfikator korelacji, aby związać wiele małych zmian w bazie z jedną akcją użytkownika.

Wreszcie, bądź jawny, co w Twoim systemie oznacza „niezmienne”. Najprostsza reguła: nigdy nie aktualizuj ani nie usuwaj wierszy audytu, tylko wstawiaj. Jeśli coś jest nie tak, zapisz nowe zdarzenie, które koryguje lub unieważnia stare, i pozostaw oryginał widoczny.

Zbuduj tabelę audytu tylko do dopisywania

Trzymaj dane audytu oddzielnie od zwykłych tabel. Dedykowany audit schema zmniejsza ryzyko przypadkowych edycji i upraszcza rozumowanie o uprawnieniach.

Cel jest prosty: wiersze mogą być dodawane, ale nigdy zmieniane lub usuwane. W PostgreSQL wymuszasz to uprawnieniami (kto może co robić) i kilkoma zabezpieczeniami w projekcie tabeli.

Oto praktyczny początkowy schemat tabeli:

CREATE SCHEMA IF NOT EXISTS audit;

CREATE TABLE audit.events (
  id            bigserial PRIMARY KEY,
  entity_type   text        NOT NULL,
  entity_id     text        NOT NULL,
  event_type    text        NOT NULL CHECK (event_type IN ('INSERT','UPDATE','DELETE')),
  actor_id      text,
  occurred_at   timestamptz NOT NULL DEFAULT now(),
  request_id    text,
  before_data   jsonb,
  after_data    jsonb,
  notes         text
);

Kilka pól jest szczególnie przydatnych w dochodzeniach:

• occurred_at z DEFAULT now(), żeby czas był stemplowany przez bazę, a nie klienta.
• entity_type i entity_id, aby śledzić jeden rekord przez zmiany.
• request_id, aby jedną akcję użytkownika powiązać z wieloma wierszami.

Zamknij to uprawnieniami. Rola aplikacji powinna móc INSERT i SELECT na audit.events, ale nie UPDATE ani DELETE. Zmiany schematu i mocniejsze uprawnienia zostaw dla roli administratora, której aplikacja nie używa.

Przechwytuj zmiany za pomocą triggerów (prosto i przewidywalnie)

Jeśli chcesz wykrywalny ślad audytu, najbardziej niezawodnym miejscem do przechwytywania zmian jest sama baza danych. Logi aplikacji mogą być pominięte, filtrowane lub przepisane. Trigger uruchamia się niezależnie od tego, która aplikacja, skrypt czy narzędzie administracyjne dotknęło tabeli.

Utrzymuj triggery proste. Ich zadaniem powinno być jedno: dopisać jedno zdarzenie audytu dla każdego INSERT, UPDATE i DELETE w tabelach, które mają znaczenie.

Praktyczny rekord audytu zazwyczaj zawiera nazwę tabeli, typ operacji, klucz główny, wartości przed i po, znacznik czasu oraz identyfikatory pozwalające grupować powiązane zmiany (id transakcji i id korelacji).

Identyfikatory korelacji to różnica między „zaktualizowano 20 wierszy” a „to było jedno kliknięcie przycisku”. Twoja aplikacja może ustawić identyfikator korelacji raz na żądanie (na przykład w ustawieniu sesji DB), a trigger może go odczytać. Przechowuj też txid_current(), aby nadal grupować zmiany, gdy identyfikator korelacji brak.

Oto prosty wzorzec triggera, który pozostaje przewidywalny, bo jedynie wstawia do tabeli audytu (dostosuj nazwy do własnego schematu):

CREATE OR REPLACE FUNCTION audit_row_change() RETURNS trigger AS $$
DECLARE
  corr_id text;
BEGIN
  corr_id := current_setting('app.correlation_id', true);

  INSERT INTO audit_events(
    occurred_at, table_name, op, row_pk,
    old_row, new_row, db_user, txid, correlation_id
  ) VALUES (
    now(), TG_TABLE_NAME, TG_OP, COALESCE(NEW.id, OLD.id),
    to_jsonb(OLD), to_jsonb(NEW), current_user, txid_current(), corr_id
  );

  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

Opieraj się pokusie robienia więcej wewnątrz triggerów. Unikaj dodatkowych zapytań, wywołań sieciowych czy skomplikowanych warunków. Małe triggery łatwiej testować, działają szybciej i trudniej się z nimi kłócić podczas przeglądu.

Dodaj łańcuchowanie hashów, żeby zmiany zostawiały odciski

Tabela tylko do dopisywania pomaga, ale ktoś z wystarczającym dostępem nadal może przepisać przeszłe wiersze. Łańcuchowanie hashów sprawia, że tego rodzaju manipulacje stają się widoczne.

Dodaj do każdego wiersza audytu dwie kolumny: prev_hash i row_hash (czasem chain_hash). prev_hash przechowuje hash poprzedniego wiersza w tym samym łańcuchu. row_hash przechowuje hash bieżącego wiersza, obliczony z danych wiersza plus prev_hash.

Co haszujesz, ma znaczenie. Chcesz stabilnego, powtarzalnego wejścia, tak aby ten sam wiersz zawsze dawał ten sam hash.

Praktyczne podejście to haszowanie kanonicznego ciągu zbudowanego ze stałych kolumn (znacznik czasu, aktor, akcja, id encji), kanonicznego payloadu (często jsonb, bo klucze są przechowywane spójnie) oraz prev_hash.

Uważaj na szczegóły, które mogą się zmieniać bez znaczenia, jak białe znaki, kolejność kluczy JSON w zwykłym tekście czy formatowanie zależne od lokalizacji. Trzymaj typy spójne i serializuj w przewidywalny sposób.

Łańcuch dla strumienia, a nie dla całej bazy

Jeśli łańczysz każde zdarzenie audytu w jednym globalnym ciągu, zapisy mogą stać się wąskim gardłem. Wiele systemów tworzy łańcuchy w ramach „strumienia”, na przykład na tenant, typ encji lub obiekt biznesowy.

Każdy nowy wiersz odczytuje ostatni row_hash dla swojego strumienia, zapisuje go jako prev_hash, a następnie oblicza własny row_hash.

-- Requires pgcrypto
-- digest() returns bytea; store hashes as bytea
row_hash = digest(
  concat_ws('|',
    stream_key,
    occurred_at::text,
    actor_id::text,
    action,
    entity,
    entity_id::text,
    payload::jsonb::text,
    encode(prev_hash, 'hex')
  ),
  'sha256'
);

Snapshottuj head łańcucha

Dla szybszych przeglądów zapisuj okresowo najnowszy row_hash (tzw. „chain head”), na przykład codziennie dla każdego strumienia, w małej tabeli snapshotów. Podczas dochodzenia możesz zweryfikować łańcuch do każdego snapshotu zamiast przeglądać całą historię naraz. Snapshoty ułatwiają też porównywanie eksportów i wykrywanie podejrzanych luk.

Równoległość i uporządkowanie bez łamania łańcucha

Łańcuchowanie hashów komplikuje się przy prawdziwym ruchu. Jeśli dwie transakcje jednocześnie zapiszą wiersze audytu i obie użyją tego samego prev_hash, możesz skończyć z rozwidleniem. To osłabia możliwość udowodnienia jednego, spójnego ciągu.

Najpierw zdecyduj, co reprezentuje Twój łańcuch. Jeden globalny łańcuch jest najprostszy do wyjaśnienia, ale ma największe problemy z zawartością. Wiele łańcuchów zmniejsza zawartość, ale musisz jasno określić, co każdy łańcuch dowodzi.

Bez względu na model, zdefiniuj ścisły porządek za pomocą monotonicznego id zdarzenia (zwykle id na sekwencji). Znaczniki czasu nie wystarczą, bo mogą się zderzać i można je manipulować.

Aby uniknąć wyścigów przy obliczaniu prev_hash, zserializuj „pobierz ostatni hash + wstaw następny wiersz” dla każdego strumienia. Popularne podejścia to zablokowanie pojedynczego wiersza reprezentującego head strumienia albo użycie advisory lock kluczowanego przez id strumienia. Celem jest, by dwóch piszących do tego samego strumienia nie mogło jednocześnie przeczytać tego samego ostatniego hasha.

Partycjonowanie i shardowanie wpływają na to, gdzie „żyje ostatni wiersz”. Jeśli planujesz partycjonować dane audytu, trzymaj każdy łańcuch w obrębie jednej partycji, używając tego samego klucza partycji co klucz strumienia (np. tenant id). Dzięki temu łańcuchy tenantów pozostaną weryfikowalne nawet, jeśli później przeniesiesz tenantów między serwerami.

Jak weryfikować łańcuch podczas dochodzenia

Łańcuchowanie hashów pomaga tylko jeśli możesz udowodnić, że łańcuch nadal się zgadza, gdy ktoś zapyta. Najbezpieczniejsze podejście to zapytanie w trybie read-only (lub zadanie), które ponownie oblicza hash każdego wiersza z przechowywanych danych i porównuje go z tym, co jest zapisane.

Prosty weryfikator, który możesz uruchomić na żądanie

Weryfikator powinien: odbudować oczekiwany hash dla każdego wiersza, potwierdzić, że każdy wiersz łączy się z poprzednim oraz oznaczyć wszystko, co wygląda podejrzanie.

Oto typowy wzorzec używający funkcji okienkowych. Dostosuj nazwy kolumn do swojej tabeli.

WITH ordered AS (
  SELECT
    id,
    created_at,
    actor_id,
    action,
    entity,
    entity_id,
    payload,
    prev_hash,
    row_hash,
    LAG(row_hash) OVER (ORDER BY created_at, id) AS expected_prev_hash,
    /* expected row hash, computed the same way as in your insert trigger */
    encode(
      digest(
        coalesce(prev_hash, '') || '|' ||
        id::text || '|' ||
        created_at::text || '|' ||
        coalesce(actor_id::text, '') || '|' ||
        action || '|' ||
        entity || '|' ||
        entity_id::text || '|' ||
        payload::text,
        'sha256'
      ),
      'hex'
    ) AS expected_row_hash
  FROM audit_log
)
SELECT
  id,
  created_at,
  CASE
    WHEN prev_hash IS DISTINCT FROM expected_prev_hash THEN 'BROKEN_LINK'
    WHEN row_hash IS DISTINCT FROM expected_row_hash THEN 'HASH_MISMATCH'
    ELSE 'OK'
  END AS status
FROM ordered
WHERE prev_hash IS DISTINCT FROM expected_prev_hash
   OR row_hash IS DISTINCT FROM expected_row_hash
ORDER BY created_at, id;

Poza „zepsute czy nie”, warto sprawdzić luki (brakujące id w zakresie), linki poza kolejnością i podejrzane duplikaty, które nie pasują do realnych workflowów.

Zapisuj wyniki weryfikacji jako zdarzenia niezmienne

Nie uruchamiaj zapytania i nie chowaj wyniku w zgłoszeniu. Zapisuj wyniki weryfikacji w oddzielnej tabeli tylko do dopisywania (np. audit_verification_runs) z czasem uruchomienia, wersją weryfikatora, kto je wywołał, zakresem sprawdzanym oraz liczbami zepsutych linków i niedopasowań hash.

Daje to drugi ślad: nie tylko log audytu jest nienaruszony, ale możesz też pokazać, że go regularnie sprawdzasz.

Praktyczna częstotliwość to: po każdym wdrożeniu dotykającym logiki audytu, codziennie dla aktywnych systemów i zawsze przed zaplanowanym audytem.

Typowe błędy, które łamią wykrywalność

Większość porażek nie dotyczy algorytmu hash. Chodzi o wyjątki i luki, które dają ludziom pole do dyskusji.

Najszybszy sposób utraty zaufania to dopuścić aktualizacje wierszy audytu. Nawet jeśli to „tylko tym razem”, tworzysz precedens i realną ścieżkę do przepisywania historii. Jeśli trzeba coś poprawić, dodaj nowe zdarzenie audytu wyjaśniające poprawkę i zostaw oryginał.

Łańcuchowanie hashów też zawodzi, gdy haszujesz niestabilne dane. JSON to częsta pułapka. Jeśli haszujesz JSON jako string, niewinne różnice (kolejność kluczy, białe znaki, formatowanie liczb) mogą zmieniać hash i robić weryfikację głośną. Preferuj kanoniczną formę: znormalizowane pola, jsonb lub inną spójną serializację.

Inne wzorce podważające obronny ślad:

• Haszowanie tylko payloadu i pomijanie kontekstu (znacznik czasu, aktor, id obiektu, akcja).
• Rejestrowanie zmian tylko w aplikacji i zakładanie, że baza zawsze odpowiada.
• Używanie jednej roli bazy danych, która może zapisywać dane biznesowe i jednocześnie modyfikować historię audytu.
• Pozwalanie na NULL w prev_hash w łańcuchu bez jasnej, udokumentowanej reguły.

Separation of duties ma znaczenie. Jeśli ta sama rola może wstawiać zdarzenia audytu i je modyfikować, wykrywalność staje się obietnicą zamiast realnej kontroli.

Szybka lista kontrolna dla obronnego śladu audytu

Obronny ślad audytu powinien być trudny do zmiany i łatwy do zweryfikowania.

Zacznij od kontroli dostępu: tabela audytu w praktyce musi być tylko do dopisywania. Rola aplikacji powinna wstawiać (i zwykle czytać), ale nie aktualizować ani usuwać. Zmiany schematu powinny być ściśle ograniczone.

Upewnij się, że każdy wiersz odpowiada na pytania dochodzeniowe: kto to zrobił, kiedy się to wydarzyło (po stronie serwera), co się wydarzyło (czytelna nazwa zdarzenia i operacja), co zostało dotknięte (nazwa encji i id) oraz jak to się łączy (request/correlation id i txid).

Następnie zweryfikuj warstwę integralności. Szybki test to odtworzenie segmentu i potwierdzenie, że każdy prev_hash pasuje do hasha poprzedniego wiersza, a każdy zapisany hash pasuje do ponownie obliczonego.

Operacyjnie traktuj weryfikację jak normalne zadanie:

• Uruchamiaj zaplanowane kontrole integralności i zapisuj wyniki (pass/fail) i zakresy.
• Alarmuj przy niedopasowaniach, lukach i zepsutych linkach.
• Przechowuj backupy wystarczająco długo, by pokryć okno retencji, i zablokuj retencję, aby historia audytu nie mogła zostać „wcześniej posprzątana”.

Przykład: wykrywanie podejrzanej edycji podczas przeglądu zgodności

Typowy przypadek testowy to spór o zwrot. Klient twierdzi, że miał zatwierdzony zwrot 250 USD, a system pokazuje teraz 25 USD. Support twierdzi, że zatwierdzenie było poprawne, a dział zgodności chce odpowiedzi.

Zacznij od zawężenia wyszukiwania przy użyciu identyfikatora korelacji (order id, ticket id lub refund_request_id) i okna czasowego. Pobierz wiersze audytu dla tego identyfikatora korelacji i otocz je wokół czasu zatwierdzenia.

Szukasz pełnego zestawu zdarzeń: żądanie utworzone, zwrot zatwierdzony, ustawiona kwota zwrotu i ewentualne późniejsze aktualizacje. W projekcie wykrywalnym sprawdzasz również, czy sekwencja pozostała nienaruszona.

Prosty przebieg dochodzenia:

• Pobierz wszystkie wiersze audytu dla identyfikatora korelacji w kolejności czasowej.
• Ponownie oblicz hash każdego wiersza z jego przechowywanych pól (w tym prev_hash).
• Porównaj obliczone hashe z zapisanymi hashami.
• Zidentyfikuj pierwszy wiersz, który różni się, i sprawdź, czy późniejsze wiersze też zawodzą.

Jeśli ktoś zmienił pojedynczy wiersz audytu (np. zmieniając kwotę z 250 na 25), hash tego wiersza nie będzie się zgadzał. Ponieważ następny wiersz zawiera poprzedni hash, niedopasowanie zwykle propaguje się naprzód. Ta kaskada to odcisk palca: pokazuje, że rekord audytu został zmieniony po fakcie.

Co łańcuch może powiedzieć: że nastąpiła edycja, gdzie łańcuch pierwszy raz się zepsuł i zakres wierszy objętych zmianą. Czego sam łańcuch nie powie: kto dokonał edycji, jaka była oryginalna wartość, jeśli została nadpisana, ani czy inne tabele też zostały zmienione.

Kolejne kroki: wdrażaj ostrożnie i utrzymuj

Traktuj ślad audytu jak każdy inny mechanizm bezpieczeństwa. Wdrażaj go etapami, udowodnij, że działa, a potem rozszerzaj.

Zacznij od działań, które najbardziej by Ci zaszkodziły, gdyby były kwestionowane: zmiany uprawnień, wypłaty, zwroty, eksporty danych i nadpisania ręczne. Gdy te zostaną objęte, dodawaj zdarzenia o niższym ryzyku bez zmiany podstawowego projektu.

Spisz kontrakt dla zdarzeń audytu: które pola są zapisywane, co znaczy każdy typ zdarzenia, jak obliczany jest hash i jak uruchomić weryfikację. Trzymaj tę dokumentację obok migracji bazy i utrzymuj procedurę weryfikacji powtarzalną.

Ćwiczenia przywracania mają znaczenie, bo dochodzenia często zaczynają się od backupów, nie od systemu produkcyjnego. Regularnie przywracaj do bazy testowej i weryfikuj łańcuch end-to-end. Jeśli nie możesz odtworzyć tego samego wyniku weryfikacji po przywróceniu, Twoja wykrywalność będzie trudna do obrony.

Jeśli tworzysz wewnętrzne narzędzia i workflowy administracyjne z AppMaster (appmaster.io), standaryzacja zapisów zdarzeń audytu przez spójne procesy po stronie serwera pomaga utrzymać schemat zdarzeń i identyfikatory korelacji jednolite w całych funkcjach, co znacznie ułatwia weryfikację i dochodzenia.

Zaplanuj czas na utrzymanie tego systemu. Ślady audytu zwykle zawodzą cicho, gdy zespoły wypuszczają nowe funkcje, ale zapominają dodać zdarzeń, zaktualizować wejścia do hasha albo utrzymać zadania weryfikacyjne i ćwiczenia przywracania.