Аудит PostgreSQL: pgaudit по сессиям и объектам, log_line_prefix, jsonlog и размер журнала в ГБ в сутки

Аудит включили, а через неделю диск под журналом заполнен, или наоборот: журнал тих, и при разборе инцидента в нём нет нужной строки. Оба случая объясняет настройка pgaudit.log: расширение pgaudit пишет по записи на каждую подходящую команду, а размер журнала равен числу записей на транзакцию, умноженному на размер записи и на темп транзакций. Ниже все три множителя измерены на pgbench.

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

Стенд

docker run -d --name dba-audit-log --memory 2g --cpus 4 -e POSTGRES_PASSWORD=pg postgres:18
docker exec dba-audit-log sh -c 'apt-get update -qq && apt-get install -y -qq postgresql-18-pgaudit'
docker exec -u postgres dba-audit-log mkdir -p /var/lib/postgresql/audit-log

ALTER SYSTEM SET shared_preload_libraries = 'pgaudit';
ALTER SYSTEM SET logging_collector = on;
ALTER SYSTEM SET log_directory = '/var/lib/postgresql/audit-log';
ALTER SYSTEM SET log_filename = 'audit.log';
ALTER SYSTEM SET log_destination = 'stderr,jsonlog';
ALTER SYSTEM SET log_line_prefix = '%m %u %d %a %h %x ';

SQL-блоки выполняй в docker exec -it dba-audit-log psql -X -U postgres -v ON_ERROR_STOP=1. После перезапуска создай расширение, затем таблицы нагрузки:

docker restart dba-audit-log
# Дождись строки «accepting connections»:
docker exec dba-audit-log pg_isready -U postgres
docker exec dba-audit-log psql -U postgres -c 'CREATE EXTENSION pgaudit'
docker exec dba-audit-log pgbench -i -s 1 -U postgres postgres

Версия расширения 18.0 рассчитана на PostgreSQL 18. Создай расширение до включения pgaudit.log: его триггеры дополняют DDL именами объектов. По умолчанию pgaudit.log = none, pgaudit.log_parameter = off, pgaudit.log_relation = off.

Одна транзакция в трёх профилях

Профиль задаётся pgaudit.log. В SQL-сеансе выполни ALTER SYSTEM SET pgaudit.log = 'write, ddl'; и SELECT pg_reload_conf();, затем из оболочки запусти docker exec dba-audit-log pgbench -U postgres -n -c 1 -t 1 postgres. Флаг -n убирает предварительные VACUUM и очистку истории.

Вот первая транзакция повторного прогона; время и номера зависят от запуска:

вывод
2026-10-28 23:00:33.922 UTC postgres postgres pgbench [local] 0 LOG:  AUDIT: SESSION,1,1,WRITE,UPDATE,,,UPDATE pgbench_accounts SET abalance = abalance + -170 WHERE aid = 83532,<not logged>
2026-10-28 23:00:33.922 UTC postgres postgres pgbench [local] 4781 LOG:  AUDIT: SESSION,2,1,WRITE,UPDATE,,,UPDATE pgbench_tellers SET tbalance = tbalance + -170 WHERE tid = 1,<not logged>
2026-10-28 23:00:33.922 UTC postgres postgres pgbench [local] 4781 LOG:  AUDIT: SESSION,3,1,WRITE,UPDATE,,,UPDATE pgbench_branches SET bbalance = bbalance + -170 WHERE bid = 1,<not logged>
2026-10-28 23:00:33.922 UTC postgres postgres pgbench [local] 4781 LOG:  AUDIT: SESSION,4,1,WRITE,INSERT,,,"INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (1, 1, 83532, -170, CURRENT_TIMESTAMP)",<not logged>

Разбор первой строки по полям.

Поле %x в первой записи равно 0: транзакция ещё не получила постоянный XID. Следующие команды уже имеют номер 4781. PID (%p) и номер команды помогают связать записи с процессом и оператором, но не определяют транзакцию. Для неё полезны идентификатор сеанса %c и виртуальная транзакция %v; в jsonlog это session_id и vxid. В этом прогоне у четырёх записей одна пара: 6ae27e91.88 и 11/2.

