CREATE ROLE в PostgreSQL: пользователи, группы и наследование прав

В PostgreSQL нет отдельной сущности «пользователь» и отдельной сущности «группа».

Есть одна универсальная сущность — роль.

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

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

Разница не в типе объекта, а в атрибутах.

Например:

CREATE ROLE analyst LOGIN PASSWORD 'secret';

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

А вот так:

CREATE ROLE app_readonly NOLOGIN;

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

Разберёмся, как это работает и почему в PostgreSQL почти всегда удобнее выдавать права не людям напрямую, а групповым ролям.

Роль в PostgreSQL — это и пользователь, и группа

В PostgreSQL роль может выполнять две задачи.

1. Роль-пользователь

Это роль, которая умеет логиниться в базу.

Например:

CREATE ROLE maria LOGIN PASSWORD 'secret';

Атрибут LOGIN означает:

под этой ролью можно подключиться к PostgreSQL.

Такую роль обычно создают для:

• разработчика;
• аналитика;
• приложения;
• ETL-процесса;
• сервиса;
• администратора.

Например:

CREATE ROLE app_user LOGIN PASSWORD 'strong_password';

Это может быть технический пользователь приложения.

2. Роль-группа

Это роль, которая сама не подключается к базе, но хранит набор прав.

Например:

CREATE ROLE readonly NOLOGIN;

Атрибут NOLOGIN означает:

под этой ролью нельзя подключиться к серверу.

Зато ей можно выдать права:

GRANT SELECT ON users, orders TO readonly;

А потом добавить в неё конкретных пользователей:

GRANT readonly TO maria;

Теперь maria получает права роли readonly.

Такой подход намного удобнее, чем выдавать права каждому пользователю отдельно.

CREATE USER — это почти то же самое, что CREATE ROLE LOGIN

В PostgreSQL есть команда:

CREATE USER analyst PASSWORD 'secret';

Новичку может показаться, что USER и ROLE — это разные сущности.

Но в PostgreSQL CREATE USER — это фактически удобный синоним для создания роли с возможностью логина.

То есть:

CREATE USER analyst PASSWORD 'secret';

по смыслу близко к:

CREATE ROLE analyst LOGIN PASSWORD 'secret';

Под капотом всё равно создаётся роль.

Поэтому в PostgreSQL чаще полезно думать так:

есть роли; некоторые роли могут логиниться, а некоторые нет.

Зачем нужны роли-группы

Представьте, что у вас есть 10 аналитиков.

Всем им нужно читать таблицы users, orders, employees.

Плохой подход — выдавать права каждому напрямую:

GRANT SELECT ON users TO maria;
GRANT SELECT ON orders TO maria;
GRANT SELECT ON employees TO maria;

GRANT SELECT ON users TO pavel;
GRANT SELECT ON orders TO pavel;
GRANT SELECT ON employees TO pavel;

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

Но если их десять, двадцать или сто, права быстро превращаются в хаос.

Лучше создать групповую роль:

CREATE ROLE analysts NOLOGIN;

Выдать права ей:

GRANT SELECT ON users, orders, employees TO analysts;

И добавлять людей в эту роль:

GRANT analysts TO maria;
GRANT analysts TO pavel;

Теперь если нужно добавить доступ к новой таблице, вы выдаёте право один раз:

GRANT SELECT ON payments TO analysts;

И все аналитики получают его автоматически через групповую роль.

Членство в роли: GRANT role TO member

Чтобы добавить одну роль в другую, используется тот же GRANT.

Например:

GRANT readonly TO maria;

Это означает:

роль maria становится членом роли readonly.

Если readonly имеет право читать таблицу orders, то maria тоже сможет её читать.

Пример:

CREATE ROLE readonly NOLOGIN;

GRANT SELECT ON orders TO readonly;

CREATE ROLE maria LOGIN PASSWORD 'secret';

GRANT readonly TO maria;

Теперь maria может выполнить:

SELECT *
FROM orders;

если у неё также есть доступ к нужной схеме.

Не забывайте про USAGE на схему

В PostgreSQL таблицы находятся внутри схем.

Например, public.orders.

Чтобы роль могла обратиться к таблице, часто нужно не только право на саму таблицу, но и право пользоваться схемой:

GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON orders TO readonly;

Без:

GRANT USAGE ON SCHEMA public TO readonly;

можно получить ошибку доступа к схеме, даже если SELECT на таблицу выдали.

Поэтому для роли только на чтение часто делают так:

CREATE ROLE readonly NOLOGIN;

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

А потом добавляют пользователей:

GRANT readonly TO maria;

INHERIT: автоматическое наследование прав

В PostgreSQL у роли есть атрибут INHERIT.

