Есенно изложение на реализацията на Row Level Security в 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

Включваме 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.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 реда)

Но в БД документът не е изтрит, само атрибутът е променен is_del

psql -d my_db
SELECT  id, name , is_del FROM repos.file ;
id |  name  | is_del
--+---------+------------
 1 |  test_1 | t
(1 ред)

Какво искахме в поставената задача.

Резюме

Ако темата е интересна, в следващия етюд можем да покажем пример за реализация на ролевата модел на разделяне на достъпа до данни с използване на Row Level Security.

Източник: habr.com