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

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

Разберём, как сделать иначе: начать с самого отчёта, выдать ровно те данные, которые в нём нужны, разделить понятия «только чтение» и «ограниченная стоимость запроса», решить, где аналитик будет работать — на основной базе или на реплике, — и проследить, чем безобидный SELECT всё же может навредить.

С отчёта, а не с прав

Обычно права выдают от базы: «дай ему доступ к схеме public». Надёжнее идти от обратной стороны. Выпишите поля, которые нужны отчёту. Для продаж по дням и категориям это, скажем, дата заказа, сумма, статус, категория товара, город доставки и признак повторной покупки. А вот email покупателя, телефон, адрес, комментарий курьера, хеш пароля, токены и платёжные реквизиты отчёту не нужны, даже если лежат в тех же таблицах.

Такой список полезен дважды. Во-первых, он определяет, что именно вы будете публиковать. Во-вторых, он показывает, какие данные вообще не должны покидать приложение. Если аналитик через неделю попросит добавить поле, это обычное небольшое изменение представления, а не пересмотр доступа ко всей базе.

Условный пример для дальнейшего разговора: в базе есть таблицы orders, order_items, products и customers. Отчёту нужны данные из первых трёх и лишь один безличный признак из четвёртой.

Отдельная роль и витрина вместо доступа к таблицам

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

Дальше вместо прямых прав на таблицы создаётся схема с представлениями. Например, схема reporting и представление reporting.sales_daily, в котором уже выбраны нужные колонки, посчитан признак повторной покупки и отброшены служебные статусы. Аналитик получает право использовать схему и читать представления, но не видит исходные таблицы вообще.

CREATE ROLE analyst_ro LOGIN PASSWORD '...';
CREATE SCHEMA reporting;

CREATE VIEW reporting.sales_daily AS
SELECT o.created_at::date AS day,
       p.category,
       o.delivery_city,
       sum(i.price * i.quantity) AS revenue
FROM orders o
JOIN order_items i ON i.order_id = o.id
JOIN products p ON p.id = i.product_id
WHERE o.status = 'paid'
GROUP BY 1, 2, 3;

GRANT USAGE ON SCHEMA reporting TO analyst_ro;
GRANT SELECT ON reporting.sales_daily TO analyst_ro;

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

Есть тонкость, о которой часто забывают: представление по умолчанию выполняется с правами своего владельца, а не вызывающего. Поэтому аналитику не нужны права на orders, но и владельцем представления должна быть роль с минимально достаточными привилегиями, а не суперпользователь. Иначе вы получите красивую витрину, построенную на всемогущей учётной записи. Заодно стоит проверить, что у роли нет лишних прав из прошлых экспериментов: схема public исторически бывает открыта шире, чем кажется, так что просмотрите существующие GRANT, а не полагайтесь на память.

Если фильтровать нужно по строкам

Иногда проблема не в колонках, а в строках: региональный аналитик должен видеть только свой регион, а партнёрский отчёт — только заказы партнёра. Для этого в PostgreSQL есть политики безопасности на уровне строк. Согласно документации по Row Security Policies, при включённом RLS без подходящей политики действует запрет по умолчанию, а политики можно задавать для отдельных команд и ролей.

Это удобно, но с оговорками, которые важны именно для доступа аналитика. Суперпользователь и роли с атрибутом BYPASSRLS обходят политики, а владелец таблицы обычно тоже, если не включён режим FORCE ROW LEVEL SECURITY. То есть RLS защищает от обычной роли только тогда, когда эта роль действительно обычная. Кроме того, команды TRUNCATE и проверки ссылочной целостности через REFERENCES не подчиняются построчной фильтрации. Для read-only аналитика это скорее теоретическая оговорка, потому что он не получает ни TRUNCATE, ни REFERENCES, но она напоминает: RLS — это фильтр видимости строк, а не замена системе GRANT.

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

Read-only и лимиты — это разные рубильники

Теперь о путанице, из-за которой возникает ложное чувство безопасности. Фраза «у аналитика доступ только на чтение» обычно смешивает три разные вещи.

• Права роли. Что она вообще может делать с объектами: читать, вставлять, удалять. Это решает GRANT и отсутствие GRANT.
• Режим транзакции. Транзакция в режиме read-only запрещает изменение обычных, не временных таблиц. Как отмечено в разделе о настройках клиентских подключений, права роли при этом задаются отдельно: режим не заменяет и не отменяет систему привилегий.
• Ограничения по времени и ресурсам. Сколько запрос вправе выполняться. К доступу на запись это вообще не относится.

Отсюда следствие: read-only не делает запрос дешёвым. Аналитик с идеально настроенными правами может запустить соединение двух крупных таблиц без условий, и с точки зрения безопасности данных всё будет в порядке, а с точки зрения сервера — нет.

Как закрепить настройки за ролью

Настройки сессии можно задать не в каждом клиенте, а прямо для роли, чтобы они применялись при каждом её подключении:

ALTER ROLE analyst_ro SET default_transaction_read_only = on;
ALTER ROLE analyst_ro SET statement_timeout = '60s';

Параметр default_transaction_read_only делает режим «только чтение» значением по умолчанию для новых транзакций этой роли. Важно понимать, что это значение по умолчанию: пользователь, который сам управляет сессией, может явно начать транзакцию в другом режиме. Поэтому он служит страховкой от случайности, а настоящей границей остаётся отсутствие прав INSERT, UPDATE и DELETE. Не строить защиту на одном лишь режиме — правило, которое стоит запомнить.

