Перейти к содержимому

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

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

Эта страница извлечена из официального PDF в нативный Markdown. Текст слайдов, код и комментарии сохранены в порядке чтения; исходные PDF и HTML остаются эталоном макета. HTML source: dbs_03_access_rows.html Archive: DBS-handouts-16.zip Страниц PDF: 32

Page 1: Управление доступом / Доступ на уровне строк

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

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

Использование материалов курса

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

Обратная связь

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

Отказ от ответственности

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

Page 2: Темы

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

Page 3: Доступ на уровне строк

Управление доступом на основе ролей

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

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

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

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

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

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

Page 4: Функции

Подход к решению

  • Характеристика безопасности

Page 5: Подход к решению

Source slide page 5: Подход к решению{#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: Функции

Создадим базу данных:

student=# CREATE DATABASE access_rows;
CREATE DATABASE
student=# \c access_rows
You are now connected to database "access_rows" as user "student".

Для демонстрации нам потребуются традиционные роли:

student=# CREATE USER alice; -- примерный пользователь
CREATE ROLE
student=# CREATE USER bob;   -- хакер в душе
CREATE ROLE

Алиса будет работать в одном сеансе, Боб — в другом.

student$ psql -d access_rows -U alice
student$ psql -d access_rows -U bob

Таблица содержит информацию о доходах и расходах.

student=# CREATE TABLE revenue(
  who text DEFAULT current_user,
  amount numeric(10,2)
);
CREATE TABLE

Заполним таблицу произвольными данными (Алиса зарабатывает, Боб тратит).

student=# INSERT INTO revenue(who, amount) VALUES
  ('alice', 1000.00), ('alice', 2000.00),
  ('bob',  -1000.00), ('bob',  -2000.00);
INSERT 0 4

Задача состоит в том, чтобы Алиса видела только свои операции, а Боб — только свои. Создадим функцию, возвращающую множество строк. Запрос фильтрует данные, оставляя только строки, соответствующие пользователю сеанса:

student=# CREATE FUNCTION get_revenue() RETURNS TABLE(who text, amount numeric)
LANGUAGE sql
BEGIN ATOMIC
  SELECT * FROM revenue WHERE who = session_user;
END;
CREATE FUNCTION

Алиса пробует воспользоваться функцией:

alice=> SELECT * FROM get_revenue();
ERROR:  permission denied for table revenue
CONTEXT:  SQL function "get_revenue" statement 1

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

