Аудит с доказуемой целостностью в PostgreSQL с использованием хеш‑цепочки

Почему обычные журналы аудита легко оспорить

Аудитный след — это запись, к которой вы обращаетесь, когда что‑то выглядит неправильно: странный возврат, изменение прав, которого никто не помнит, или клиентская запись, которая «исчезла». Если журнал аудита можно редактировать, он перестаёт быть доказательством и превращается в ещё один фрагмент данных, который можно переписать.

Многие «журналы аудита» на деле — обычные таблицы. Если строки можно обновлять или удалять, можно обновить или удалить и историю.

Ключевое различие: предотвратить правки и сделать их обнаруживаемыми — не одно и то же. Вы можете сократить число изменений через права доступа, но любой с достаточными привилегиями (или с украденными админ‑учетными данными) всё ещё может изменить историю. Подход с обнаружением подделки принимает эту реальность. Вы, возможно, не предотвратите каждое изменение, но заставите изменения оставлять очевидный отпечаток.

Обычные журналы аудита оспаривают по предсказуемым причинам. Привилегированные пользователи могут «исправить» журнал задним числом. Скомпрометированная учётная запись приложения может добавить правдоподобные записи, похожие на нормальный трафик. Метки времени могут быть подправлены, чтобы скрыть позднее изменение. Или кто‑то удаляет только самые вредные строки.

«Обнаруживаемость подделки» означает, что вы проектируете журнал так, чтобы даже небольшая правка (изменение одного поля, удаление строки, перестановка событий) становилась заметной позже. Вы не обещаете магию. Вы обещаете, что когда кто‑то спросит: «Как мы знаем, что этот журнал настоящий?», вы сможете выполнить проверки, которые покажут, трогали ли журнал.

Решите, что нужно доказать

Журнал с обнаружением подделки полезен только если отвечает на вопросы, которые возникнут позже: кто что сделал, когда это произошло и что изменилось.

Начните с событий, которые важны для вашего бизнеса. Изменения данных (создание, обновление, удаление) — это базис, но расследования часто зависят и от событий безопасности и доступа: логины, сбросы пароля, изменения прав и блокировки аккаунтов. Если вы работаете с платежами, возвратами, кредитами или выплатами, рассматривайте движение денег как первоочередные события, а не как побочный эффект обновлённой строки.

Затем решите, что делает событие достоверным. Аудиторы обычно ожидают актера (пользователь или сервис), серверную метку времени, выполненное действие и объект, на который это действие повлияло. Для обновлений храните значения до и после (или хотя бы чувствительные поля), а также request id или correlation id, чтобы связать множество мелких изменений в БД с одним действием пользователя.

Наконец, явно обозначьте, что в вашей системе означает «неизменяемый». Самое простое правило: никогда не обновлять и не удалять строки аудита, только вставлять. Если что‑то неверно, записывайте новое событие, которое поправляет или заменяет старое, и сохраняйте оригинал видимым.

Постройте таблицу аудита только для добавления

Держите данные аудита отдельно от обычных таблиц. Выделённая схема audit снижает вероятность случайных правок и упрощает рассуждения о правах доступа.

Цель проста: строки можно добавлять, но нельзя менять или удалять. В PostgreSQL это обеспечивается через привилегии (кто что может делать) и несколько защитных мер в дизайне таблицы.

Вот практичная стартовая таблица:

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
);

Несколько полей особенно полезны при расследованиях:

• occurred_at с DEFAULT now(), чтобы время проставлялось базой данных, а не клиентом.
• entity_type и entity_id, чтобы можно было отслеживать одну запись через изменения.
• request_id, чтобы одно действие пользователя можно было проследить по нескольким строкам.

Закройте доступ ролями. Роль приложения должна иметь право INSERT и SELECT в audit.events, но не UPDATE и не DELETE. Изменения схемы и более сильные права оставьте за ролью администратора, которую приложение не использует.