Он означает:

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

По умолчанию роли создаются с INHERIT.

Например:

CREATE ROLE analyst LOGIN PASSWORD 'secret';
CREATE ROLE app_readonly NOLOGIN;

GRANT USAGE ON SCHEMA public TO app_readonly;
GRANT SELECT ON users, orders TO app_readonly;

GRANT app_readonly TO analyst;

Теперь analyst автоматически может читать users и orders.

Ему не нужно вручную переключаться в роль app_readonly.

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

SELECT *
FROM orders;

если право пришло через app_readonly.

NOINHERIT: права есть, но их нужно включить через SET ROLE

Иногда автоматическое наследование не нужно.

Например, у вас есть роль для деплоя, которая обычно должна иметь мало прав, но иногда может временно «надеть» роль с расширенными доступами.

Тогда можно создать роль с NOINHERIT:

CREATE ROLE deploy_bot LOGIN NOINHERIT PASSWORD 'secret';

Добавим её в группу:

GRANT app_readonly TO deploy_bot;

Теперь deploy_bot формально является членом app_readonly, но не использует её права автоматически.

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

Чтобы временно использовать права группы, нужно выполнить:

SET ROLE app_readonly;

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

SELECT *
FROM orders;

А потом вернуться обратно:

RESET ROLE;

То есть NOINHERIT делает доступ более явным:

роль имеет членство, но должна сознательно переключиться через SET ROLE.

SET ROLE простыми словами

Команда:

SET ROLE app_readonly;

означает:

временно выполнять действия как роль app_readonly.

Это похоже на то, как человек надевает рабочую каску только тогда, когда заходит на стройку.

Обычно он просто deploy_bot. Но на время конкретной операции он переключается в app_readonly.

После:

RESET ROLE;

роль возвращается обратно.

Это удобно для ситуаций, где повышенные права нужны не всегда, а только для отдельных операций.

Важная ловушка: не все атрибуты наследуются

Через членство в роли можно получить обычные привилегии:

• SELECT на таблицу;
• INSERT;
• UPDATE;
• DELETE;
• USAGE на схему;
• USAGE на sequence.

Но важные системные атрибуты роли через членство не становятся обычными правами в стиле SELECT.

Например, такие атрибуты требуют особой осторожности:

SUPERUSER
CREATEDB
CREATEROLE
LOGIN
REPLICATION
BYPASSRLS

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

Например, если есть роль:

CREATE ROLE admin_group SUPERUSER NOLOGIN;

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

Практическое правило:

обычные права на объекты удобно хранить в групповых ролях; опасные системные атрибуты вроде SUPERUSER не раздавайте через сложные цепочки без крайней необходимости.

Основные атрибуты роли

При создании роли можно указать разные атрибуты.

LOGIN / NOLOGIN

Можно ли подключаться к серверу:

CREATE ROLE analyst LOGIN PASSWORD 'secret';

или:

CREATE ROLE readonly NOLOGIN;

SUPERUSER / NOSUPERUSER

Суперпользователь обходит почти все проверки прав.

CREATE ROLE dba LOGIN SUPERUSER PASSWORD 'secret';

Это очень опасное право.

Суперпользователь может сделать почти всё, поэтому его дают только администраторам платформы или базы.

Обычным приложениям, аналитикам и разработчикам SUPERUSER почти никогда не нужен.

CREATEDB

Разрешает создавать базы данных:

CREATE ROLE etl_owner LOGIN CREATEDB PASSWORD 'secret';

Если право больше не нужно, его можно убрать:

ALTER ROLE etl_owner NOCREATEDB;

CREATEROLE

Разрешает создавать и изменять роли:

CREATE ROLE security_admin LOGIN CREATEROLE PASSWORD 'secret';

Это тоже сильное право. Если роль может управлять другими ролями, она может влиять на доступы в базе.

PASSWORD

Пароль для login-роли:

CREATE ROLE analyst LOGIN PASSWORD 'secret';

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

VALID UNTIL

Можно задать срок действия пароля:

CREATE ROLE temp_user LOGIN
    PASSWORD 'secret'
    VALID UNTIL '2026-12-31';

После этой даты пароль перестанет работать.

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

CONNECTION LIMIT

Можно ограничить количество одновременных подключений:

ALTER ROLE analyst CONNECTION LIMIT 5;

Это полезно, если вы не хотите, чтобы один пользователь или сервис открыл слишком много соединений к базе.

ALTER ROLE: изменить роль после создания

Роль не обязательно создавать идеально с первого раза.

Многие параметры можно поменять позже через ALTER ROLE.

Например, добавить лимит соединений:

ALTER ROLE analyst CONNECTION LIMIT 5;

