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

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

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

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

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

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

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

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

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

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

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, можно использовать простую политику:

CREATE POLICY user_policy ON users
    USING (user_name = current_user);

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

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

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:

-- Simple passwd-file based example
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;  -- Administrator
CREATE ROLE bob;    -- Normal user
CREATE ROLE alice;  -- Normal user

-- Populate the table
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');

-- Be sure to enable row-level security on the table
ALTER TABLE passwd ENABLE ROW LEVEL SECURITY;

-- Create policies
-- Administrator can see all rows and add any rows
CREATE POLICY admin_all ON passwd TO admin USING (true) WITH CHECK (true);
-- Normal users can view all rows
CREATE POLICY all_view ON passwd FOR SELECT USING (true);
-- Normal users can update their own records, but
-- limit which shells a normal user is allowed to set
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')
  );

-- Allow admin all normal rights
GRANT SELECT, INSERT, UPDATE, DELETE ON passwd TO admin;
-- Users only get select access on public columns
GRANT SELECT
  (user_name, uid, gid, real_name, home_phone, extra_info, home_dir, shell)
  ON passwd TO public;
-- Allow users to update certain columns
GRANT UPDATE
  (pwhash, real_name, home_phone, extra_info, shell)
  ON passwd TO public;

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

-- admin can view all rows and fields
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)

-- Test what Alice is able to do
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 is allowed to change her own real_name, but no others
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 can change her own password; RLS silently prevents updating other rows
postgres=> update passwd set pwhash = 'abc';
UPDATE 1

Все определенные до сих пор политики были разрешительными политиками, что означает, что при применении нескольких политик они объединяются с использованием логического оператора "ИЛИ" (“OR”). Хотя разрешительные политики могут быть настроены так, чтобы разрешить доступ только к строкам в определённых случаях, проще объединить разрешительные политики с ограничительными политиками (которым должны удовлетворять записи и которые объединяются с использованием логического оператора "И" (“AND”)). На основе приведенного выше примера мы добавляем ограничительную политику, которая дает администратору, подключенному через локальный сокет Unix доступ к записям таблицы passwd.

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

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

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

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

-- definition of privilege groups
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 is the administrator
GRANT SELECT ON groups TO public;

-- definition of users' privilege levels
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;

-- table holding the information to be protected
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;

-- a row should be visible to/updatable by users whose security group_id is
-- greater than or equal to the row's 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));

-- we rely only on RLS to protect the information table
GRANT ALL ON information TO public;

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

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 одновременно выполняет, скажем,

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 можно встроить в функцию, определяющую политику защиты). Кроме того, активное параллельное использование блокировок на строки в целевой таблице может вызвать проблемы с производительностью, особенно если обновления в ней происходят часто. Другое решение, применимое при редких обновлениях целевой таблицы, заключается в том, чтобы установить блокировку ACCESS EXCLUSIVE на целевую таблицу при ее обновлении, чтобы никакие параллельные транзакции не могли просматривать старые значения строк. Или можно просто дождаться завершения всех параллельных транзакций после обновления целевой таблицы, прежде чем вносить изменения, рассчитанные на новые условия безопасности.

Дополнительные сведения см. в CREATE POLICY и ALTER TABLE.