Page 7: Права для подпрограмм

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 slide page 8: Характеристики безопасности{#source-figure-008 caption=“Source slide page 8: Характеристики безопасности”}

Поменяем характеристику безопасности функции:

student=# ALTER FUNCTION get_revenue SECURITY DEFINER;
ALTER FUNCTION

Обратите внимание, что в условии мы использовали функцию session_user, а не current_user. Поскольку функция с характеристикой SECURITY DEFINER выполняется от имени владельца, то и current_user в ней принимает значение имени роли-владельца. Теперь Алиса видит свои строки, а Боб — свои, как и задумано:

alice=> SELECT * FROM get_revenue();
  who  | amount
-------+---------
 alice | 1000.00
 alice | 2000.00
(2 rows)
bob=> SELECT * FROM get_revenue();
 who |  amount
-----+----------
 bob | -1000.00
 bob | -2000.00
(2 rows)

Изменение данных

Для примера создадим функцию для вставки строки:

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;
CREATE FUNCTION

У Алисы все работает:

alice=> SELECT ins_revenue(current_user,100.00);
 ins_revenue
-------------
(1 row)
alice=> SELECT * FROM get_revenue();
  who  | amount
-------+---------
 alice | 1000.00
 alice | 2000.00
 alice |  100.00
(3 rows)

Но Боб решил посмотреть, получится ли у него вставить строку от имени Алисы:

bob=> SELECT ins_revenue('alice',-9999.99);
 ins_revenue
-------------
(1 row)

Строка добавилась, но Боб ее не видит:

bob=> SELECT * FROM get_revenue();

Page 9: Page 9

 who |  amount
-----+----------
 bob | -1000.00
 bob | -2000.00
(2 rows)

Зато Алиса удивилась:

alice=> SELECT * FROM get_revenue();
  who  |  amount
-------+----------
 alice |  1000.00
 alice |  2000.00
 alice |   100.00
 alice | -9999.99
(4 rows)

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

student=# DELETE FROM revenue WHERE who = 'alice' AND amount < 0;
DELETE 1
student=# DROP FUNCTION ins_revenue;
DROP FUNCTION
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;
CREATE FUNCTION

Page 10: Представления

Подход к решению

  • Барьер безопасности и герметичные функции

Page 11: Подход к решению

Source slide page 11: Подход к решению{#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 slide page 12: Представления{#source-figure-012 caption=“Source slide page 12: Представления”}

Создадим представление с тем же условием фильтрации, что и раньше в функции:

student=# CREATE VIEW user_revenue AS
SELECT * FROM revenue
WHERE who = session_user;
CREATE VIEW

Предоставим Алисе и Бобу право читать данные из представления:

student=# GRANT SELECT ON user_revenue TO alice, bob;
GRANT

Все работает, как задумано:

alice=> SELECT * FROM user_revenue;
  who  | amount
-------+---------
 alice | 1000.00
 alice | 2000.00
 alice |  100.00
(3 rows)
bob=> SELECT * FROM user_revenue;
 who |  amount
-----+----------
 bob | -1000.00
 bob | -2000.00
(2 rows)

Обратите внимание, что представление работает как функция SECURITY DEFINER: права на базовые таблицы не требуются. Если требуется аналог функции SECURITY INVOKER, нужно установить соответствующий параметр представления:

student=# ALTER VIEW user_revenue SET (security_invoker);
ALTER VIEW
alice=> SELECT * FROM user_revenue;
ERROR:  permission denied for table revenue

Теперь представление требует привилегии на базовые таблицы. Вернем поведение по умолчанию:

student=# ALTER VIEW user_revenue SET (security_invoker = false);
ALTER VIEW

Боб подумал, что может использовать в условии WHERE функцию, выводящую на терминал все строки, которые попадают ей на вход. Если при выполнении запроса эта функция будет вызываться для каждой строки таблицы, он сможет увидеть секретные сведения. У Боба нет привилегий ни на одну схему, но через псевдороль PUBLIC он получил на базу данных привилегию TEMPORARY, позволяющую создавать объекты во временной схеме.

bob=> CREATE FUNCTION pg_temp.hack(who text, amount numeric) RETURNS boolean AS
$$
BEGIN
  RAISE NOTICE '%, %', who, amount;
  RETURN true;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION
bob=> SELECT * FROM user_revenue WHERE pg_temp.hack(who, amount);

Page 13: Page 13

Source slide page 13: Page 13{#source-figure-013 caption=“Source slide page 13: Page 13”}

NOTICE:  bob, -1000.00
NOTICE:  bob, -2000.00
 who |  amount
-----+----------
 bob | -1000.00
 bob | -2000.00
(2 rows)

Не получилось, но Боб не отчаивается и смотрит план выполнения запроса:

bob=> EXPLAIN SELECT * FROM user_revenue WHERE pg_temp.hack(who, amount);
                           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):

bob=> ALTER FUNCTION pg_temp.hack COST 0.0000000000000000000001;
ALTER FUNCTION
bob=> SELECT * FROM user_revenue WHERE pg_temp.hack(who, amount);
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: Барьер безопасности

Защита от потенциально опасных (негерметичных) функций

  • не содержит
  • негерметичных
  • 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 slide page 15: Барьер безопасности{#source-figure-015 caption=“Source slide page 15: Барьер безопасности”}

Включим барьер безопасности:

student=# ALTER VIEW user_revenue SET (security_barrier);
ALTER VIEW

Теперь уловка Боба не работает:

bob=> SELECT * FROM user_revenue WHERE pg_temp.hack(who, amount);
NOTICE:  bob, -1000.00
NOTICE:  bob, -2000.00
 who |  amount
-----+----------
 bob | -1000.00
 bob | -2000.00
(2 rows)

В плане запроса видно, что сначала вычисляется условие из представления, а затем уже функция Боба:

bob=> EXPLAIN SELECT * FROM user_revenue WHERE pg_temp.hack(who, amount);
                            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)

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

bob=> ALTER FUNCTION pg_temp.hack LEAKPROOF;
ERROR:  only superuser can define a leakproof function

Пометить функцию как герметичную может только суперпользователь, это действие недоступно даже ее владельцу.

Изменение данных

Наше представление обновляемое. Проверим, например, как работает вставка:

student=# GRANT INSERT ON user_revenue TO alice, bob;
GRANT
alice=> INSERT INTO user_revenue(who, amount) VALUES (current_user, 200.00);
INSERT 0 1
alice=> SELECT * FROM user_revenue;
  who  | amount
-------+---------
 alice | 1000.00
 alice | 2000.00
 alice |  100.00
 alice |  200.00
(4 rows)

У Алисы все работает. Но Боб снова пытается вставить чужую строку, и у него снова получается:

bob=> INSERT INTO user_revenue(who, amount) VALUES ('alice', -9999.99);
INSERT 0 1
bob=> SELECT * FROM user_revenue;
 who |  amount
-----+----------
 bob | -1000.00
 bob | -2000.00
(2 rows)

Page 16: Page 16

Source slide page 16: Page 16{#source-figure-016 caption=“Source slide page 16: Page 16”}

Можно пойти тем же путем, что и в подходе с функциями: разрешить вставку только для столбца amount.

student=# REVOKE INSERT ON user_revenue FROM alice, bob;
REVOKE
student=# GRANT INSERT(amount) ON user_revenue TO alice, bob;
GRANT
student=# DELETE FROM revenue WHERE who = 'alice' AND amount < 0;
DELETE 1

Теперь Боб не может указать пользователя:

bob=> INSERT INTO user_revenue(who, amount) VALUES ('alice', -9999.99);
ERROR:  permission denied for view user_revenue

Но с указанием только суммы вставка работает:

bob=> INSERT INTO user_revenue(amount) VALUES (-100.00);
INSERT 0 1
bob=> SELECT * FROM user_revenue;
 who |  amount
-----+----------
 bob | -1000.00
 bob | -2000.00
 bob |  -100.00
(3 rows)

Для обновляемых представлений есть и другой способ. Можно запретить любое изменение данных, результат которого пользователь не сможет увидеть в представлении. Вернем привилегии на представление и зададим его параметр:

student=# GRANT INSERT ON user_revenue TO alice, bob;
GRANT
student=# ALTER VIEW user_revenue SET (check_option = local);
ALTER VIEW

Боб проверяет и терпит неудачу:

bob=> INSERT INTO user_revenue(who, amount) VALUES ('alice', -9999.99);
ERROR:  new row violates check option for view "user_revenue"
DETAIL:  Failing row contains (who, amount) = (alice, -9999.99).

Page 17: Политики защиты строк

Подход к решению

  • Условия применения политик
  • Комбинирование политик

Page 18: Подход к решению

Source slide page 18: Подход к решению{#source-figure-018 caption=“Source slide page 18: Подход к решению”}

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

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

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

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

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

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

Page 19: Условия применения

Политика применяется

  • к таблице, для которой включена защита
  • для указанных ролей и для указанных операторов
  • (SELECT, INSERT, UPDATE, DELETE)
  • Политика не применяется
    • при проверке ограничений целостности
    • для суперпользователей и ролей с атрибутом BYPASSRLS
    • для владельца (если не включить принудительно)
  • Применение приводит к ошибке
    • при создании логической копии утилитой pg_dump
    • row_security = off

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

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

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

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

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

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

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

Page 20: Политики защиты строк

Определим политику с тем же условием, что раньше, и включим защиту строк на таблице:

student=# CREATE POLICY users ON revenue
USING ( who = current_user );
CREATE POLICY
student=# ALTER TABLE revenue ENABLE ROW LEVEL SECURITY;
ALTER TABLE

Политики, созданные для таблицы, показывают команды psql \d (описание объекта) и \dp (описание привилегий), например:

student=# \d revenue
                    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 системного каталога. Теперь можно выдать Алисе и Бобу привилегии на таблицу:

student=# GRANT SELECT ON revenue TO alice, bob;
GRANT

Проверяем:

alice=> SELECT * FROM revenue;
  who  | amount
-------+---------
 alice | 1000.00
 alice | 2000.00
 alice |  100.00
 alice |  200.00
(4 rows)
bob=> SELECT * FROM revenue;
 who |  amount
-----+----------
 bob | -1000.00
 bob | -2000.00
 bob |  -100.00
(3 rows)

Изменение данных

Наша политика работает так же, как представление с параметром check_option — не допускает добавления строк, не удовлетворяющих условию.

student=# GRANT INSERT ON revenue TO alice, bob;
GRANT

У Алисы все работает:

alice=> INSERT INTO revenue(who, amount) VALUES (current_user, 300.00);
INSERT 0 1
alice=> SELECT * FROM revenue;

Page 21: Page 21

Source slide page 21: Page 21{#source-figure-021 caption=“Source slide page 21: Page 21”}

  who  | amount
-------+---------
 alice | 1000.00
 alice | 2000.00
 alice |  100.00
 alice |  200.00
 alice |  300.00
(5 rows)

Боб пробует свои наработки, но безуспешно:

bob=> SELECT * FROM revenue WHERE pg_temp.hack(who, amount);
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)
bob=> INSERT INTO revenue(who, amount) VALUES ('alice', -9999.99);
ERROR:  new row violates row-level security policy for table "revenue"

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

student=# DROP POLICY users ON revenue;
DROP POLICY
student=# CREATE POLICY users ON revenue
USING ( who = current_user )
WITH CHECK ( true );
CREATE POLICY

Теперь Боб может развернуться:

bob=> INSERT INTO revenue(who, amount) VALUES ('alice', -9999.99);
INSERT 0 1
bob=> SELECT * FROM revenue;
 who |  amount
-----+----------
 bob | -1000.00
 bob | -2000.00
 bob |  -100.00
(3 rows)

Обратите внимание, что команда COPY FROM не работает при включенной защите строк:

bob=> COPY revenue FROM stdin;
ERROR:  COPY FROM not supported with row-level security
HINT:  Use INSERT statements instead.

Page 22: Комбинирование политик

Разрешительные политики

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

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

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

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

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

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

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

P1 OR … OR PN.

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

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

Page 23: Комбинирование политик

Source slide page 23: Комбинирование политик{#source-figure-023 caption=“Source slide page 23: Комбинирование политик”}

Сначала убедимся, что при включенной защите строк отсутствие политик приводит к тому, что не видна ни одна строка:

student=# DROP POLICY users ON revenue;
DROP POLICY
alice=> SELECT * FROM revenue;
 who | amount
-----+--------
(0 rows)

Теперь создадим две политики — разрешительные по умолчанию. Первая, как и раньше, разрешает пользователю видеть свои строки:

student=# CREATE POLICY users ON revenue
USING ( who = current_user );
CREATE POLICY

Вторая разрешает видеть строки любого пользователя, если сумма не превышает пятисот рублей:

student=# CREATE POLICY small_amount ON revenue
USING ( abs(amount) <= 500.00 );
CREATE POLICY

Проверим.

bob=> SELECT * FROM revenue;
  who  |  amount
-------+----------
 bob   | -1000.00
 bob   | -2000.00
 alice |   100.00
 alice |   200.00
 bob   |  -100.00
 alice |   300.00
(6 rows)

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

student=# CREATE POLICY big_amount ON revenue AS RESTRICTIVE
USING ( abs(amount) < 1000.00 );
CREATE POLICY
bob=> SELECT * FROM revenue;
  who  | amount
-------+---------
 alice |  100.00
 alice |  200.00
 bob   | -100.00
 alice |  300.00
(4 rows)

Таким образом можно комбинировать несколько условий.

Page 24: Итоги

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

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

Page 25: Практика

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

Page 26: 1. Разграничение с помощью функции

Создадим необходимые роли и базу данных.

student=# CREATE USER alice;
CREATE ROLE
student=# CREATE USER bob;
CREATE ROLE
student=# CREATE USER charlie;
CREATE ROLE
student=# CREATE USER dave;
CREATE ROLE
student=# CREATE ROLE staff ROLE alice, bob, charlie, dave;
CREATE ROLE
student=# CREATE DATABASE access_rows;
CREATE DATABASE
student=# \c access_rows
You are now connected to database "access_rows" as user "student".

Создадим таблицу распределения сотрудников по отделам.

student=# CREATE TABLE staff(
  who text PRIMARY KEY,
  department text
);
CREATE TABLE
student=# INSERT INTO staff(who, department) VALUES
  ('alice', 'Sales'),
  ('bob', 'PR'),
  ('charlie', 'Sales'),
  ('dave', 'PR');
INSERT 0 4

Таблица доходов и расходов из демонстрации теперь ссылается на staff.

student=# CREATE TABLE revenue(
  who text DEFAULT current_user REFERENCES staff,
  amount numeric(10,2)
);
CREATE TABLE
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);
INSERT 0 4000

Функция для разграничения доступа по строкам:

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;
CREATE FUNCTION

Проверяем в сеансе Алисы:

Page 27: Page 27

Source slide page 27: Page 27{#source-figure-027 caption=“Source slide page 27: Page 27”}

student$ psql -d access_rows -U alice
alice=> SELECT who, sum(amount)
FROM revenue
GROUP BY who;
ERROR:  permission denied for table revenue

Прямого доступа к таблице нет.

alice=> SELECT who, sum(amount)
FROM get_revenue()
GROUP BY who;
   who   |    sum
---------+-----------
 charlie | 489497.33
 alice   | 510889.47
(2 rows)

Функция работает. Проверяем в сеансе Боба:

student$ psql -d access_rows -U bob
bob=> SELECT who, sum(amount)
FROM get_revenue()
GROUP BY who;
 who  |    sum
------+------------
 dave | -498182.39
 bob  | -490463.06
(2 rows)

2. Разграничение с помощью представления

Представление для разграничения доступа:

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;
CREATE VIEW
student=# GRANT SELECT ON user_revenue TO staff;
GRANT

Проверяем.

alice=> SELECT who, sum(amount)
FROM user_revenue
GROUP BY who;
   who   |    sum
---------+-----------
 alice   | 510889.47
 charlie | 489497.33
(2 rows)
bob=> SELECT who, sum(amount)
FROM user_revenue
GROUP BY who;
 who  |    sum
------+------------
 bob  | -490463.06
 dave | -498182.39
(2 rows)

3. Разграничение с помощью политик

Политика защиты строк:

Page 28: Page 28

Source slide page 28: Page 28{#source-figure-028 caption=“Source slide page 28: Page 28”}

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
  )
);
CREATE POLICY
student=# ALTER TABLE revenue ENABLE ROW LEVEL SECURITY;
ALTER TABLE
student=# GRANT SELECT ON staff, revenue TO staff;
GRANT

Проверяем.

alice=> SELECT who, sum(amount)
FROM revenue
GROUP BY who;
   who   |    sum
---------+-----------
 charlie | 489497.33
 alice   | 510889.47
(2 rows)
bob=> SELECT who, sum(amount)
FROM revenue
GROUP BY who;
 who  |    sum
------+------------
 dave | -498182.39
 bob  | -490463.06
(2 rows)

Уборка

student=# \c student
You are now connected to database "student" as user "student".
student=# DROP DATABASE access_rows WITH (FORCE);
DROP DATABASE
student=# DROP ROLE alice, bob, charlie, dave, staff;
DROP ROLE

Page 29: Практика+

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

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

  1. Установите у представления параметр security_invoker.

Page 30: 1. Настройка политик

Создаем роли, база данных и таблицы как в Практике.

student=# CREATE USER alice;
CREATE ROLE
student=# CREATE USER bob;
CREATE ROLE
student=# CREATE USER charlie;
CREATE ROLE
student=# CREATE USER dave;
CREATE ROLE
student=# CREATE ROLE staff ROLE alice, bob, charlie, dave;
CREATE ROLE
student=# CREATE DATABASE access_rows;
CREATE DATABASE
student=# \c access_rows
You are now connected to database "access_rows" as user "student".
student=# CREATE TABLE staff(
  who text PRIMARY KEY,
  department text
);
CREATE TABLE
student=# INSERT INTO staff(who, department) VALUES
  ('alice', 'Sales'),
  ('bob', 'PR'),
  ('charlie', 'Sales'),
  ('dave', 'PR');
INSERT 0 4
student=# CREATE TABLE revenue(
  who text DEFAULT current_user REFERENCES staff,
  amount numeric(10,2)
);
CREATE TABLE
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);
INSERT 0 4000