Параметр statement_timeout прерывает слишком долго выполняющийся запрос; по умолчанию он отключён. Здесь и скрыт тонкий момент: если задать его глобально, он затронет все сессии, включая приложение и служебные операции, и документация не рекомендует делать это без разбора. Привязка к роли решает проблему изящно: лимит действует на аналитика и не мешает миграциям, обслуживанию и самому приложению.

Какое значение выбрать, зависит от отчётов. Универсального числа нет. Разумный подход — взять самый тяжёлый законный отчёт, измерить его на реальных данных и задать лимит с запасом. Шестьдесят секунд в примере — условность. Если отчёт, который нужен раз в месяц, честно выполняется десять минут, то его лучше вынести в отдельную роль или расписание, чем поднимать лимит для всех интерактивных запросов.

Что лимит делает и чего не делает

Прерванный по таймауту запрос освобождает ресурсы, и это хорошо. Но пока он шёл, он уже потребовал процессорного времени и дискового ввода-вывода. Таймаут ограничивает длительность вреда, а не его интенсивность. Минута тяжёлого чтения в пике нагрузки — всё ещё минута тяжёлого чтения.

Основная база или реплика

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

Но реплика — не бесплатное решение и не волшебная кнопка. Её нужно развернуть и настроить; как это делается, подробно разобрано в статье про репликацию PostgreSQL на VPS. Здесь важнее другое: что реплика меняет в самой задаче доступа.

Отставание как часть договорённости

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

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

Есть и неочевидная неприятность: на физической реплике долгий запрос может конфликтовать с применением изменений из основного журнала. Когда реплика вынуждена выбирать между завершением чтения и применением обновлений, она либо откладывает применение (отставание растёт), либо прерывает запрос аналитика. Какое поведение будет у вашей системы, зависит от её настроек, и это стоит проверить на своей конфигурации, а не принимать на веру. Практический вывод: статементный таймаут на аналитической роли помогает и здесь, потому что короткие запросы реже мешают репликации.

Права на реплике

Ещё одна особенность: физическая реплика — копия основного сервера целиком, включая роли и их права. Роль аналитика, созданная на основном сервере, появится и на реплике, а вот изменить что-либо непосредственно на реплике нельзя: изменения роли надо делать на основном сервере. Параметры роли вроде statement_timeout тоже живут в системном каталоге и приезжают вместе с остальным. Отсюда удобство: настроили один раз, а аналитик подключается к реплике и получает те же ограничения.

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

Почему безобидный SELECT не безобиден

Теперь вернёмся к главному заблуждению: раз запрос ничего не меняет, он ничего не стоит. Проследим, что на самом деле происходит с сервером, когда аналитик запускает «просто выборку».

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

Второе — память. Сортировки и хеш-соединения используют рабочую память, а если её не хватает, данные сбрасываются на диск во временные файлы. Это замедляет сам запрос и добавляет дисковую нагрузку.

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

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

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

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

Как проверить, что всё работает как задумано

Настройку доступа стоит проверить глазами самой роли, а не владельца. Подключитесь под analyst_ro и сделайте три вещи.

1. Прочитайте reporting.sales_daily и убедитесь, что отчёт собирается из доступных полей.
2. Попробуйте обратиться к исходной таблице, например orders. Ожидаемый результат — отказ в доступе. Если запрос сработал, значит, у роли есть лишние права из группы или из схемы по умолчанию.
3. Запустите заведомо долгий запрос, например с искусственной задержкой, и убедитесь, что он прерывается по таймауту, а не висит бесконечно.

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

Где разместить реплику и сколько ресурсов ей нужно

Если вы решили вынести аналитику на отдельный сервер, реплике нужен собственный VPS. Заранее назвать подходящие параметры нельзя: всё зависит от размера данных, сложности отчётов и числа одновременных пользователей. Ориентиры для порядка величин такие. Реплика должна вместить копию данных с запасом на рост и временные файлы сортировок, а значит, дисковый объём стоит оценивать от размера базы, а не от размера отчёта. Процессор и память определяют, как быстро выполняются тяжёлые запросы и сколько их пройдёт параллельно.

Среди тарифов VPS MAATRIX есть варианты от Ba с 1 vCPU, 2 ГБ памяти и 40 ГБ NVMe до Djed с 16 vCPU, 32 ГБ и 320 ГБ. Эти ресурсы — описание конфигураций, а не обещание ёмкости: сколько запросов аналитики выдержит конкретный тариф, покажет только замер на ваших данных. На VPS предоставляется root-доступ, что нужно для установки и настройки PostgreSQL и репликации, а локации в публичном предложении — UK, US, RU и FR. Точную доступность образов и локаций проверяйте в форме заказа.

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

Как это выглядит в итоге

Вернёмся к отчёту по продажам. Аналитик подключается под своей ролью к реплике, открывает reporting.sales_daily и строит выручку по дням, категориям и городам. Он не видит адресов и телефонов покупателей, не может изменить ни одной записи, а запрос дольше заданного лимита прекращается сам. Отчёт показывает данные с небольшим известным отставанием, о котором он знает заранее, а утренняя нагрузка на приложение не зависит от его вечерних экспериментов.

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