В дополнение к стандартной системе привилегий SQL, доступной через команду GRANT, таблицы могут иметь политики безопасности строк (row security policies). Эти политики ограничивают для каждого конкретного пользователя список строк, которые могут быть возвращены обычными запросами или добавлены, изменены или удалены командами модификации данных. Эта функция также известна как безопасность на уровне строк (Row-Level Security, RLS). По умолчанию таблицы не имеют никаких политик, поэтому, если пользователь имеет привилегии доступа к таблице в соответствии со стандартной системой привилегий SQL, все строки в ней одинаково доступны для чтения или обновления.

Когда безопасность строк включена для таблицы (с помощью команды ALTER TABLE ... ENABLE ROW LEVEL SECURITY), любой обычный доступ к таблице для выборки или изменения строк должен быть разрешен политикой безопасности строк. (Однако на владельца таблицы политики безопасности строк обычно не распространяются.) Если для таблицы не существует ни одной политики, используется политика запрета по умолчанию (default-deny), что означает, что ни одна строка не будет видна и не сможет быть изменена. Операции, которые применяются к таблице в целом, такие как TRUNCATE и REFERENCES, не подчиняются безопасности строк.

Политики безопасности строк могут быть привязаны к конкретным командам, ролям или к тому и другому одновременно. Можно указать, чтобы политика применялась ко всем командам (ALL) или к конкретным: SELECT, INSERT, UPDATE или DELETE. Одной политике можно назначить несколько ролей; при этом применяются стандартные правила членства в ролях и наследования.

Чтобы определить, какие строки видимы или могут быть изменены в соответствии с политикой, требуется выражение, возвращающее логическое значение (Boolean). Это выражение будет вычисляться для каждой строки до применения любых условий или функций из запроса пользователя. (Единственным исключением из этого правила являются герметичные функции — leakproof, которые гарантированно не допускают утечки информации; оптимизатор может принять решение применить такие функции до проверки безопасности строк.) Строки, для которых выражение не возвращает true, обрабатываться не будут. Можно указать отдельные выражения для независимого контроля над строками, которые видимы, и строками, которые разрешено изменять. Выражения политик выполняются как часть запроса и с привилегиями пользователя, выполняющего запрос, хотя для доступа к данным, недоступным вызывающему пользователю, могут использоваться функции с определением безопасности создателя (security-definer).

Суперпользователи и роли с атрибутом BYPASSRLS всегда обходят систему безопасности строк при доступе к таблице. Владельцы таблиц обычно также обходят безопасность строк, хотя владелец таблицы может согласиться на применение безопасности строк с помощью команды ALTER TABLE ... FORCE ROW LEVEL SECURITY.

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

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

Каждая политика имеет имя, и для одной таблицы может быть определено несколько политик. Поскольку политики привязаны к конкретным таблицам, каждая политика для таблицы должна иметь уникальное имя. Разные таблицы могут иметь политики с одинаковыми именами.

Когда к конкретному запросу применяются несколько политик, они объединяются либо с помощью оператора OR (для разрешающих политик — permissive, которые используются по умолчанию), либо с помощью оператора AND (для ограничивающих политик — restrictive). Поведение OR аналогично правилу, согласно которому данная роль обладает привилегиями всех ролей, в которых она состоит. Разрешающие и ограничивающие политики более подробно рассматриваются ниже.

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

sqlКопировать
CREATE TABLE accounts (manager text, company text, contact_email text);
ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;
CREATE POLICY account_managers ON accounts TO managers USING (manager = current_user);

Приведенная выше политика неявно предоставляет предложение WITH CHECK, идентичное ее предложению USING, так что ограничение применяется как к строкам, выбираемым командой (таким образом, менеджер не может выполнить SELECT, UPDATE или DELETE для существующих строк, принадлежащих другому менеджеру), так и к строкам, изменяемым командой (таким образом, строки, принадлежащие другому менеджеру, не могут быть созданы с помощью INSERT или UPDATE).

