# Занятие 03: Управление доступом / Доступ на уровне строк

> Исходная страница в нативном Markdown: Управление доступом / Доступ на уровне строк

---

Индекс LLMS: [llms.txt](/ru/llms.txt)

---

<!-- Generated by scripts/generate_skeleton.py. -->

> [!NOTE]
> Эта страница извлечена из официального PDF в нативный Markdown. Текст слайдов, код и комментарии сохранены в порядке чтения; исходные PDF и HTML остаются эталоном макета.
> **HTML source:** [dbs_03_access_rows.html](https://edu.postgrespro.ru/16/dbs-16/dbs_03_access_rows.html)
> **Archive:** [DBS-handouts-16.zip](https://edu.postgrespro.ru/16/DBS-handouts-16.zip)
> **Страниц PDF:** 32

## Page 1: Управление доступом / Доступ на уровне строк {#source-page-001}

### Авторские права

© Postgres Professional, 2017–2026 Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Алексей Береснев Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)
### Использование материалов курса

Некоммерческое использование материалов курса (презентации, демонстрации) разрешается без ограничений. Коммерческое использование возможно только с письменного разрешения компании Postgres Professional. Запрещается внесение изменений в материалы курса.
### Обратная связь

Отзывы, замечания и предложения направляйте по адресу: edu@postgrespro.ru
### Отказ от ответственности

Компания Postgres Professional не несет никакой ответственности за любые повреждения и убытки, включая потерю дохода, нанесенные прямым или непрямым, специальным или случайным использованием материалов курса. Компания Postgres Professional не предоставляет каких-либо гарантий на материалы курса. Материалы курса предоставляются на основе принципа «как есть» и компания Postgres Professional не обязана предоставлять сопровождение, поддержку, обновления, расширения и изменения.

## Page 2: Темы {#source-page-002}

- Задача доступа на уровне строк
- Реализация с помощью функций
- Реализация с помощью представлений
- Политики защиты строк

## Page 3: Доступ на уровне строк {#source-page-003}

**Управление доступом на основе ролей**
  - разграничение на уровне отношений (столбцов)
  - роли и привилегии, базовый механизм
- Управление доступом на уровне строк
  - разграничение на уровне отдельных строк отношений
  - требуется доверенный контекст
  - различные способы реализации

### Комментарии преподавателя

Роли и привилегии реализуют ролевую модель доступа (Role-Based Access Control, RBAC), базовый механизм управления доступом PostgreSQL. Эта модель регламентирует доступ к отношениям (таблицам, представлениям), возможно к отдельным их столбцам.

Более детальное разграничение информации на уровне отдельных строк как правило реализуется в приложениях, но может возникнуть потребность ограничить доступ и на уровне базы данных. Такое разграничение требует учета детальных условий (Fine-Grained Access Control, FGAC).

Критерии доступа должны быть основаны на информации, которой можно доверять. Очевидно, что нельзя полагаться ни на какие данные, передаваемые самим пользователем (параметры функций, условия в запросах и т. п.), поскольку злоумышленник может их подменить. По сути, единственное, чему можно доверять — это имя пользователя, поскольку СУБД проводит его проверку (аутентификацию). Если же аутентификация клиентов выполняется приложением, задача осложняется (см. задание Практики+).

Существуют разные подходы к решению задачи, основные из которых рассмотрены дальше в этой теме.

## Page 4: Функции {#source-page-004}

**Подход к решению**
- Характеристика безопасности

## Page 5: Подход к решению {#source-page-005}

![Source slide page 5: Подход к решению](images/page-005.png){#source-figure-005 caption="Source slide page 5: Подход к решению"}

**Табличные функции для чтения данных**
- Обычные функции или процедуры для изменения данных

### Комментарии преподавателя

Одно из возможных решений состоит в предоставлении пользователю API, реализованного с помощью функций. Для чтения данных применяется табличная функция (возвращающая множество строк). К такой функции можно обращаться в предложении FROM как к таблице, а при необходимости можно и передавать параметры (которые функция может использовать, в том числе, для фильтрации данных).

<https://postgrespro.ru/docs/postgresql/16/xfunc-sql#XFUNC-SQL-FUNCTIO> NS-RETURNING-SET

<https://postgrespro.ru/docs/postgresql/16/xfunc-sql#XFUNC-SQL-FUNCTIO> NS-RETURNING-TABLE

Для изменения данных можно использовать обычные функции; перед собственно изменением в них можно выполнить все необходимые проверки.

Привилегий на базовую таблицу пользователь иметь не должен, ему необходима только привилегия EXECUTE на интерфейсные функции (такая привилегия есть у псевдороли PUBLIC по умолчанию).

## Page 6: Функции {#source-page-006}

Создадим базу данных:
```sql
student=# CREATE DATABASE access_rows;
```
```sql
CREATE DATABASE
```
```sql
student=# \c access_rows
```
```text
You are now connected to database "access_rows" as user "student".
```
Для демонстрации нам потребуются традиционные роли:
```sql
student=# CREATE USER alice; -- примерный пользователь
```
```sql
CREATE ROLE
```
```sql
student=# CREATE USER bob;   -- хакер в душе
```
```sql
CREATE ROLE
```
Алиса будет работать в одном сеансе, Боб — в другом.
```sh
student$ psql -d access_rows -U alice
```
```sh
student$ psql -d access_rows -U bob
```
Таблица содержит информацию о доходах и расходах.
```sql
student=# CREATE TABLE revenue(
  who text DEFAULT current_user,
  amount numeric(10,2)
);
```
```sql
CREATE TABLE
```
Заполним таблицу произвольными данными (Алиса зарабатывает, Боб тратит).
```sql
student=# INSERT INTO revenue(who, amount) VALUES
  ('alice', 1000.00), ('alice', 2000.00),
  ('bob',  -1000.00), ('bob',  -2000.00);
```
```sql
INSERT 0 4
```
Задача состоит в том, чтобы Алиса видела только свои операции, а Боб — только свои.
Создадим функцию, возвращающую множество строк. Запрос фильтрует данные, оставляя только строки, соответствующие пользователю сеанса:
```sql
student=# CREATE FUNCTION get_revenue() RETURNS TABLE(who text, amount numeric)
LANGUAGE sql
BEGIN ATOMIC
  SELECT * FROM revenue WHERE who = session_user;
END;
```
```sql
CREATE FUNCTION
```
Алиса пробует воспользоваться функцией:
```sql
alice=> SELECT * FROM get_revenue();
```
```text
ERROR:  permission denied for table revenue
CONTEXT:  SQL function "get_revenue" statement 1
```
Вызвать функцию можно, но при ее выполнении возникает ошибка, поскольку у Алисы нет права читать таблицу. Давать Алисе такое право, конечно, нельзя, поскольку тогда она сможет увидеть все строки.

## Page 7: Права для подпрограмм {#source-page-007}

**SECURITY INVOKER (по умолчанию)**
  - выполняется с правами вызывающего
  - тело функции может подставляться в запрос
- SECURITY DEFINER
  - выполняется с правами владельца
  - тело функции никогда не подставляется в запрос

### Комментарии преподавателя

Как мы уже знаем, для подпрограмм (функций и процедур) есть единственная привилегия EXECUTE, разрешающая выполнение этой подпрограммы.

При этом важно, от имени какого пользователя будет выполняться подпрограмма. Если подпрограмма объявлена как SECURITY INVOKER (по умолчанию), она выполняется с правами вызывающего пользователя. В этом случае операторы внутри подпрограммы смогут обращаться только к тем объектам, к которым у вызывающего пользователя есть доступ.

Если же указать фразу SECURITY DEFINER, подпрограмма работает с правами ее владельца. Так можно позволить другим пользователям выполнять определенные действия над объектами, к которым у них нет непосредственного доступа.

Тонкий момент состоит в том, что при определенных условиях тело SQL-функции с характеристикой безопасности SECURITY INVOKER может подставляться в основной запрос (рассматривается подробно в курсах QPT и DEV2) — обычно это хорошо, поскольку планировщик получает больше свободы и может найти более оптимальный план. Но тело функции с характеристикой SECURITY DEFINER никогда не подставляется. Таким образом, сначала функция полностью вычисляется, и только затем к полученному результату применяются дополнительные условия. Это может быть менее эффективно.

<https://postgrespro.ru/docs/postgresql/16/sql-createfunction#SQL-CREATEF> UNCTION-SECURITY

## Page 8: Характеристики безопасности {#source-page-008}

![Source slide page 8: Характеристики безопасности](images/page-008.png){#source-figure-008 caption="Source slide page 8: Характеристики безопасности"}

Поменяем характеристику безопасности функции:
```sql
student=# ALTER FUNCTION get_revenue SECURITY DEFINER;
```
```sql
ALTER FUNCTION
```
Обратите внимание, что в условии мы использовали функцию session\_user, а не current\_user. Поскольку функция с характеристикой SECURITY DEFINER выполняется от имени владельца, то и current\_user в ней принимает значение имени роли-владельца.
Теперь Алиса видит свои строки, а Боб — свои, как и задумано:
```sql
alice=> SELECT * FROM get_revenue();
```
```text
  who  | amount
-------+---------
 alice | 1000.00
 alice | 2000.00
(2 rows)
```
```sql
bob=> SELECT * FROM get_revenue();
```
```text
 who |  amount
-----+----------
 bob | -1000.00
 bob | -2000.00
(2 rows)
```
### Изменение данных
Для примера создадим функцию для вставки строки:
```sql
student=# CREATE FUNCTION ins_revenue(who text, amount numeric) RETURNS void
LANGUAGE sql
SECURITY DEFINER
BEGIN ATOMIC
  INSERT INTO revenue(who, amount) VALUES (ins_revenue.who, ins_revenue.amount);
END;
```
```sql
CREATE FUNCTION
```
У Алисы все работает:
```sql
alice=> SELECT ins_revenue(current_user,100.00);
```
```text
 ins_revenue
-------------
(1 row)
```
```sql
alice=> SELECT * FROM get_revenue();
```
```text
  who  | amount
-------+---------
 alice | 1000.00
 alice | 2000.00
 alice |  100.00
(3 rows)
```
Но Боб решил посмотреть, получится ли у него вставить строку от имени Алисы:
```sql
bob=> SELECT ins_revenue('alice',-9999.99);
```
```text
 ins_revenue
-------------
(1 row)
```
Строка добавилась, но Боб ее не видит:
```sql
bob=> SELECT * FROM get_revenue();
```

## Page 9: Page 9 {#source-page-009}

```text
 who |  amount
-----+----------
 bob | -1000.00
 bob | -2000.00
(2 rows)
```
Зато Алиса удивилась:
```sql
alice=> SELECT * FROM get_revenue();
```
```text
  who  |  amount
-------+----------
 alice |  1000.00
 alice |  2000.00
 alice |   100.00
 alice | -9999.99
(4 rows)
```
Восстановим справедливость и решим проблему тем, что уберем первый параметр функции. Теперь Боб в принципе не сможет передать в функцию неправильное значение. В более сложном случае мы могли бы добавить необходимые проверки в саму функцию.
```sql
student=# DELETE FROM revenue WHERE who = 'alice' AND amount < 0;
```
```sql
DELETE 1
```
```sql
student=# DROP FUNCTION ins_revenue;
```
```sql
DROP FUNCTION
```
```sql
student=# CREATE FUNCTION ins_revenue(amount numeric) RETURNS void
LANGUAGE sql
SECURITY DEFINER
BEGIN ATOMIC
  INSERT INTO revenue(who, amount) VALUES (session_user, ins_revenue.amount);
END;
```
```sql
CREATE FUNCTION
```

## Page 10: Представления {#source-page-010}

**Подход к решению**
- Барьер безопасности и герметичные функции

## Page 11: Подход к решению {#source-page-011}

![Source slide page 11: Подход к решению](images/page-011.png){#source-figure-011 caption="Source slide page 11: Подход к решению"}

**Представления для чтения**
- Обновляемые представления или триггеры для изменения

### Комментарии преподавателя

Другое решение состоит в том, чтобы предоставить пользователю доступ к представлению, фильтрующему данные базовой таблицы, но не к самой таблице.

В простом случае, когда представление строится на одной таблице, не использует группировку и т. д., представление будет обновляемым и для него будут работать команды INSERT, UPDATE, DELETE, MERGE. В более сложном случае для обновления придется создать триггеры INSTEAD OF (триггеры подробно обсуждаются в одноименной теме курса DEV1).

<https://postgrespro.ru/docs/postgresql/16/sql-createview>

## Page 12: Представления {#source-page-012}

![Source slide page 12: Представления](images/page-012.png){#source-figure-012 caption="Source slide page 12: Представления"}

Создадим представление с тем же условием фильтрации, что и раньше в функции:
```sql
student=# CREATE VIEW user_revenue AS
SELECT * FROM revenue
WHERE who = session_user;
```
```sql
CREATE VIEW
```
Предоставим Алисе и Бобу право читать данные из представления:
```sql
student=# GRANT SELECT ON user_revenue TO alice, bob;
```
```text
GRANT
```
Все работает, как задумано:
```sql
alice=> SELECT * FROM user_revenue;
```
```text
  who  | amount
-------+---------
 alice | 1000.00
 alice | 2000.00
 alice |  100.00
(3 rows)
```
```sql
bob=> SELECT * FROM user_revenue;
```
```text
 who |  amount
-----+----------
 bob | -1000.00
 bob | -2000.00
(2 rows)
```
Обратите внимание, что представление работает как функция SECURITY DEFINER: права на базовые таблицы не требуются. Если требуется аналог функции SECURITY INVOKER, нужно установить соответствующий параметр представления:
```sql
student=# ALTER VIEW user_revenue SET (security_invoker);
```
```sql
ALTER VIEW
```
```sql
alice=> SELECT * FROM user_revenue;
```
```text
ERROR:  permission denied for table revenue
```
Теперь представление требует привилегии на базовые таблицы. Вернем поведение по умолчанию:
```sql
student=# ALTER VIEW user_revenue SET (security_invoker = false);
```
```sql
ALTER VIEW
```
Боб подумал, что может использовать в условии WHERE функцию, выводящую на терминал все строки, которые попадают ей на вход. Если при выполнении запроса эта функция будет вызываться для каждой строки таблицы, он сможет увидеть секретные сведения.
У Боба нет привилегий ни на одну схему, но через псевдороль PUBLIC он получил на базу данных привилегию TEMPORARY, позволяющую создавать объекты во временной схеме.
```sql
bob=> CREATE FUNCTION pg_temp.hack(who text, amount numeric) RETURNS boolean AS
$$
BEGIN
  RAISE NOTICE '%, %', who, amount;
  RETURN true;
END;
$$ LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
```sql
bob=> SELECT * FROM user_revenue WHERE pg_temp.hack(who, amount);
```

## Page 13: Page 13 {#source-page-013}

![Source slide page 13: Page 13](images/page-013.png){#source-figure-013 caption="Source slide page 13: Page 13"}

```text
NOTICE:  bob, -1000.00
NOTICE:  bob, -2000.00
 who |  amount
-----+----------
 bob | -1000.00
 bob | -2000.00
(2 rows)
```
Не получилось, но Боб не отчаивается и смотрит план выполнения запроса:
```sql
bob=> EXPLAIN SELECT * FROM user_revenue WHERE pg_temp.hack(who, amount);
```
```text
                           QUERY PLAN
----------------------------------------------------------------
 Seq Scan on revenue  (cost=0.00..293.55 rows=2 width=48)
   Filter: ((who = SESSION_USER) AND pg_temp.hack(who, amount))
(2 rows)
```
Очевидно, условие на пользователя сработало раньше его функции. Если бы он мог убедить планировщик в том, что функцию надо выполнить первой... и такой способ действительно есть: можно задать функции очень низкую стоимость (это подробно рассматривается в теме "Функции" курса QPT):
```sql
bob=> ALTER FUNCTION pg_temp.hack COST 0.0000000000000000000001;
```
```sql
ALTER FUNCTION
```
```sql
bob=> SELECT * FROM user_revenue WHERE pg_temp.hack(who, amount);
```
```text
NOTICE:  alice, 1000.00
NOTICE:  alice, 2000.00
NOTICE:  bob, -1000.00
NOTICE:  bob, -2000.00
NOTICE:  alice, 100.00
 who |  amount
-----+----------
 bob | -1000.00
 bob | -2000.00
(2 rows)
```
Вуаля! Боб увидел строки, которые от него скрывают.

## Page 14: Барьер безопасности {#source-page-014}

**Защита от потенциально опасных (негерметичных) функций**
  - не содержит
  - негерметичных
  - SELECT ...
  - FROM view
  - WHERE условие1 AND условие2
  - функций
  - барьер
  - безопасности
  - SELECT ...
  - FROM (
- SELECT ... FROM ... WHERE фильтр AND условие1 AND условие2
  - SELECT ...
  - FROM ...
  - WHERE фильтр
  - AND условие2
  - )
  - WHERE условие1

### Комментарии преподавателя

Как мы только что видели, фильтрация строк в представлении ненадежна, если пользователь может создавать функции. Проблема в том, что представление прозрачно для планировщика: по сути, текст представления подставляется в запрос пользователя и получившийся запрос оптимизируется целиком. Поэтому дополнительное условие может быть вычислено раньше, чем условие из представления.

С функциями такой проблемы не возникает, они непрозрачны: сначала вычисляется результат, потом к получившемуся набору строк применяются дополнительные условия. Исключение составляет подстановка тела SQL-функций в запрос (inlining), но для функций с характеристикой SECURITY DEFINER подстановка никогда не выполняется.

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

<https://postgrespro.ru/docs/postgresql/16/sql-createview>

<https://postgrespro.ru/docs/postgresql/16/sql-createfunction>

## Page 15: Барьер безопасности {#source-page-015}

![Source slide page 15: Барьер безопасности](images/page-015.png){#source-figure-015 caption="Source slide page 15: Барьер безопасности"}

Включим барьер безопасности:
```sql
student=# ALTER VIEW user_revenue SET (security_barrier);
```
```sql
ALTER VIEW
```
Теперь уловка Боба не работает:
```sql
bob=> SELECT * FROM user_revenue WHERE pg_temp.hack(who, amount);
```
```text
NOTICE:  bob, -1000.00
NOTICE:  bob, -2000.00
 who |  amount
-----+----------
 bob | -1000.00
 bob | -2000.00
(2 rows)
```
В плане запроса видно, что сначала вычисляется условие из представления, а затем уже функция Боба:
```sql
bob=> EXPLAIN SELECT * FROM user_revenue WHERE pg_temp.hack(who, amount);
```
```text
                            QUERY PLAN
-------------------------------------------------------------------
 Subquery Scan on user_revenue  (cost=0.00..26.10 rows=2 width=48)
   Filter: pg_temp.hack(user_revenue.who, user_revenue.amount)
   ->  Seq Scan on revenue  (cost=0.00..26.05 rows=5 width=48)
         Filter: (who = SESSION_USER)
(4 rows)
```
Боб хотел бы разрешить своей функции преодолевать барьер, пометив ее как герметичную, но у него недостаточно прав:
```sql
bob=> ALTER FUNCTION pg_temp.hack LEAKPROOF;
```
```text
ERROR:  only superuser can define a leakproof function
```
Пометить функцию как герметичную может только суперпользователь, это действие недоступно даже ее владельцу.
### Изменение данных
Наше представление обновляемое. Проверим, например, как работает вставка:
```sql
student=# GRANT INSERT ON user_revenue TO alice, bob;
```
```text
GRANT
```
```sql
alice=> INSERT INTO user_revenue(who, amount) VALUES (current_user, 200.00);
```
```sql
INSERT 0 1
```
```sql
alice=> SELECT * FROM user_revenue;
```
```text
  who  | amount
-------+---------
 alice | 1000.00
 alice | 2000.00
 alice |  100.00
 alice |  200.00
(4 rows)
```
У Алисы все работает. Но Боб снова пытается вставить чужую строку, и у него снова получается:
```sql
bob=> INSERT INTO user_revenue(who, amount) VALUES ('alice', -9999.99);
```
```sql
INSERT 0 1
```
```sql
bob=> SELECT * FROM user_revenue;
```
```text
 who |  amount
-----+----------
 bob | -1000.00
 bob | -2000.00
(2 rows)
```

## Page 16: Page 16 {#source-page-016}

![Source slide page 16: Page 16](images/page-016.png){#source-figure-016 caption="Source slide page 16: Page 16"}

Можно пойти тем же путем, что и в подходе с функциями: разрешить вставку только для столбца amount.
```sql
student=# REVOKE INSERT ON user_revenue FROM alice, bob;
```
```text
REVOKE
```
```sql
student=# GRANT INSERT(amount) ON user_revenue TO alice, bob;
```
```text
GRANT
```
```sql
student=# DELETE FROM revenue WHERE who = 'alice' AND amount < 0;
```
```sql
DELETE 1
```
Теперь Боб не может указать пользователя:
```sql
bob=> INSERT INTO user_revenue(who, amount) VALUES ('alice', -9999.99);
```
```text
ERROR:  permission denied for view user_revenue
```
Но с указанием только суммы вставка работает:
```sql
bob=> INSERT INTO user_revenue(amount) VALUES (-100.00);
```
```sql
INSERT 0 1
```
```sql
bob=> SELECT * FROM user_revenue;
```
```text
 who |  amount
-----+----------
 bob | -1000.00
 bob | -2000.00
 bob |  -100.00
(3 rows)
```
Для обновляемых представлений есть и другой способ. Можно запретить любое изменение данных, результат которого пользователь не сможет увидеть в представлении. Вернем привилегии на представление и зададим его параметр:
```sql
student=# GRANT INSERT ON user_revenue TO alice, bob;
```
```text
GRANT
```
```sql
student=# ALTER VIEW user_revenue SET (check_option = local);
```
```sql
ALTER VIEW
```
Боб проверяет и терпит неудачу:
```sql
bob=> INSERT INTO user_revenue(who, amount) VALUES ('alice', -9999.99);
```
```text
ERROR:  new row violates check option for view "user_revenue"
DETAIL:  Failing row contains (who, amount) = (alice, -9999.99).
```

## Page 17: Политики защиты строк {#source-page-017}

**Подход к решению**
- Условия применения политик
- Комбинирование политик

## Page 18: Подход к решению {#source-page-018}

![Source slide page 18: Подход к решению](images/page-018.png){#source-figure-018 caption="Source slide page 18: Подход к решению"}

**Доступ к базовой таблице со включенной защитой строк**

### Комментарии преподавателя

Третий подход заключается в использовании специально созданного механизма — политик защиты строк (row-level security, RLS). В этом случае пользователю предоставляется доступ к самой таблице, а проверка условий, заданных в политике, происходит автоматически.

По сути, политика работает так же, как представление с барьером безопасности: гарантируется, что дополнительные условия на выборку из таблицы будут вычислены после того, как будут отфильтрованы строки, не удовлетворяющие политике (за исключением безопасных выражений с герметичными функциями). Как и в представлении, условия, описанные в политике, вычисляются с правами вызывающего.

Однако то, что у пользователя есть доступ к самой таблице, добавляет определенные нюансы.

<https://postgrespro.ru/docs/postgresql/16/ddl-rowsecurity>

## Page 19: Условия применения {#source-page-019}

**Политика применяется**
  - к таблице, для которой включена защита
  - для указанных ролей и для указанных операторов
  - (SELECT, INSERT, UPDATE, DELETE)
- Политика не применяется
  - при проверке ограничений целостности
  - для суперпользователей и ролей с атрибутом BYPASSRLS
  - для владельца (если не включить принудительно)
- Применение приводит к ошибке
  - при создании логической копии утилитой pg\_dump
  - row\_security = off

### Комментарии преподавателя

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

При создании политики можно также задать, для каких ролей она будет работать (по умолчанию — для всех) и для каких операторов (по умолчанию — также для всех).

Политики не применяются при проверке ограничений целостности — независимо от настроенных политик СУБД гарантирует целостность данных.

Политики не применяются для суперпользователей (для них, как обычно, никакие проверки безопасности не выполняются) и для ролей с атрибутом BYPASSRLS.

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

При выполнении логической резервной копии утилита pg\_dump устанавливает параметр row\_security в значение off. Это не отключает политику защиты строк, но фиксирует ошибку, если хотя бы одна строка была отфильтрована. Таким образом гарантируется, что созданная копия будет полной.

## Page 20: Политики защиты строк {#source-page-020}

Определим политику с тем же условием, что раньше, и включим защиту строк на таблице:
```sql
student=# CREATE POLICY users ON revenue
USING ( who = current_user );
```
```sql
CREATE POLICY
```
```sql
student=# ALTER TABLE revenue ENABLE ROW LEVEL SECURITY;
```
```sql
ALTER TABLE
```
Политики, созданные для таблицы, показывают команды psql \\d (описание объекта) и \\dp (описание привилегий), например:
```sql
student=# \d revenue
```
```text
                    Table "public.revenue"
 Column |     Type      | Collation | Nullable |   Default
--------+---------------+-----------+----------+--------------
 who    | text          |           |          | CURRENT_USER
 amount | numeric(10,2) |           |          |
Policies:
    POLICY "users"
      USING ((who = CURRENT_USER))
```
Эту информацию можно получить и из представления pg\_policies системного каталога.
Теперь можно выдать Алисе и Бобу привилегии на таблицу:
```sql
student=# GRANT SELECT ON revenue TO alice, bob;
```
```text
GRANT
```
Проверяем:
```sql
alice=> SELECT * FROM revenue;
```
```text
  who  | amount
-------+---------
 alice | 1000.00
 alice | 2000.00
 alice |  100.00
 alice |  200.00
(4 rows)
```
```sql
bob=> SELECT * FROM revenue;
```
```text
 who |  amount
-----+----------
 bob | -1000.00
 bob | -2000.00
 bob |  -100.00
(3 rows)
```
### Изменение данных
Наша политика работает так же, как представление с параметром check\_option — не допускает добавления строк, не удовлетворяющих условию.
```sql
student=# GRANT INSERT ON revenue TO alice, bob;
```
```text
GRANT
```
У Алисы все работает:
```sql
alice=> INSERT INTO revenue(who, amount) VALUES (current_user, 300.00);
```
```sql
INSERT 0 1
```
```sql
alice=> SELECT * FROM revenue;
```

## Page 21: Page 21 {#source-page-021}

![Source slide page 21: Page 21](images/page-021.png){#source-figure-021 caption="Source slide page 21: Page 21"}

```text
  who  | amount
-------+---------
 alice | 1000.00
 alice | 2000.00
 alice |  100.00
 alice |  200.00
 alice |  300.00
(5 rows)
```
Боб пробует свои наработки, но безуспешно:
```sql
bob=> SELECT * FROM revenue WHERE pg_temp.hack(who, amount);
```
```text
NOTICE:  bob, -1000.00
NOTICE:  bob, -2000.00
NOTICE:  bob, -100.00
 who |  amount
-----+----------
 bob | -1000.00
 bob | -2000.00
 bob |  -100.00
(3 rows)
```
```sql
bob=> INSERT INTO revenue(who, amount) VALUES ('alice', -9999.99);
```
```text
ERROR:  new row violates row-level security policy for table "revenue"
```
Для новых строк можно задать другие условия в предложении WITH CHECK. Например, создадим аналог представления с выключенным параметром check\_option, допускающий вставку любых строк.
```sql
student=# DROP POLICY users ON revenue;
```
```sql
DROP POLICY
```
```sql
student=# CREATE POLICY users ON revenue
USING ( who = current_user )
WITH CHECK ( true );
```
```sql
CREATE POLICY
```
Теперь Боб может развернуться:
```sql
bob=> INSERT INTO revenue(who, amount) VALUES ('alice', -9999.99);
```
```sql
INSERT 0 1
```
```sql
bob=> SELECT * FROM revenue;
```
```text
 who |  amount
-----+----------
 bob | -1000.00
 bob | -2000.00
 bob |  -100.00
(3 rows)
```
Обратите внимание, что команда COPY FROM не работает при включенной защите строк:
```sql
bob=> COPY revenue FROM stdin;
```
```sql
ERROR:  COPY FROM not supported with row-level security
HINT:  Use INSERT statements instead.
```

## Page 22: Комбинирование политик {#source-page-022}

**Разрешительные политики**
  - видимость должна предоставить хотя бы одна разрешительная политика
  - если не определена ни одна политика, строка не видима
- Ограничительные политики
  - видимость должны предоставить все ограничительные политики,
  - если они определены

### Комментарии преподавателя

На одной таблице можно определить несколько политик. В этом случае будут учитываться условия всех политик.

По умолчанию создаются разрешительные (permissive) политики. Чтобы строка была доступна, достаточно, чтобы хотя бы одно из условий таких политик оказалось истинным.

Но если на таблице включена защита строк, и при этом не определено ни одной разрешительной политики, не будет доступна ни одна строка.

Дополнительно можно создать и ограничительные (restrictive) политики. Если они заданы, то все их предикаты должны быть истинны.

Иными словами, если определены только разрешительные политики с условиями P1,..., PN, то для каждой строки вычисляется выражение

P1 OR ... OR PN.

А если к тому же определены ограничительные политики с предикатами R1,..., RM, то для каждой строки будет вычисляться выражение (P1 OR ... OR PN) AND R1 AND ... AND RM.

То есть чтобы строка была видна, доступ к ней должны предоставить хотя бы одна разрешительная и все ограничительные политики.

## Page 23: Комбинирование политик {#source-page-023}

![Source slide page 23: Комбинирование политик](images/page-023.png){#source-figure-023 caption="Source slide page 23: Комбинирование политик"}

Сначала убедимся, что при включенной защите строк отсутствие политик приводит к тому, что не видна ни одна строка:
```sql
student=# DROP POLICY users ON revenue;
```
```sql
DROP POLICY
```
```sql
alice=> SELECT * FROM revenue;
```
```text
 who | amount
-----+--------
(0 rows)
```
Теперь создадим две политики — разрешительные по умолчанию. Первая, как и раньше, разрешает пользователю видеть свои строки:
```sql
student=# CREATE POLICY users ON revenue
USING ( who = current_user );
```
```sql
CREATE POLICY
```
Вторая разрешает видеть строки любого пользователя, если сумма не превышает пятисот рублей:
```sql
student=# CREATE POLICY small_amount ON revenue
USING ( abs(amount) <= 500.00 );
```
```sql
CREATE POLICY
```
Проверим.
```sql
bob=> SELECT * FROM revenue;
```
```text
  who  |  amount
-------+----------
 bob   | -1000.00
 bob   | -2000.00
 alice |   100.00
 alice |   200.00
 bob   |  -100.00
 alice |   300.00
(6 rows)
```
Теперь добавим ограничительную политику: строки с суммой больше тысячи не должны быть видны никому.
```sql
student=# CREATE POLICY big_amount ON revenue AS RESTRICTIVE
USING ( abs(amount) < 1000.00 );
```
```sql
CREATE POLICY
```
```sql
bob=> SELECT * FROM revenue;
```
```text
  who  | amount
-------+---------
 alice |  100.00
 alice |  200.00
 bob   | -100.00
 alice |  300.00
(4 rows)
```
Таким образом можно комбинировать несколько условий.

## Page 24: Итоги {#source-page-024}

**Ограничения доступа на уровне строк реализуют с помощью**
  - табличных функций, вызываемых с правами владельца
  - представлений с барьером безопасности
  - политик защиты строк
- Каждый способ реализации доступа по строкам имеет свои особенности и ограничения

## Page 25: Практика {#source-page-025}

1. Дополните пример из демонстрации таблицей staff с распределением сотрудников по отделам. Настройте разграничение доступа на уровне строк с помощью функции так, чтобы пользователи видели суммы только сотрудников своего отдела.
- 2. Решите ту же задачу с помощью представления.
- 3. Решите ту же задачу с помощью политик защиты строк.

## Page 26: 1. Разграничение с помощью функции {#source-page-026}

Создадим необходимые роли и базу данных.
```sql
student=# CREATE USER alice;
```
```sql
CREATE ROLE
```
```sql
student=# CREATE USER bob;
```
```sql
CREATE ROLE
```
```sql
student=# CREATE USER charlie;
```
```sql
CREATE ROLE
```
```sql
student=# CREATE USER dave;
```
```sql
CREATE ROLE
```
```sql
student=# CREATE ROLE staff ROLE alice, bob, charlie, dave;
```
```sql
CREATE ROLE
```
```sql
student=# CREATE DATABASE access_rows;
```
```sql
CREATE DATABASE
```
```sql
student=# \c access_rows
```
```text
You are now connected to database "access_rows" as user "student".
```
Создадим таблицу распределения сотрудников по отделам.
```sql
student=# CREATE TABLE staff(
  who text PRIMARY KEY,
  department text
);
```
```sql
CREATE TABLE
```
```sql
student=# INSERT INTO staff(who, department) VALUES
  ('alice', 'Sales'),
  ('bob', 'PR'),
  ('charlie', 'Sales'),
  ('dave', 'PR');
```
```sql
INSERT 0 4
```
Таблица доходов и расходов из демонстрации теперь ссылается на staff.
```sql
student=# CREATE TABLE revenue(
  who text DEFAULT current_user REFERENCES staff,
  amount numeric(10,2)
);
```
```sql
CREATE TABLE
```
```sql
student=# INSERT INTO revenue(who, amount)
SELECT
  s.who,
  random()*1000 * CASE WHEN s.department = 'Sales' THEN 1 ELSE -1 END
FROM staff s, generate_series(1,1000);
```
```sql
INSERT 0 4000
```
Функция для разграничения доступа по строкам:
```sql
student=# CREATE FUNCTION get_revenue() RETURNS TABLE (LIKE revenue)
LANGUAGE sql
SECURITY DEFINER
BEGIN ATOMIC
  SELECT r.*
  FROM revenue r
    JOIN staff s ON s.who = r.who
    JOIN staff s0 ON s0.department = s.department AND s0.who = session_user;
END;
```
```sql
CREATE FUNCTION
```
Проверяем в сеансе Алисы:

## Page 27: Page 27 {#source-page-027}

![Source slide page 27: Page 27](images/page-027.png){#source-figure-027 caption="Source slide page 27: Page 27"}

```sh
student$ psql -d access_rows -U alice
```
```sql
alice=> SELECT who, sum(amount)
FROM revenue
GROUP BY who;
```
```text
ERROR:  permission denied for table revenue
```
Прямого доступа к таблице нет.
```sql
alice=> SELECT who, sum(amount)
FROM get_revenue()
GROUP BY who;
```
```text
   who   |    sum
---------+-----------
 charlie | 489497.33
 alice   | 510889.47
(2 rows)
```
Функция работает.
Проверяем в сеансе Боба:
```sh
student$ psql -d access_rows -U bob
```
```sql
bob=> SELECT who, sum(amount)
FROM get_revenue()
GROUP BY who;
```
```text
 who  |    sum
------+------------
 dave | -498182.39
 bob  | -490463.06
(2 rows)
```
### 2. Разграничение с помощью представления
Представление для разграничения доступа:
```sql
student=# CREATE VIEW user_revenue AS
  SELECT r.*
  FROM revenue r
    JOIN staff s ON s.who = r.who
    JOIN staff s0 ON s0.department = s.department AND s0.who = session_user;
```
```sql
CREATE VIEW
```
```sql
student=# GRANT SELECT ON user_revenue TO staff;
```
```text
GRANT
```
Проверяем.
```sql
alice=> SELECT who, sum(amount)
FROM user_revenue
GROUP BY who;
```
```text
   who   |    sum
---------+-----------
 alice   | 510889.47
 charlie | 489497.33
(2 rows)
```
```sql
bob=> SELECT who, sum(amount)
FROM user_revenue
GROUP BY who;
```
```text
 who  |    sum
------+------------
 bob  | -490463.06
 dave | -498182.39
(2 rows)
```
### 3. Разграничение с помощью политик
Политика защиты строк:

## Page 28: Page 28 {#source-page-028}

![Source slide page 28: Page 28](images/page-028.png){#source-figure-028 caption="Source slide page 28: Page 28"}

```sql
student=# CREATE POLICY dept ON revenue
USING (
  who IN (
    SELECT s.who
    FROM staff s0
      JOIN staff s ON s.department = s0.department AND s0.who = session_user
  )
);
```
```sql
CREATE POLICY
```
```sql
student=# ALTER TABLE revenue ENABLE ROW LEVEL SECURITY;
```
```sql
ALTER TABLE
```
```sql
student=# GRANT SELECT ON staff, revenue TO staff;
```
```text
GRANT
```
Проверяем.
```sql
alice=> SELECT who, sum(amount)
FROM revenue
GROUP BY who;
```
```text
   who   |    sum
---------+-----------
 charlie | 489497.33
 alice   | 510889.47
(2 rows)
```
```sql
bob=> SELECT who, sum(amount)
FROM revenue
GROUP BY who;
```
```text
 who  |    sum
------+------------
 dave | -498182.39
 bob  | -490463.06
(2 rows)
```
### Уборка
```sql
student=# \c student
```
```text
You are now connected to database "student" as user "student".
```
```sql
student=# DROP DATABASE access_rows WITH (FORCE);
```
```sql
DROP DATABASE
```
```sql
student=# DROP ROLE alice, bob, charlie, dave, staff;
```
```sql
DROP ROLE
```

## Page 29: Практика+ {#source-page-029}

1. Выполните настройку политик защиты строк, как описано в задании 3 Практики.
- 2. Создайте представление, показывающее итоговые суммы по отделам, и выдайте доступ к нему пользователям.
- 3. Убедитесь, что запрос к представлению не учитывает политики: любой сотрудник может видеть итоги по всем отделам.
- 4. Измените представление так, чтобы любой сотрудник видел итоги только своего отдела.

### Комментарии преподавателя

4. Установите у представления параметр security\_invoker.

## Page 30: 1. Настройка политик {#source-page-030}

Создаем роли, база данных и таблицы как в Практике.
```sql
student=# CREATE USER alice;
```
```sql
CREATE ROLE
```
```sql
student=# CREATE USER bob;
```
```sql
CREATE ROLE
```
```sql
student=# CREATE USER charlie;
```
```sql
CREATE ROLE
```
```sql
student=# CREATE USER dave;
```
```sql
CREATE ROLE
```
```sql
student=# CREATE ROLE staff ROLE alice, bob, charlie, dave;
```
```sql
CREATE ROLE
```
```sql
student=# CREATE DATABASE access_rows;
```
```sql
CREATE DATABASE
```
```sql
student=# \c access_rows
```
```text
You are now connected to database "access_rows" as user "student".
```
```sql
student=# CREATE TABLE staff(
  who text PRIMARY KEY,
  department text
);
```
```sql
CREATE TABLE
```
```sql
student=# INSERT INTO staff(who, department) VALUES
  ('alice', 'Sales'),
  ('bob', 'PR'),
  ('charlie', 'Sales'),
  ('dave', 'PR');
```
```sql
INSERT 0 4
```
```sql
student=# CREATE TABLE revenue(
  who text DEFAULT current_user REFERENCES staff,
  amount numeric(10,2)
);
```
```sql
CREATE TABLE
```
```sql
student=# INSERT INTO revenue(who, amount)
SELECT
  s.who,
  random()*1000 * CASE WHEN s.department = 'Sales' THEN 1 ELSE -1 END
FROM staff s, generate_series(1,1000);
```
```sql
INSERT 0 4000
```
Политика защиты строк:
```sql
student=# CREATE POLICY dept ON revenue
USING (
  who IN (
    SELECT s.who
    FROM staff s0
      JOIN staff s ON s.department = s0.department AND s0.who = session_user
  )
);
```
```sql
CREATE POLICY
```
```sql
student=# ALTER TABLE revenue ENABLE ROW LEVEL SECURITY;
```
```sql
ALTER TABLE
```
```sql
student=# GRANT SELECT ON staff, revenue TO staff;
```
```text
GRANT
```

## Page 31: Page 31 {#source-page-031}

Проверяем работу политик, сначала в сеансе Алисы:
```sh
student$ psql -d access_rows -U alice
```
```sql
alice=> SELECT who, sum(amount)
FROM revenue
GROUP BY who;
```
```text
   who   |    sum
---------+-----------
 charlie | 498936.20
 alice   | 505916.69
(2 rows)
```
Затем в сеансе Боба:
```sh
student$ psql -d access_rows -U bob
```
```sql
bob=> SELECT who, sum(amount)
FROM revenue
GROUP BY who;
```
```text
 who  |    sum
------+------------
 dave | -502933.63
 bob  | -506120.34
(2 rows)
```
### 2. Представление
```sql
student=# CREATE VIEW v_dept_totals AS
  SELECT department, sum(amount)
  FROM revenue r
    JOIN staff s ON s.who = r.who
  GROUP BY s.department;
```
```sql
CREATE VIEW
```
Владелец по умолчанию видит все:
```sql
student=# SELECT * FROM v_dept_totals;
```
```text
 department |     sum
------------+-------------
 PR         | -1009053.97
 Sales      |  1004852.89
(2 rows)
```
```sql
student=# GRANT SELECT ON v_dept_totals TO staff;
```
```text
GRANT
```
### 3. Проверка представления
Обратимся к представлению в сеансе Алисы:
```sql
alice=> SELECT * FROM v_dept_totals;
```
```text
 department |     sum
------------+-------------
 PR         | -1009053.97
 Sales      |  1004852.89
(2 rows)
```
Несмотря на наличие политики, Алиса видит все строки исходной таблицы, так как запрос, определяющий представление, по умолчанию выполняется от имени его владельца student.
То же и для Боба:
```sql
bob=> SELECT * FROM v_dept_totals;
```
```text
 department |     sum
------------+-------------
 PR         | -1009053.97
 Sales      |  1004852.89
(2 rows)
```

## Page 32: 4. Свойство представления security_invoker {#source-page-032}

![Source slide page 32: 4. Свойство представления security_invoker](images/page-032.png){#source-figure-032 caption="Source slide page 32: 4. Свойство представления security_invoker"}

Чтобы запрос представления выполнялся от имени текущего пользователя, нужно задать параметр security\_invoker:
```sql
student=# ALTER VIEW v_dept_totals SET (security_invoker);
```
```sql
ALTER VIEW
```
Теперь и Алиса, и Боб видят строки только своих отделов:
```sql
alice=> SELECT * FROM v_dept_totals;
```
```text
 department |    sum
------------+------------
 Sales      | 1004852.89
(1 row)
```
```sql
bob=> SELECT * FROM v_dept_totals;
```
```text
 department |     sum
------------+-------------
 PR         | -1009053.97
(1 row)
```
### Уборка
```sql
student=# \c student
```
```text
You are now connected to database "student" as user "student".
```
```sql
student=# DROP DATABASE access_rows WITH (FORCE);
```
```sql
DROP DATABASE
```
```sql
student=# DROP ROLE alice, bob, charlie, dave, staff;
```
```sql
DROP ROLE
```