Фиксация изменений триггерами (чисто и предсказуемо)

Если вы хотите журнал с обнаружением подделки, самое надёжное место для фиксации изменений — сама база данных. Логи приложений могут быть пропущены, отфильтрованы или переписаны. Триггер сработает независимо от того, какое приложение, скрипт или админ‑инструмент коснулся таблицы.

Держите триггеры простыми. Их задача — одна вещь: добавить одно событие аудита для каждого INSERT, UPDATE и DELETE в важных таблицах.

Практичная запись аудита обычно включает имя таблицы, тип операции, первичный ключ, значения до и после, временную метку и идентификаторы, которые позволяют группировать связанные изменения (id транзакции и correlation id).

Correlation id — это разница между «обновлено 20 строк» и «это было один клик кнопки». Ваше приложение может установить correlation id один раз на запрос (например, в настройке сессии БД), а триггер читает его. Храните txid_current() тоже, чтобы можно было группировать изменения, когда correlation id отсутствует.

Вот простой шаблон триггера, который остаётся предсказуемым, потому что только вставляет в таблицу аудита (подгоните имена под вашу схему):

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;

Удерживайтесь от соблазна сделать в триггере больше. Избегайте дополнительных запросов, сетевых вызовов или сложных ветвлений. Маленькие триггеры легче тестировать, они быстрее работают и сложнее оспорить при ревью.

Добавьте хеш‑цепочку, чтобы правки оставляли отпечатки

Таблица только для добавления помогает, но кто‑то с достаточным доступом всё ещё может переписать прошлые строки. Хеш‑цепочка делает такое вмешательство заметным.

Добавьте в каждую строку аудита два столбца: prev_hash и row_hash (иногда chain_hash). prev_hash хранит хеш предыдущей строки в той же цепочке. row_hash хранит хеш текущей строки, вычисленный из данных строки плюс prev_hash.

То, что вы хешируете, имеет значение. Нужен стабильный, детерминированный вход, чтобы одна и та же строка всегда давала один и тот же хеш.

Практичный подход — хешировать каноническую строку, собранную из фиксированных столбцов (метка времени, актор, действие, id сущности), канонической полезной нагрузки (часто jsonb, потому что ключи хранятся последовательно) и prev_hash.

Будьте осторожны с деталями, которые могут меняться без смысла, например пробелы, порядок ключей JSON в текстовой форме или форматирование чисел, зависящее от локали. Держите типы согласованными и сериализуйте одним предсказуемым способом.

Цепочка по потоку, а не по всей базе

Если вы связываете все события аудита в одну глобальную последовательность, запись может стать узким местом. Во многих системах цепочки ведутся внутри «потока», например по арендаторам, по типу сущности или по бизнес‑объекту.

Каждая новая строка смотрит на последний row_hash своего потока, сохраняет его как prev_hash, затем вычисляет собственный 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'
);

Снимайте состояние головы цепочки

Для ускорения ревью храните последний row_hash («голову цепочки») периодически, например ежедневно для каждого потока, в небольшой таблице снимков. Во время расследования вы сможете верифицировать цепочку до каждого снимка вместо сканирования всей истории. Снимки также упрощают сравнение экспортов и обнаружение подозрительных разрывов.

Конкурентность и порядок, не ломая цепочку

Хеш‑цепочки становятся сложными при реальном трафике. Если две транзакции одновременно пишут строки аудита и обе используют один и тот же prev_hash, может получиться форк. Это ослабляет вашу возможность доказать единую непрерывную последовательность.

Сначала решите, что представляет собой ваша цепочка. Одна глобальная цепочка проще для объяснения, но даёт наибольшее содержание. Несколько цепочек уменьшают нагрузку, но вам нужно ясно объяснять, что каждая цепочка доказывает.

Какой бы модели вы ни придерживались, задайте строгий порядок с монотонным id события (обычно последовательность, поддерживаемая sequence). Меток времени недостаточно: они могут совпадать и их можно изменить.

