PostgreSQL для мультитенантных SaaS: типы для денег, RLS и шесть ключевых решений

Денежные колонки и изоляция арендаторов — два места, где PostgreSQL ведёт себя не так, как подсказывает интуиция. numeric хранит не то, что видно в тексте запроса, а RLS фильтрует строки не там, где ожидает разработчик. Разберём механику обеих подсистем и посмотрим, какие решения из неё следуют.

Как numeric хранит десятичные числа

numericвстроенный тип PostgreSQL для чисел с произвольной точностью хранит цифры группами по два байта, в каждой группе число от 0 до 9999 — позиционная запись по основанию 10000. Рядом лежит вес (weight) — номер разряда самой первой группы, считая в степенях 10000. Значение восстанавливается как

значение = digit[0]·10000^weight + digit[1]·10000^(weight−1) + digit[2]·10000^(weight−2) + …

Третье поле, dscale, задаёт, сколько знаков после запятой печатать, и хранится в самом значении. Для 123456.00 цифры группируются как 12 и 3456; первая группа стоит в разряде 10000¹, поэтому weight = 1, а dscale = 2, так как после запятой нужно напечатать два знака.

Отсюда — неочевидное следствие: 1.5 и 1.50 имеют одинаковые цифры [1, 5000] и одинаковый вес 0, различается только dscale (1 и 2). Именно он решает, напечатается «1.5» или «1.50». При этом 1.0 и 1.00 система считает одним и тем же числом, хотя печатает их по-разному. Хеши этих значений тоже совпадают, потому что при вычислении хеша масштаб не учитывается: два numeric могут считаться равными, но иметь разные масштабы.

Почему numeric медленный

Операция a + b для bigintцелочисленный тип фиксированной ширины — одна инструкция с результатом в регистре. Для numeric это цепочка: выровнять масштабы, сложить, проверить пределы, выделить память под результат, записать результат в память. Плюс numeric передаётся по ссылке, а не по значению: большой масштаб и гибкая граница промежуточного результата не влезают в восемь байт.

Вторая причина — доступ к колонкам внутри строки. Строка в PostgreSQL — это заголовок, битовая карта NULL-ов и дальше значения колонок подряд, без разделителей. Чтобы добраться до двадцатой колонки, нужно знать её смещение от начала. Если все колонки фиксированной ширины, смещение считается арифметически и кэшируется в дескрипторе таблицы один раз на всё время жизни. Но кэшировать смещения можно только до первой колонки нефиксированной длины — правило записано прямо в коде, который строит описатель таблицы: «не кэшируем смещения дальше атрибутов фиксированной ширины». Numeric — ровно такой случай. Кэш обрывается на первой такой колонке, и дальше каждое смещение вычисляется заново чтением заголовка каждого предыдущего значения: чтобы узнать смещение c20, надо прочитать заголовки c1…c19 на каждой строке заново.

Масштаб результата: сложение, умножение, деление

Для сложения и умножения масштаб результата задаёт стандарт SQL: у сложения он равен максимуму из масштабов аргументов, у умножения — сумме. Отсюда select 1.5 + 1.50 даёт 3.00 (2 знака), а select 2.0 * 3.00 — 6.000 (3 знака).

Для деления масштаб выбирается функцией select_div_scale, которая вызывается в numeric_div_safe перед самим делением:

rscale = select_div_scale(&arg1, &arg2);

Пример: select 10.0 / 4 даёт 2.5000000000000000 — 16 знаков после запятой. Формула rscale в исходниках не приводится, поэтому связь со стандартом SQL и с числом 20 знаков у деления по этим источникам не видна.

Тип money и его ограничения

Документация не рекомендует использовать числа с плавающей точкой для денег из-за возможных ошибок округления. Значение money можно преобразовать в numeric без потери точности, но преобразование в другие типы может потерять точность и должно выполняться в два этапа.

При приведении '12.34'::float8::numeric::money сначала float8 преобразуется в numeric, а затем numeric_cash переводит numeric в money. Он берёт frac_digits из локали; если значение меньше 0 или больше 10, используется 2:

fpoint = lconvert->frac_digits;
if (fpoint < 0 || fpoint > 10)
	fpoint = 2;

