Проучване за внедряване на защита на ниво ред в PostgreSQL

Като допълнение към Проучване за внедряване на бизнес логика на ниво съхранени функции на PostgreSQL и главно за подробен отговор на коментар.

Теоретичната част е добре описана в документацията PostgreSQL - Политики за защита на редове. По-долу е практическо изпълнение на малък специфична бизнес задача - скриване на изтрити данни. Скица, посветена на изпълнението Моделиране на роли с помощта на RLS представени отделно.

В статията няма нищо ново, няма скрит смисъл или тайно знание. Просто скица за практическото прилагане на една теоретична идея. Ако на някой му е интересно да прочете. Ако не се интересувате, не си губете времето.

Проблем изявление

Без да навлизаме дълбоко в предметната област, накратко проблемът може да се формулира по следния начин: Има таблица, която реализира определен бизнес обект. Редове в таблицата могат да бъдат изтрити, но редовете не могат да бъдат физически изтрити; те трябва да бъдат скрити.

Защото се казва: „Не изтривайте нищо, просто го преименувайте. Интернет съхранява ВСИЧКО"

По пътя е препоръчително да не пренаписвате съществуващи съхранени функции, които работят с този обект.

За да реализира тази концепция, таблицата има атрибута е_изтрито. Тогава всичко е просто - трябва да сте сигурни, че клиентът може да види само редовете, в които атрибутът е_изтрито невярно За какво се използва механизмът? Сигурност на ниво ред.

Изпълнение

Създайте отделна роля и схема

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)

Което се изискваше в изложението на проблема.

Общо

Ако темата е интересна, в следващото проучване можете да покажете пример за внедряване на ролеви модел за разделяне на достъпа до данни чрез защита на ниво ред.

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