Чтобы избежать состязаний при вычислении prev_hash, сериализуйте операцию «получить последний хеш + вставить следующую строку» для каждого потока. Обычные подходы — блокировка одной строки, представляющей голову потока, или использование advisory lock, привязанного к id потока. Цель — сделать так, чтобы два писателя в один поток не могли оба прочитать один и тот же последний хеш.

Партиционирование и шардинг влияют на то, где хранится «последняя строка». Если вы ожидаете партиционирование данных аудита, держите каждую цепочку целиком в одном разделе, используя тот же ключ партиции, что и ключ потока (например, tenant id). Тогда цепочки арендаторов останутся верифицируемыми даже если арендаторы позже переместятся между серверами.

Как верифицировать цепочку при расследовании

Хеш‑цепочка помогает только если вы можете доказать её целостность, когда кто‑то спросит. Самый безопасный подход — запрос только для чтения (или джоба), который пересчитывает хеш каждой строки из сохранённых данных и сравнивает с тем, что записано.

Простой верификатор, который можно запустить по требованию

Верификатор должен: перестроить ожидаемый хеш для каждой строки, убедиться, что каждая строка ссылается на предыдущую, и пометить всё, что выглядит неверным.

Вот распространённый паттерн с оконными функциями. Подгоняйте имена столбцов под вашу таблицу.

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;

Помимо «сломано/нет», стоит проверять разрывы (пропущенные id в диапазоне), ссылки вне порядка и подозрительные дубли, которые не соответствуют реальным рабочим потокам.

Фиксируйте результаты верификации как неизменяемые события

Не запускайте запрос и не прячьте вывод в тикете. Сохраняйте результаты верификации в отдельной таблице только для добавления (например, audit_verification_runs) с временем прогона, версией верификатора, кто запустил, проверенным диапазоном и счётчиками сломанных ссылок и несовпадений хешей.

Так у вас появится вторая дорожка: не только журнал аудита цел, но и видно, что вы его проверяете.

Практичная частота: запуск после каждого деплоя, который трогает логику аудита, ночная проверка для активных систем и всегда перед плановым аудитом.

Частые ошибки, которые ломают обнаруживаемость подделок

Большая часть ошибок не в алгоритме хеширования. Они в исключениях и пропусках, которые дают людям повод спорить.

Самый быстрый способ потерять доверие — разрешить обновления строк аудита. Даже если это «всего лишь один раз», вы создаёте прецедент и рабочий путь для переписывания истории. Если нужно исправление, добавляйте новое событие аудита, которое объясняет исправление, и сохраняйте оригинал.

Хеш‑цепочка также терпит неудачу, когда вы хешируете нестабильные данные. JSON — частая ловушка. Если вы хешируете JSON как строку, безобидные различия (порядок ключей, пробелы, формат чисел) могут изменить хеш и сделать верификацию шумной. Предпочитайте каноническую форму: нормализованные поля, jsonb или другую стабильную сериализацию.

Другие паттерны, подрывающие защищаемый след:

• Хешировать только полезную нагрузку, пропуская контекст (метку времени, актор, id объекта, действие).
• Фиксировать изменения только в приложении и полагаться, что база данных всегда совпадает.
• Использовать одну роль базы данных, которая одновременно пишет бизнес‑данные и может менять историю аудита.
• Разрешать NULL в prev_hash внутри цепочки без чётких документированных правил.

Разделение обязанностей важно. Если одна и та же роль может вставлять события аудита и менять их, обнаруживаемость подделки превращается из контроля в пустое обещание.

Быстрый чеклист для защищаемого журнала аудита

Защищаемый журнал аудита должен быть трудно изменить и легко проверить.

Начните с контроля доступа: таблица аудита должна быть практично только для добавления. Роль приложения должна вставлять (и обычно читать), но не обновлять и не удалять. Изменения схемы должны быть строго ограничены.