Дальше вычисляется масштаб как 10 в степени fpoint, входная сумма умножается на этот масштаб и округляется до ближайшего целого. Если при этом возникает ошибка, преобразование не даёт результата. Поскольку float8 уже мог внести ошибку округления до преобразования в numeric, итоговое значение money может оказаться неточным.

Деление money на целое усекает дробную часть к нулю. cash_div_int4 принимает значение money и 4-байтовое целое, после чего делегирует работу cash_div_int64, приводя делитель к int64. Тот сначала проверяет делитель на ноль и при i == 0 сообщает об ошибке «division by zero». Случай i == -1 обрабатывается отдельно: INT64_MIN / -1 не представим на машине с two's-complement, поэтому деление заменяется на отрицание через pg_neg_s64_overflow, и при переполнении выдаётся ошибка «money out of range». Во всех остальных случаях переполнение невозможно, и возвращается обычный результат c / i.

RLS: как политики превращаются в quals

Сборка политик для отношения начинается с get_policies_for_relation, которая по команде и пользователю раскладывает подходящие политики в два списка — permissiveразрешающие политики, которые объединяются с другими через OR и restrictiveограничивающие политики, которые применяются независимо от остальных через AND. Для каждой политики проверяется совпадение команды и роли, и в зависимости от флага permissive она попадает в соответствующий список. Затем restrictive-политики сортируются по имени, чтобы порождаемые ими WCOWithCheckOption — проверка условия WITH CHECK для новых строк проверялись в определённом порядке, а после этого к спискам добавляются политики от расширений через хуки row_security_policy_hook_restrictive и row_security_policy_hook_permissive.

Далее add_security_quals формирует securityQuals: сначала собирает USING-условия всех permissive-политик, затем добавляет по одному условию каждой restrictive-политики, а в конце объединяет permissive-условия через OR в одно условие и тоже добавляет его. Если permissive-условий нет, добавляется всегда-ложное условие. Параллельно add_with_check_options формирует withCheckOptions: для permissive-политик берётся with_check_qual, а при его отсутствии — qual, они объединяются через OR в один WithCheckOption без имени политики; для каждой restrictive-политики создаётся отдельный WithCheckOption с именем политики, чтобы ошибка указывала на конкретную политику. При отсутствии permissive-условий добавляется всегда-ложный WCO.

Почему так: permissive-политики объединяются через OR — при нескольких применённых политиках они комбинируются логическим «OR». Restrictive объединяются через AND — записи должны пройти каждую из них. Если permissive-политик нет, доступ запрещается: политика запрета по умолчанию предотвращает все новые строки.

Как RLS применяется к разным командам

При UPDATE, DELETE и MERGE, если требуются права SELECT (например, из-за RETURNING или WHERE по столбцам отношения), сначала собираются политики CMD_SELECT и добавляются как security quals, чтобы отфильтровать невидимые строки. Для INSERT и UPDATE дополнительно добавляются withCheckOptions — проверки, которым должна удовлетворять записываемая строка: для INSERT — WCO_RLS_INSERT_CHECKпроверка WITH CHECK-условий INSERT-политик для вставляемой строки, для UPDATE — WCO_RLS_UPDATE_CHECKпроверка WITH CHECK-условий UPDATE-политик для новой версии строки, используя WITH CHECK или USING политик. Если при этом требуются права SELECT, ALL/SELECT-политики тоже добавляются как WCO с force_usingрежимом, при котором для проверки берётся USING-условие политики, а нарушение вызывает ошибку вместо молчаливого отбрасывания строки — без него строка, не прошедшая проверку, была бы просто отброшена без сообщения об ошибке.

Для INSERT ... ON CONFLICT DO UPDATE/SELECT при наличии ACL_UPDATE политики CMD_UPDATE добавляются как WCO_RLS_CONFLICT_CHECKпроверка USING-условий UPDATE-политик для уже существующей конфликтующей строки с force_using=true — это проверка существующей строки. ALL/SELECT-политики добавляются туда же. При ONCONFLICT_UPDATE дополнительно WITH CHECK политик UPDATE добавляются как WCO_RLS_UPDATE_CHECK — проверка новой строки, а ALL/SELECT-политики — как WCO_RLS_UPDATE_CHECK с force_using=true.

