Теоретическая часть отлично описана в документации Postgres Pro Политики защиты строк. Ниже рассмотрена практическая реализация маленькой конкретной бизнес задачи скрытия удаленных данных . Этюд посвященный реализации Ролевой модели с использованием RLS и бизнес-логики в хранимых процедурах будет представлен позже.
В статье ничего нового, нет скрытого смысла и тайных знаний. Просто зарисовка о практической реализации теоретической идеи. Если кому интересно читайте. Кому не интересно не тратьте свое время зря.
Постановка задачи
Не погружаясь глубоко в предметную область, кратко, задачу можно сформулировать следующим образом: имеется таблица реализующая некую бизнес сущность. Строки в таблице могут удаляться, но физически удалять строки нельзя, нужно их скрывать.Ибо сказано Ничего не удаляй, только переименовывай. Интернет хранит ВСЁ
Попутно, желательно не переписывать уже имеющиеся хранимые функции работающие с данной сущностью.
Для реализации данной концепции, таблица имеет атрибут is_deleted. Далее все просто необходимо сделать так, что бы клиент мог видеть только строки в которых атрибут is_deleted ложен. Для чего и используется механизм Row Level Security.
Реализация
Создаем отдельную роль и схему
CREATE ROLE repos;CREATE SCHEMA repos;
Создаем целевую таблицу
CREATE TABLE repos.file(...is_del BOOLEAN DEFAULT FALSE);CREATE SCHEMA repos
Включаем Row Level Security
ALTER TABLE repos.file ENABLE ROW LEVEL SECURITY ;CREATE POLICY file_invisible_deleted ON repos.file FOR ALL TO dba_role USING ( NOT is_deleted );GRANT ALL ON TABLE repos.file to dba_role ;GRANT USAGE ON SCHEMA repos TO dba_role ;
Сервисная функция-удаление строки в таблице
CREATE OR REPLACE repos.delete( curr_id repos.file.id%TYPE)RETURNS integer AS $$BEGIN...UPDATE repos.fileSET is_del = TRUE WHERE id = curr_id ; ...END$$ LANGUAGE plpgsql SECURITY DEFINER;
Бизнес функция-удаление документа
CREATE OR REPLACE business_functions.deleteDoc( doc_for_delete JSON )RETURNS JSON AS $$BEGIN...PERFORM repos.delete( doc_id ) ;...END$$ LANGUAGE plpgsql SECURITY DEFINER;
Результаты
Клиент удаляет документ
SELECT business_functions.delCFile( (SELECT json_build_object( 'CId', 3 )) );
После удаления, клиент документа не видит
SELECT business_functions.getCFile"( (SELECT json_build_object( 'CId', 3 )) ) ;-----------------(0 rows)
Но в БД документ не удален, только изменен атрибут is_del
psql -d my_dbSELECT id, name , is_deleted FROM repos.file ;id | name | is_deleted--+---------+------------ 1 | test_1 | t(1 row)
Что и требовалось в постановке задачи.
