Аудит PostgreSQL за три дня: роль только на чтение, снимки SQL, отчёт с влиянием и трудоёмкостью

Тебе дают чужую базу и три дня. Пароль суперпользователя никто не отдаст, а жалоба «иногда тормозит» не называет ни запроса, ни таблицы. Метод превращает это в конвейер: в первый день ты забираешь снимки состояния под ролью только на чтение, во второй проходишь по областям справочника, в третий пишешь отчёт, где у каждой находки есть влияние, трудоёмкость и статья с механизмом. Какие сигналы вообще собирать, описано в что мониторить в PostgreSQL, как разбирать один запрос — в методе разбора медленного запроса; здесь — порядок работы над всей базой. Рамка проекта вокруг аудита — форматы, доступы и их отзыв, передача команде — описана в статье о работе консультантом.

Стенд

Учебная база с заложенными проблемами: внешний ключ order_items.order_id без индекса, таблица events с отключённым автовакуумом, из которой удалено 500 тыс. строк и обновлено 250 тыс., индекс по events.payload, который никто не читает, и track_io_timing = off. Приложение держит открытую транзакцию. Масштаб небольшой: проблема видна и на 245 МБ.

docker run -d --name dba-audit-pg --memory 2g -e POSTGRES_PASSWORD=pg postgres:18 \
  -c shared_preload_libraries=pg_stat_statements -c track_io_timing=off

CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION pgstattuple;
CREATE ROLE app LOGIN PASSWORD 'app';
CREATE ROLE plain LOGIN PASSWORD 'plain';
CREATE TABLE orders (id bigint PRIMARY KEY, customer_id int NOT NULL, created_at timestamptz NOT NULL, amount numeric(10,2));
CREATE TABLE order_items (id bigint PRIMARY KEY, order_id bigint NOT NULL REFERENCES orders(id), sku int, qty int);
CREATE TABLE events (id bigint PRIMARY KEY, kind int, payload text, created_at timestamptz) WITH (autovacuum_enabled = false);
INSERT INTO orders SELECT g, g % 50000, now() - (g % 365) * interval '1 day', (g % 1000) / 10.0 FROM generate_series(1, 200000) g;
INSERT INTO order_items SELECT g, 1 + g % 200000, g % 500, 1 + g % 5 FROM generate_series(1, 1000000) g;
INSERT INTO events SELECT g, g % 20, repeat('x', 60), now() - (g % 100) * interval '1 hour' FROM generate_series(1, 1000000) g;
CREATE INDEX events_payload_idx ON events (payload);
CREATE INDEX events_kind_idx ON events (kind);
ANALYZE;
DELETE FROM events WHERE id % 2 = 0;
UPDATE events SET kind = kind + 1 WHERE id % 4 = 1;
GRANT ALL ON ALL TABLES IN SCHEMA public TO app;
SELECT pg_stat_statements_reset();

Нагрузка приложения: 40 запросов суммы позиций заказа через pgbench под ролью app, один подсчёт по events и транзакция, которую клиент открыл и забыл.

docker exec -i dba-audit-pg sh -c 'cat > /tmp/w.sql && pgbench -U app -n -f /tmp/w.sql -c 2 -t 20 postgres' <<'EOF'
\set o random(1, 200000)
SELECT sum(qty) FROM order_items WHERE order_id = :o;
EOF
docker exec dba-audit-pg psql -U app postgres -c 'select count(*) from events where kind=3'
docker exec -d dba-audit-pg sh -c "(echo 'BEGIN; UPDATE orders SET amount = amount WHERE id = 1;'; sleep 900) | psql -U app postgres"

День 1. Роль аудитора и что она видит

Аудитору не нужны данные, ему нужны метаданные и статистика. Роль pg_monitor собирает именно их: она включает pg_read_all_settings, pg_read_all_stats и pg_stat_scan_tables, это описано в документации. Больше ничего не выдаётся, кроме права подключиться.

CREATE ROLE auditor LOGIN PASSWORD 'audit' IN ROLE pg_monitor;
GRANT CONNECT ON DATABASE postgres TO auditor;

Проверка границы: десять запросов под auditor, первая строка ответа каждого.

вывод:

> SELECT count(*) FROM orders;
ERROR:  permission denied for table orders
> SELECT count(*) FROM pg_hba_file_rules;
ERROR:  permission denied for view pg_hba_file_rules
> SELECT pg_stat_statements_reset();
ERROR:  permission denied for function pg_stat_statements_reset
> SELECT pg_terminate_backend(215);
ERROR:  permission denied to terminate process
> CREATE TABLE t (i int);
ERROR:  permission denied for schema public
> SELECT pg_stat_file('postgresql.conf');
ERROR:  permission denied for function pg_stat_file
> SELECT count(*) FROM pg_statistic;
ERROR:  permission denied for table pg_statistic
> SELECT query FROM pg_stat_activity WHERE usename = 'app';
UPDATE orders SET amount = amount WHERE id = 1;
> SELECT count(*) FROM pg_ls_waldir();
33
> SELECT dead_tuple_percent FROM pgstattuple('events');
23.93

Роль не читает данные, не правит конфигурацию, не сбрасывает статистику и не завершает чужие сессии. Зато видит текст чужих запросов, настройки, каталог WAL и меру раздутия таблицы: pgstattuple отработал без права SELECT на events. Под ролью plain без pg_monitor тот же запрос к pg_stat_activity вернул <insufficient privilege>. Две строки кода объясняют границу: pg_statistic закрыт для всех (system_views.sql, L271), а каталог WAL отдан pg_monitor явно (system_functions.sql, L784):

REVOKE ALL ON pg_statistic FROM public;
GRANT EXECUTE ON FUNCTION pg_ls_waldir() TO pg_monitor;

pgstattuple читает все страницы таблицы (events с индексами — 157 655 040 байт в 04_tables.csv), поэтому на таблице в сотни гигабайт это заметная нагрузка на диск. Такие вызовы идут вне пика и по одной таблице.

День 1. Снимки

Снимок — это набор файлов, снятых одним сеансом psql. Аудитор не может создавать таблицы, поэтому \copy пишет CSV на машину аудитора. Девять запросов покрывают области второго дня; файл целиком:

\set ON_ERROR_STOP on
\copy (SELECT now() AS taken_at, version(), pg_postmaster_start_time() AS started) TO 'snap/00_meta.csv' CSV HEADER
\copy (SELECT name, setting, unit, source FROM pg_settings WHERE name IN ('track_io_timing','archive_mode','idle_in_transaction_session_timeout','autovacuum','max_connections','shared_buffers') ORDER BY name) TO 'snap/01_settings.csv' CSV HEADER
\copy (SELECT pid, usename, state, now() - xact_start AS xact_age, age(backend_xid) AS xid_age, left(query, 60) AS query FROM pg_stat_activity WHERE backend_type = 'client backend' AND pid <> pg_backend_pid()) TO 'snap/02_sessions.csv' CSV HEADER
\copy (SELECT queryid, calls, round(total_exec_time) AS total_ms, rows, shared_blks_read, shared_blks_hit, shared_blk_read_time, left(query, 70) AS query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5) TO 'snap/03_statements.csv' CSV HEADER
\copy (SELECT relname, n_live_tup, n_dead_tup, seq_scan, idx_scan, last_autovacuum, last_autoanalyze, pg_total_relation_size(relid) AS bytes, (SELECT reloptions FROM pg_class WHERE oid = relid) AS reloptions FROM pg_stat_user_tables) TO 'snap/04_tables.csv' CSV HEADER
\copy (SELECT s.relname, s.indexrelname, s.idx_scan, pg_relation_size(s.indexrelid) AS bytes, i.indisunique FROM pg_stat_user_indexes s JOIN pg_index i USING (indexrelid)) TO 'snap/05_indexes.csv' CSV HEADER
\copy (SELECT datname, age(datfrozenxid) AS xid_age, (SELECT stats_reset FROM pg_stat_database d WHERE d.datname = pg_database.datname) FROM pg_database) TO 'snap/06_xid_age.csv' CSV HEADER
\copy (SELECT 'archiver' AS what, archived_count::text, failed_count::text FROM pg_stat_archiver UNION ALL SELECT 'replicas', count(*)::text, NULL FROM pg_stat_replication UNION ALL SELECT 'slots', count(*)::text, NULL FROM pg_replication_slots) TO 'snap/07_backup_ha.csv' CSV HEADER
\copy (SELECT c.conrelid::regclass AS child, c.conname FROM pg_constraint c WHERE c.contype = 'f' AND NOT EXISTS (SELECT 1 FROM pg_index i WHERE i.indrelid = c.conrelid AND (i.indkey::int2[])[0:cardinality(c.conkey)-1] @> c.conkey AND i.indisvalid)) TO 'snap/08_fk_without_index.csv' CSV HEADER

