Роли и привилегии PostgreSQL: принцип минимальных прав

Приложение ходит в базу под владельцем таблиц или под суперпользователем, а аналитику «временно» выдали ту же роль. Любая ошибка в коде приложения становится DROP TABLE. Защита здесь не в одной настройке, а в раскладе: кто владеет объектами, кто их меняет, кто только читает и что получат объекты, которых ещё нет. На PostgreSQL 18.6 собрана матрица из пяти ролей, и каждая клетка проверена запросом под этой ролью. Построчная изоляция клиентов (RLS) — последний слой поверх этой матрицы, он разобран в мультитенантности.

DBA / 01

Матрица ролей сервиса: что разрешено и что отвечает ошибкой

Права распределены по ролям так, что приложение не владеет объектами, аналитик не пишет, мониторинг не читает данные. Все ответы сервера в клетках получены в опытах ниже дословно.

Опыт 1: роли и права на будущие таблицы

Контейнер, затем пять ролей. owner_r без входа владеет объектами, migrator входит в него и применяет миграции, app не входит никуда. Права предопределённых ролей выдаются как членство.

docker run -d --name dba-roles --memory 512m -e POSTGRES_PASSWORD=pg postgres:18

CREATE ROLE owner_r NOLOGIN;
CREATE ROLE migrator LOGIN PASSWORD 'm' IN ROLE owner_r;
CREATE ROLE app LOGIN PASSWORD 'a';
CREATE ROLE analyst LOGIN PASSWORD 'n';
CREATE ROLE mon LOGIN PASSWORD 'o';
GRANT pg_monitor TO mon;
GRANT pg_read_all_data TO analyst;
CREATE DATABASE shop OWNER owner_r;
\c shop
REVOKE ALL ON DATABASE shop FROM PUBLIC;
GRANT CONNECT ON DATABASE shop TO app, analyst, mon, migrator;
CREATE SCHEMA sales AUTHORIZATION owner_r;
GRANT USAGE ON SCHEMA sales TO app;

Теперь права для объектов, которых ещё нет. ALTER DEFAULT PRIVILEGES действует на объекты, которые создаст названная в FOR ROLE роль, и только в указанной схеме (документация).

ALTER DEFAULT PRIVILEGES FOR ROLE owner_r IN SCHEMA sales
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app;
ALTER DEFAULT PRIVILEGES FOR ROLE owner_r IN SCHEMA sales
  GRANT USAGE ON SEQUENCES TO app;
SET ROLE owner_r;
CREATE TABLE sales.orders (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, note text);
RESET ROLE;
\dp sales.*
SELECT defaclrole::regrole, defaclnamespace::regnamespace, defaclobjtype, defaclacl FROM pg_default_acl;

Вывод:

 Schema |     Name      |   Type   |    Access privileges     | Column privileges | Policies 
--------+---------------+----------+--------------------------+-------------------+----------
 sales  | orders        | table    | owner_r=arwdDxtm/owner_r+|                   | 
        |               |          | app=arwd/owner_r         |                   | 
 sales  | orders_id_seq | sequence | owner_r=rwU/owner_r     +|                   | 
        |               |          | app=U/owner_r            |                   | 
(2 rows)

 defaclrole | defaclnamespace | defaclobjtype |     defaclacl      
------------+-----------------+---------------+--------------------
 owner_r    | sales           | r             | {app=arwd/owner_r}
 owner_r    | sales           | S             | {app=U/owner_r}
(2 rows)

Таблица и её sequence появились сразу с правами app: arwd — INSERT, SELECT, UPDATE, DELETE. Правила хранятся в pg_default_acl (r — таблицы, S — sequences). Подвох в FOR ROLE: миграция, выполненная под migrator без SET ROLE, создаёт объект с другим владельцем, и правило для owner_r на него не действует:

docker exec -e PGPASSWORD=m dba-roles psql -h 127.0.0.1 -U migrator -d shop -c "CREATE TABLE sales.items (id int, name text)"
docker exec -e PGPASSWORD=a dba-roles psql -h 127.0.0.1 -U app -d shop -c "SELECT * FROM sales.items"
docker exec -e PGPASSWORD=m dba-roles psql -h 127.0.0.1 -U migrator -d shop -c "SET ROLE owner_r; CREATE TABLE sales.items2 (id int)"
docker exec -e PGPASSWORD=a dba-roles psql -h 127.0.0.1 -U app -d shop -c "SELECT count(*) FROM sales.items2"

CREATE TABLE
ERROR:  permission denied for table items
SET
CREATE TABLE
 count 
-------
     0
(1 row)

Миграции должны идти под SET ROLE owner_r.

Опыт 2: что каждая роль может и чего не может

Сначала матрица через has_table_privilege и has_schema_privilege. etl — отдельная роль в pg_write_all_data, добавлена, чтобы показать, что эта роль не читает.

