Что делает ROLE под капотом при добавлении пользователя в PostgreSQL?

Принцип работы ROLE в PostgreSQL

Под капотом PostgreSQL при создании или модификации ROLE (роли) происходит серия системных операций, которые фундаментально отличаются от простого добавления записи в таблицу. Важно понимать, что в PostgreSQL USER и GROUP - это просто синонимы для ROLE, созданные для совместимости с другими СУБД.

Внутренняя механика

Когда выполняется команда CREATE ROLE или ALTER ROLE, PostgreSQL:

1. Модифицирует системный каталог - добавляет или изменяет запись в таблице pg_authid (основное хранилище аутентификационной информации)

-- Пример создания роли
CREATE ROLE dev_user WITH LOGIN PASSWORD 'secure_pass';
-- На системном уровне это создаст запись в pg_authid

1. Управляет привилегиями через ACL (Access Control Lists) - каждая роль получает уникальный OID (Object Identifier), который используется во всех ACL системных и пользовательских объектов

2. Создает запись в pg_shadow - для ролей с атрибутом LOGIN (т.е. пользователей), дополнительная информация хранится в pg_shadow, включая хэши паролей

Ключевые системные таблицы

Вот что происходит на уровне базы данных:

-- Пример просмотра созданных ролей через системные представления
SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin 
FROM pg_roles 
WHERE rolname = 'dev_user';

-- Хранимая информация в pg_authid (только для суперпользователей)
-- SELECT * FROM pg_authid WHERE rolname = 'dev_user';

Атрибуты ролей и их хранение

При добавлении атрибутов роли:

CREATE ROLE admin_user WITH 
    SUPERUSER
    CREATEDB
    CREATEROLE
    INHERIT
    LOGIN
    REPLICATION
    BYPASSRLS
    CONNECTION LIMIT 10
    VALID UNTIL '2024-12-31';

Каждый атрибут сохраняется как битовая маска в поле pg_authid.rolconfig, что позволяет эффективно хранить и проверять права.

Наследование ролей и групп

Когда используется наследование ролей:

CREATE ROLE developers;
CREATE ROLE senior_dev IN ROLE developers;
GRANT developers TO senior_dev;

PostgreSQL создает запись в таблице pg_auth_members, которая связывает родительские и дочерние роли через их OID:

pg_auth_members
| roleid | member | grantor | admin_option |
|--------|--------|---------|--------------|
| developers_oid | senior_dev_oid | grantor_oid | false |

Внутренние процессы безопасности

1. Аутентификация: При подключении, PostgreSQL проверяет pg_hba.conf, затем хэш пароля из pg_authid

2. Авторизация: Во время выполнения запросов проверяются:

   • Привилегии через ACL каждого объекта
   • Наследование ролей через рекурсивный обход pg_auth_members
   • Атрибуты роли из pg_authid.rolconfig

Практический пример: что происходит при GRANT

-- При выполнении этой команды:
GRANT SELECT ON TABLE sales TO dev_user;

-- PostgreSQL:
-- 1. Находит OID роли dev_user в pg_authid
-- 2. Находит OID таблицы sales в pg_class
-- 3. Добавляет запись в pg_class.relacl или создает новый ACL
-- 4. Обновляет кэш привилегий в памяти

Важные особенности реализации

• Кэширование привилегий: PostgreSQL кэширует информацию о ролях и привилегиях в памяти для производительности
• Транзакционность: Все операции с ролями полностью транзакционны
• Рекурсивная проверка: При проверке прав PostgreSQL рекурсивно проверяет все родительские роли
• Безопасность: Пароли никогда не хранятся в открытом виде - только хэши с солью

Мониторинг и отладка

Для понимания текущего состояния ролей можно использовать:

-- Проверка активных ролей в сессии
SELECT current_user, session_user;

-- Просмотр наследования
SELECT r.rolname, 
       ARRAY(SELECT b.rolname 
             FROM pg_catalog.pg_auth_members m 
             JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid) 
             WHERE m.member = r.oid) as member_of
FROM pg_catalog.pg_roles r
WHERE r.rolname NOT LIKE 'pg_%';

Ключевой вывод: ROLE в PostgreSQL - это не просто запись в таблице, а сложная система управления доступом, интегрированная во все компоненты СУБД, с собственным механизмом наследования, кэширования и проверки привилегий, что обеспечивает как гибкость управления доступом, так и высокую производительность при проверке прав доступа.