Убедитесь, что каждая строка отвечает на вопросы следователя: кто это сделал, когда это произошло (серверное время), что произошло (понятное имя события и операция), что было затронуто (имя сущности и id) и как это связано (request/correlation id и id транзакции).

Затем проверьте слой целостности. Быстрый тест — проиграть сегмент и подтвердить, что каждый prev_hash совпадает с хешем предыдущей строки, а каждый сохранённый хеш совпадает с пересчитанным.

В операционных задачах относитесь к верификации как к обычной работе:

• Запускайте плановые проверки целостности и сохраняйте результаты pass/fail и диапазоны.
• Настраивайте оповещения по несовпадениям, разрывам и сломанным ссылкам.
• Храните бэкапы достаточно долго для вашего окна ретенции и ограничьте возможность преждевременной «чистки» истории аудита.

Пример: обнаружение подозрительной правки при проверке на соответствие

Распространённый кейс — спор по возврату. Клиент утверждает, что ему одобрили возврат $250, а система сейчас показывает $25. Саппорт настаивает, что одобрение было верным, и служба комплаенса требует ответа.

Начните с сужения поиска, используя correlation id (order id, ticket id или refund_request_id) и временное окно. Вытяните строки аудита для этого correlation id и возьмите окружение вокруг времени одобрения.

Вам нужен полный набор событий: запрос создан, возврат одобрен, сумма возврата установлена и любые последующие обновления. С дизайном, обнаруживающим подделки, вы также проверяете, сохранилась ли последовательность.

Простой сценарий расследования:

• Вытяните все строки аудита для correlation id в хронологическом порядке.
• Пересчитайте хеш каждой строки из её полей (включая prev_hash).
• Сравните пересчитанные хеши с сохранёнными.
• Найдите первую строку, которая отличается, и посмотрите, не дают ли сбои и последующие строки.

Если кто‑то отредактировал одну строку аудита (например, изменил сумму с 250 на 25), хеш этой строки больше не совпадёт. Поскольку следующая строка содержит предыдущий хеш, несовпадение обычно даёт каскад вперёд. Этот каскад — отпечаток: он показывает, что запись аудита была изменена задним числом.

Что цепочка может сказать: была ли правка, где впервые сломалась цепочка и какой объём строк затронут. Чего она сама по себе не скажет: кто сделал правку, каково было оригинальное значение, если оно перезаписано, или были ли изменены и другие таблицы.

Следующие шаги: вводите безопасно и поддерживайте систему

Относитесь к журналу аудита как к любому другому средству безопасности. Вводите поэтапно, подтвердите работоспособность и затем расширяйте.

Начните с действий, которые нанесли бы вам наибольший вред при споре: изменения прав, выплаты, возвраты, экспорты данных и ручные корректировки. Когда они покрыты, добавляйте события с меньшим риском, не меняя базового дизайна.

Опишите контракт ваших событий аудита: какие поля фиксируются, что означает каждый тип события, как вычисляется хеш и как запускать верификацию. Держите эту документацию рядом с миграциями базы и делайте процедуру верификации воспроизводимой.

Учитесь восстанавливать из бэкапов, потому что расследования часто начинаются с резервных копий, а не с живой системы. Регулярно восстанавливайте на тестовую базу и проверяйте цепочку от начала до конца. Если вы не можете получить тот же результат верификации после восстановления, вашу обнаруживаемость подделки будет трудно отстоять.

Если вы строите внутренние инструменты и админ‑воркфлоу с AppMaster (appmaster.io), стандартизация записи событий аудита через согласованные серверные процессы помогает держать схему событий и correlation id единообразными между фичами, что упрощает верификацию и расследования.

Запланируйте время на поддержку этой системы. Журналы аудита обычно тихо ломаются, когда команды выпускают новые фичи, но забывают добавить события, обновить входы для хеша или поддерживать джобы верификации и прогон восстановления.