Етюд з реалізації Row Level Secutity у PostgreSQL

Як доповнення до Етюд з реалізації бізнес-логіки на рівні збережених функцій PostgreSQL и в основному для розгорнутої відповіді на коментар.

Теоретична частина відмінно описана у документації 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

включаємо Безпека рівня рядка

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.file
SET 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_db
SELECT  id, name , is_del FROM repos.file ;
id |  name  | is_del
--+---------+------------
 1 |  test_1 | t
(1 row)

Що й вимагалося у постановці завдання.

Підсумок

Якщо тема буде цікавою, у наступному етюді можна показати приклад реалізації рольової моделі поділу доступу до даних із використанням Row Level Security.

Джерело: habr.com