Нидерландах от 390р
Права в PostgreSQL часто начинают настраивать по остаточному принципу: приложение подключается суперпользователем, миграции идут тем же логином, аналитик получает доступ «на всякий случай», а затем внезапно оказывается, что любая ошибка в коде может удалить таблицу или выполнить лишнюю функцию. Хорошая новость: модель ролей PostgreSQL гибкая и позволяет построить аккуратный доступ по принципу least privilege — минимально необходимых прав.
В этой статье разберём практическую схему: отдельный владелец объектов, отдельные логины для приложения, чтения и миграций, осознанные GRANT и REVOKE, права на схемы, default privileges для будущих таблиц и безопасный search_path. Материал рассчитан на админов, DevOps и веб-мастеров, которые держат PostgreSQL на своём сервере, на VDS или обслуживают несколько проектов на одной инсталляции.
Как PostgreSQL думает о ролях
В PostgreSQL нет жёсткого разделения на «пользователей» и «группы» в привычном смысле. Есть roles. Роль может иметь право входа в базу — тогда она фактически является пользователем. Роль может быть без входа — тогда она удобна как группа или технический владелец объектов.
Например, роль app_owner может владеть схемой и таблицами, но не иметь пароля и не использоваться приложением напрямую. Роль app_rw может подключаться к базе и читать/изменять данные, но не менять структуру. Роль app_ro может только читать. Такой подход сразу снижает радиус поражения: компрометация пароля приложения не даёт прав на DROP TABLE или изменение DDL.
Главная идея: владелец объектов и логин приложения — это разные роли. Приложение не должно владеть таблицами, если ему не нужно управлять структурой базы.
У роли есть атрибуты: LOGIN, NOLOGIN, CREATEDB, CREATEROLE, SUPERUSER, REPLICATION, ограничения по подключениям и другие параметры. Для обычного веб-приложения почти всегда достаточно LOGIN без административных атрибутов.
Базовый набор ролей для веб-приложения
Возьмём типовой проект: база appdb, схема app, приложение с правами чтения и записи, отдельный пользователь для read-only задач и отдельный логин для миграций. Выполнять команды нужно под ролью, которая имеет право создавать роли и базу, например под административной ролью PostgreSQL.
CREATE ROLE app_owner NOLOGIN;
CREATE ROLE app_rw LOGIN PASSWORD 'change_this_rw_password';
CREATE ROLE app_ro LOGIN PASSWORD 'change_this_ro_password';
CREATE ROLE app_migrator LOGIN NOINHERIT PASSWORD 'change_this_migrator_password';Роль app_owner не подключается к базе. Её задача — владеть объектами. Логины app_rw, app_ro и app_migrator будут использоваться в строках подключения, CI/CD или вручную администратором. Атрибут NOINHERIT у миграционной роли помогает не получать права владельца неявно: миграции должны явно выполнить SET ROLE app_owner.
Создадим базу и сразу сделаем её владельцем техническую роль:
CREATE DATABASE appdb OWNER app_owner;Дальше подключаемся к appdb и настраиваем доступ внутри неё:
REVOKE ALL ON DATABASE appdb FROM PUBLIC;
GRANT CONNECT ON DATABASE appdb TO app_rw, app_ro, app_migrator;
GRANT TEMPORARY ON DATABASE appdb TO app_rw, app_ro;PUBLIC в PostgreSQL — это не схема, а псевдороль, в которую входят все роли. Если право выдано PUBLIC, оно фактически доступно всем пользователям базы. Поэтому в production-среде полезно явно убрать лишние права с базы и схем, а затем выдать нужное конкретным ролям.
Схемы: почему одного GRANT на таблицу недостаточно
Схема в PostgreSQL — это namespace для таблиц, представлений, функций, типов и других объектов. Чтобы обратиться к таблице app.users, роли мало иметь SELECT на таблицу: ей также нужен USAGE на схему app. Это частая причина ошибки permission denied for schema.
Создадим схему от имени владельца. Если вы работаете под админом, можно указать владельца явно:
CREATE SCHEMA app AUTHORIZATION app_owner;Теперь выдадим базовые права на схему:
GRANT USAGE ON SCHEMA app TO app_rw, app_ro;
GRANT USAGE ON SCHEMA app TO app_migrator;Если миграции должны создавать объекты не через SET ROLE, миграционной роли понадобится ещё CREATE на схему. Но более чистый вариант — разрешить мигратору временно становиться владельцем объектов через членство в роли app_owner и запускать миграции с SET ROLE app_owner.
GRANT app_owner TO app_migrator;В начале миграции тогда выполняется:
SET ROLE app_owner;После этого созданные таблицы и индексы будут принадлежать app_owner. Это удобно: все DDL-объекты имеют предсказуемого владельца, а права для приложения настраиваются единообразно.
Отдельно стоит проверить схему public. В PostgreSQL 15 и новее поведение стало безопаснее: право CREATE в public по умолчанию уже не выдаётся всем подряд. Но на старых кластерах или базах, которые давно обновлялись, это право могло остаться. Для закрытой прикладной базы обычно разумно убрать лишнее:
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM PUBLIC;Если в вашей базе реально используются расширения или объекты в public, не применяйте команду вслепую: сначала проверьте зависимости. Но для нового проекта лучше сразу жить в отдельной схеме app, а public оставить пустой или использовать очень осознанно.
GRANT на существующие таблицы, sequences и функции
GRANT выдаёт права на конкретные объекты. Для таблиц обычно используются SELECT, INSERT, UPDATE, DELETE, реже TRUNCATE, REFERENCES и TRIGGER. Для read-write роли типовой набор выглядит так:
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_rw;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_ro;Если таблицы используют serial, bigserial или identity-колонки, рядом почти всегда есть sequences. Для вставки строк приложению обычно нужен доступ к sequence, иначе можно получить ошибку permission denied for sequence.
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO app_rw;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA app TO app_ro;USAGE на sequence позволяет вызывать nextval, а SELECT — смотреть текущее значение через currval и похожие операции. Для read-only роли SELECT на sequences нужен не всегда, но в некоторых ORM и отчётных инструментах он помогает избежать неожиданных ошибок. Если хотите совсем строгую модель, не выдавайте read-only роли доступ к sequences до появления реальной необходимости.
С функциями и процедурами важно помнить неприятную особенность: по умолчанию PostgreSQL выдаёт EXECUTE на функции псевдороли PUBLIC. Если функции простые и без побочных эффектов, это может быть приемлемо. Но если функции меняют данные, ходят во внешние системы или работают с чувствительной информацией, лучше явно отозвать доступ у всех и выдать только нужным ролям.
REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA app FROM PUBLIC;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA app TO app_rw;Не выдавайте EXECUTE read-only роли автоматически, если у вас есть функции, которые изменяют состояние. Для таких функций лучше делать отдельную роль, например app_reports или app_worker, и выдавать права точечно.
Россия
Нидерланды
Default privileges: права для будущих объектов
Команды выше работают только с уже существующими объектами. Если завтра миграция создаст новую таблицу app.orders, у app_rw и app_ro не появятся права автоматически. Для этого в PostgreSQL существуют default privileges — правила, применяемые к новым объектам, которые создаёт конкретная роль в конкретной схеме.
Ключевая фраза здесь — «создаёт конкретная роль». Если таблицы создаёт app_owner, настраивайте ALTER DEFAULT PRIVILEGES FOR ROLE app_owner. Если миграции случайно создают объекты от имени app_migrator, настройки для app_owner не сработают.
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app GRANT SELECT ON TABLES TO app_ro;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app GRANT USAGE, SELECT ON SEQUENCES TO app_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app GRANT SELECT ON SEQUENCES TO app_ro;Для функций можно сначала закрыть доступ по умолчанию от PUBLIC, а затем выдать нужным ролям:
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app GRANT EXECUTE ON FUNCTIONS TO app_rw;Важно: default privileges не исправляют права на уже существующие таблицы. Поэтому после настройки правил для будущих объектов обычно выполняют два блока: GRANT ON ALL TABLES для текущего состояния и ALTER DEFAULT PRIVILEGES для будущих миграций.
Если новые таблицы появляются без прав для приложения, почти всегда причина в одном из двух: default privileges не настроены или настроены не для той роли-владельца.
REVOKE: как безопасно отбирать права
REVOKE отменяет ранее выданные права. На практике он нужен не только при увольнении сотрудника или удалении интеграции. Его полезно применять в самом начале настройки базы, чтобы убрать широкие права с PUBLIC, а затем выдать доступ точечно.
REVOKE ALL ON DATABASE appdb FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA app FROM PUBLIC;У REVOKE есть нюанс с наследованием прав. Пользователь может иметь доступ напрямую, через одну роль, через другую роль или через PUBLIC. Если вы отозвали SELECT у app_ro, но пользователь всё ещё входит в другую роль с SELECT, доступ останется. Поэтому при расследовании лишних прав смотрите не только объект, но и членство ролей.
Есть также GRANT OPTION — право передавать выданное право дальше. В прикладных ролях оно почти никогда не нужно. Если оно было выдано ошибочно, его можно отозвать отдельно:
REVOKE GRANT OPTION FOR SELECT ON ALL TABLES IN SCHEMA app FROM app_rw;При отзыве прав, которые уже были переданы дальше, PostgreSQL может потребовать CASCADE. Используйте его осторожно: он удалит зависимые выдачи прав. В production лучше сначала посмотреть текущие права и понять цепочку.
search_path: удобство, которое может стать уязвимостью
search_path определяет, в каких схемах PostgreSQL ищет объекты при обращении без явного имени схемы. Например, запрос SELECT * FROM users будет искать таблицу users в схемах из search_path. Если путь настроен неаккуратно и в нём есть схема, куда пользователь может создавать объекты, появляется риск подмены имён.
Для приложения лучше не полагаться на глобальный дефолт. Настройте путь на уровне роли в конкретной базе:
ALTER ROLE app_rw IN DATABASE appdb SET search_path = pg_catalog, app;
ALTER ROLE app_ro IN DATABASE appdb SET search_path = pg_catalog, app;
ALTER ROLE app_migrator IN DATABASE appdb SET search_path = pg_catalog, app;Почему pg_catalog стоит первым? Так системные функции и операторы будут искаться в системной схеме до пользовательских объектов. Это снижает риск shadowing-атак, когда кто-то создаёт объект с именем, похожим на системный. Ещё лучше — в критичных запросах и миграциях указывать схему явно: app.users, app.orders, app.calculate_total.
Особенно внимательно относитесь к функциям SECURITY DEFINER. Они выполняются с правами владельца функции, поэтому небезопасный search_path внутри такой функции может привести к выполнению не того объекта. Для таких функций задавайте путь явно:
CREATE FUNCTION app.secure_example()
RETURNS integer
LANGUAGE sql
SECURITY DEFINER
SET search_path = pg_catalog, app, pg_temp
AS $$
SELECT 1;
$$;Не добавляйте в search_path схемы, где обычные пользователи могут создавать объекты, если потом используете неуточнённые имена функций или таблиц. Это простое правило закрывает целый класс неприятных проблем.
Проверка текущих прав
В psql удобно начинать с обзорных команд. Они показывают роли, схемы и права на объекты в человекочитаемом виде:
\du
\dn+
\dp app.*
\ddp\ddp особенно полезна для проверки default privileges. Если новых таблиц создаётся много, а права «теряются», начните именно с неё и проверьте, для какой роли заданы правила.
Для автоматических проверок удобнее SQL-запросы. Например, можно посмотреть права на таблицы в схеме app:
SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE table_schema = 'app'
ORDER BY grantee, table_name, privilege_type;Права на схемы можно проверить через has_schema_privilege:
SELECT 'app_rw' AS role_name, has_schema_privilege('app_rw', 'app', 'USAGE') AS can_use_schema, has_schema_privilege('app_rw', 'app', 'CREATE') AS can_create_in_schema;А права на конкретную таблицу — через has_table_privilege:
SELECT has_table_privilege('app_ro', 'app.users', 'SELECT') AS app_ro_can_select;
SELECT has_table_privilege('app_rw', 'app.users', 'DELETE') AS app_rw_can_delete;Такие проверки удобно добавлять в runbook после миграций: если CI создал таблицы, но не выдал права, ошибка обнаружится до того, как приложение начнёт падать в production. А если параллельно разбираетесь с производительностью, полезно держать рядом отдельный чек-лист по autovacuum, индексам и базовому тюнингу PostgreSQL.
Типовые ошибки и быстрые диагностики
permission denied for schema
Если роль видит базу, но не может обратиться к таблице в схеме, проверьте USAGE на схему. Право на таблицу без права на схему не поможет.
GRANT USAGE ON SCHEMA app TO app_rw;permission denied for relation
Здесь речь уже о таблице, view или materialized view. Проверьте, выданы ли права на существующие объекты, а не только default privileges.
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_rw;permission denied for sequence
Часто появляется при INSERT в таблицу с автоинкрементным идентификатором. Таблица доступна, но sequence — отдельный объект со своими правами.
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO app_rw;Новые таблицы не доступны приложению
Почти всегда проблема в default privileges. Проверьте, кто является владельцем новых таблиц, и для этой роли настройте правила по умолчанию.
SELECT schemaname, tablename, tableowner
FROM pg_tables
WHERE schemaname = 'app'
ORDER BY tablename;Read-only пользователь всё равно может менять данные
Проверьте членство в ролях. Возможно, пользователь входит в роль, которой выданы INSERT, UPDATE или DELETE. Также проверьте функции SECURITY DEFINER: read-only роль может не иметь прямого UPDATE, но вызывать функцию, которая меняет данные с правами владельца.
Практический шаблон настройки с нуля
Ниже — компактный шаблон для новой базы. Его нужно адаптировать под свой проект: имена ролей, схему, набор прав, требования ORM и миграционного инструмента. Но как стартовая точка он хорошо отражает безопасную модель доступа.
CREATE ROLE app_owner NOLOGIN;
CREATE ROLE app_rw LOGIN PASSWORD 'change_this_rw_password';
CREATE ROLE app_ro LOGIN PASSWORD 'change_this_ro_password';
CREATE ROLE app_migrator LOGIN NOINHERIT PASSWORD 'change_this_migrator_password';
CREATE DATABASE appdb OWNER app_owner;Следующий блок выполняйте уже в базе appdb:
REVOKE ALL ON DATABASE appdb FROM PUBLIC;
GRANT CONNECT ON DATABASE appdb TO app_rw, app_ro, app_migrator;
GRANT TEMPORARY ON DATABASE appdb TO app_rw, app_ro;
CREATE SCHEMA app AUTHORIZATION app_owner;
REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA app TO app_rw, app_ro, app_migrator;
GRANT app_owner TO app_migrator;ALTER ROLE app_rw IN DATABASE appdb SET search_path = pg_catalog, app;
ALTER ROLE app_ro IN DATABASE appdb SET search_path = pg_catalog, app;
ALTER ROLE app_migrator IN DATABASE appdb SET search_path = pg_catalog, app;GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_rw;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_ro;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO app_rw;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA app TO app_ro;
REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA app FROM PUBLIC;ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app GRANT SELECT ON TABLES TO app_ro;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app GRANT USAGE, SELECT ON SEQUENCES TO app_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app GRANT SELECT ON SEQUENCES TO app_ro;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;Если миграции выполняются через app_migrator, добавьте в начало миграционного процесса SET ROLE app_owner. Многие инструменты позволяют выполнить SQL-хук до миграций. Если такой возможности нет, придётся либо настраивать default privileges для app_migrator, либо принять, что владельцем объектов будет миграционная роль.
Чек-лист least privilege для PostgreSQL
- Не используйте суперпользователя PostgreSQL в приложении, миграциях и фоновых воркерах.
- Разделяйте владельца объектов, read-write логин, read-only логин и миграционный логин.
- Отзывайте лишние права у
PUBLIC, особенно на базе, схемеpublicи функциях. - Выдавайте
USAGEна схему отдельно от прав на таблицы. - Не забывайте про sequences: для автоинкремента нужны отдельные права.
- Настраивайте
default privilegesдля той роли, которая реально создаёт объекты. - Держите
search_pathкоротким и предсказуемым; избегайте схем, куда могут писать непроверенные роли. - Проверяйте членство в ролях при расследовании лишнего доступа.
- Для
SECURITY DEFINERфункций задавайте безопасныйsearch_pathвнутри определения функции. - После миграций запускайте проверки прав, особенно если релизы автоматизированы через CI/CD.
Итоги
Безопасная модель доступа в PostgreSQL строится не одной командой GRANT, а набором привычек. Роли должны отражать реальные обязанности: владелец объектов владеет, приложение работает с данными, read-only пользователь читает, мигратор меняет структуру под контролем. REVOKE убирает широкие права, default privileges защищают будущие объекты, а аккуратный search_path снижает риск подмены имён.
Если вы настраиваете новую базу, лучше заложить эту схему сразу. Если база уже живёт в production, двигайтесь постепенно: сначала инвентаризация ролей и прав, затем отзыв доступа у PUBLIC, потом разделение владельца и прикладных логинов, и только после этого автоматизация проверок. Так вы получите понятную, сопровождаемую и предсказуемую систему доступа без лишнего риска для приложения.
