Аудит изменений в PostgreSQL: триггеры или приложение

Для аудита в PostgreSQL важно не только сохранить старое и новое значение, но и не потерять запись, если изменение пришло не через основной сервис. Триггер охватывает операции с таблицей независимо от приложения, но часто не знает настоящего автора изменения. Код приложения знает бизнес-контекст, зато обходит сам себя при миграции, ручном SQL или работе другого сервиса. Ниже — сравнение подходов для PostgreSQL 16–17 и критерии выбора.

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

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

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

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

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

Триггер: контроль рядом с таблицей

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

Это главное преимущество триггера: при обычных правах на таблицу изменение не зависит от того, какой сервис или оператор отправил SQL. Импорт данных, административный скрипт и новый сервис не забудут вызвать отдельный код аудита. При этом триггер покрывает именно те операции, для которых его настроили. Например, построчный триггер не следует считать журналом чтения данных, а для TRUNCATE в PostgreSQL предусмотрены отдельные условия срабатывания триггеров.

Цена — дополнительная работа при каждой операции записи и тесная связь поведения таблицы с логикой в базе. Если сохранять целые строки в JSONB, журнал может быстро стать больше исходных таблиц; насколько именно — зависит от объёма, частоты обновлений и набора данных. Ошибка в обработчике может сорвать пользовательскую транзакцию. Кроме того, триггер не знает бизнес-смысл операции, если приложение специально не передаёт его в базу.

Кто считается автором

В PostgreSQL триггер может записать роль базы данных, выполняющую запрос, но в приложении с пулом соединений это часто общий технический аккаунт. Значит, запись «изменил app_user» ещё не отвечает на вопрос, какой сотрудник или клиент нажал кнопку.

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

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

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

Критичное условие: изменение бизнес-данных и запись аудита должны фиксироваться в одной транзакции PostgreSQL. Если сначала сохранить заказ, а потом отправить событие во внешний брокер без согласованного механизма доставки, между этими действиями возможен сбой. Тогда заказ изменится, а аудита не будет. Если аудит обязательно должен храниться отдельно, нужна продуманная схема надёжной доставки; это уже отдельная архитектурная задача.

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

Сравнение подходов

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

Когда что выбирать

Выбирайте триггер, если

• данные меняют несколько сервисов, скриптов или операторов, а журнал должен охватывать их все;
• важно сохранить прежнее состояние строки при каждой операции записи;
• команда умеет тестировать и сопровождать логику базы наравне с кодом приложения.

Выбирайте аудит в приложении, если

• единственный контролируемый путь изменения данных — приложение;
• в журнале важны бизнес-пользователь, причина и понятный тип действия;
• вы можете обеспечить одну транзакцию для изменения и записи аудита и проверять новые пути записи.

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

Что ломается в эксплуатации

• Журнал разрастается. Полные снимки удобны для восстановления и сравнения, но дублируют данные. Определите срок хранения, объём полей и политику удаления до запуска.
• Аудит мешает записи. Ошибка триггера или нехватка места в таблице журнала может привести к ошибке исходной транзакции. Для строгого аудита это может быть правильным отказом, но решение нужно согласовать с требованиями доступности.
• Автор не тот. Общая роль приложения показывает только технический аккаунт. Поле с бизнес-пользователем бесполезно, если его может бесконтрольно подменить сам вызывающий запрос.
• События дублируются. Триггер и приложение записали одну операцию раздельно или повтор запроса создал несколько событий. Нужны правила идемпотентности и понятный способ связать записи.
• Журнал считают неизменяемым. Таблица аудита в той же базе не защищена от владельца базы с широкими правами. Для доказательной ценности продумайте отдельные права, резервное копирование и, если угроза этого требует, вынос копии в систему с независимым контролем доступа.

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

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

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

В проде следите за задержкой операций записи, размером и скоростью роста журнала, ошибками обработчика и долей событий без бизнес-контекста. Сверяйте документацию именно с вашей основной версией PostgreSQL: детали определения и срабатывания триггеров описаны в официальном руководстве PostgreSQL 16 и 17.