В postgresql нет отдельной сущности "пользователь" и отдельной "группа" на уровне реализации - есть одна универсальная сущность под названием роль. Роль может входить под именем в систему, владеть таблицами, выдавать права другим ролям и одновременно сама быть контейнером для других ролей. Этот урок про то, как создавать учётки, какими атрибутами они управляются, чем CREATE USER отличается от CREATE ROLE, как устроено членство и групповые роли, как хранятся пароли с алгоритмом SCRAM и что за магическая роль PUBLIC раздаёт права всем подряд. Разберёмся, как навести порядок в учётках так, чтобы потом не было мучительно больно при аудите доступа.
Как это работает
Роль - это запись в общем для всего кластера каталоге pg_authid (видна через представление pg_roles). Общий значит, что роли живут не внутри базы, а на уровне всего экземпляра сервера, и одна и та же роль работает во всех базах кластера. Поэтому удалить базу можно, а роли при этом останутся.
Разница между ролью и пользователем чисто косметическая. Команда CREATE USER - это просто CREATE ROLE с неявно добавленным атрибутом LOGIN, то есть с правом подключаться к серверу. CREATE ROLE без LOGIN создаёт роль, под которой войти нельзя, но которую удобно использовать как групповую: накидать на неё прав и раздать членство реальным учёткам.
Поведение роли определяется набором атрибутов. LOGIN разрешает вход. SUPERUSER снимает вообще все проверки прав - суперпользователь может всё, включая обход политик безопасности строк, поэтому раздавать его направо и налево опасно. CREATEDB разрешает создавать базы. CREATEROLE разрешает создавать и менять другие роли (но в pg 15 без права раздавать суперпользователя). Ещё есть REPLICATION для подключений потоковой репликации, CONNECTION LIMIT для ограничения числа сессий и VALID UNTIL для срока годности пароля.
Членство - это отдельный механизм. Когда роль A сделана членом роли B, она получает доступ к привилегиям B. Дальше важна тонкость: если у групповой роли стоит атрибут INHERIT (он по умолчанию), член автоматически пользуется правами группы без лишних действий. Если же стоит NOINHERIT, права группы надо явно "надеть" командой SET ROLE. Это даёт модель, похожую на sudo: обычно ты под своими правами, а к мощным переключаешься осознанно.
Пароли в современном postgresql хранятся не в открытом виде и не примитивным md5, а по схеме SCRAM-SHA-256. При проверке пароль не передаётся по сети как есть, стороны обмениваются доказательствами знания пароля. За выбор алгоритма отвечает параметр password_encryption, а кто и как подключается - конфиг pg_hba.conf, где для метода аутентификации указывают scram-sha-256.
SQL и примеры
Создаём групповую роль (без входа) и обычную учётку с входом и паролем:
Код: Выделить всё
-- групповая роль: только контейнер прав, войти под ней нельзя
CREATE ROLE bookings_ro NOLOGIN;
-- учётка приложения: вход, пароль, лимит соединений, срок годности
CREATE ROLE app_reader LOGIN
PASSWORD 'StrongPass#2026'
CONNECTION LIMIT 20
VALID UNTIL '2026-12-31';
Код: Выделить всё
GRANT USAGE ON SCHEMA bookings TO bookings_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA bookings TO bookings_ro;
-- чтобы будущие таблицы тоже были доступны
ALTER DEFAULT PRIVILEGES IN SCHEMA bookings
GRANT SELECT ON TABLES TO bookings_ro;
-- делаем app_reader членом группы: права придут по наследованию
GRANT bookings_ro TO app_reader;
Код: Выделить всё
SELECT f.departure_airport, count(*) AS flights
FROM bookings.flights f
GROUP BY f.departure_airport
ORDER BY flights DESC
LIMIT 5;
Код: Выделить всё
ALTER ROLE app_reader CONNECTION LIMIT 50;
ALTER ROLE app_reader PASSWORD 'NewStrongPass#2026';
ALTER ROLE app_reader VALID UNTIL 'infinity'; -- снять срок
Код: Выделить всё
-- список ролей с атрибутами
\du
-- то же через каталог
SELECT rolname, rolsuper, rolcreatedb, rolcanlogin, rolconnlimit
FROM pg_roles
WHERE rolname IN ('app_reader', 'bookings_ro');
-- кто в какой группе состоит
SELECT r.rolname AS member, g.rolname AS group_role
FROM pg_auth_members m
JOIN pg_roles r ON r.oid = m.member
JOIN pg_roles g ON g.oid = m.roleid;
Код: Выделить всё
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE bookings FROM PUBLIC;
Код: Выделить всё
REASSIGN OWNED BY app_reader TO bookings_ro; -- передать владение
DROP OWNED BY app_reader; -- снять оставшиеся права
DROP ROLE app_reader;
- Думают, что USER и ROLE - разные сущности. Нет, USER это ROLE с LOGIN. Путаница приводит к попыткам "сделать из группы пользователя".
- Создали групповую роль, выдали ей SELECT, а член группы всё равно не видит таблицы - забыли, что NOINHERIT требует явного SET ROLE, либо у самой группы нет USAGE на схему.
- GRANT SELECT ON ALL TABLES выдаёт права только на существующие на момент команды таблицы. Новые таблицы прав не получат - нужен ALTER DEFAULT PRIVILEGES.
- Раздают SUPERUSER приложению "чтобы не возиться с правами". Это дыра: суперпользователь обходит RLS и любые ограничения.
- В pg 16 поведение CREATEROLE и членства поменяли: появились опции WITH ADMIN/INHERIT/SET при GRANT роли, а член-создатель больше не получает автоматически полный контроль над чужими ролями. Скрипты с pg 15 могут вести себя иначе.
- Оставили в pg_hba.conf метод md5 или trust. md5 устарел, trust пускает без пароля. Для postgresql для 1с и любых боевых систем - только scram-sha-256.
- Пытаются DROP ROLE, а он падает с ошибкой о зависимостях - не сделали REASSIGN OWNED / DROP OWNED.
- Шаг 1. Создайте групповую роль analytics_ro с NOLOGIN и учётку analyst1 с LOGIN, паролем и CONNECTION LIMIT 5.
- Шаг 2. Выдайте analytics_ro права USAGE на схему bookings и SELECT на все её таблицы, добавьте ALTER DEFAULT PRIVILEGES.
- Шаг 3. Сделайте analyst1 членом analytics_ro через GRANT.
- Шаг 4. Подключитесь как analyst1 (psql -U analyst1) и выполните SELECT count(*) FROM bookings.tickets - убедитесь, что чтение работает, а INSERT в любую таблицу падает с отказом в доступе.
- Шаг 5. Через \du и pg_auth_members посмотрите атрибуты и членство analyst1.
- Шаг 6. Снимите у PUBLIC лишние права на базе и проверьте, что analyst1 по-прежнему подключается (если членство даёт CONNECT) или нет.
- Шаг 7. Переназначьте владение через REASSIGN OWNED и удалите analyst1 командой DROP ROLE.
- Чем CREATE USER отличается от CREATE ROLE и почему в каталоге они неразличимы?
- Что произойдёт с правами члена группы при NOINHERIT и как ими воспользоваться?
- Зачем нужен ALTER DEFAULT PRIVILEGES, если уже выдан GRANT ON ALL TABLES?
- Почему scram-sha-256 безопаснее md5 при аутентификации?
- Что такое PUBLIC и какое важное изменение про схему public произошло в pg 15?
- Какие команды нужны, чтобы корректно удалить роль, владеющую объектами?
