Контекст
Я использую безопасность на уровне строк вместе с триггерами для реализации чистой реализации 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 таково, что следующие вещи должны происходить по порядку:
- Новый ряд
('fails')
поставлен дляINSERT
- Триггер
BEFORE
срабатывает сNEW
, установленным на новую строку - Строка
('fails')
вставляется вb
и возвращается из процедуры триггера без изменений. WITH CHECK
политикаtrue
INSERT
оценивается какtrue
- Оценивается
USING
политикаselect * from b where a.id = b.id
SELECT
. Это должно вернуть истину согласно шагу 3 - После прохождения всех политик строка
('fails')
вставляется в таблицу - Возвращается идентификатор (
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)
Что я пробовал?
- Experimented with
BEFORE
versusAFTER
in the trigger definitionAFTER
results in the trigger not executing at all
- Experimented with defining a single policy which applies to
ALL
commands (with the same using/with check expression)- results in the same behavior
Приложение
- Postgres Version
PostgreSQL 10.3 on x86_64-pc-linux-musl, compiled by gcc (Alpine 6.4.0) 6.4.0, 64-bit
- Если вы попытаетесь воспроизвести проблему, убедитесь, что вы не работаете с разрешениями SUPER, так как это игнорирует безопасность строк