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

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

PostgreSQL использует одно понятие — роль — как для пользователей входа, так и для групп. Роль с LOGIN может подключаться. Роль без LOGIN часто используется как группа, несущая общие привилегии.

Создание и изменение ролей

Создайте роль для входа приложения с паролем:

CREATE ROLE app_user WITH LOGIN PASSWORD 'strong_password_here';

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

CREATE ROLE reporting_readonly NOLOGIN;

Общие атрибуты ролей включают:

Атрибут	Что он разрешает
LOGIN	Подключаться к серверу базы данных.
CREATEDB	Создавать базы данных.
CREATEROLE	Создавать, изменять и удалять многие роли, с важными ограничениями относительно суперпользователей.
REPLICATION	Использовать репликационные подключения.
SUPERUSER	Обходить обычные проверки разрешений. Используйте только для строго контролируемых административных ролей.
INHERIT	Использовать привилегии от ролей, членом которых он является. Это значение по умолчанию.

Измените пароль или отключите вход с помощью ALTER ROLE:

ALTER ROLE app_user WITH PASSWORD 'new_strong_password';
ALTER ROLE old_employee NOLOGIN;

Перед удалением роли обработайте все, что ей принадлежит, и все привилегии, которые ей были предоставлены:

REASSIGN OWNED BY old_employee TO postgres;
DROP OWNED BY old_employee;
DROP ROLE old_employee;

REASSIGN OWNED передает принадлежащие объекты. DROP OWNED удаляет привилегии, предоставленные роли, и может удалить объекты, все еще принадлежащие ей в текущей базе данных, поэтому внимательно проверяйте перед выполнением.

Использование групповых ролей для более чистого доступа

Предоставляйте разрешения групповым ролям, затем добавляйте людей или роли приложений в эти группы.

GRANT reporting_readonly TO alice, bob;
REVOKE reporting_readonly FROM bob;

WITH ADMIN OPTION позволяет участнику предоставлять и отзывать членство в этой роли для других. Само по себе это не делает участника суперпользователем и не дает неограниченной власти над базой данных.

GRANT reporting_readonly TO team_lead WITH ADMIN OPTION;

Этот шаблон упрощает аудит доступа. Вместо проверки 50 пользователей на предмет предоставления прав на таблицы, вы можете проверить групповую роль.

Предоставление разрешений на объекты

Используйте GRANT, чтобы разрешить определенные действия на определенных объектах.

GRANT SELECT ON TABLE production.orders TO reporting_readonly;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE app.orders TO app_user;

Общие привилегии таблиц: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES и TRIGGER.

Разрешения схемы отделены от разрешений таблицы. Роли часто требуется USAGE на схеме и привилегии на таблицы внутри нее:

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

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

GRANT USAGE, SELECT ON SEQUENCE app.orders_id_seq TO app_user;

Для функций явно предоставьте право выполнения:

GRANT EXECUTE ON FUNCTION app.audit_log_insert(text) TO app_user;

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

REVOKE удаляет прямое предоставление прав.

REVOKE INSERT ON TABLE production.orders FROM app_user;
REVOKE ALL PRIVILEGES ON TABLE production.orders FROM reporting_readonly;

Если пользователь все еще имеет доступ после отзыва, проверьте членство в группах. Пользователь может наследовать ту же привилегию через другую роль.

Настройка разрешений для будущих таблиц

GRANT ON ALL TABLES влияет только на существующие таблицы. Для таблиц, созданных позже, используйте ALTER DEFAULT PRIVILEGES от роли, которая будет создавать эти таблицы.

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT SELECT ON TABLES TO reporting_readonly;

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

Аудит текущего доступа

В psql эти команды являются быстрыми проверками:

\du
\du+ app_user
\dp app.orders
\dn+

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

SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin
FROM pg_roles
ORDER BY rolname;

Для предоставления прав на таблицы:

SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.table_privileges
WHERE table_schema = 'app'
ORDER BY grantee, table_name, privilege_type;

Привычки безопасности, которые окупаются

Давайте приложениям выделенные роли вместо использования учетной записи DBA-человека. Предоставляйте минимальный набор привилегий, необходимых приложению. Избегайте SUPERUSER и широких предоставлений ALL PRIVILEGES для повседневной работы.

Используйте групповые роли для общего доступа и регулярно проверяйте членство. Когда кто-то меняет команду, удаление одного членства в группе безопаснее, чем поиск предоставлений прав на уровне таблиц.

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