Убрать право создавать базы:

ALTER ROLE etl_owner NOCREATEDB;

Изменить пароль:

ALTER ROLE analyst PASSWORD 'new_secret';

Запретить логин:

ALTER ROLE analyst NOLOGIN;

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

Практичная модель: пользователи отдельно, права отдельно

Хорошая модель доступа обычно строится так:

1. Создаём групповые роли под обязанности.
2. Выдаём права групповым ролям.
3. Создаём login-роли для людей и сервисов.
4. Добавляем login-роли в нужные группы.

Например:

CREATE ROLE readers NOLOGIN;
CREATE ROLE writers NOLOGIN;

Выдаём права:

GRANT USAGE ON SCHEMA public TO readers;
GRANT SELECT ON users, orders TO readers;

GRANT USAGE ON SCHEMA public TO writers;
GRANT SELECT, INSERT, UPDATE ON orders TO writers;

Если те, кто пишет, должны ещё и читать, можно сделать одну групповую роль членом другой:

GRANT readers TO writers;

Теперь writers получает права readers.

Создаём людей:

CREATE ROLE maria LOGIN PASSWORD 'secret';
CREATE ROLE pavel LOGIN PASSWORD 'secret';

Выдаём членство:

GRANT readers TO maria;
GRANT writers TO pavel;

Теперь:

• maria может читать;
• pavel может читать и писать.

Если Павел больше не должен писать, достаточно одной команды:

REVOKE writers FROM pavel;

Не нужно вспоминать, на какие таблицы ему когда-то выдавали права.

Почему не стоит выдавать права напрямую людям

Плохой подход:

GRANT SELECT ON users TO maria;
GRANT SELECT ON orders TO maria;
GRANT INSERT ON orders TO maria;

GRANT SELECT ON users TO pavel;
GRANT SELECT ON orders TO pavel;
GRANT INSERT ON orders TO pavel;

Через месяц становится трудно понять:

• почему у Марии есть именно эти права;
• чем Мария отличается от Павла;
• кому ещё выдали похожий доступ;
• что нужно отозвать, если человек сменил команду.

Лучше так:

CREATE ROLE order_managers NOLOGIN;

GRANT USAGE ON SCHEMA public TO order_managers;
GRANT SELECT, INSERT, UPDATE ON orders TO order_managers;

GRANT order_managers TO maria;
GRANT order_managers TO pavel;

Теперь роль называется по обязанности, а не по имени человека.

Это легче сопровождать и легче проверять.

Цепочки ролей: полезно, но не усложняйте слишком сильно

PostgreSQL позволяет строить цепочки ролей.

Например:

GRANT readers TO writers;
GRANT writers TO support_leads;
GRANT support_leads TO pavel;

Так pavel может получить права через несколько уровней.

Это мощный механизм, но с ним легко перестараться.

Если модель доступа нельзя объяснить за минуту, она уже слишком сложная.

Плохие признаки:

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

Лучше держать структуру простой:

люди и сервисы -> группы по обязанностям -> права на объекты

Как посмотреть роли и членство

В psql можно посмотреть роли командой:

\du

Она покажет список ролей и их атрибуты.

Чтобы посмотреть членство ролей через SQL, можно использовать системный каталог:

SELECT
    roleid::regrole AS role_name,
    member::regrole AS member_name,
    grantor::regrole AS grantor_name
FROM pg_auth_members;

Так можно увидеть, какая роль в какую входит.

Проверить, есть ли у роли право на таблицу, можно так:

SELECT has_table_privilege('maria', 'public.orders', 'SELECT');

Если вернётся true, значит maria может читать таблицу public.orders.

Частые ошибки с CREATE ROLE

Ошибка 1. Создать роль-группу с LOGIN

Плохо:

CREATE ROLE readonly LOGIN PASSWORD 'secret';

Если это группа для прав, ей не нужен логин.

Лучше:

CREATE ROLE readonly NOLOGIN;

Ошибка 2. Выдавать права людям напрямую

Плохо:

GRANT SELECT ON orders TO maria;
GRANT SELECT ON orders TO pavel;

Лучше:

CREATE ROLE readonly NOLOGIN;

GRANT SELECT ON orders TO readonly;

GRANT readonly TO maria;
GRANT readonly TO pavel;

Ошибка 3. Забыть USAGE на схему

Есть:

GRANT SELECT ON orders TO readonly;

Но нет:

GRANT USAGE ON SCHEMA public TO readonly;

В итоге доступ может не работать.

Хороший набор:

GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON orders TO readonly;

Ошибка 4. Давать SUPERUSER «чтобы точно работало»

Плохо:

CREATE ROLE app_user LOGIN SUPERUSER PASSWORD 'secret';

