[PostgreSQL] Этюд по реализации Row Level Secutity в PostgreSQL

Ответить на тему

Автор

Сообщение

news_bot ^®

Стаж: 6 лет 9 месяцев
Сообщений: 27286

news_bot ^® написал(а)
20-Авг-2020 14:37

Цитировать

В качестве дополнения к Этюд по реализация бизнес-логики на уровне хранимых функций 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_deleted FROM repos.file ;
id | name | is_deleted
--+---------+------------
1 | test_1 | t
(1 row)

Что и требовалось в постановке задачи.
ИтогЕсли тема будет интересна, в следующем этюде можно показать пример реализации ролевой модели разделения доступа к данным с использованием Row Level Security.
===========
Источник:
habr.com
===========
Похожие новости:

Теги для поиска: #_postgresql, #_postgresql, #_administrirovanie_baz_dannyh (администрирование баз данных), #_postgresql

Профиль ЛС

Ответить на тему

Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы

Текущее время: 22-Ноя 22:24
Часовой пояс: UTC + 5