Как сделать аудит-лог — пошагово, чтобы он не врал

Аудит-лог нужен ровно в тот момент, когда кто-то спрашивает: «а кто поменял цену?» Если журнала нет, честный ответ — «не знаю, и уже не узнаю».
Соблазн сделать это в коде: перед каждым обновлением дописывать строчку в таблицу истории. Работает — до первого раза, когда данные меняют мимо приложения. Скрипт миграции, кнопка в панели хостинга, ты сам ночью из SQL-консоли. Такой аудит-лог не заметит ничего и будет выглядеть чистым. Это хуже, чем его отсутствие: ты ему веришь.
Правильное место для аудита — сама база. Разберём на PostgreSQL: он под Supabase, Neon, Railway и большинством управляемых баз.
Что получится
Отдельная таблица, куда база сама пишет строку при каждом изменении: что за операция, когда, кто, и как выглядела запись. Мимо неё не пройдёт ни приложение, ни скрипт, ни человек с доступом к консоли.
1. Заведи таблицу для записей
Начнём с журнала. Официальный пример из документации PostgreSQL выглядит так:
CREATE TABLE emp_audit(
operation char(1) NOT NULL,
stamp timestamp NOT NULL,
userid text NOT NULL,
empname text NOT NULL,
salary integer
);
Схема: операция, время, кто, и колонки самой записи.
Для своего проекта удобнее универсальный вариант — один журнал на все таблицы, а данные строки складывать в JSON:
CREATE TABLE audit_log (
id bigserial PRIMARY KEY,
table_name text NOT NULL,
operation text NOT NULL,
changed_at timestamptz NOT NULL DEFAULT now(),
db_user text NOT NULL,
app_user text,
row_before jsonb,
row_after jsonb
);
2. Напиши функцию, которая будет писать в журнал
Функция вызывается базой при каждом изменении. Внутри доступны три вещи: TG_OP — тип операции, OLD — строка до, NEW — строка после.
CREATE OR REPLACE FUNCTION write_audit() RETURNS TRIGGER AS $$
BEGIN
INSERT INTO audit_log (table_name, operation, db_user, app_user, row_before, row_after)
VALUES (
TG_TABLE_NAME,
TG_OP,
current_user,
current_setting('app.user_id', true),
CASE WHEN TG_OP = 'INSERT' THEN NULL ELSE to_jsonb(OLD) END,
CASE WHEN TG_OP = 'DELETE' THEN NULL ELSE to_jsonb(NEW) END
);
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
RETURN NULL здесь не ошибка: результат AFTER-триггера всё равно игнорируется — так и написано в документации в комментарии к их примеру.
3. Повесь триггер на таблицу
CREATE TRIGGER products_audit
AFTER INSERT OR UPDATE OR DELETE ON products
FOR EACH ROW EXECUTE FUNCTION write_audit();
Два слова тут важны. AFTER — пишем после того, как изменение состоялось, чтобы не записать отменённое. FOR EACH ROW — на каждую строку: если один UPDATE затронул 50 строк, в журнале будет 50 записей, а не одна.
Одну и ту же функцию вешают на сколько угодно таблиц — она узнаёт имя таблицы сама через TG_TABLE_NAME.
4. Проверь прямо сейчас
UPDATE products SET price = 990 WHERE id = 1;
SELECT operation, db_user, row_before -> 'price', row_after -> 'price' FROM audit_log;
Должна появиться строка UPDATE со старой и новой ценой. Если не появилась — почти всегда триггер повешен на другую таблицу или на другую схему.
5. Запиши, кто это был на самом деле
Вот шаг, который обычно пропускают — и зря.
В журнале уже есть current_user, но это роль подключения к базе. Через пул соединений и через Supabase все запросы приходят под одной ролью. В журнале будет 40 000 записей от authenticated — то есть ни от кого.
Настоящего пользователя надо передать самому. PostgreSQL разрешает завести свою настройку: документация говорит прямо, что база «примет настройку для любого имени параметра из двух частей». Пользуемся этим.
В коде, перед изменением данных, в той же транзакции:
SELECT set_config('app.user_id', '8f21c0a4-...', true);
Третий аргумент true означает «только на текущую транзакцию» — значение не утечёт в следующий запрос через пул. Триггер читает его через current_setting('app.user_id', true), и второй аргумент true тут тоже не случаен: без него база бросит ошибку, если настройку не выставили, и твоё обновление упадёт. С ним вернётся пустое значение.
Что это даёт: запись из приложения будет подписана человеком, а запись из консоли — нет. И это ровно то, что тебе нужно знать.
6. Закрой журнал от правок
Журнал, который можно тихо подчистить, доказывает мало. Отбери у обычной роли всё, кроме вставки:
REVOKE ALL ON audit_log FROM PUBLIC;
GRANT INSERT ON audit_log TO your_app_role;
Читать журнал пусть может отдельная роль или ты через консоль. Идеальной защиты от суперпользователя не будет — но случайное «а давайте почистим» уже не пройдёт.
7. Реши, что делать с ростом
Журнал растёт быстрее данных: одна строка живёт вечно, а записей об её изменениях — десятки. Через полгода это заметно.
Два спокойных варианта: удалять записи старше года задачей по расписанию или не аудировать таблицы, где изменения не важны. Логи приложения для этого не подходят — они про другое и живут обычно недели, а не годы.
Аудит-лог заменяет бэкапы?
Нет. Журнал отвечает на вопрос «кто и что изменил», бэкап — на вопрос «как вернуть, как было». По журналу можно вручную восстановить отдельную строку, но не базу целиком.
Не замедлит ли триггер запись?
Каждое изменение теперь делает ещё одну вставку — примерно вдвое больше работы на запись. Для панели администратора и обычных пользовательских действий это незаметно. Для массовой загрузки миллиона строк триггер лучше отключить на время.
Как это сделать не в PostgreSQL?
Механика та же: триггер плюс таблица журнала. Синтаксис отличается, названия переменных тоже. А вот в документных базах триггеров часто нет — там аудит придётся строить на потоке изменений или писать из приложения, со всеми оговорками из начала статьи. Добавляй аудит миграцией, а не руками в консоли — иначе на проде его не окажется.
Курс «Профессия 2027: ИИ-архитектор»: шаг за шагом от основ компьютера до своего приложения и первых клиентов. 543 коротких урока на телефоне.





