Read-only роль в PostgreSQL: доступ к данным без права что-то сломать

Аналитикам, BI-инструментам и отчётам часто нужен доступ к базе: посмотреть пользователей, посчитать заказы, собрать дашборд, проверить гипотезу. Но почти никогда им не нужно менять данные.

И вот здесь появляется правильная идея: сделать отдельную роль, которая умеет только читать. Она может выполнять SELECT, строить отчёты и подключаться из Metabase, Superset или другого BI-инструмента, но не может случайно удалить заказ, обновить статус платежа или испортить таблицу.

В PostgreSQL такая настройка делается через роли и права доступа. На первый взгляд всё просто: выдать SELECT — и готово. Но есть одна ловушка, из-за которой read-only доступ часто ломается после первой же миграции.

Разберём нормальный рабочий рецепт.

Зачем делать отдельную read-only роль

Допустим, у вас есть продакшен-база интернет-магазина. В ней лежат пользователи, заказы, платежи, товары и история операций.

Команде аналитики нужно отвечать на вопросы вроде:

SELECT country, count(*) AS users_count
FROM users
GROUP BY country
ORDER BY users_count DESC;

Или:

SELECT date_trunc('day', created_at) AS day,
       sum(amount) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY day
ORDER BY day;

Это обычные запросы на чтение. Они не меняют данные.

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

UPDATE orders
SET status = 'cancelled'
WHERE amount < 0;

DELETE FROM users
WHERE last_login_at IS NULL;

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

Групповая роль и конкретные пользователи

Обычно делают не одну роль на каждого аналитика, а общую групповую роль, например readonly.

Смысл такой:

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

Создадим групповую роль без права логина:

CREATE ROLE readonly NOLOGIN;

NOLOGIN означает, что под этой ролью нельзя подключиться к базе напрямую. Она нужна именно как контейнер прав.

Теперь создадим конкретного пользователя:

CREATE ROLE analyst_anna LOGIN PASSWORD 'change_me';

И добавим его в read-only группу:

GRANT readonly TO analyst_anna;

Теперь analyst_anna сможет пользоваться правами, которые мы выдадим роли readonly.

Шаг 1. Разрешаем подключение к базе

В PostgreSQL право на подключение к базе называется CONNECT.

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

Например, если база называется shop:

GRANT CONNECT ON DATABASE shop TO readonly;

Это ещё не даёт доступа к таблицам. Это только разрешает подключиться к самой базе.

Шаг 2. Даём доступ к схеме

В PostgreSQL таблицы живут внутри схем. Самая привычная схема по умолчанию называется public.

И здесь есть важный момент: права на таблицы и права на схему — это разные вещи.

Можно выдать SELECT на таблицу, но если у роли нет USAGE на схему, она всё равно не сможет нормально обратиться к таблице по имени.

Поэтому сначала открываем доступ к схеме:

GRANT USAGE ON SCHEMA public TO readonly;

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

Проще говоря:

• USAGE ON SCHEMA — можно зайти в «комнату»;
• SELECT ON TABLE — можно читать конкретные «документы» внутри этой комнаты.

Шаг 3. Даём SELECT на уже существующие таблицы

Теперь выдадим право на чтение всех таблиц, которые уже есть в схеме public:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

После этого пользователь из роли readonly сможет читать данные:

SELECT country, count(*) AS users_count
FROM users
GROUP BY country
ORDER BY users_count DESC;

Но не сможет их менять:

UPDATE orders
SET status = 'cancelled'
WHERE amount < 0;

PostgreSQL ответит ошибкой доступа, потому что у роли есть только SELECT, но нет UPDATE.

Точно так же не сработают:

INSERT INTO orders (user_id, amount, status)
VALUES (10, 500, 'paid');

DELETE FROM orders
WHERE status = 'cancelled';

Для read-only роли это именно то поведение, которое нам нужно.

А что с представлениями и последовательностями

Команда:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