Для MERGE UPDATE USING-политики проверяются как WCO_RLS_MERGE_UPDATE_CHECKпроверка USING-условий UPDATE-политик для существующей строки при MERGE на существующей строке, WITH CHECK — как WCO_RLS_UPDATE_CHECK, а ALL/SELECT — как WCO_RLS_UPDATE_CHECK; для DELETE USING — как WCO_RLS_MERGE_DELETE_CHECKпроверка USING-условий DELETE-политик для существующей строки при MERGE; для INSERT — как WCO_RLS_INSERT_CHECK, а ALL/SELECT при RETURNING — как WCO_RLS_INSERT_CHECK.

Инвалидация планов и хуки расширений

get_row_security_policies() при первом вызове устанавливает выходной параметр *hasRowSecurity в false, а затем, если RLS на таблице включена, помечает запрос как содержащий row security, присваивая *hasRowSecurity = true, чтобы plancache мог инвалидировать план при изменении политик — например, при смене роли. Флаг также выставляется в true, чтобы заставить перепланировать запрос при изменении GUC или роли.

Расширения могут добавлять свои политики через два хука: row_security_policy_hook_permissive — для политик, объединяемых с другими permissive через OR, и row_security_policy_hook_restrictive — для политик, применяемых независимо от остальных через AND. В get_policies_for_relation() после сбора встроенных политик вызываются эти хуки, и возвращённые ими политики добавляются в соответствующие списки, если применимы к указанной роли.

Ссылочная целостность обходит RLS

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

Из этого следуют косвенные способы определить существование значения. Попытка вставить дубликат в столбец первичного ключа или уникального ограничения: если вставка не удаётся, пользователь может сделать вывод, что значение уже существует. Другой пример — пользователь может вставлять значения в таблицу, ссылающуюся на скрытую таблицу, и успех будет означать, что значение существует в referenced-таблице. Эти проблемы можно решать тщательным составлением политик, запрещающих вставку, удаление или обновление записей, которые могут выдать значение, или использованием генерируемых значений (например, суррогатных ключей) вместо ключей с внешним смыслом.

pg_dump под RLS: молча битые дампы

pg_dump по умолчанию выставляет row_security = off, чтобы выгрузить все строки таблицы без исключения. Если у роли не хватает прав обойти RLS, он завершается с ошибкой, а не выгружает часть данных. Права обходить RLS есть у суперпользователя, у роли с атрибутом BYPASSRLS и у владельца таблицы — если не включён FORCE ROW LEVEL SECURITY.

Типичная ловушка: дамп идёт под ролью с именем вида ..._owner, но владельцем таблиц является postgres, атрибута BYPASSRLS у роли нет. Дамп падает на первой же таблице с политикой с ошибкой «query would be affected by row-level security policy for table». pg_dump открывает выходной файл в начале и, упав, оставляет недописанный файл — поэтому в каталоге бэкапов файлы появляются по расписанию и выглядят нормально.

Запуск через runuser -u postgres переключает пользователя на суперпользователя postgres, у которого есть право обходить RLS, а env -u PGUSER -u PGPASSWORD сбрасывает переменные окружения, которые могли бы подменить пользователя и пароль подключения. В результате pg_dump под RLS видит все строки таблиц, а не только разрешённые политикой, и дамп собирается полностью.

Передача tenant_id через SET LOCAL

В транзакции tenant_id извлекается из контекста, и при отсутствии или пустом значении вызывается panic, чтобы не продолжать без tenant_id. Затем открывается транзакция, после чего выполняется SELECT setconfig('app.current_tenant', $1, true) — третий параметр true означает, что настройка живёт только до конца транзакции, и Postgres сбросит её при возврате соединения в пул. После этого вызывается бизнес-логика, и при успехе транзакция коммитится.

Это безопаснее подстановки tenant_id в каждый WHERE: при логической изоляции в коде разработчик обязан помнить про tenant_id в каждом запросе, и человеческий фактор — бомба замедленного действия. Вместо этого политика tenant_isolation_policy на таблице orders использует USING (tenant_id = current_setting('app.current_tenant')::uuid), а ALTER TABLE orders FORCE ROW LEVEL SECURITY запрещает владельцам обходить правила.

View поверх RLS без security_barrier

