Основные команды для управления пользователями, ролями и разрешениями в PostgreSQL

Безопасность базы данных зависит от надежного контроля доступа. В PostgreSQL этот контроль осуществляется в первую очередь через Роли и связанные с ними Разрешения (или привилегии). Понимание того, как создавать, изменять и управлять этими сущностями безопасности, является основополагающим для любого администратора или разработчика баз данных.

Это всеобъемлющее руководство рассматривает основные команды SQL, необходимые для безопасного управления пользователями, определения политик доступа и реализации принципа наименьших привилегий в вашей среде PostgreSQL. Мы рассмотрим создание ролей, установку атрибутов, членство в группах и детальное управление разрешениями объектов с помощью GRANT и REVOKE.

Понимание ролей PostgreSQL

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

1. Пользователя базы данных (сущность, которая может войти в систему, обычно имеющая атрибут LOGIN).
2. Группу пользователей (сущность, используемая исключительно для группировки привилегий, обычно не имеющая атрибута LOGIN).

Эффективная безопасность начинается с определения ролей, которые точно отражают обязанности лиц или приложений, обращающихся к базе данных.

1. Управление ролями: создание, изменение и удаление

Основные команды для управления субъектами базы данных строятся вокруг CREATE ROLE, ALTER ROLE и DROP ROLE.

Создание новых ролей

При создании роли вы должны указать ее атрибуты, в частности, может ли она входить в систему (LOGIN) и ее учетные данные для аутентификации (PASSWORD).

Создание базового пользователя для входа

Для создания стандартной пользовательской роли, требующей пароль для подключения:

CREATE ROLE app_user WITH LOGIN PASSWORD 'strong_password_here';

Создание роли с определенными атрибутами

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

Атрибут	Описание
LOGIN	Позволяет роли подключаться к базе данных.
SUPERUSER	Предоставляет все привилегии базы данных (использовать осторожно).
CREATEROLE	Позволяет роли создавать, изменять и удалять другие роли.
CREATEDB	Позволяет роли создавать новые базы данных.
REPLICATION	Позволяет роли инициировать потоковую репликацию.
INHERIT / NOINHERIT	Контролирует, наследует ли роль автоматически привилегии от ролей, членом которых она является (по умолчанию — INHERIT).

Пример: Создание роли администратора (не суперпользователя)

CREATE ROLE db_admin WITH
    LOGIN
    PASSWORD 'admin_secret'
    CREATEROLE
    CREATEDB
    VALID UNTIL '2025-01-01'; -- Дополнительная дата истечения срока действия

Изменение существующих ролей

Используйте ALTER ROLE для изменения атрибутов или обновления пароля для существующей роли.

-- Изменить пароль для существующего пользователя
ALTER ROLE app_user WITH PASSWORD 'new_strong_password';

-- Отменить возможность входа (превращая пользователя в группу)
ALTER ROLE db_admin NOLOGIN;

-- Заблокировать пользователя
ALTER ROLE old_employee NOLOGIN;

Удаление ролей

Прежде чем удалить роль, убедитесь, что ей не принадлежат никакие объекты базы данных (таблицы, схемы и т. д.). Если это так, вы должны сначала передать право собственности с помощью REASSIGN OWNED.

-- Удалить все объекты, принадлежащие роли, и переназначить их 'postgres'
REASSIGN OWNED BY old_employee TO postgres;

-- Затем удалить роль
DROP ROLE old_employee;

Внимание: Удаление роли необратимо. Будьте осторожны, особенно с ролями, которым принадлежит много объектов.

2. Управление членством в группах

Роли часто функционируют как группы, чтобы упростить управление разрешениями. Вместо того, чтобы предоставлять разрешения 50 отдельным пользователям, вы предоставляете их одной групповой роли, а затем делаете пользователей членами этой группы.

Создание групповой роли

Группы обычно создаются без атрибута LOGIN.

CREATE ROLE data_analysts NOLOGIN;

Предоставление и отзыв членства в группе

Используйте команду GRANT, чтобы добавить членов в групповую роль, и REVOKE, чтобы удалить их.

-- Добавить пользователя 'alice' и пользователя 'bob' в группу 'data_analysts'
GRANT data_analysts TO alice, bob;

-- Удалить 'bob' из группы 'data_analysts'
REVOKE data_analysts FROM bob;

Опция WITH ADMIN OPTION

Если вы включаете WITH ADMIN OPTION при предоставлении членства, роль-получатель затем может предоставить членство в этой группе другим, а также может удалить (DROP) групповую роль.

GRANT data_analysts TO supervisor WITH ADMIN OPTION;

3. Управление разрешениями объектов (привилегиями)

Разрешения определяют, какие действия может выполнять роль в отношении каких объектов базы данных (таблиц, представлений, функций, схем и т. д.). Это основа безопасности базы данных.

Синтаксис команды GRANT

GRANT privilege_list ON object_type object_name TO role_name [WITH GRANT OPTION];

Распространенные привилегии и примеры

Привилегии таблиц