в PostgreSQL работает не только для обычных таблиц, но и для представлений, если они уже есть в этой схеме.

Но последовательности — это отдельный тип объектов. Они часто используются для генерации id:

CREATE SEQUENCE order_id_seq;

В большинстве аналитических сценариев права на последовательности вообще не нужны. Аналитик читает таблицы, а не генерирует новые id.

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

GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;

Важно не путать: nextval() — это не просто чтение. Он двигает последовательность вперёд, то есть меняет её состояние. Для строгого read-only доступа обычно не нужно разрешать такие операции.

Главная ловушка: новые таблицы не получат права автоматически

Вот место, где чаще всего ошибаются.

Команда:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

работает только с теми таблицами, которые уже существуют в момент выполнения команды.

Это не правило на будущее. Это разовая выдача прав на текущий набор таблиц.

Представим, что сегодня у вас есть таблицы:

users
orders
payments

Вы выдали на них SELECT, и аналитика всё видит.

А завтра после миграции появляется новая таблица:

CREATE TABLE employees (
  id         bigint PRIMARY KEY,
  name       text NOT NULL,
  manager_id bigint,
  dept       text,
  salary     numeric(12,2)
);

Аналитик пробует выполнить запрос:

SELECT dept, avg(salary)
FROM employees
GROUP BY dept;

И получает ошибку:

permission denied for table employees

На первый взгляд странно: ведь мы же дали SELECT ON ALL TABLES.

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

Решение: ALTER DEFAULT PRIVILEGES

Чтобы новые таблицы автоматически получали права для read-only роли, нужны default privileges.

Это правило для будущих объектов.

Например:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly;

Теперь все новые таблицы, созданные в схеме public, будут автоматически получать SELECT для роли readonly.

Но здесь есть ещё один важный нюанс.

Default privileges привязаны не просто к схеме, а к роли, которая создаёт объекты.

Если миграции запускает пользователь app_owner, лучше настроить правило явно для него:

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly;

Именно это часто забывают.

Например:

• вы настроили default privileges под своим админским пользователем;
• а таблицы в продакшене создаёт app_owner;
• в итоге новые таблицы снова появляются без доступа для аналитики.

Поэтому всегда смотрите, кто реально создаёт таблицы: приложение, мигратор, DBA-пользователь или отдельная роль владельца схемы.

Если таблицы создают несколько разных ролей, default privileges нужно настроить для каждой из них.

Правильный порядок настройки

Хороший порядок такой:

CREATE ROLE readonly NOLOGIN;

GRANT CONNECT ON DATABASE shop TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

Почему так?

ALTER DEFAULT PRIVILEGES закрывает будущее: новые таблицы будут получать права автоматически.

GRANT SELECT ON ALL TABLES закрывает прошлое: уже существующие таблицы тоже становятся доступными.

Вместе эти две команды дают нормальную read-only роль, которая не ломается после следующего релиза.

Отдельный пользователь для BI-инструмента

Для BI-инструментов лучше создавать отдельного пользователя, а не подключать Metabase или Superset под личным аккаунтом аналитика.

Например:

CREATE ROLE bi_reader LOGIN PASSWORD 'strong_password_here';
GRANT readonly TO bi_reader;

Теперь BI-инструмент подключается под bi_reader.

Это удобно по нескольким причинам:

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

Как ограничить тяжёлые запросы

Read-only доступ защищает данные от изменения, но не защищает базу от тяжёлых запросов.

Аналитик может случайно запустить что-то вроде:

SELECT *
FROM orders o
JOIN events e ON e.user_id = o.user_id;

Если таблицы большие, такой запрос может надолго нагрузить базу.

Поэтому для BI-пользователя полезно поставить ограничение по времени выполнения запроса:

ALTER ROLE bi_reader IN DATABASE shop
  SET statement_timeout = '30s';

Обратите внимание: таймаут лучше ставить на ту роль, под которой реально происходит подключение к базе.