Если роль не указана или используется специальное имя пользователя PUBLIC, то политика применяется ко всем пользователям в системе. Чтобы разрешить всем пользователям доступ только к их собственной строке в таблице users, можно использовать простую политику:

sqlКопировать
CREATE POLICY user_policy ON users USING (user_name = current_user);

Это работает аналогично предыдущему примеру.

Чтобы использовать разные политики для строк, добавляемых в таблицу, по сравнению со строками, которые видимы, можно объединить несколько политик. Эта пара политик позволит всем пользователям просматривать все строки в таблице users, но изменять только свои собственные:

sqlКопировать
CREATE POLICY user_sel_policy ON users FOR SELECT USING (true);
CREATE POLICY user_mod_policy ON users USING (user_name = current_user);

В команде SELECT эти две политики объединяются с помощью OR, в результате чего могут быть выбраны все строки. В других типах команд применяется только вторая политика, поэтому эффект остается прежним.

Безопасность строк также можно отключить с помощью команды ALTER TABLE. Отключение безопасности строк не удаляет политики, определенные для таблицы; они просто игнорируются. В этом случае все строки в таблице становятся видимыми и изменяемыми в соответствии со стандартной системой привилегий SQL.

Ниже приведен более крупный пример того, как эта функция может использоваться в рабочих средах. Таблица passwd имитирует файл паролей Unix:

sqlКопировать
-- Простой пример на основе файла passwd
CREATE TABLE passwd (
    user_name text UNIQUE NOT NULL,
    pwhash text,
    uid int PRIMARY KEY,
    gid int NOT NULL,
    real_name text NOT NULL,
    home_phone text,
    extra_info text,
    home_dir text NOT NULL,
    shell text NOT NULL
);

CREATE ROLE admin; -- Администратор
CREATE ROLE bob;   -- Обычный пользователь
CREATE ROLE alice; -- Обычный пользователь

-- Заполнение таблицы
INSERT INTO passwd VALUES ('admin','xxx',0,0,'Admin','111-222-3333',null,'/root','/bin/dash');
INSERT INTO passwd VALUES ('bob','xxx',1,1,'Bob','123-456-7890',null,'/home/bob','/bin/zsh');
INSERT INTO passwd VALUES ('alice','xxx',2,1,'Alice','098-765-4321',null,'/home/alice','/bin/zsh');

-- Обязательно включаем безопасность на уровне строк для таблицы
ALTER TABLE passwd ENABLE ROW LEVEL SECURITY;

-- Создание политик
-- Администратор может видеть все строки и добавлять любые строки
CREATE POLICY admin_all ON passwd TO admin USING (true) WITH CHECK (true);

-- Обычные пользователи могут просматривать все строки
CREATE POLICY all_view ON passwd FOR SELECT USING (true);

-- Обычные пользователи могут обновлять свои собственные записи,
-- но ограничиваем оболочки (shells), которые обычный пользователь может установить
CREATE POLICY user_mod ON passwd FOR UPDATE USING (current_user = user_name) WITH CHECK (
    current_user = user_name AND shell IN ('/bin/bash','/bin/sh','/bin/dash','/bin/zsh','/bin/tcsh')
);

-- Предоставление администратору всех обычных прав
GRANT SELECT, INSERT, UPDATE, DELETE ON passwd TO admin;

-- Пользователи получают доступ на чтение только для публичных столбцов
GRANT SELECT (user_name, uid, gid, real_name, home_phone, extra_info, home_dir, shell) ON passwd TO public;

-- Разрешаем пользователям обновлять определенные столбцы
GRANT UPDATE (pwhash, real_name, home_phone, extra_info, shell) ON passwd TO public;

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

-- admin может просматривать все строки и поля
postgres=> set role admin;
SET
postgres=> table passwd;
 user_name | pwhash | uid | gid |  real_name |  home_phone  | extra_info |  home_dir   |   shell   
-----------+--------+-----+-----+------------+--------------+------------+-------------+-----------
 admin     | xxx    |   0 |   0 | Admin      | 111-222-3333 |            | /root       | /bin/dash
 bob       | xxx    |   1 |   1 | Bob        | 123-456-7890 |            | /home/bob   | /bin/zsh
 alice     | xxx    |   2 |   1 | Alice      | 098-765-4321 |            | /home/alice | /bin/zsh