mkdir snap && PGPASSWORD=audit psql -h 127.0.0.1 -U auditor postgres -f snap.sql

вывод (файлы 01, 02, 04, 05, 07, 08 и две строки 03):

name,setting,unit,source
archive_mode,off,,default
autovacuum,on,,default
idle_in_transaction_session_timeout,0,ms,command line
max_connections,100,,configuration file
shared_buffers,16384,8kB,configuration file
track_io_timing,off,,command line

pid,usename,state,xact_age,xid_age,query
215,app,idle in transaction,00:04:44.281901,2,UPDATE orders SET amount = amount WHERE id = 1;

relname,n_live_tup,n_dead_tup,seq_scan,idx_scan,last_autovacuum,last_autoanalyze,bytes,reloptions
order_items,1000000,0,121,0,2026-10-28 12:08:41.834366+00,,74711040,
events,500000,750000,10,1,,,157655040,{autovacuum_enabled=false}
orders,200000,0,1,1000000,2026-10-28 12:08:42.209975+00,,16596992,

relname,indexrelname,idx_scan,bytes,indisunique
orders,orders_pkey,1000000,4513792,t
order_items,order_items_pkey,0,22487040,t
events,events_pkey,0,22487040,t
events,events_payload_idx,0,7593984,f
events,events_kind_idx,1,10485760,f

what,archived_count,failed_count
archiver,0,0
replicas,0,
slots,0,

child,conname
order_items,order_items_order_id_fkey

queryid,calls,total_ms,rows,shared_blks_read,shared_blks_hit,shared_blk_read_time,query
892611691976955445,40,652,40,26994,226980,0,SELECT sum(qty) FROM order_items WHERE order_id = $1
6519463637377698518,1,59,1,14088,243,0,select count(*) from events where kind=$1

Все девять запросов выполнились за 0,03 с, итого 37 строк в девяти файлах. 00_meta.csv фиксирует момент снимка (12:13:32) и время запуска сервера (12:07:40): база прожила 5 минут 52 секунды, и любой счётчик в файлах относится только к этому окну.

День 2. Анализ по областям

Снимок читается по областям. Одна строка таблицы — область, ключевые проверки и статья справочника с механизмом.

postgres-checkup — готовый сборщик такого же набора. Его структура по релизному тегу 1.5.8: каждый отчёт состоит из «Observations», «Conclusions» и «Recommendations», проверки лежат в resources/checks/ и названы буквой раздела и номером. В 1.5.8 есть разделы A (версия, настройки, расширения, хост), D (инструменты хоста и настройки pg_stat_statements), F (автовакуум и раздутие), G (память, соединения, таймауты), H (индексы), K (запросы) и L (размеры таблиц), всего 26 проверок. Проект в архиве, его преемник — npx postgresai checkup; 1.5.8 — его последний тег (июнь 2026).

Сборка в контейнере без хоста: postgres:18, apt-get install golang-go jq gawk make ca-certificates, затем make main в каталоге pghrep. Без ca-certificates Go не скачивает модули (ошибка x509: certificate signed by unknown authority). Запуск под тем же auditor, через --pg-hostname без ssh:

USER=root PGPASSWORD=audit ./checkup --pg-hostname 172.17.0.6 -U auditor -d postgres --project aud -e 1

вывод (последние строки, без меток времени):

WARNING: Can't generate JSON reports (see errors above):
        'A001_system_info.json'
        'A006_pg_conf_diff.json'
        'A008_disk_usage_fstype.json'
        'D002_useful_linux_tools.json'
        'F005_index_bloat.json'
        'K000_query_analysis.json'
ALL DONE!

Готово 20 отчётов из 26. Шесть отказов объясняют границы доступа:

• A001, A008, D002 — нужен ssh на хост. Скрипт пропускает проверку и выходит с ошибкой (A008_disk_usage_fstype.sh, L3-L6):

if [[ "${SSH_SUPPORT}" = "false" ]]; then
  echo "SSH is not supported, skipping..." >&2
  exit 1
fi

• A006 — permission denied for view pg_config, F005 — permission denied for table pg_statistic (оценка раздутия индексов читает pg_statistic).
• K000 — не ошибка: сравнение нагрузки требует двух прогонов за одну эпоху, скрипт пишет 2 runs are needed.

Что инструмент нашёл: H003 вывел order_items_order_id_fkey без индекса, H002 — events_payload_idx (7,24 МиБ, 0 обращений) с пометкой «статистика моложе 30 дней», F004 оценил раздутие events в 76,00 %, общее — в 64,76 %. Измерение pgstattuple другое: мёртвых версий 23,93 %, свободного места 23,66 %, итого около 47,6 %. Оценка завышена, но порядок верный; для решения берут измерение. Счётчик n_dead_tup в снимке (750 000) расходится с pgstattuple (250 025 мёртвых версий) втрое, поэтому по одному счётчику решение не принимают.

Чего инструмент не сказал: track_io_timing = off лежит в отчёте A003 строкой таблицы без вывода, а G002 показывает одну сессию idle in transaction, но не её возраст в 4 минуты 44 секунды при idle_in_transaction_session_timeout = 0. Эти две находки пришли из снимка и из чтения shared_blk_read_time = 0 при 26 994 прочитанных блоках.

День 3. Отчёт

Находка — это строка с пятью полями: что видно (число и источник), влияние, трудоёмкость, действие и статья. Влияние: критично (теряем данные или простой), высокое (деградирует сейчас), среднее (мешает диагностике или копит риск), низкое. Трудоёмкость — оценка консультанта в человеко-часах, а не измерение.

Топ действий идёт по убыванию влияния:

1. Настроить архив WAL и полную копию, затем провести восстановление (находка 1).
2. Создать индекс по order_items(order_id) через CREATE INDEX CONCURRENTLY (находка 2).
3. Включить автовакуум на events, затем убрать накопленное раздутие (находка 3).
4. Поставить idle_in_transaction_session_timeout и найти в приложении забытую транзакцию (находка 4).
5. Включить track_io_timing и через сутки перечитать 03_statements.csv (находка 5).

Находка 6 не входит в топ: решение об удалении индекса принимают после 30 дней статистики, а не после пяти минут.

Что не смотрели. Раздел обязателен, он защищает и тебя, и заказчика:

• ОС и диск: ssh не было, проверки A001, A008, D002 пропущены; диагностика хоста остаётся на второй этап.
• Безопасность: pg_hba_file_rules закрыт, роли и шифрование не разбирались.
• Восстановление: бэкапов нет, проверять нечего.
• Содержимое таблиц: SELECT закрыт, качество данных и размер ключей не оценивались.
• Нагрузка за период: окно снимка 5 минут 52 секунды, K000 требует двух прогонов, а idx_scan = 0 не доказательство.

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

• Роль pg_monitor закрывает сбор, но не закрывает хост, pg_hba.conf и pg_statistic; границу описывают в отчёте, а не молча обходят суперпользователем.
• Готовый инструмент и свои снимки дополняют друг друга: checkup нашёл внешний ключ и оценил раздутие, снимок поймал track_io_timing и возраст транзакции.
• Окно статистики входит в находку. Снимок с uptime в минутах годится для структуры базы, но не для вывода «индекс не нужен».

Чек-лист

1. Выдай аудитору pg_monitor и CONNECT, затем проверь границу: SELECT из таблицы и pg_terminate_backend должны упасть.
2. Сними девять файлов одним сеансом psql, запиши момент снимка и время запуска сервера.
3. Пройди по одиннадцати областям; для каждой находки укажи число, источник, влияние, трудоёмкость и статью.
4. Отдели оценку от измерения: оценку раздутия checkup проверь pgstattuple на одной таблице.
5. Напиши «не смотрели», затем убери стенд командой docker rm -f -v dba-audit-pg: без -v том с данными остаётся на диске.