View поверх RLS-таблицы без security_barrier позволяет злоумышленнику поместить вызов своей функции в WHERE внешнего запроса, и планировщик может выполнить эту функцию до фильтрации строк внутри View. Запрос вида SELECT * FROM public_docs WHERE heavy_function(secret_field) даёт функции доступ к приватным данным, если планировщик решит выполнить её до фильтрации is_public.

Опция WITH (security_barrier) заставляет Postgres сначала отфильтровать строки внутри View, и только потом отдавать их наружу. Любые View поверх RLS-таблиц должны быть с security_barrier.

LEAKPROOF и Index Scan

Функция, объявленная как LEAKPROOFпометка, сообщающая планировщику, что функция не может раскрыть данные в обход RLS, возвращает возможность использовать Index Scan. «Утечка» здесь — это когда функция получает доступ к строке, которую политика пользователю показывать не должна, и по её результату можно судить о скрытых данных. Без пометки планировщик не знает, безопасна ли функция, поэтому может перестраховаться и сначала применить условие RLS (tenant_id = ...) ко всем строкам через Seq Scan, а уже потом фильтр по title. С LEAKPROOF планировщик может сначала использовать индекс по title (Index Cond), а затем отфильтровать результат по tenant_id (Filter). Это даёт Index Scan вместо Seq Scan.

Поэтапная миграция на RLS

Фаза 1 включает сам механизм RLS, но доступ не блокируется: создаётся политика migration_phase_1, которая пропускает всех через USING (true) WITH CHECK (true). Приложение работает как раньше, БД никак не ограничивает доступ, проверяется, что ENABLE RLS не убил перформанс планировщика.

В фазе 2 middleware приложения начинает слать set_config('app.current_tenant', ...), а политика migration_phase_2 становится гибридной: если контекст есть, строки фильтруются по tenant_id = current_setting('app.current_tenant')::uuid, а если контекста нет — пускаются все. Эта фаза логически опасна: забытый контекст вернёт всю базу, поэтому её используют кратковременно под наблюдением и логированием.

В финальной фазе политика tenant_isolation_policy жёстко требует tenant_id = current_setting('app.current_tenant')::uuid, а ALTER TABLE orders FORCE ROW LEVEL SECURITY запрещает владельцам обходить правила — приложение без контекста больше не получает доступ.

Роли с BYPASSRLS

Роли с BYPASSRLS отделяются от app_user тем, что app_user (клиентский API) жёстко ограничен, а admin_user (воркеры) видит всё; для этого выдаётся атрибут роли командой ALTER ROLE background_worker WITH BYPASSRLS. При включённом на таблице RLS весь обычный доступ к строкам должен быть разрешён политикой, и если политики нет, действует default-deny — ни одна строка не видна и не может быть изменена. Политики могут быть привязаны к командам (ALL, SELECT, INSERT, UPDATE, DELETE) и к ролям, а обычные правила членства и наследования ролей применяются.

Как сравнивали и что получилось

numeric против double precision

Сравнивались два плана выполнения одного и того же запроса группировки по 12 колонкам: первый — когда колонки имеют тип numeric, второй — когда те же колонки заменены на double precision. Второй план описан как «ровно тот же самый запрос, но только колонки имеют тип double precision: в интересах теста мы пренебрегли точностью». В первом плане сканируется таблица tt4, во втором — tt4_dbl. Отдельно показан пример DDL и INSERT для демонстрации размера значения numeric: CREATE TABLE t(x numeric(15,2)); INSERT INTO t VALUES (123456.00); с последующими вызовами pg_column_size.

Сканирование шло шестью параллельными последовательными сканами по 500000 строк. Вариант с double precision просканировал больше дисковых страниц (125 тыс. против 111 тыс.), но агрегация с numeric требует в 9 раз больше памяти. В источнике заявлено ускорение практически в три раза.

Оговорки: в тесте с double precision точностью пренебрегли в интересах теста. AVG по денежной колонке в DuckDB — это double, и на таком типе не выполняется требование регламента ЕС 1103/97 «shall not be rounded or truncated». DuckDB не выполняет работу numeric по аккуратному и предсказуемому определению масштаба деления и теряет точное десятичное деление, восстановить которое в этом дизайне уже нельзя. Полученные цифры относятся к варианту с double precision, а не к точной десятичной арифметике.