Привилегия	Разрешенное действие
SELECT	Чтение данных из таблицы.
INSERT	Добавление новых строк.
UPDATE	Изменение существующих строк.
DELETE	Удаление существующих строк.
TRUNCATE	Полное опустошение таблицы.
REFERENCES	Создание ограничений внешнего ключа.

Пример: Предоставление доступа только для чтения к определенной таблице.

GRANT SELECT ON TABLE production.orders TO data_analysts;

-- Предоставление всех операций DML для таблицы
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE staging.temp_data TO app_user;

Привилегии баз данных и схем

Привилегии баз данных и схем имеют решающее значение для контроля структуры среды.

Объект	Ключевая привилегия	Назначение
DATABASE	CONNECT	Разрешает подключение к базе данных.
DATABASE	CREATE	Разрешает создание новых схем, табличных пространств и т. д.
SCHEMA	USAGE	Разрешает доступ к объектам внутри схемы.
SCHEMA	CREATE	Разрешает создание новых объектов внутри схемы.

Пример: Предоставление доступа к схеме

Если пользователю требуется доступ к таблицам в схеме app_schema, он должен иметь USAGE для этой схемы.

GRANT USAGE ON SCHEMA app_schema TO app_user;
GRANT SELECT ON ALL TABLES IN SCHEMA app_schema TO app_user;

Привилегии последовательностей и функций

Использование последовательностей (для автоматически инкрементируемых идентификаторов) и выполнение функций требуют определенных привилегий.

-- Разрешить пользователю продвигать последовательность (необходимо для INSERT)
GRANT USAGE, SELECT ON SEQUENCE app_schema.user_id_seq TO app_user;

-- Разрешить выполнение определенной хранимой процедуры или функции
GRANT EXECUTE ON FUNCTION audit_log_insert(text) TO app_user;

Отзыв разрешений

Используйте команду REVOKE для удаления определенных ранее предоставленных разрешений. Синтаксис повторяет GRANT.

-- Отменить возможность вставлять новые записи в таблицу orders
REVOKE INSERT ON TABLE production.orders FROM app_user;

-- Отменить все ранее предоставленные привилегии (примечание: не отменяет право собственности)
REVOKE ALL PRIVILEGES ON TABLE production.orders FROM data_analysts;

Примечание об отзыве: Если привилегия была предоставлена роли, и эта роль является членом другой группы, отзыв привилегии влияет только на напрямую предоставленное разрешение. Если роль по-прежнему наследует привилегию через членство в группе, она сохраняет доступ.

Применение разрешений к будущим объектам

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

Пример: Обеспечение возможности пользователю SELECT из всех будущих таблиц, созданных ролью app_owner в схеме app_schema:

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app_schema
    GRANT SELECT ON TABLES TO app_user;

4. Просмотр текущих разрешений

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

Команда/Представление	Описание
\du (в psql)	Перечисляет все роли и их атрибуты.
\du+ role_name (в psql)	Показывает подробные атрибуты роли и членство.
\dp table_name (в psql)	Перечисляет разрешения (привилегии), предоставленные таблице.
pg_roles	Представление системного каталога, содержащее определения ролей.
information_schema.table_privileges	Представление, показывающее предоставленные привилегии на таблицы.

Пример: Проверка привилегий на таблице через psql

=> \dp production.orders

Рекомендации по безопасности для управления пользователями

1. Принцип наименьших привилегий (PoLP): Предоставляйте только минимальный набор разрешений, необходимый роли для выполнения ее функции. Избегайте предоставления ALL PRIVILEGES или SUPERUSER, если это абсолютно не требуется.
2. Разделяйте роли приложений: Используйте выделенные роли для приложений (например, api_service_role) отдельно от ролей администраторов баз данных. Приложения обычно должны иметь только разрешения DML (SELECT, INSERT, UPDATE, DELETE), а не разрешения DDL (CREATE, DROP).
3. Активно используйте группы: Создавайте роли без возможности входа (группы) для управления наборами разрешений (например, read_only_group, etl_writer_group). Назначайте отдельных пользователей этим группам, а не предоставляйте разрешения индивидуально.
4. Избегайте использования ролей по умолчанию: Никогда не используйте роль суперпользователя postgres для общих задач приложений или разработки.
5. Безопасная аутентификация: Всегда используйте надежные пароли и, по возможности, используйте аутентификацию на основе клиентских сертификатов или централизованные решения для управления удостоверениями вместо аутентификации на основе пароля.

Заключение

Эффективное управление ролями и разрешениями является основой безопасности PostgreSQL. Освоив CREATE ROLE, ALTER ROLE, GRANT и REVOKE, администраторы баз данных могут реализовать детальный контроль, гарантируя, что каждый пользователь или приложение, обращающееся к базе данных, имеет ровно тот доступ, который требуется, и не более того. Последовательное применение принципа наименьших привилегий и использование групповых ролей упрощает долгосрочное обслуживание и усиливает защиту вашей базы данных от несанкционированного доступа.