Роли и права в PostgreSQL: минимальные привилегии, которые держатся

Большинство приложений подключаются к PostgreSQL суперпользователем, потому что именно этот пароль выдал хостинг и он работает. Работает до того дня, когда ошибка, внедрённый запрос или уставший человек в терминале делает то, что приложение вообще не должно было мочь: удаляет таблицу, читает чужие строки или отключает настройку, защищающую сервер. Минимальные привилегии здесь - не паранойя, а около десяти минут SQL, и эта статья - именно этот SQL плюс те части модели, которые заставляют его держаться.

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

Роль - это одновременно пользователь и группа#

В PostgreSQL одно понятие там, где в других системах два. Роль может входить в систему, обладать привилегиями и содержать другие роли. CREATE USER - это CREATE ROLE ... LOGIN, записанное иначе, и больше ничто их не различает.

CREATE ROLE app_owner NOLOGIN;CREATE ROLE app LOGIN PASSWORD 'generated-not-chosen';CREATE ROLE readonly LOGIN PASSWORD 'also-generated' CONNECTION LIMIT 3;GRANT app_owner TO app;   -- only if you want app to act as the owner

Атрибутов у роли немного, и каждый - это решение:

Над INHERIT стоит подумать. Когда он включён, участник группы получает права группы в момент подключения. С NOINHERIT ему придётся запросить их: SET ROLE app_owner;. Этот лишний шаг - неплохой ремень безопасности для учётной записи человека, которому иногда нужно выполнить миграцию, потому что опасные права не оказываются включёнными случайно.

\du в psql показывает всю картину: каждую роль, её атрибуты и членство. Это первая команда, которую стоит выполнить на базе, доставшейся вам от кого-то другого.

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

Что раздаёт свежая база данных#

Новая база данных не пуста по части прав. В PostgreSQL есть псевдороль PUBLIC, в которую неявно входит каждая роль и которую нельзя удалить, и из коробки у PUBLIC больше прав, чем принято думать:

• CONNECT к базе данных, так что любая роль, способная пройти аутентификацию, может её открыть.
• TEMP, то есть право создавать временные таблицы.
• USAGE на схему public, так что роль видит, что в ней лежит.
• EXECUTE на функции, включая те, что установили ваши расширения.

До PostgreSQL 15 у PUBLIC было ещё и CREATE на схему public, то есть любая роль, способная подключиться, могла создавать в ней таблицы. В 15 это изменилось: схема public теперь принадлежит pg_database_owner, а CREATE для PUBLIC не выдаётся. Это самый частый сюрприз в этой области, зависящий от версии, причём в обе стороны. В 15 и новее вы столкнётесь с permission denied for schema public, когда миграция выполняется ролью, не являющейся владельцем. В 14 и старше обнаружите, что роль только для чтения всё равно может создавать таблицы.

Закройте эту брешь явно, на любой версии:

REVOKE ALL ON DATABASE shop FROM PUBLIC;REVOKE ALL ON SCHEMA public FROM PUBLIC;GRANT CONNECT ON DATABASE shop TO app, readonly;

Сделайте это до создания ролей приложения, и вы начнёте с запрета, а не с умолчания, которое приходится помнить.

Настройка с минимальными привилегиями целиком#

Три роли, одна схема, выполняется один раз суперпользователем или владельцем базы данных. Имена подставьте свои; важна форма.

-- 1. The owner. Owns every object, runs migrations, never logs in from the app.CREATE ROLE shop_owner NOLOGIN;ALTER DATABASE shop OWNER TO shop_owner;CREATE SCHEMA IF NOT EXISTS app AUTHORIZATION shop_owner;-- 2. The application. Reads and writes rows, changes nothing structural.CREATE ROLE shop_app LOGIN PASSWORD 'from-a-password-manager';GRANT CONNECT ON DATABASE shop TO shop_app;GRANT USAGE ON SCHEMA app TO shop_app;GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO shop_app;GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO shop_app;-- 3. Reporting and humans. Reads, and only reads.CREATE ROLE shop_read LOGIN PASSWORD 'a-different-one' CONNECTION LIMIT 3;GRANT CONNECT ON DATABASE shop TO shop_read;GRANT USAGE ON SCHEMA app TO shop_read;GRANT SELECT ON ALL TABLES IN SCHEMA app TO shop_read;

