Политики защиты строк в PostgreSQL (RLS) необходимы чтобы разграничить доступ к разным строкам. Применяется это редко, но знать про это нужно.
Что такое политики защиты строк
Привилегии для ролей определяют доступ к целым табличкам, или к столбцам. Идея защиты строк состоит в том, чтобы разграничивать доступы к разным строчкам. В документации про этот механизм можете почитать тут.
Пишется специальное логическое выражение и вычисляется для каждой строки. Если такое выражение истинно, то мы видим строку, а в противном случае не видим. Такое выражение называют предикатом.
Один предикат применяется для существующих строк и используется командами:
- SELECT
- UPDATE
- DELETE
По умолчанию, если предикат возвращает ложь, то никакой ошибки не будет. Просто делается вид, что этой строчки в таблице нет. Это поведение можно изменить сбросив параметр row_security.
Второй предикат применяется для новых строк и используется командами:
- INSERT
- UPDATE
При работе второго предиката, если он возвращает значение ложь, то появляется ошибка. Ошибка говорит нам, что у нас нет права доступа вставить или обновить определённую строку.
Эти предикаты работают в дополнение к привилегиям. Сначала проверяются ваши привилегии на объект, а затем еще и предикаты, если они у вас используются. По умолчанию политики защиты строк выключены.
Условия применения политик
Включить разграничение прав к строкам можно для таблицы. При включении указывается для каких ролей и операторов мы включаем эту политику.
Эти политики не применяются:
- при проверки ограничений целостности (внешние ключи будут работать в любом случае);
- для суперпользователя и ролей с атрибутом BYPASSRLS;
- для владельца объекта (если не включить принудительно).
На одну таблицу можно повесить несколько политик.
По умолчанию в PostgreSQL если вы включили RLS (политика защиты строк) для таблицы то все запрещается. Дальше вы должны создать разрешительные политики.
Также существуют ограничительные политики дополнительно к разрешительным.
Практика
Создание предиката для существующих объектов
Подключимся к базе данных и сделаем таблицу в которую добавим две строки:
postgres@s-pg13:~$ psql
Timing is on.
psql (13.3)
Type "help" for help.
postgres@postgres=# CREATE TABLE users_depts(login text, department text);
CREATE TABLE
Time: 3,448 ms
postgres@postgres=# INSERT INTO users_depts VALUES ('alice', 'PR'), ('bob', 'Sales');
INSERT 0 2
Time: 0,863 ms
Создадим ещё таблицу «revenue«, в которой будет информация о доходах и расходах PR отдела и Sales отдела:
postgres@postgres=# CREATE TABLE revenue(department text, amount numeric(10,2)); CREATE TABLE Time: 1,329 ms postgres@postgres=# INSERT INTO revenue SELECT 'PR', -random()* 100.00 FROM generate_series(1,100000); INSERT 0 100000 Time: 214,237 ms postgres@postgres=# INSERT INTO revenue SELECT 'Sales', random()*1000.00 FROM generate_series(1,10000); INSERT 0 10000 Time: 28,831 ms
PR дают небольшие расходы (100.00) но количество их большое (100000). А отдел продаж приносит большие доходы (1000.00) но количество их меньше (10000).
Теперь создадим предикат:
postgres@postgres=# CREATE POLICY departments ON revenue USING (department = (SELECT department FROM users_depts WHERE login = current_user)); CREATE POLICY Time: 1,101 ms
Само логическое выражение это — department = (SELECT department FROM users_depts WHERE login = current_user). То есть название отдела (department из таблицы revenue) должно совпадать с именем пользователя из таблички users_depts (SELECT department FROM users_depts WHERE login = current_user).
И включим эту политику с помощью команды ALTER:
postgres@postgres=# ALTER TABLE revenue ENABLE ROW LEVEL SECURITY; ALTER TABLE Time: 0,558 ms
Далее нужно выдать привилегии Алисе и Бобу, которых мы делали раньше:
postgres@postgres=# GRANT SELECT ON users_depts, revenue TO alice; GRANT Time: 0,705 ms postgres@postgres=# GRANT SELECT ON users_depts, revenue TO bob; GRANT Time: 0,611 ms
Суперпользователь видит все строчки:
postgres@postgres=# SELECT department, SUM(amount) FROM revenue GROUP BY department; department | sum ------------+------------- PR | -5007687.16 Sales | 4997577.95 (2 rows) Time: 22,451 ms
Теперь проверим что увидят Алиса и Боб:
postgres@postgres=# \c - alice You are now connected to database "postgres" as user "alice". alice@postgres=> SELECT department, SUM(amount) FROM revenue GROUP BY department; department | sum ------------+------------- PR | -5007687.16 (1 row) Time: 18,413 ms alice@postgres=> \c - bob You are now connected to database "postgres" as user "bob". bob@postgres=> SELECT department, SUM(amount) FROM revenue GROUP BY department; department | sum ------------+------------ Sales | 4997577.95 (1 row) Time: 8,707 ms
Создание предикатов для новых объектов
Разрешим теперь Бобу добавлять строки в таблицу, но только для своего отдела и только в пределах 100 рублей:
- первое требование уже выполнено, так как первый предикат работает и для существующих и для новых строк;
- для второго создадим новую ограничительную политику (AS RESTRICTIVE):
bob@postgres=> \c - postgres You are now connected to database "postgres" as user "postgres". postgres@postgres=# CREATE POLICY amount ON revenue AS RESTRICTIVE USING (true) WITH CHECK (abs(amount) <= 100.00); CREATE POLICY Time: 1,397 ms
В команде выше два условия:
- USING (true) — все существующие строки видны в любом случае;
- WITH CHECK (abs(amount) <= 100.00) — новые строки должны быть не более 100.000;
И дадим Бобу еще привилегию на вставку в эту таблицу:
postgres@postgres=# GRANT INSERT ON revenue TO bob; GRANT Time: 0,520 ms
Проверим работу для Боба:
postgres@postgres=# \c - bob
You are now connected to database "postgres" as user "bob".
bob@postgres=> INSERT INTO revenue VALUES ('Sales', 42.00);
INSERT 0 1
Time: 1,732 ms
bob@postgres=> INSERT INTO revenue VALUES ('PR', 42.00);
ERROR: new row violates row-level security policy for table "revenue"
Time: 0,427 ms
bob@postgres=> INSERT INTO revenue VALUES ('Sales', 1000.00);
ERROR: new row violates row-level security policy "amount" for table "revenue"
Time: 0,279 ms
В выводе выше первая ошибка фиксируется первой политикой, а вторая второй (запретительной).