Приложению почти никогда не нужен SUPERUSER.

Лучше выдать конкретные права:

CREATE ROLE app_user LOGIN PASSWORD 'secret';

GRANT app_read TO app_user;
GRANT app_write TO app_user;

SUPERUSER — это не способ быстро починить доступы. Это способ случайно дать приложению полный контроль над базой.

Ошибка 5. Делать слишком сложную иерархию ролей

Если доступ выглядит так:

junior_read -> analyst_base -> reporting_team -> data_users -> temp_access -> maria

разобраться в нём будет тяжело.

Лучше иметь простые роли:

readonly
reporting
app_write
support
migration_runner

и понятное членство.

Пример полной настройки доступа

Допустим, у нас есть:

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

Создадим групповые роли:

CREATE ROLE app_read NOLOGIN;
CREATE ROLE app_write NOLOGIN;
CREATE ROLE analyst_read NOLOGIN;
CREATE ROLE migration_runner NOLOGIN;

Выдадим доступ к схеме:

GRANT USAGE ON SCHEMA public TO app_read;
GRANT USAGE ON SCHEMA public TO app_write;
GRANT USAGE ON SCHEMA public TO analyst_read;

Права на чтение:

GRANT SELECT ON users, orders, employees TO app_read;
GRANT SELECT ON users, orders TO analyst_read;

Права на запись для приложения:

GRANT INSERT, UPDATE ON orders TO app_write;
GRANT UPDATE ON users TO app_write;

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

GRANT app_read TO app_write;

Создадим login-роли:

CREATE ROLE app_user LOGIN PASSWORD 'strong_app_password';
CREATE ROLE alice LOGIN PASSWORD 'secret';
CREATE ROLE migrator LOGIN PASSWORD 'migration_secret';

Добавим их в группы:

GRANT app_write TO app_user;
GRANT analyst_read TO alice;
GRANT migration_runner TO migrator;

Теперь:

• app_user получает права приложения;
• alice получает аналитический доступ;
• migrator можно отдельно настроить под миграции.

Такая схема читается намного лучше, чем разрозненные права на каждого пользователя.

MySQL и ClickHouse: идея похожая, детали другие

В PostgreSQL CREATE ROLE универсален: им можно создать и login-роль, и роль-группу.

В MySQL модель другая. Там есть отдельные команды CREATE USER и CREATE ROLE.

В MySQL 8 роли уже есть, но их нужно правильно активировать, например через SET ROLE или SET DEFAULT ROLE, в зависимости от сценария.

В ClickHouse тоже есть пользователи, роли и GRANT, но модель прав отличается от PostgreSQL. Поэтому общая идея «права лучше группировать через роли» сохраняется, а конкретный синтаксис нужно проверять под нужную СУБД.

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

Короткая шпаргалка

Создать пользователя:

CREATE ROLE analyst LOGIN PASSWORD 'secret';

Создать группу для прав:

CREATE ROLE readonly NOLOGIN;

Создать пользователя через CREATE USER:

CREATE USER maria PASSWORD 'secret';

Выдать права группе:

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

Добавить пользователя в группу:

GRANT readonly TO maria;

Создать роль без автоматического наследования:

CREATE ROLE deploy_bot LOGIN NOINHERIT PASSWORD 'secret';

Временно переключиться в роль:

SET ROLE readonly;

Вернуться обратно:

RESET ROLE;

Изменить роль:

ALTER ROLE analyst CONNECTION LIMIT 5;

Запретить логин:

ALTER ROLE analyst NOLOGIN;

Убрать пользователя из группы:

REVOKE readonly FROM maria;

Посмотреть роли в psql:

\du

Проверить право на таблицу:

SELECT has_table_privilege('maria', 'public.orders', 'SELECT');

Главное правило

В PostgreSQL всё строится вокруг ролей.

Роль с LOGIN — это пользователь или сервис, который может подключаться к базе.

Роль с NOLOGIN — это удобная группа для хранения прав.

Хорошая модель доступа обычно выглядит так:

1. Создаём групповые роли по обязанностям: readonly, app_write, analyst_read, support.
2. Выдаём права именно этим групповым ролям.
3. Создаём login-роли для людей и сервисов.
4. Добавляем login-роли в нужные группы через GRANT role TO user.
5. Не раздаём SUPERUSER без крайней необходимости.
6. Держим иерархию ролей простой и понятной.

Запомните коротко:

права — на группы, людей — в группы.

Так нового человека можно подключить одной командой:

GRANT readonly TO maria;

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

REVOKE readonly FROM maria;

Если модель ролей можно объяснить за минуту, её будет проще поддерживать, проверять и безопасно развивать.