CREATE ROLE etl LOGIN PASSWORD 'e' IN ROLE pg_write_all_data;
GRANT CONNECT ON DATABASE shop TO etl;
SELECT r.rolname,
 has_table_privilege(r.rolname,'sales.orders','SELECT') AS sel,
 has_table_privilege(r.rolname,'sales.orders','INSERT') AS ins,
 has_table_privilege(r.rolname,'sales.orders','TRUNCATE') AS trunc,
 has_schema_privilege(r.rolname,'sales','CREATE') AS cr_sales,
 has_schema_privilege(r.rolname,'public','CREATE') AS cr_public
FROM pg_roles r WHERE r.rolname IN ('owner_r','migrator','app','analyst','mon','etl') ORDER BY 1;

 rolname  | sel | ins | trunc | cr_sales | cr_public 
----------+-----+-----+-------+----------+-----------
 analyst  | t   | f   | f     | f        | f
 app      | t   | t   | f     | f        | f
 etl      | f   | t   | f     | f        | f
 migrator | t   | t   | t     | t        | t
 mon      | f   | f   | f     | f        | f
 owner_r  | t   | t   | t     | t        | t
(6 rows)

cr_public = t у owner_r и migrator — не ошибка: схема public принадлежит служебной роли pg_database_owner, то есть владельцу базы (здесь owner_r), а migrator наследует его права. Теперь те же клетки настоящими запросами, по одному под каждой ролью (q — обёртка docker exec -e PGPASSWORD=… dba-roles psql -h 127.0.0.1 -U <роль> -d shop -c):

-- app: CREATE TABLE sales.t (i int)
ERROR:  permission denied for schema sales
-- app: CREATE TABLE public.t (i int)
ERROR:  permission denied for schema public
-- app: TRUNCATE sales.orders
ERROR:  permission denied for table orders
-- app: DROP TABLE sales.orders
ERROR:  must be owner of table orders
-- analyst: UPDATE sales.orders SET note='y'
ERROR:  permission denied for table orders
-- mon: SELECT count(*) FROM sales.orders
ERROR:  permission denied for schema sales

Предопределённые роли ведут себя ровно как описано в документации, но есть нюансы, которые видны только в опыте. pg_write_all_data даёт INSERT, UPDATE и DELETE, но не SELECT: UPDATE без условия проходит, а с WHERE id=1 и с RETURNING нужен SELECT, и etl получает отказ:

-- etl: UPDATE sales.orders SET note='etl'
UPDATE 1
-- etl: UPDATE sales.orders SET note='etl' WHERE id=1
ERROR:  permission denied for table orders

pg_signal_backend отменяет запросы других ролей, но не суперпользователя. Роль sig с этим членством (в опыте app в это время выполнял pg_sleep(60)):

-- sig: SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename='postgres' LIMIT 1
ERROR:  permission denied to terminate process
DETAIL:  Only roles with the SUPERUSER attribute may terminate processes of roles with the SUPERUSER attribute.
-- sig: SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE usename='app' LIMIT 1
 pg_cancel_backend 
-------------------
 t

Где это решается в коде

Проверка права на таблицу — pg_class_aclmask_ext в src/backend/catalog/aclchk.c (экспортируемая pg_class_aclmask только вызывает её). Сначала считается обычный ACL из pg_class.relacl, и только потом, если нужного бита нет, функция смотрит предопределённые роли (строки 3378–3392):

	if (mask & ACL_SELECT && !(result & ACL_SELECT) &&
		has_privs_of_role(roleid, ROLE_PG_READ_ALL_DATA))
		result |= ACL_SELECT;

	/*
	 * Check if ACL_INSERT, ACL_UPDATE, or ACL_DELETE is being checked and, if
	 * so, and not set already as part of the result, then check if the user
	 * is a member of the pg_write_all_data role, which allows
	 * INSERT/UPDATE/DELETE access to all relations (except system catalogs,
	 * which requires superuser, see above).
	 */
	if (mask & (ACL_INSERT | ACL_UPDATE | ACL_DELETE) &&
		!(result & (ACL_INSERT | ACL_UPDATE | ACL_DELETE)) &&
		has_privs_of_role(roleid, ROLE_PG_WRITE_ALL_DATA))
		result |= (mask & (ACL_INSERT | ACL_UPDATE | ACL_DELETE));

Поэтому роль из pg_read_all_data читает таблицу, на которой ей ничего не выдано, а pg_write_all_data добавляет только запись: бит SELECT в этой ветке не появляется, что и показал etl. Права предопределённых ролей не видны в \dp и в relacl, их нужно искать через pg_auth_members.

Опыт 3: public и SECURITY DEFINER

С PostgreSQL 15 у PUBLIC нет права CREATE на схему public (примечания к выпуску); при обновлении кластера и восстановлении дампа старые права сохраняются, поэтому в старых базах это нужно проверить. В новой базе проверка такая:

SELECT nspname, nspowner::regrole, nspacl FROM pg_namespace WHERE nspname='public';
CREATE DATABASE fresh;
\c fresh
SELECT has_schema_privilege('app','public','USAGE') u, has_schema_privilege('app','public','CREATE') c;

 nspname | nspowner          | nspacl