DuckDB: противоположный подход к DECIMAL

DuckDB принял ровно противоположные решения по всем четырём пунктам, и на контрасте видно, где именно Postgres теряет производительность. При фиксированной ширине DECIMAL унификация масштаба и пределы точности операций вместе приводят к проблеме: умножение DECIMAL(18,2) на DECIMAL(18,2) даёт DECIMAL(18,4), хотя должно быть 36,4, а DECIMAL(20,2) * DECIMAL(20,2) даёт DECIMAL(38,4), где вход уже int128 и правило не сработало. Конкретный пример переполнения: умножение двух decimal(18,0) из 999999999999999999 возвращает ошибку «Out of Range Error: Overflow in multiplication of DECIMAL(18)».

Для деления и агрегатов DuckDB теряет точность: typeof(DECIMAL(10,2) / DECIMAL(10,2)) → DOUBLE, typeof(AVG(DECIMAL(10,2))) → DOUBLE, тогда как typeof(DECIMAL(10,2) % DECIMAL(10,2)) → DECIMAL(10,2), то есть остаток остаётся точным.

RLS и производительность

Замеры получены на нагрузочных тестах в Docker на примерно 10 тысячах записей.

LEAKPROOF-функция возвращает Index Scan и 50 мс. SELECT COUNT(*) с RLS на миллионе строк — это всегда Full Scan.

В старых версиях PostgreSQL при использовании RLS Index Scan сначала находил топ-K ближайших записей по индексу, а уже потом RLS отфильтровывал чужие строки, из-за чего пользователь мог получить 0 результатов. В версии 0.8.0+ появился итеративный скан, который продолжает искать в индексе, пока не наберёт нужное количество (LIMIT) видимых пользователю записей. В PostgreSQL до 15 версии вьюхи по умолчанию выполняются с правами владельца вьюхи, а не текущего пользователя; для PostgreSQL 15+ рекомендуется использовать security_invokerрежим выполнения вьюхи с правами и политиками вызывающего пользователя, а не её владельца = true.

Что из этого следует на практике

• Деньги — только numeric. float8 может внести ошибку округления до преобразования в money, и итоговое значение окажется неточным. money тоже неудобен: масштаб берётся из локали, а деление усекает дробную часть к нулю.
• numeric дорог не в арифметике, а в доступе к колонкам. Кэш смещений обрывается на первой колонке нефиксированной длины, и дальше каждое смещение вычисляется заново чтением заголовков всех предыдущих значений на каждой строке. Двадцать numeric-колонок в строке ломают кэширование смещений.
• Масштаб результата предсказуем для сложения и умножения (максимум и сумма масштабов аргументов соответственно), но для деления он выбирается функцией select_div_scale, и формула в исходниках не приводится.
• RLS не применяется к проверкам ссылочной целостности. Через попытку вставить дубликат или вставить значение в таблицу со ссылкой на скрытую таблицу можно косвенно определить существование значения. Лечится тщательными политиками или суррогатными ключами.
• pg_dump под RLS падает, но оставляет недописанный файл. Дампы выглядят нормально, пока не попробуешь восстановить. Запуск через runuser -u postgres с env -u PGUSER -u PGPASSWORD даёт суперпользователя с правом обхода RLS и сбрасывает переменные окружения подключения.
• View поверх RLS-таблиц — только с security_barrier, иначе функция в WHERE внешнего запроса может выполниться до фильтрации внутри View и получить доступ к приватным данным.
• LEAKPROOF-функции возвращают Index Scan вместо Seq Scan, когда планировщик иначе перестраховывается и применяет условие RLS ко всем строкам до фильтра.
• tenant_id через SET LOCAL безопаснее подстановки в каждый WHERE: настройка живёт до конца транзакции и сбрасывается при возврате соединения в пул, а FORCE ROW LEVEL SECURITY закрывает обход правил владельцем.

Источники

• habr.com/ru/articles/1073040/
• habr.com/ru/articles/987364/
• github.com/…/rowsecurity.c
• habr.com/ru/articles/1092008/
• www.postgresql.org/…/sql-createpolicy.html
• www.postgresql.org/…/ddl-rowsecurity.html
• www.postgresql.org/…/datatype-money.html
• github.com/…/numeric.c