До сих пор права в postgresql мы раздавали грубо: GRANT на таблицу открывает все её строки целиком. Но в жизни часто нужно тоньше - чтобы менеджер видел только своих клиентов, а арендатор сервиса - только свои заказы в общей таблице. Этот урок про Row-Level Security (RLS): механизм, который фильтрует строки автоматически, на уровне ядра, какой бы запрос ни прислал клиент. Заодно разберём шифрование чувствительных полей через pgcrypto, маскирование данных и в двух словах - что такое SE-PostgreSQL для мандатного доступа.
Как это работает
Идея RLS простая: к каждому обращению к таблице сервер незаметно дописывает условие WHERE, которое вы задали заранее в виде политики. Пользователь пишет SELECT * FROM orders, а планировщик подставляет ваш фильтр, и человек физически не может вытащить чужие строки - даже подбором, даже хитрым запросом. Защита живёт в базе, а не в коде приложения, поэтому её нельзя обойти через прямое подключение.
Включается RLS в два шага. Сначала ALTER TABLE ... ENABLE ROW LEVEL SECURITY переводит таблицу в режим с проверкой. Важная деталь: сразу после включения и до создания политик действует принцип запрета по умолчанию - не подходящих под политику строк не видно, а если политик нет вообще, обычный пользователь не видит ничего. Это безопасное поведение: забыли описать правило - данные закрыты, а не открыты.
Сами правила создаются командой CREATE POLICY. У политики две независимые части. USING - это предикат видимости: к каким строкам разрешено читать и трогать (SELECT, UPDATE, DELETE). WITH CHECK - предикат записи: какие строки разрешено вставлять или каким делать после UPDATE. Их разделение нужно, чтобы нельзя было через INSERT или UPDATE записать строку, которую потом сам же не увидишь и не имеешь права создавать.
Две тонкости про обход. Владелец таблицы и роли с атрибутом BYPASSRLS политику игнорируют - это удобно для миграций, но опасно, если приложение ходит под владельцем. Чтобы правила действовали и на владельца, ставят ALTER TABLE ... FORCE ROW LEVEL SECURITY. Суперпользователь обходит RLS всегда.
Откуда политика берёт контекст текущего пользователя или арендатора? Из двух источников. Либо из current_user - имя роли подключения. Либо из параметра сессии, который приложение выставляет через set_config или SET, а политика читает функцией current_setting. Второй способ - основа мультиарендности одной таблицей: все клиенты лежат в одном orders, а tenant_id берётся из переменной сессии.
SQL и примеры
Покажем RLS на демобазе Авиаперевозки (схема bookings). Представим, что у нас несколько агентств, и каждое должно видеть только свои бронирования. Добавим колонку-владельца в копию таблицы, чтобы не ломать оригинал.
Код: Выделить всё
CREATE TABLE bookings.my_bookings AS
SELECT b.*, 'agency_a'::text AS tenant FROM bookings.bookings b LIMIT 1000;
ALTER TABLE bookings.my_bookings ENABLE ROW LEVEL SECURITY;
Код: Выделить всё
CREATE POLICY tenant_isolation ON bookings.my_bookings
USING (tenant = current_setting('app.tenant', true))
WITH CHECK (tenant = current_setting('app.tenant', true));
Код: Выделить всё
SET app.tenant = 'agency_a';
SELECT count(*) FROM bookings.my_bookings; -- видны строки агентства A
SET app.tenant = 'agency_b';
SELECT count(*) FROM bookings.my_bookings; -- 0 строк, чужое скрыто
Код: Выделить всё
CREATE POLICY read_all ON bookings.my_bookings
FOR SELECT TO staff USING (true);
CREATE POLICY del_own ON bookings.my_bookings
FOR DELETE TO staff USING (tenant = current_setting('app.tenant', true));
Теперь чувствительные поля. Хранить паспорт или телефон в открытом виде нельзя - шифруем расширением pgcrypto. Симметричное шифрование с ключом-фразой.
Код: Выделить всё
CREATE EXTENSION IF NOT EXISTS pgcrypto;
-- зашифровать
SELECT pgp_sym_encrypt('1234 567890', 'secret_key');
-- расшифровать обратно
SELECT pgp_sym_decrypt(passport_enc, 'secret_key') FROM clients;
Код: Выделить всё
-- сохранить хеш пароля
INSERT INTO users(login, pass) VALUES ('ivan', crypt('p@ss', gen_salt('bf')));
-- проверить при входе
SELECT login FROM users WHERE login='ivan' AND pass = crypt('p@ss', pass);
Код: Выделить всё
CREATE VIEW tickets_masked AS
SELECT ticket_no,
left(passenger_name,1) || '***' AS passenger_name,
'***' || right(passenger_id,4) AS passenger_id
FROM bookings.tickets;
Частые грабли
- Включили ENABLE ROW LEVEL SECURITY, но забыли создать политику - и удивляетесь, что таблица отдаёт ноль строк. Это не баг, это запрет по умолчанию.
- Приложение ходит в базу под владельцем таблицы - RLS на него не действует без FORCE ROW LEVEL SECURITY. Заводите отдельную непривилегированную роль для приложения.
- Описали только USING и думаете, что INSERT защищён. Нет: для вставки нужен WITH CHECK, иначе чужие строки спокойно запишутся.
- Не указали второй аргумент true в current_setting - при отсутствии переменной сессии запрос падает с ошибкой вместо тихого пустого результата.
- Предикат политики тяжёлый (подзапрос, функция без индекса) - он выполняется для каждой строки и роняет производительность. Проверяйте план через explain analyze.
- Ключ для pgcrypto лежит в коде или в SQL-логах - тогда шифрование бессмысленно. Ключами должно управлять приложение или внешнее хранилище.
- Маскирующее представление обходится, если у пользователя остался прямой доступ к базовой таблице. Отзывайте GRANT на исходную таблицу.
- Создайте таблицу-копию из bookings.bookings с колонкой tenant и заполните двумя значениями арендаторов.
- Включите на ней RLS и убедитесь под обычной ролью, что строк не видно вообще.
- Создайте политику с USING и WITH CHECK по current_setting('app.tenant', true).
- Через SET app.tenant переключайтесь между арендаторами и проверьте, что count меняется.
- Попробуйте INSERT строки с чужим tenant - убедитесь, что WITH CHECK её отвергает.
- Подключите pgcrypto и сохраните одно поле через pgp_sym_encrypt, затем расшифруйте.
- Включите FORCE ROW LEVEL SECURITY и проверьте, что теперь и владелец таблицы подчиняется политике.
- Чем отличается роль USING от WITH CHECK в политике и какие команды каждая затрагивает?
- Почему сразу после ENABLE ROW LEVEL SECURITY таблица может вернуть ноль строк?
- Кто обходит RLS по умолчанию и как заставить правила действовать на владельца таблицы?
- Как объединяются несколько PERMISSIVE политик и чем от них отличается RESTRICTIVE?
- Почему для паролей применяют crypt с gen_salt, а не pgp_sym_encrypt?
- Как реализовать мультиарендность одной таблицей через переменную сессии?