Если readonly — это NOLOGIN группа, а подключается пользователь bi_reader, то настройку нужно ставить именно на bi_reader.

Ещё можно добавить предохранитель:

ALTER ROLE bi_reader IN DATABASE shop
  SET default_transaction_read_only = on;

Это не замена нормальным правам через GRANT, но дополнительная защита от случайных операций записи.

Главная защита всё равно должна быть на уровне прав: у роли не должно быть INSERT, UPDATE, DELETE, TRUNCATE и других лишних разрешений.

Если нужно открыть не все данные

Иногда аналитикам нельзя показывать сырые таблицы целиком.

Например, в таблице orders есть служебные статусы, антифродовые метки или персональные данные. В таком случае лучше не выдавать доступ к таблице напрямую, а сделать отдельное представление.

Например:

CREATE VIEW orders_public AS
SELECT id,
       user_id,
       amount,
       status,
       created_at
FROM orders
WHERE status <> 'fraud';

И выдать доступ только к этому представлению:

GRANT SELECT ON orders_public TO readonly;

Тогда аналитик сможет читать:

SELECT status, count(*)
FROM orders_public
GROUP BY status;

Но не будет работать напрямую с полной таблицей orders, если вы не выдали на неё права.

Представления удобны, когда нужно:

• скрыть часть колонок;
• отфильтровать строки;
• дать более понятную структуру для отчётов;
• защитить чувствительные данные.

Например, вместо того чтобы отдавать таблицу пользователей целиком, можно сделать представление без email и телефона:

CREATE VIEW users_for_analytics AS
SELECT id,
       country,
       created_at,
       last_login_at
FROM users;

И уже на него выдать SELECT.

Как быстро проверить, что роль действительно read-only

После настройки полезно не просто поверить командам, а проверить роль на практике.

Подключитесь под пользователем, который входит в readonly, и попробуйте выполнить обычный запрос:

SELECT count(*)
FROM users;

Он должен работать.

Теперь попробуйте запись:

UPDATE users
SET country = 'Unknown'
WHERE country IS NULL;

Она должна упасть с ошибкой доступа.

После этого проверьте главный сценарий: новую таблицу.

Создайте тестовую таблицу от имени той роли, которая обычно выполняет миграции:

CREATE TABLE test_readonly_access (
  id bigint PRIMARY KEY,
  name text NOT NULL
);

И попробуйте прочитать её под read-only пользователем:

SELECT *
FROM test_readonly_access;

Если запрос работает, значит default privileges настроены правильно.

Если нет — почти наверняка правило ALTER DEFAULT PRIVILEGES было задано не для той роли, которая создаёт таблицы.

Чем это отличается от MySQL и ClickHouse

В MySQL подход похожий, но детали другие.

Там база и схема обычно воспринимаются как одно и то же, поэтому отдельного USAGE ON SCHEMA, как в PostgreSQL, нет.

Пример для MySQL:

GRANT SELECT ON shop.* TO 'readonly'@'%';

Шаблон shop.* означает: дать SELECT на объекты внутри базы shop. Для новых таблиц отдельный аналог ALTER DEFAULT PRIVILEGES обычно не нужен.

В ClickHouse тоже можно выдать чтение на все таблицы базы:

GRANT SELECT ON shop.* TO readonly;

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

Коротко: рецепт хорошей read-only роли

Для PostgreSQL нужно запомнить четыре вещи.

Первая: лучше делать групповую роль без логина:

CREATE ROLE readonly NOLOGIN;

Вторая: пользователи добавляются в эту роль:

GRANT readonly TO analyst_anna;

Третья: нужно дать доступ к базе, схеме и существующим таблицам:

GRANT CONNECT ON DATABASE shop TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

Четвёртая: нужно не забыть про будущие таблицы:

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly;

Именно последний пункт чаще всего пропускают. В результате всё работает сегодня, но ломается завтра, когда миграция создаёт новую таблицу.

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