Четыре момента здесь сделаны намеренно, и о них стоит сказать прямо.

Роль приложения не является владельцем. GRANT SELECT, INSERT, UPDATE, DELETE - это не то же самое, что владеть таблицей: владелец может выполнить ALTER и DROP, и никакой GRANT этого не передаёт. Внедрённый DROP TABLE orders от роли приложения завершится ошибкой прав, а не испортит вам вечер.

Роль для миграций отдельная. Ваш инструмент миграций подключается как shop_owner (или как роль с логином, входящая в неё) с другим паролем, и им пользуется задача деплоя, а не обработчики запросов. Именно это делает безопасной автоматизацию шаблона «расширить и сузить» из статьи миграции схемы без простоя.

Последовательности выдаются отдельно. Столбец serial вызывает nextval у последовательности, и роль с INSERT на таблицу, но без прав на последовательность получит permission denied for sequence orders_id_seq при первой же вставке. Столбцы-идентификаторы (GENERATED ... AS IDENTITY) ведут себя иначе, поэтому выполните одну вставку от имени роли приложения и убедитесь, а не предполагайте ни в ту, ни в другую сторону.

`ALL TABLES IN SCHEMA` означает все таблицы, существующие прямо сейчас. Это цикл по сегодняшним объектам, а не постоянное правило. Что подводит нас к шагу, благодаря которому всё это выживает.

Привилегии по умолчанию: шаг, который все пропускают#

Первая же миграция после настройки ролей создаёт таблицу, приложение говорит permission denied for table invoices, и кто-то вручную повторяет GRANT. Через месяц это случается снова. Решение - ALTER DEFAULT PRIVILEGES:

ALTER DEFAULT PRIVILEGES FOR ROLE shop_owner IN SCHEMA app  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO shop_app;ALTER DEFAULT PRIVILEGES FOR ROLE shop_owner IN SCHEMA app  GRANT USAGE, SELECT ON SEQUENCES TO shop_app;ALTER DEFAULT PRIVILEGES FOR ROLE shop_owner IN SCHEMA app  GRANT SELECT ON TABLES TO shop_read;

Ловушка - FOR ROLE. Привилегии по умолчанию привязываются к роли, которая создаёт объект, а не к схеме вообще. Если вы задали их для shop_owner, а затем выполнили миграцию суперпользователем, новая таблица не получит ни одной из них. От имени какой роли выполняются ваши миграции, та роль и должна стоять в предложении FOR ROLE, и каждый раз это должна быть одна и та же роль.

\ddp в psql выводит действующие сейчас привилегии по умолчанию; это самый быстрый способ убедиться, что вы привязали их к нужной роли.

Схемы и search_path#

Схема - это пространство имён для таблиц, а также естественная граница прав. GRANT USAGE ON SCHEMA - это ворота: без него недоступна никакая привилегия на любую таблицу внутри.

search_path определяет, в какой схеме разрешается имя таблицы без квалификатора. По умолчанию он равен "$user", public, то есть схема с именем подключающейся роли, если такая есть, а затем public. Задайте его для роли, чтобы приложению не приходилось указывать схему в каждом имени:

ALTER ROLE shop_app SET search_path = app, public;ALTER ROLE shop_app SET statement_timeout = '30s';ALTER ROLE shop_read SET statement_timeout = '120s';

Две последние строки стоят хлопот на небольшом сервере. statement_timeout на уровне роли означает, что вышедший из-под контроля отчёт не сможет удерживать соединение час, и действует это без единой правки в коде приложения. Остальные настройки этого семейства разобраны в статье настройка PostgreSQL для небольших серверов.

У search_path есть и грань безопасности. Функция SECURITY DEFINER, обращающаяся к имени таблицы без квалификатора, разрешит его через search_path вызывающего, а это позволяет вызывающему направить её на таблицу, которую он контролирует. Всегда задавайте путь у таких функций явно:

CREATE FUNCTION app.recalc_totals() RETURNS void  LANGUAGE sql SECURITY DEFINER SET search_path = app, pg_tempAS $$ UPDATE app.orders SET total = ... $$;

Встроенные роли, которые стоит знать#

PostgreSQL поставляется с набором предопределённых ролей, чтобы для типовых задач не требовался суперпользователь. Выдать одну из них учётной записи человека почти всегда лучше, чем выдать SUPERUSER.

pg_monitor вместе с pg_signal_backend покрывают почти всё, что нужно дежурному для диагностики зависшей базы, и ни одна из них не читает ни строки данных клиентов. pg_read_all_data - честный способ дать аналитику доступ ко всему, не давая возможности что-либо менять.

Безопасность на уровне строк и когда она оправдана#

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

ALTER TABLE app.orders ENABLE ROW LEVEL SECURITY;CREATE POLICY tenant_isolation ON app.orders  USING (tenant_id = current_setting('app.tenant_id', true)::uuid);GRANT SELECT, INSERT, UPDATE, DELETE ON app.orders TO shop_app;

Затем приложение один раз за транзакцию задаёт арендатора, и каждый запрос фильтруется независимо от того, не забыл ли он об этом сам:

BEGIN;SET LOCAL app.tenant_id = '9f1c...';SELECT * FROM app.orders;   -- only this tenant's rows, alwaysCOMMIT;

Три оговорки определяют, хорошая ли это идея для вас. Включённая RLS без единой политики запрещает всё - это безопасное умолчание и запутанные первые пять минут. Владелец таблицы не подчиняется её политикам, если вы не добавите FORCE ROW LEVEL SECURITY, поэтому проверяйте от имени роли приложения, а не владельца. А суперпользователи и любая роль с BYPASSRLS игнорируют политики полностью, и это ещё одна причина, по которой приложение не должно быть суперпользователем.

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

Аудит выданного и как это отозвать#

Права расходятся с задуманным. Проверяйте их так же, как всё остальное, запросом, а не по памяти.

-- Who can do what to a table\dp app.orders-- Every table privilege held by one roleSELECT table_schema, table_name, privilege_typeFROM information_schema.role_table_grantsWHERE grantee = 'shop_app'ORDER BY table_schema, table_name;-- A direct questionSELECT has_table_privilege('shop_app', 'app.orders', 'DELETE');

В выводе \dp привилегии обозначены буквами: r - SELECT, a - INSERT, w - UPDATE, d - DELETE, D - TRUNCATE, x - REFERENCES, t - TRIGGER. Запись =r/shop_owner означает, что у PUBLIC есть SELECT, выданный shop_owner, и это обычно стоит отозвать.

Удаление роли - то место, где люди застревают. DROP ROLE не выполняется, пока роль чем-либо владеет или имеет какие-то привилегии, и выдаёт сообщение со списком зависимых объектов. Последовательность такая:

REASSIGN OWNED BY old_role TO shop_owner;DROP OWNED BY old_role;DROP ROLE old_role;

REASSIGN OWNED BY передаёт владение объектами; DROP OWNED BY убирает оставшиеся права. Обе команды действуют только на текущую базу данных, поэтому выполните их в каждой базе, которой касалась роль, перед последним DROP ROLE, действующим на весь кластер.

Для ротации меняйте пароль, а не роль. В psql используйте \password shop_app: он запрашивает пароль, хеширует его на вашей машине и отправляет только хеш, так что открытый текст не попадёт ни в журнал сервера, ни в историю вашей оболочки. ALTER ROLE shop_app PASSWORD 'literal' делает обратное.

Две заметки, специфичные для RE:NODE, потому что эти два уровня путают. Пароль суперпользователя базы данных генерируется для каждого сервера, и он принадлежит вам; роли выше вы создаёте внутри этой базы, и панель о них ничего не знает. Доступ к панели - отдельная вещь: субпользователи, роли и команды определяют, кто может открыть консоль, трогать файлы или сделать backup, и у человека может быть одно без другого. Дайте коллеге субпользователя панели с нужными ему правами и роль базы данных с нужными ему строками и считайте это двумя разными вопросами. Сторона панели описана в статье субпользователи и минимальные привилегии, а остальное - в чек-листе безопасности базы данных.