---------+-------------------+---------------------------------------------------------------
 public  | pg_database_owner | {pg_database_owner=UC/pg_database_owner,=U/pg_database_owner}

 u | c 
---+---
 t | f

У PUBLIC (пустое имя перед =) только U, USAGE. В базе, перенесённой со старой версии, в nspacl будет =UC/…, и закрывается это командой REVOKE CREATE ON SCHEMA public FROM PUBLIC.

Функции SECURITY DEFINER выполняются с правами владельца. Неквалифицированные имена внутри них разрешаются по search_path вызывающего, а временная схема pg_temp, если её нет в пути явно, просматривается первой при поиске таблиц и типов. Документация (CREATE FUNCTION) требует исключить из пути схемы, доступные ненадёжным ролям, и ставить pg_temp последней. Класс проблемы описан в бюллетене CVE-2018-1058 (1 марта 2018 года); воспроизводить его здесь не нужно — достаточно найти функции, объявленные без закреплённого search_path. Правильное объявление и неправильное:

SET ROLE owner_r;
CREATE FUNCTION sales.order_count() RETURNS bigint LANGUAGE sql SECURITY DEFINER
AS $$ SELECT count(*) FROM sales.orders $$;
CREATE FUNCTION sales.order_count_safe() RETURNS bigint LANGUAGE sql SECURITY DEFINER
SET search_path = pg_catalog, pg_temp
AS $$ SELECT count(*) FROM sales.orders $$;
RESET ROLE;

Запрос, который находит такие функции во всей базе: prosecdef отбирает SECURITY DEFINER, proconfig хранит SET-параметры функции.

SELECT p.oid::regprocedure AS func, p.proowner::regrole AS owner, p.proconfig
FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE p.prosecdef
  AND n.nspname NOT IN ('pg_catalog','information_schema')
  AND NOT EXISTS (SELECT 1 FROM unnest(coalesce(p.proconfig,'{}')) c WHERE c LIKE 'search_path=%')
ORDER BY 1;

        func         |  owner  | proconfig 
---------------------+---------+-----------
 sales.order_count() | owner_r | 
(1 row)

order_count_safe в списке нет: в proconfig у неё {"search_path=pg_catalog, pg_temp"}. Второй дефект тех же функций — EXECUTE по умолчанию получает PUBLIC. Роль mon могла вызвать функцию, пока право не отозвали:

SELECT has_function_privilege('mon','sales.order_count()','EXECUTE') AS mon_before;
REVOKE EXECUTE ON FUNCTION sales.order_count(), sales.order_count_safe() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION sales.order_count_safe() TO app;
SELECT has_function_privilege('mon','sales.order_count_safe()','EXECUTE') AS mon_after,
       has_function_privilege('app','sales.order_count_safe()','EXECUTE') AS app_after;

 mon_before 
------------
 t

 mon_after | app_after 
-----------+-----------
 f         | t

Под app SELECT sales.order_count_safe() вернул 1, под mon — ERROR: permission denied for schema sales. Контейнер удаляется вместе с томом: docker rm -f -v dba-roles.

Что из этого следует

Приложение работает под ролью, которая ничем не владеет и не имеет CREATE ни в одной схеме: ошибка в коде не превращается в DROP, что показали must be owner of table orders и permission denied for schema public. Миграции идут под отдельной ролью с SET ROLE на владельца, иначе ALTER DEFAULT PRIVILEGES не сработает, как с таблицей items. Аналитику и мониторингу достаточно предопределённых ролей, но pg_read_all_data читает вообще всё в базе: если в базе есть таблицы с чужими данными, нужна отдельная роль с точечным GRANT SELECT или RLS. И запрос на SECURITY DEFINER без search_path стоит запускать после каждой миграции, а не раз в год.

Чек-лист

Роль приложения не владелец и не суперпользователь: SELECT rolsuper FROM pg_roles WHERE rolname = 'app' даёт f, has_schema_privilege('app', 'public', 'CREATE') даёт f.
Для каждой схемы, где миграции создают таблицы, есть ALTER DEFAULT PRIVILEGES FOR ROLE <владелец>, миграции идут под SET ROLE этого владельца; pg_default_acl совпадает с ожидаемым.
В старых базах после обновления проверен nspacl схемы public: если там =UC/…, выполнен REVOKE CREATE ON SCHEMA public FROM PUBLIC.
Запрос по pg_proc.prosecdef и proconfig не находит SECURITY DEFINER без search_path; у каждой такой функции отозван EXECUTE у PUBLIC.
Членство в pg_read_all_data, pg_write_all_data, pg_signal_backend выдано осознанно и пересматривается по pg_auth_members.

Роли и привилегии PostgreSQL: матрица ролей сервиса, DEFAULT PRIVILEGES, предопределённые роли и SECURITY DEFINER с закреплённым search_path