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

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

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

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

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

Суперпользователи и роли с атрибутом BYPASSRLS при доступе к таблице всегда обходят систему защиты строк. Владельцы таблиц обычно тоже обходят защиту строк, хотя они могут включить ее для себя с помощью ALTER TABLE ... FORCE ROW LOWEL 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:

-- Простой пример на основе файла 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);
-- Обычные пользователи могут изменять свои записи, но
-- не могут задавать любую произвольную оболочку
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;

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

-- администратор может видеть все строки и поля
qhb=> set role admin;
SET
qhb=> 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)

-- Проверка того, что может делать Элис
qhb=> set role alice;
SET
qhb=> table passwd;
ERROR:  permission denied for table passwd
-- ОШИБКА: отсутствие прав доступа для таблицы passwd
qhb=> 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)

qhb=> update passwd set user_name = 'joe';
ERROR:  permission denied for table passwd
-- Элис разрешено изменить свое имя (поле real_name), но не другие имена
qhb=> update passwd set real_name = 'Alice Doe';
UPDATE 1
qhb=> update passwd set real_name = 'John Doe' where user_name = 'admin';
UPDATE 0
qhb=> update passwd set shell = '/bin/xx';
ERROR:  new row violates WITH CHECK OPTION for "passwd"
-- ОШИБКА: новая роль нарушает ПАРАМЕТР WITH CHECK для "passwd"
qhb=> delete from passwd;
ERROR:  permission denied for table passwd
qhb=> insert into passwd (user_name) values ('xxx');
ERROR:  permission denied for table passwd
-- Элис может изменить свой пароль; изменение других строк RLS автоматически предотвращает
qhb=> update passwd set pwhash = 'abc';
UPDATE 1

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

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

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

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

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

-- определение групп прав
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
GRANT ALL ON information TO public;

Теперь предположим, что alice хочет изменить «слегка секретную» (slightly secret) информацию, но решает, что 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. Однако она не извлекает измененную строку из users для неявного SELECT, потому что этот подзапрос SELECT не содержал FOR UPDATE; вместо этого строка users читается из снимка, сделанного в начале запроса. Поэтому выражение политики проверяет старое значение уровня прав mallory и позволяет ей видеть эту измененную строку.

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

Дополнительную информацию см. на справочных страницах команд CREATE POLICY и ALTER TABLE.