Read-only Role in PostgreSQL: Correct Grants for Analytics and BI

Аналитикам и BI-инструментам нужен доступ к данным, но не право их менять. В PostgreSQL это решается отдельной ролью с правом только на чтение. Ниже — рабочий рецепт и главная ловушка, на которой спотыкаются почти все.

Создаём роль и даём доступ к схеме

Роль для чтения обычно делают как групповую (без логина), а конкретных пользователей включают в неё. Это удобно: гранты живут на группе, а людей добавляют и убирают по одному.

CREATE ROLE readonly NOLOGIN;

CREATE ROLE analyst_anna LOGIN PASSWORD 'change_me';
GRANT readonly TO analyst_anna;

Первый и неочевидный шаг — USAGE на схему. Без него роль не увидит ни одной таблицы внутри public, даже если на сами таблицы выданы права.

GRANT USAGE ON SCHEMA public TO readonly;

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

SELECT на существующие таблицы

Теперь выдаём право на чтение всех таблиц, которые уже есть в схеме. Удобно — одной командой через ALL TABLES.

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

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

-- works for readonly
SELECT country, count(*) AS users_cnt
FROM users
GROUP BY country
ORDER BY users_cnt DESC;

-- fails: permission denied
UPDATE orders SET status = 'cancelled' WHERE amount < 0;

Если в схеме есть представления и нужно читать последовательности (например, для отчётов по nextval), права на них выдаются отдельно:

GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;

Ловушка: новые таблицы недоступны

Вот где ломается большинство настроек. GRANT ... ON ALL TABLES — это разовый снимок: он выдаёт права только на таблицы, существующие в момент выполнения. Создайте завтра employees — и аналитик получит ошибку доступа.

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

-- readonly gets: permission denied for table employees
SELECT dept, avg(salary) FROM employees GROUP BY dept;

Решение — ALTER DEFAULT PRIVILEGES. Это правило «на будущее»: для всех таблиц, которые будут созданы дальше, автоматически выдавать SELECT нашей роли.

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

Важно: default privileges привязаны к роли, которая создаёт объекты. Правило выше срабатывает для таблиц, создаваемых текущей ролью. Если миграции запускает другой пользователь (например, app_owner), задайте правило явно: ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public GRANT SELECT ON TABLES TO readonly;. Иначе новые таблицы снова выпадут из доступа.

Правильный порядок такой: сначала ALTER DEFAULT PRIVILEGES для будущих таблиц, потом GRANT ... ON ALL TABLES для уже существующих. Вместе они закрывают и прошлое, и будущее.

Доступ для аналитики и BI

BI-инструменты (Metabase, Superset, Looker) подключаются под этой ролью и спокойно строят дашборды — записать в базу они физически не смогут. Чтобы ограничить нагрузку, полезно выставить лимит времени на запрос:

ALTER ROLE readonly SET statement_timeout = '30s';

Если нужен доступ только к части данных, не открывайте сырые таблицы целиком — отдайте представление и выдайте SELECT на него:

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;

Отличия в других СУБД

• MySQL: схемы и базы — это одно и то же, отдельного USAGE ON SCHEMA нет. Read-only роль создают так: GRANT SELECT ON shop.* TO 'readonly'@'%';. Права на будущие таблицы работают автоматически благодаря шаблону shop.* — отдельный аналог ALTER DEFAULT PRIVILEGES не нужен.
• ClickHouse: используется GRANT SELECT ON shop.* TO readonly;, а ограничения чаще задаются через профили и квоты (readonly = 1), а не через объектные гранты.

Запомните главное: роль без логина для группы, USAGE на схему, SELECT на существующие таблицы и ALTER DEFAULT PRIVILEGES для будущих. Пропустите последний шаг — и доступ тихо сломается на следующей миграции.

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