DDL в профиле ddl приходит с типом и именем объекта, их добавляют триггеры событий расширения. Выполни SET pgaudit.log = 'ddl';, затем CREATE TABLE t_audit(id int);, ALTER TABLE t_audit ADD COLUMN note text;, DROP TABLE t_audit;. Ниже поле сообщения без префикса:

вывод
AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.t_audit,CREATE TABLE t_audit(id int),<not logged>
AUDIT: SESSION,2,1,DDL,ALTER TABLE,TABLE,public.t_audit,ALTER TABLE t_audit ADD COLUMN note text,<not logged>
AUDIT: SESSION,3,1,DDL,DROP TABLE,TABLE,public.t_audit,DROP TABLE t_audit,<not logged>

При pgaudit.log = 'all' в той же транзакции записей семь: BEGIN и COMMIT (класс MISC), три UPDATE, SELECT (класс READ) и INSERT.

вывод (классы и команды, текст команд обрезан)
MISC,BEGIN
WRITE,UPDATE   pgbench_accounts
READ,SELECT    pgbench_accounts
WRITE,UPDATE   pgbench_tellers
WRITE,UPDATE   pgbench_branches
WRITE,INSERT   pgbench_history
MISC,COMMIT

Объектный аудит через роль

Сессионный режим пишет всё из выбранных классов. Объектный выбирает таблицы: пишется команда, для которой у роли pgaudit.role есть привилегия на объект.

CREATE ROLE auditor NOLOGIN;
GRANT INSERT ON pgbench_history TO auditor;
GRANT SELECT ON pgbench_accounts TO auditor;
ALTER SYSTEM SET pgaudit.log = 'none';
ALTER SYSTEM SET pgaudit.role = 'auditor';
SELECT pg_reload_conf();

вывод (поле сообщения, одна транзакция pgbench)
AUDIT: OBJECT,1,1,WRITE,UPDATE,TABLE,public.pgbench_accounts,UPDATE pgbench_accounts SET abalance = abalance + -170 WHERE aid = 83532,<not logged>
AUDIT: OBJECT,2,1,READ,SELECT,TABLE,public.pgbench_accounts,SELECT abalance FROM pgbench_accounts WHERE aid = 83532,<not logged>
AUDIT: OBJECT,3,1,WRITE,INSERT,TABLE,public.pgbench_history,"INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (1, 1, 83532, -170, CURRENT_TIMESTAMP)",<not logged>

Записей три вместо семи, и в них есть тип и имя объекта. UPDATE таблицы pgbench_accounts записан при праве SELECT у auditor, хотя UPDATE ему не выдан: команда читает aid в WHERE, то есть требует оба права. Обновления pgbench_tellers и pgbench_branches в журнале нет: прав на эти таблицы у auditor нет. Принцип выбора — в хуке проверки прав исполнителя:

static bool
pgaudit_ExecutorCheckPerms_hook(List *rangeTabls,
                                List *permInfos,
                                bool ereport_on_violation)
{
    Oid auditOid;

    /* Get the audit oid if the role exists */
    auditOid = get_role_oid(auditRole, true);

    /* Log DML if the audit role is valid or session logging is enabled */
    if ((auditOid != InvalidOid || auditLogBitmap != 0) &&
        !IsAbortedTransactionBlockState() && !IsParallelWorker())
    {
        /* If auditLogRows is on, wait for rows processed to be set */

Это pgaudit.c, строки 1494-1507, тег 18.0: хук вызывается при проверке прав в исполнителе. Это вход в обработку аудита; дальше события отбираются по классу и правам аудиторской роли. Из-за этого условия одна лишняя настройка pgaudit.role включает путь логирования даже при pgaudit.log = none. Команды DML в прерванной транзакции и в параллельных рабочих процессах хук пропускает.

Сколько пишет журнал

Пять профилей проверены на одной базе: каждый запуск — 2000 транзакций, один клиент, один и тот же seed генератора. Роль auditor и её права созданы выше. Следующий Bash-блок выполняется только на одноразовом стенде: перед прогоном он очищает два файла журналов.

set -euo pipefail
D=dba-audit-log
L=/var/lib/postgresql/audit-log
for profile in none ddl 'write, ddl' all object; do
  mode="$profile"; role=''
  if [ "$profile" = object ]; then mode=none; role=auditor; fi
  docker exec "$D" psql -X -U postgres -v ON_ERROR_STOP=1 -q \
    -c "ALTER SYSTEM SET pgaudit.log = '$mode'" \
    -c "ALTER SYSTEM SET pgaudit.role = '$role'" \
    -c 'SELECT pg_reload_conf()' >/dev/null
  docker exec -u postgres "$D" sh -c ": > $L/audit.log; : > $L/audit.json"
  docker exec "$D" pgbench -U postgres -n -c 1 -t 2000 --random-seed=42 postgres
  sleep 1
  docker exec "$D" sh -c "wc -l -c $L/audit.log $L/audit.json"
done

Из вывода wc (строки и байты каждого файла):

У all две лишние записи — подготовительные SELECT, которыми pgbench определяет масштаб и секционирование таблицы. Это 14000 записей транзакций плюс 2, а не новая команда внутри каждой транзакции. При ddl журнал пуст, поскольку эта нагрузка не меняет схему. Если размер ещё растёт после паузы, дождись окончания записи и повтори wc.

На транзакцию получилось около 806 байт stderr при write, ddl, 1236 при all, 681 при объектном аудите. Jsonlog на этом наборе занимает в 2,2–2,6 раза больше: к сообщению добавляются именованные поля. Это отношение зависит от запросов, префикса, адреса подключения и длины идентификаторов. Включение обоих форматов означает хранение суммы двух файлов.

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

ГБ в сутки = TPS × байт на транзакцию × 86400 / 10^9

Это прогноз при постоянной нагрузке, а не замер за сутки. Аналогично строк/с = TPS × записей на транзакцию. Цена хранения равна объёму за выбранный срок, умноженному на тариф хранилища, с учётом копий и сжатия. Потерю TPS этот опыт не устанавливает: машина общая, влияние соседних процессов не контролировалось.

jsonlog: та же запись

log_destination = 'stderr,jsonlog' пишет событие в два файла. Первая запись UPDATE из того же прогона (для чтения добавлены переносы строк):

{
  "timestamp": "2026-10-28 23:00:33.922 UTC",
  "user": "postgres",
  "dbname": "postgres",
  "pid": 136,
  "remote_host": "[local]",
  "session_id": "6ae27e91.88",
  "line_num": 1,
  "ps": "UPDATE",
  "session_start": "2026-10-28 23:00:33 UTC",
  "vxid": "11/2",
  "txid": 0,
  "error_severity": "LOG",
  "message": "AUDIT: SESSION,1,1,WRITE,UPDATE,,,UPDATE pgbench_accounts SET abalance = abalance + -170 WHERE aid = 83532,<not logged>",
  "application_name": "pgbench",
  "backend_type": "client backend",
  "query_id": 0
}

Поля префикса стали ключами (user, dbname, application_name, remote_host, txid), добавились pid, session_id, vxid, backend_type; запись pgaudit осталась строкой CSV внутри message. Значит, SIEM получает готовые поля для события, но поля pgaudit (класс, команду, объект) всё равно режет разбором message. Формат jsonlog появился в PostgreSQL 15 и требует logging_collector = on.

Параметры запроса

Расширенный протокол передаёт значения отдельно от текста. Запрос update pgbench_accounts set abalance = abalance + $1 where aid = $2 с параметрами 5 и 16491 проверяется в psql так. Повтори после смены off на on; знак ; перед \bind не ставь:

SET pgaudit.role = '';
SET pgaudit.log = 'write';
SET pgaudit.log_parameter = off;
update pgbench_accounts set abalance = abalance + $1 where aid = $2
\bind 5 16491
\g

вывод (pgaudit.log_parameter = off, затем on)
AUDIT: SESSION,4,1,WRITE,UPDATE,,,update pgbench_accounts set abalance = abalance + $1 where aid = $2,<not logged>
AUDIT: SESSION,5,1,WRITE,UPDATE,,,update pgbench_accounts set abalance = abalance + $1 where aid = $2,"5,16491"

Без параметров запись не отвечает на вопрос, чьи данные изменены: видно $2, но не 16491. С параметрами значения пишутся как есть, в журнал попадает всё, что клиент передал, в том числе персональные данные и секреты. Маскирования значений в pgaudit нет: есть pgaudit.log_parameter_max_size, который заменяет слишком длинное значение строкой <long param suppressed>:

                if (auditLogParameterMaxSize > 0 &&
                    typeIsVarLena &&
                    VARSIZE_ANY_EXHDR(prm->value) > auditLogParameterMaxSize)
                {
                    append_valid_csv(&paramStrResult,
                                     "<long param suppressed>");
                }

Это pgaudit.c, строки 836-842. Подавляются только значения переменной длины и только по размеру. Пароль в тексте команды CREATE ROLE расширение скрывает отдельно. При SET pgaudit.log = 'role'; выполни create role report_ro login password 'example-only';:

вывод
AUDIT: SESSION,6,1,ROLE,CREATE ROLE,,,create role report_ro login password <REDACTED>,<none>

В исходниках комментарий предупреждает, что это покрывает «не все случаи, где пароли могут утечь»: код обрабатывает CREATE/ALTER ROLE и USER MAPPING. Если приложение шлёт пароль параметром обычного запроса, при log_parameter = on он окажется в журнале.

От файла к SIEM

В опыте доставка не проверялась, ниже — по документации PostgreSQL. Файлы читает агент сбора логов, формат jsonlog удобен для программ. Вторая возможность — log_destination = 'syslog'. Сборщик журнала «рассчитан не терять сообщения»: при перегрузке серверные процессы блокируются, пока он догоняет. Syslog, наоборот, «предпочитает отбрасывать сообщения», если не может записать, но остальную систему не блокирует. Для аудита, который не должен ничего терять, и для базы, которая не должна вставать, это противоположные риски; выбирай их осознанно и измеряй на своём темпе.

Требования к журналам

Проверка источника — 29.10.2026: 152-ФЗ на сайте Минтруда, редакция от 24.06.2025, ст. 19 ч. 2 п. 8 предусматривает регистрацию и учёт действий с персональными данными. Эта норма сама по себе не назначает единый срок хранения всех журналов.

Для карточного контура применимую редакцию и требования к журналам бери из официальной библиотеки PCI DSS. Доступ к библиотеке проверен; полный текст актуального стандарта здесь не проверен, поэтому конкретные сроки из него не приводятся. Состав событий, сроки и защиту от изменения согласуй с ответственным за ИБ и юристом. Настройка pgaudit и расчёт места не подтверждают соответствие закону или стандарту.

Что из этого следует

Размер журнала задаёт профиль. write, ddl оставляет записи об изменениях, all добавляет чтения и BEGIN/COMMIT, объектный аудит пишет только выбранные таблицы. Формула из измеренных величин позволяет посчитать сутки до включения, а не после заполнения диска.

В записи нужны поля %m %u %d %a %h; номер транзакции %x в первой записи равен 0, поэтому для корреляции добавь %c %v %p. Параметры включай только вместе с решением, что именно из них может попасть в журнал.

Чек-лист

1. Создай расширение pgaudit, проверь SHOW pgaudit.log, SHOW pgaudit.log_parameter и SHOW pgaudit.role; по умолчанию аудит выключен.
2. Выбери профиль: все изменения (write, ddl), выбранные таблицы (pgaudit.role и GRANT) или всё (all); снять число строк на транзакцию можно на одной тестовой транзакции.
3. Посчитай ГБ в сутки = TPS × байт на транзакцию × 86400 / 10^9 на своей нагрузке и сравни со свободным местом и ротацией (log_rotation_age, log_rotation_size).
4. Задай log_line_prefix = '%m %u %d %a %h %c %v %p %x '; для SIEM добавь jsonlog и измерь размер: здесь файл был в 2,2–2,6 раза больше.
5. Решение о pgaudit.log_parameter фиксируй письменно: в журнал попадают значения параметров, а маскирования в расширении нет.

Удалить стенд: docker rm -f -v dba-audit-log.