Политики защиты строк (RLS) в PostgreSQL

Политики защиты строк в PostgreSQL (RLS) необходимы чтобы разграничить доступ к разным строкам. Применяется это редко, но знать про это нужно.
Tags:

Что такое политики защиты строк

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

Пишется специальное логическое выражение и вычисляется для каждой строки. Если такое выражение истинно, то мы видим строку, а в противном случае не видим. Такое выражение называют предикатом.

Один предикат применяется для существующих строк и используется командами:

  • 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

В выводе выше первая ошибка фиксируется первой политикой, а вторая второй (запретительной).

Если понравилась статья, подпишись на мой канал в  VK  или  Telegram .