Политика защиты строк:

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
  )
);
CREATE POLICY
student=# ALTER TABLE revenue ENABLE ROW LEVEL SECURITY;
ALTER TABLE
student=# GRANT SELECT ON staff, revenue TO staff;
GRANT

Page 31: Page 31

Проверяем работу политик, сначала в сеансе Алисы:

student$ psql -d access_rows -U alice
alice=> SELECT who, sum(amount)
FROM revenue
GROUP BY who;
   who   |    sum
---------+-----------
 charlie | 498936.20
 alice   | 505916.69
(2 rows)

Затем в сеансе Боба:

student$ psql -d access_rows -U bob
bob=> SELECT who, sum(amount)
FROM revenue
GROUP BY who;
 who  |    sum
------+------------
 dave | -502933.63
 bob  | -506120.34
(2 rows)

2. Представление

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;
CREATE VIEW

Владелец по умолчанию видит все:

student=# SELECT * FROM v_dept_totals;
 department |     sum
------------+-------------
 PR         | -1009053.97
 Sales      |  1004852.89
(2 rows)
student=# GRANT SELECT ON v_dept_totals TO staff;
GRANT

3. Проверка представления

Обратимся к представлению в сеансе Алисы:

alice=> SELECT * FROM v_dept_totals;
 department |     sum