Ошибки прав, которые вы действительно встретите#

Сообщения PostgreSQL точны, когда знаешь, с какого уровня они приходят. Вот те, на которые уходит большинство потерянных вечеров.

`permission denied for schema public` - почти всегда PostgreSQL 15 или новее, где у PUBLIC больше нет CREATE на эту схему. Роль, выполняющая миграцию, не является владельцем схемы. Либо выполняйте миграции от владельца, либо GRANT CREATE ON SCHEMA public TO shop_owner, либо перенесите таблицы в собственную схему, что в любом случае аккуратнее.

`permission denied for table orders` - три кандидата в порядке вероятности: право не было выдано на эту таблицу, потому что она создана после того, как вы выполнили GRANT ... ON ALL TABLES; роль читает не ту схему, что вы думаете, поэтому проверьте SHOW search_path; либо вы выдали право не той роли, и никто не заметил, потому что суперпользователь продолжал работать.

`permission denied for sequence orders_id_seq` - путь вставки. Выдайте USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app и добавьте соответствующую привилегию по умолчанию.

`must be owner of table orders` - роль пытается выполнить ALTER, DROP, добавить индекс или ограничение. Владение - не привилегия, которую можно выдать; оно передаётся командой ALTER TABLE ... OWNER TO. Эта ошибка означает, что ваша миграция выполняется от имени роли приложения, то есть разделение работает именно так, как задумано.

`must be superuser to execute ALTER SYSTEM` и подобные - конфигурация уровня сервера. Учтите, что CREATE EXTENSION не всегда попадает в эту категорию: начиная с PostgreSQL 13 часть расширений помечена как доверенные, и владелец базы данных может устанавливать их без суперпользователя.

Запрос возвращает ноль строк и не выдаёт ошибки - если на таблице включена безопасность на уровне строк, не подошедшая политика - это не ошибка, а пустой результат. Проверьте pg_policies и убедитесь, что переменная сеанса, которую читает ваша политика, действительно задана.

`role "old_app" cannot be dropped because some objects depend on it` - последовательность REASSIGN OWNED BY и DROP OWNED BY, описанная выше, в каждой базе, которой касалась роль.

`no pg_hba.conf entry for host ...` - это вообще не проблема прав. Это узловая аутентификация по хосту отклоняет подключение до того, как будут рассматриваться привилегии какой-либо роли, а исправляется это в другом файле. Этот уровень разобран в статье удалённые подключения к PostgreSQL.

FAQ#

Чем роль отличается от пользователя в PostgreSQL?

Ничем, кроме написания. CREATE USER - сокращение для CREATE ROLE ... LOGIN. Роль с LOGIN может подключаться; роль без него используется как группа или как владелец, под которым никто не входит.

Почему приложение получает «permission denied» только для новых таблиц?

Потому что GRANT ... ON ALL TABLES IN SCHEMA применился к таблицам, существовавшим на момент выполнения. Задайте ALTER DEFAULT PRIVILEGES FOR ROLE <роль, от имени которой выполняются ваши миграции>, чтобы будущие таблицы получали право автоматически.

Должно ли приложение подключаться как владелец базы данных?

Нет. Владелец может удалять и изменять каждый объект, а приложению ничего из этого не нужно. Пусть задача миграций подключается как владелец, а обработчики запросов - как роль с правами только на данные.

Как безопасно дать доступ только на чтение?

Создайте роль с логином, выдайте CONNECT на базу данных, USAGE на схемы, SELECT на таблицы и добавьте привилегии по умолчанию для будущих. В PostgreSQL 14 и новее GRANT pg_read_all_data TO analyst делает то же самое одной строкой для всех схем.

Нужна ли безопасность на уровне строк для приложения с несколькими арендаторами?

Не всегда, но это единственный подход, переживающий забытое условие WHERE. Если утечка между арендаторами была бы серьёзным инцидентом, включите её, добавьте FORCE ROW LEVEL SECURITY на таблицах и проиндексируйте столбец арендатора.

Как сменить пароль базы данных без простоя?

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