Аудит изменений в PostgreSQL: как не потерять следы

Если после обновления заказа нужно выяснить, кто поменял цену и что стояло в поле раньше, одного журнала SQL недостаточно: он обычно фиксирует команду, а не надёжный снимок изменённых строк. Для PostgreSQL 16 разберём три подхода — триггеры, pgAudit и логическую репликацию — и настроим триггерный журнал, который записывает старое и новое состояние каждой строки.

Сначала определите, что именно нужно доказать

Словом «аудит» называют разные задачи. Иногда нужно расследовать ошибку приложения: сравнить состояние записи до и после UPDATE. Иногда — подтвердить, кто запускал запросы и к каким объектам обращался. А иногда — передать поток изменений в отдельное хранилище или аналитическую систему. Это не одно и то же, и один механизм не закрывает все три задачи.

Перед выбором ответьте на четыре вопроса:

• Нужны значения до и после изменения или достаточно факта выполнения SQL?
• Нужно фиксировать только успешные транзакции или также неудачные попытки?
• Кто будет иметь доступ к журналу и сможет ли администратор его изменить?
• Как долго хранить записи и как исключить из них секретные или персональные данные?

Дальше — практический пример для PostgreSQL 16. Описанный триггерный механизм работает на уровне строк и не требует стороннего расширения. Перед внедрением проверьте его на тестовой копии: аудит увеличивает объём записей и добавляет работу к каждой операции изменения.

Три способа вести аудит

Эти способы можно сочетать. Например, триггер хранит историю выбранных бизнес-таблиц, а pgAudit помогает расследовать доступ и выполнение SQL. Если задача — доставлять изменения в отдельную систему, логическая репликация может быть уместнее, но её потребитель и хранение придётся проектировать отдельно.

Аудит полезен ровно настолько, насколько точно отвечает на вопрос «что произошло». Журнал команд, история строк и поток для интеграции — разные источники доказательств.

Настраиваем журнал строк через триггер

Ниже — минимальная схема для PostgreSQL 16. Она записывает операцию, время, идентификатор транзакции, имя сессии, адрес клиента, таблицу и значения строки до и после изменения. Для INSERT поле «до» будет пустым, для DELETE — пустым будет поле «после».

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

CREATE SCHEMA audit;

CREATE TABLE audit.row_change (
    change_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    occurred_at timestamptz NOT NULL,
    transaction_id xid8 NOT NULL,
    db_user name NOT NULL,
    application_name text,
    client_address inet,
    table_schema text NOT NULL,
    table_name text NOT NULL,
    operation text NOT NULL,
    before_row jsonb,
    after_row jsonb
);

Теперь создайте триггерную функцию. Она работает с любой таблицей, для которой установлен триггер. Атрибут SECURITY DEFINER позволяет функции записывать в журнал с правами её владельца, а не приложения; поэтому владелец должен быть ограниченной служебной ролью. Фиксированный search_path и полное имя таблицы журнала уменьшают риск подмены объектов.

CREATE FUNCTION audit.capture_row_change()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog
AS $function$
BEGIN
    INSERT INTO audit.row_change (
        occurred_at,
        transaction_id,
        db_user,
        application_name,
        client_address,
        table_schema,
        table_name,
        operation,
        before_row,
        after_row
    )
    VALUES (
        clock_timestamp(),
        pg_current_xact_id(),
        session_user,
        current_setting('application_name', true),
        inet_client_addr(),
        TG_TABLE_SCHEMA,
        TG_TABLE_NAME,
        TG_OP,
        CASE WHEN TG_OP IN ('UPDATE', 'DELETE')
             THEN to_jsonb(OLD) ELSE NULL END,
        CASE WHEN TG_OP IN ('INSERT', 'UPDATE')
             THEN to_jsonb(NEW) ELSE NULL END
    );

    IF TG_OP = 'DELETE' THEN
        RETURN OLD;
    END IF;
    RETURN NEW;
END;
$function$;

Установите функцию на конкретную таблицу. В примере аудит включён для public.orders; замените имя на нужную таблицу. Триггер срабатывает для вставки, изменения и удаления строк.

CREATE TRIGGER orders_row_audit
AFTER INSERT OR UPDATE OR DELETE ON public.orders
FOR EACH ROW
EXECUTE FUNCTION audit.capture_row_change();

Проверьте результат на тестовой записи: выполните INSERT, UPDATE и DELETE, затем убедитесь, что в audit.row_change появилась запись для каждой операции и что поля before_row и after_row отражают ожидаемые состояния. Проверяйте внутри транзакции, которую затем можно отменить, если используете тестовые данные.

Права — часть механизма, а не дополнительная настройка

Не выдавайте приложению права на изменение или удаление строк журнала. Разделите роли: приложение меняет бизнес-таблицы, служебная роль владеет схемой аудита, а аналитики получают только необходимый доступ на чтение. Доступ суперпользователя или владельца базы таким разграничением не блокируется: администратор с достаточными правами способен менять настройки и объекты базы.

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

Что чаще всего ломается

• Журнал меняется вместе с бизнес-транзакцией. Если транзакция откатили, запись триггера тоже откатится. Такой аудит хранит историю зафиксированных изменений, но не неудачных попыток и не отменённых операций.
• Добавили колонку с секретом. Снимок to_jsonb(NEW) сохраняет все поля строки. Если таблица содержит токены, пароли или лишние персональные данные, они попадут в журнал. До запуска проверьте состав таблиц и исключите чувствительные значения отдельным явным формированием JSON.
• Забыли про операции вне строки. Показанный триггер не фиксирует TRUNCATE. Если такая операция возможна и важна для аудита, настройте отдельный механизм для неё и проверьте поведение на вашей версии и схеме прав.
• Журнал растёт без срока хранения. JSON-снимки могут быть крупнее исходных данных, а UPDATE создаёт дополнительную запись. Оцените скорость роста на реальной нагрузке, задайте срок хранения и процедуру архивирования до включения аудита на больших таблицах.
• Администратор может изменить следы. Триггер не защищает журнал от роли с полномочиями владельца или суперпользователя. Для требований к независимому хранению копируйте события за пределы базы и контролируйте доставку и доступ к копии.

Сноска. WAL (Write-Ahead Log) — журнал предварительной записи PostgreSQL, из которого база восстанавливает состояние и который используют, в частности, механизмы репликации. Это не удобная таблица аудита: для чтения изменений нужны соответствующий механизм декодирования и отдельная обработка.

Когда выбирать каждый вариант

• Вы разбираете изменения конкретных бизнес-записей и нужны значения до и после — начните с триггера на выбранных таблицах.
• Нужно знать, какие SQL-команды выполнялись и какие объекты затрагивались, — оцените pgAudit и требования к хранению серверных журналов. Настройку расширения сверяйте с документацией его версии и вашей сборки PostgreSQL.
• Нужно передавать изменения внешнему потребителю — проектируйте логическую репликацию и мониторинг её отставания. Не используйте её как синоним готового аудиторского архива.

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

Что сделать дальше

На тестовой базе выберите одну таблицу, установите триггер и проверьте INSERT, UPDATE, DELETE, откат транзакции и доступы разных ролей. Затем измерьте объём новых записей за типичный рабочий период и оцените срок хранения. Для production отдельно решите, кто может читать журнал, как выносить его копию за пределы базы и какие поля нельзя сохранять.