Побочный эффект Postgres Trigger происходит не по порядку с политикой выбора безопасности на уровне строк

Question

контекст

Я использую безопасность на уровне строк вместе с триггерами для реализации чистой реализации RBAC SQL. При этом я столкнулся со странным поведением между INSERT триггеры и SELECT политики безопасности на уровне строк.

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

CREATE TABLE a (id TEXT);
ALTER TABLE a ENABLE ROW LEVEL SECURITY;
ALTER TABLE a FORCE ROW LEVEL SECURITY;

CREATE TABLE b (id TEXT);

вопрос

Рассмотрим следующие политики и триггеры:

CREATE POLICY aSelect ON a FOR SELECT
USING (EXISTS(
    select * from b where a.id = b.id
));

CREATE POLICY aInsert ON a FOR INSERT
WITH CHECK (true);

CREATE FUNCTION reproHandler() RETURNS TRIGGER AS $$
BEGIN
    RAISE NOTICE USING MESSAGE = 'inside trigger handler';
    INSERT INTO b (id) VALUES (NEW.id);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER reproTrigger BEFORE INSERT ON a
FOR EACH ROW EXECUTE PROCEDURE reproHandler();

Теперь рассмотрим следующее утверждение:

INSERT INTO a VALUES ('fails') returning id;

Ожидание, которое я основал на чтении политик, применяемых таблицей типов команд, и общее понимание SQL, состоит в том, что следующие вещи должны происходить по порядку:

1. Новый ряд ('fails') ставится для INSERT
2. BEFORE вызвать огонь с NEW установить в новый ряд
3. Ряд ('fails') вставляется в b и вернул из триггера процедуру без изменений
4. INSERT"s WITH CHECK политика true оценивается в true
5. SELECT"s USING политика select * from b where a.id = b.id оценивается. Это должно вернуть true из-за шага 3
6. Пройдя все полисы, грести ('fails') вставляется в таблицу
7. Идентификатор (fails) вставленной строки возвращается

К сожалению (как вы уже догадались), вместо того, чтобы делать описанные выше шаги, мы видим это:

test=> INSERT INTO a VALUES ('fails') returning id;
NOTICE:  inside trigger handler
ERROR:  new row violates row-level security policy for table "a"

Цель этого вопроса - выяснить, почему ожидаемое поведение не происходит.

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

test=> INSERT INTO a VALUES ('works');
NOTICE:  inside trigger handler
INSERT 0 1
test=> select * from a; select * from b;
  id   
-------
 works
(1 row)

  id   
-------
 works
(1 row)

Что я пробовал?

• Экспериментировал с BEFORE против AFTER в определении триггера
  • AFTER приводит к тому, что триггер не выполняется вообще
• Экспериментировал с определением единой политики, которая применяется к ALL команды (с одинаковым использованием / с проверочным выражением)
  • приводит к тому же поведению

аппендикс

• Версия Postgres
  • PostgreSQL 10.3 on x86_64-pc-linux-musl, compiled by gcc (Alpine 6.4.0) 6.4.0, 64-bit
• Если вы попытаетесь воспроизвести проблему, убедитесь, что вы не работаете с разрешениями SUPER, поскольку это игнорирует безопасность строк.

Answer 1

После некоторых обсуждений с другими пользователями / разработчиками PostgreSQL в общем списке рассылки было определено, что эта конкретная проблема вызвана видимостью мутаций в одном выражении. Вы можете просмотреть всю дискуссию здесь. Отдельное спасибо Дину Рашиду за то, что он объяснил проблему и нашел решение. Я суммировал его ответ здесь для пользы сообщества переполнения стека.

Таким образом, строка, вставленная триггером, не видна последующим EXISTS пункт в безопасности на уровне строк SELECT политика из-за того, что весь оператор выполняется в одном снимке PostgreSQL.

Один из способов обойти эту проблему - убедиться, что EXISTS Предложение запускается с новым снимком. Для этого EXISTS предложение может использовать функцию PostgreSQL с пометкой VOLATILE, Этот атрибут функции позволит функции наблюдать за изменениями, внесенными в пределах одного и того же оператора. Для получения дополнительной информации обратитесь к документации. Соответствующий параграф извлечен здесь для справки:

Для функций, написанных на SQL или на любом из стандартных процедурных языков, существует другое важное свойство, определяемое категорией волатильности, а именно видимость любых изменений данных, которые были внесены командой SQL, вызывающей функцию. Функция VOLATILE будет видеть такие изменения, а функция STABLE или IMMUTABLE - нет. Это поведение реализовано с использованием поведения моментальных снимков MVCC (см. Главу 13): функции STABLE и IMMUTABLE используют моментальный снимок, созданный в начале вызывающего запроса, тогда как функции VOLATILE получают новый снимок в начале каждого выполняемого ими запроса.

Таким образом, одним из решений этой проблемы является реализация политики выбора RLS как VOLATILE функция. Пример изменения политики:

CREATE OR REPLACE FUNCTION rlsCheck(_id text) RETURNS TABLE (id text) AS $$
    select * from b where b.id = _id
$$ LANGUAGE sql VOLATILE;

CREATE POLICY reproPolicySelect ON a FOR SELECT
USING (
    EXISTS(select * from rlsCheck(a.id))
);

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

Если вы сделаете вышеуказанную модификацию и запустите тест, вы увидите следующее поведение:

test=> select * from a;
id 
----
(0 rows)

test=> select * from b;
id 
----
(0 rows)

test=> insert into a values ('hi') returning id;
NOTICE:  inside trigger handler
id 
----
hi
(1 row)

INSERT 0 1

Такое поведение соответствует моим ожиданиям, поэтому я принимаю это как ответ на проблему. К сожалению, эта функция приводит к неприемлемому ограничению оптимизации во время выполнения запроса, поэтому я не буду использовать это в моей реализации RBAC. Я не верю, что возможно найти оптимальное решение моей проблемы, так как EXISTS выражение в SELECT Политика не может быть встроенной и волатильной одновременно.