------------+-------------
 PR         | -1009053.97
 Sales      |  1004852.89
(2 rows)

Несмотря на наличие политики, Алиса видит все строки исходной таблицы, так как запрос, определяющий представление, по умолчанию выполняется от имени его владельца student. То же и для Боба:

bob=> SELECT * FROM v_dept_totals;
 department |     sum
------------+-------------
 PR         | -1009053.97
 Sales      |  1004852.89
(2 rows)

Page 32: 4. Свойство представления security_invoker

Source slide page 32: 4. Свойство представления security_invoker{#source-figure-032 caption=“Source slide page 32: 4. Свойство представления security_invoker”}

Чтобы запрос представления выполнялся от имени текущего пользователя, нужно задать параметр security_invoker:

student=# ALTER VIEW v_dept_totals SET (security_invoker);
ALTER VIEW

Теперь и Алиса, и Боб видят строки только своих отделов:

alice=> SELECT * FROM v_dept_totals;
 department |    sum
------------+------------
 Sales      | 1004852.89
(1 row)
bob=> SELECT * FROM v_dept_totals;
 department |     sum
------------+-------------
 PR         | -1009053.97
(1 row)

Уборка

student=# \c student
You are now connected to database "student" as user "student".
student=# DROP DATABASE access_rows WITH (FORCE);
DROP DATABASE
student=# DROP ROLE alice, bob, charlie, dave, staff;
DROP ROLE