Роль для чтения: права схемы и таблицы проверяются отдельно

Роли выдали SELECT на таблицу, а запрос всё равно заканчивается отказом в доступе. В PostgreSQL разрешение читать таблицу не заменяет право обращаться к её схеме. На небольшом примере создадим роль аналитика, выдадим необходимые права и убедимся, что читать она может, а добавлять строки — нет.

Роль и права для примера

CREATE SCHEMA demo_access;
CREATE TABLE demo_access.metrics (id integer PRIMARY KEY, value integer);
INSERT INTO demo_access.metrics VALUES (1, 12);
CREATE ROLE demo_reader NOLOGIN;
GRANT USAGE ON SCHEMA demo_access TO demo_reader;
GRANT SELECT ON demo_access.metrics TO demo_reader;
SET ROLE demo_reader;
SELECT * FROM demo_access.metrics;
RESET ROLE;

Команды настройки выполняет администратор тестовой базы, имеющий право создать роль и переключиться на неё. NOLOGIN делает роль набором полномочий, а не самостоятельной учётной записью для подключения. Для реального аналитика членство в такой роли назначают отдельной роли входа. Это позволяет менять набор прав без пересоздания личных учётных записей.

Проверяем разрешение и запрет

SELECT has_schema_privilege('demo_reader', 'demo_access', 'USAGE');
SELECT has_table_privilege('demo_reader', 'demo_access.metrics', 'SELECT');
SELECT has_table_privilege('demo_reader', 'demo_access.metrics', 'INSERT');
SET ROLE demo_reader;
-- Выполнить отдельно: ожидается отказ.
INSERT INTO demo_access.metrics VALUES (2, 99);
RESET ROLE;

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

Новые таблицы требуют отдельного решения

GRANT на одну таблицу не распространяется автоматически на будущие таблицы схемы. Для повторяющегося процесса создания объектов можно заранее спроектировать ALTER DEFAULT PRIVILEGES с учётом роли, которая действительно создаёт таблицы. Не путайте такие настройки с изменением прав уже существующих объектов. Добавьте в проверку создание новой тестовой таблицы от имени обычного владельца схемы. Это выявляет ситуацию, когда права настроены для административной роли, а миграции выполняет другая.

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

Источники и дальнейшее чтение

• PostgreSQL 12: роли
• PostgreSQL 12: GRANT