(3 rows)

-- Проверим, что может делать Alice
postgres=> set role alice;
SET
postgres=> table passwd;
ERROR:  permission denied for table passwd
postgres=> select user_name,real_name,home_phone,extra_info,home_dir,shell from passwd;
 user_name | real_name |  home_phone  | extra_info |  home_dir   |   shell   
-----------+-----------+--------------+------------+-------------+-----------
 admin     | Admin     | 111-222-3333 |            | /root       | /bin/dash
 bob       | Bob       | 123-456-7890 |            | /home/bob   | /bin/zsh
 alice     | Alice     | 098-765-4321 |            | /home/alice | /bin/zsh
(3 rows)

postgres=> update passwd set user_name = 'joe';
ERROR:  permission denied for table passwd

-- Alice разрешено изменять свое собственное имя (real_name), но ничье другое
postgres=> update passwd set real_name = 'Alice Doe';
UPDATE 1
postgres=> update passwd set real_name = 'John Doe' where user_name = 'admin';
UPDATE 0
postgres=> update passwd set shell = '/bin/xx';
ERROR:  new row violates WITH CHECK OPTION for "passwd"
postgres=> delete from passwd;
ERROR:  permission denied for table passwd
postgres=> insert into passwd (user_name) values ('xxx');
ERROR:  permission denied for table passwd

-- Alice может изменить свой собственный пароль; RLS незаметно предотвращает обновление других строк
postgres=> update passwd set pwhash = 'abc';
UPDATE 1

Все политики, созданные до сих пор, были разрешающими (permissive), что означает, что при применении нескольких политик они объединяются с помощью логического оператора «OR». Хотя разрешающие политики можно составить так, чтобы доступ к строкам предоставлялся только в запланированных случаях, может быть проще объединить разрешающие политики с ограничивающими (restrictive) политиками (которым записи должны соответствовать и которые объединяются с помощью логического оператора «AND»). На основе приведенного выше примера мы добавим ограничивающую политику, требующую, чтобы администратор подключался через локальный Unix-сокет для доступа к записям таблицы passwd:

sqlКопировать
CREATE POLICY admin_local_only ON passwd AS RESTRICTIVE TO admin USING (pg_catalog.inet_client_addr() IS NULL);

Затем мы увидим, что администратор, подключающийся по сети, не увидит никаких записей из-за этой ограничивающей политики:

=> SELECT current_user;
 current_user 
--------------
 admin
(1 row)

=> select inet_client_addr();
 inet_client_addr 
------------------
 127.0.0.1
(1 row)

=> TABLE passwd;
 user_name | pwhash | uid | gid | real_name | home_phone | extra_info | home_dir | shell 
-----------+--------+-----+-----+-----------+------------+------------+----------+-------
(0 rows)

=> UPDATE passwd set pwhash = NULL;
UPDATE 0

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

В некоторых контекстах важно быть уверенным, что безопасность строк не применяется. Например, при создании резервной копии было бы катастрофой, если бы безопасность строк незаметно привела к исключению некоторых строк из бэкапа. В такой ситуации вы можете установить параметр конфигурации row_security в значение off. Само по себе это не обходит безопасность строк; вместо этого генерируется ошибка, если результаты любого запроса будут отфильтрованы политикой. После этого причину ошибки можно исследовать и устранить.

В приведенных выше примерах выражения политик учитывают только текущие значения в строке, к которой осуществляется доступ или которая обновляется. Это самый простой и наиболее производительный случай; по возможности лучше проектировать приложения безопасности строк именно так. Если для принятия решения по политике необходимо обратиться к другим строкам или другим таблицам, это можно сделать с помощью подзапросов SELECT или функций, содержащих SELECT, в выражениях политик. Однако имейте в виду, что такие обращения могут создавать состояния гонки (race conditions), которые могут привести к утечке информации, если не проявить осторожность. В качестве примера рассмотрим следующую структуру таблиц:

sqlКопировать
-- определение групп привилегий
CREATE TABLE groups (group_id int PRIMARY KEY, group_name text NOT NULL);
INSERT INTO groups VALUES (1, 'low'), (2, 'medium'), (5, 'high');
GRANT ALL ON groups TO alice; -- alice является администратором
GRANT SELECT ON groups TO public;

-- определение уровней привилегий пользователей
CREATE TABLE users (user_name text PRIMARY KEY, group_id int NOT NULL REFERENCES groups);
INSERT INTO users VALUES ('alice', 5), ('bob', 2), ('mallory', 2);
GRANT ALL ON users TO alice;
GRANT SELECT ON users TO public;

-- таблица, содержащая защищаемую информацию
CREATE TABLE information (info text, group_id int NOT NULL REFERENCES groups);
INSERT INTO information VALUES ('barely secret', 1), ('slightly secret', 2), ('very secret', 5);
ALTER TABLE information ENABLE ROW LEVEL SECURITY;

-- строка должна быть видна/доступна для обновления пользователям, чей group_id безопасности
-- больше или равен group_id строки
CREATE POLICY fp_s ON information FOR SELECT USING (group_id <= (SELECT group_id FROM users WHERE user_name = current_user));
CREATE POLICY fp_u ON information FOR UPDATE USING (group_id <= (SELECT group_id FROM users WHERE user_name = current_user));

-- мы полагаемся только на RLS для защиты таблицы information
GRANT ALL ON information TO public;

Теперь предположим, что alice хочет изменить «слегка секретную» информацию, но решает, что mallory не следует доверять новое содержимое этой строки, поэтому она делает следующее:

sqlКопировать
BEGIN;
UPDATE users SET group_id = 1 WHERE user_name = 'mallory';
UPDATE information SET info = 'secret from mallory' WHERE group_id = 2;
COMMIT;

Это выглядит безопасно; нет временного окна, в котором mallory должна иметь возможность увидеть строку «secret from mallory». Однако здесь существует состояние гонки. Если mallory параллельно выполняет, например, запрос:

sqlКопировать
SELECT * FROM information WHERE group_id = 2 FOR UPDATE;

и ее транзакция находится в режиме изоляции READ COMMITTED, она может увидеть строку «secret from mallory». Это происходит, если ее транзакция обращается к строке таблицы information сразу после транзакции alice. Она блокируется в ожидании фиксации транзакции alice, а затем извлекает обновленное содержимое строки благодаря предложению FOR UPDATE. Однако она не извлекает обновленную строку для неявного запроса SELECT из таблицы users, поскольку этот подзапрос SELECT не содержал FOR UPDATE; вместо этого строка users считывается со снимком данных, сделанным в начале запроса. Таким образом, выражение политики проверяет старое значение уровня привилегий mallory и позволяет ей увидеть обновленную строку.

Есть несколько способов обойти эту проблему. Один простой ответ — использовать SELECT ... FOR SHARE в подзапросах SELECT в политиках безопасности строк. Однако для этого требуется предоставить привилегию UPDATE для ссылочной таблицы (в данном случае users) затрагиваемым пользователям, что может быть нежелательно. (Но можно применить другую политику безопасности строк, чтобы предотвратить фактическое использование ими этой привилегии; или подзапрос SELECT может быть встроен в функцию с определением безопасности security definer). Кроме того, интенсивное параллельное использование блокировок совместного доступа к строкам (row share) на ссылочной таблице может вызвать проблемы с производительностью, особенно если ее обновления происходят часто. Другое решение, практичное в случае редких обновлений ссылочной таблицы, — брать блокировку ACCESS EXCLUSIVE на ссылочную таблицу при ее обновлении, чтобы никакие параллельные транзакции не могли анализировать старые значения строк. Или можно просто дождаться завершения всех параллельных транзакций после фиксации обновления ссылочной таблицы и перед внесением изменений, которые зависят от новой ситуации с безопасностью.

Для получения дополнительных сведений см. разделы CREATE POLICY и ALTER TABLE.

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