Этюд по реализации 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

Включаем 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 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.
Источник: https://habr.com/ru/post/515896/

Интересные статьи

Интересные статьи

Сегодня я хочу представить читателям Хабра утилиту, написанную на Python, для работы с зависимостями таблиц в СУБД PostgreSQL. API утилиты простое и состоит из трех методов: ...
В сложных ERP-системах многие сущности имеют иерархическую природу, когда однородные объекты выстраиваются в дерево отношений «предок — потомок» — это и организационная структура пред...
Дисклеймер Я — разработчик. Я пишу код, с базой данных взаимодействую лишь как пользователь. Я ни в коем случае не претендую на должность системного администратора и, тем более, dba. Но… Та...
Некоторое время назад мне довелось пройти больше десятка собеседований на позицию php-программиста (битрикс). К удивлению, требования в различных организациях отличаются совсем незначительно и...
С версии 12.0 в Bitrix Framework доступно создание резервных копий в автоматическом режиме. Задание параметров автоматического резервного копирования производится в Административной части на странице ...