НОВОСТИ Реализация ролевой модели доступа с использованием Row Level Security в PostgreSQL

[NewsBot]

Развитие темы

You must be registered for see links

и для развернутого ответа на

You must be registered for see links

Использованная стратегия подразумевает использование концепции «Бизнес-логика в БД», что было чуть подробнее описано здесь —

You must be registered for see links

Теоретическая часть отлично описана в документации

You must be registered for see links

—

You must be registered for see links

. Ниже рассмотрена практическая реализация конкретной бизнес задачи — ролевая модель доступа к данным.

В статье ничего нового, нет скрытого смысла и тайных знаний. Просто зарисовка о практической реализации теоретической идеи. Если кому интересно — читайте. Кому не интересно — не тратьте свое время зря.​

Постановка задачи

Необходимо разграничить доступ на просмотр/вставку/изменение/удаление документа в соответствии с ролью пользователя приложения. Под ролью подразумевается запись в таблице roles связанной отношением многие-ко-многим с таблицей users. Детали реализации таблиц, по причине тривиальности, опущены. Также опущены конкретные детали реализации связанные с предметной областью.

Реализация


Создаем роли, схемы, таблицу

Создание объектов БД

CREATE ROLE store;
CREATE SCHEMA store AUTHORIZATION store;
CREATE TABLE store.docs
(
id integer , --id документа
man_id integer , --id менеджера документа
stat_id integer , --id статуса документа
...
is_del BOOLEAN DEFAULT FALSE
);
ALTER TABLE store.docs ADD CONSTRAINT doc_pk PRIMARY KEY (id);
ALTER TABLE store.docs OWNER TO store ;

Создаем функции для реализации RLS

Проверка возможности выполнить SELECT строки
check_select

CREATE OR REPLACE FUNCTION store.check_select ( current_id store.docs.id%TYPE ) RETURNS boolean AS $$
DECLARE
result boolean ;
curr_pid integer ;
curr_stat_id integer ;
doc_man_id integer ;
BEGIN
-- DBA имеет доступ ко всем документам
IF SESSION_USER = 'curr_dba'
THEN
RETURN TRUE ;
END IF ;
--------------------------------

--Если документ имеет метку 'удален' - не показывать в выборке
SELECT
is_del
INTO
result
FROM
store.docs
WHERE
id = current_id ;
IF result = TRUE
THEN
RETURN FALSE ;
END IF ;
--------------------------------

--Получить id текущего пользователя
SELECT
service_function.get_curr_pid ()
INTO
curr_pid ;
--------------------------------

--Получить id менеджера документа
SELECT
man_id
INTO
doc_man_id
FROM
store.docs
WHERE
id = current_id ;
--------------------------------

--Если менеджер документа не текущий пользователь или менеджер не назначен
--добавить документ в выборку
IF doc_man_id != curr_pid OR doc_man_id IS NULL
THEN
RETURN TRUE ;
ELSE
--Получить текущий статус документа
SELECT
stat_id
INTO
curr_statid
FROM
store.docs
WHERE
id = current_id ;

--Если статус позволяет просмотреть документ - добавить документ в выборку
IF curr_statid = 4 OR curr_statid = 9
THEN
RETURN TRUE ;
ELSE
--Иначе - исключить документ из выборки
RETURN FALSE ;
END IF ;
END IF ;
--------------------------------

RETURN FALSE ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
ALTER FUNCTION store.check_select( store.docs.id%TYPE ) OWNER TO store ;
REVOKE EXECUTE ON FUNCTION store.check_select( store.docs.id%TYPE ) FROM public;
GRANT EXECUTE ON FUNCTION store.check_select( store.docs.id%TYPE ) TO service_functions;

Проверка возможности выполнить INSERT строки
check_insert

CREATE OR REPLACE FUNCTION store.check_insert ( current_id store.docs.id%TYPE ) RETURNS boolean AS $$
DECLARE
curr_role_id integer ;
BEGIN
--DBA может добавлять строку в любом случае
IF SESSION_USER = 'curr_dba'
THEN
RETURN TRUE ;
END IF ;
--------------------------------

--Получить id роли текущего пользователя
SELECT
service_functions.current_rid()
INTO
curr_role_id ;
--------------------------------

--Если роль допускает возможность создания нового документа
--разрешить
IF curr_role_id = 3 OR curr_role_id = 5
THEN
RETURN TRUE ;
END IF ;
--------------------------------
RETURN FALSE ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
ALTER FUNCTION store.check_insert( store.docs.id%TYPE ) OWNER TO store ;
REVOKE EXECUTE ON FUNCTION store.check_insert( store.docs.id%TYPE ) FROM public;
GRANT EXECUTE ON FUNCTION store.check_insert( store.docs.id%TYPE ) TO service_functions;

Проверка возможности выполнить DELETE строки
check_delete

CREATE OR REPLACE FUNCTION store.check_delete ( current_id store.docs.id%TYPE )
RETURNS boolean AS $$
BEGIN
--Только DBA может удалять строку
IF SESSION_USER = 'curr_dba'
THEN
RETURN TRUE ;
END IF ;
--------------------------------

RETURN FALSE ;
END
$$ LANGUAGE plpgsql
SECURITY DEFINER;
ALTER FUNCTION store.check_delete( store.docs.id%TYPE ) OWNER TO store ;
REVOKE EXECUTE ON FUNCTION store.check_delete( store.docs.id%TYPE ) FROM public;

Проверка возможности выполнить UPDATE строки.
update_using

CREATE OR REPLACE FUNCTION store.update_using ( current_id store.docs.id%TYPE , is_del boolean )
RETURNS boolean AS $$
BEGIN
--Документы имеющие статус 'удален' - не редактируются
IF is_del
THEN
RETURN FALSE ;
ELSE
RETURN TRUE ;
END IF ;

END
$$ LANGUAGE plpgsql SECURITY DEFINER;
ALTER FUNCTION store.update_using( store.docs.id%TYPE , boolean ) OWNER TO store ;
REVOKE EXECUTE ON FUNCTION store.update_using( store.docs.id%TYPE , boolean ) FROM public;
GRANT EXECUTE ON FUNCTION store.update_using( store.docs.id%TYPE ) TO service_functions;
update_check

CREATE OR REPLACE FUNCTION store.update_with_check ( current_id store.docs.id%TYPE , is_del boolean )
RETURNS boolean AS $$
DECLARE
current_rid integer ;
current_statid integer ;
BEGIN

--DBA может просматривать строку
IF SESSION_USER = 'curr_dba'
THEN
RETURN TRUE ;
END IF ;
--------------------------------

--Получить id роли текущего пользователя
SELECT
service_functions.current_rid()
INTO
curr_role_id ;
--------------------------------

--Удаление документа - изменение признака
IF is_deleted
THEN
--Если роль пользователя ***
IF current_role_id = 3
THEN
SELECT
stat_id
INTO
curr_statid
FROM
store.docs
WHERE
id = current_id ;

--Документ в статусе *** нельзя удалить
IF current_status_id = 11
THEN
RETURN FALSE ;
ELSE
--Можно удалить документ в других статусах
RETURN TRUE ;
END IF ;

--Иначе , если роль пользователя ***
ELSIF current_role_id = 5
THEN
--Все статусы документа
RETURN TRUE ;
ELSE
--Другие пользователи не могут удалять документы
RETURN FALSE ;
END IF ;
ELSE
--Обновление документа разрешено
RETURN TRUE ;
END IF ;

RETURN FALSE ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
ALTER FUNCTION store.update_with_check( storg.docs.id%TYPE , boolean ) OWNER TO store ;
REVOKE EXECUTE ON FUNCTION store.update_with_check( storg.docs.id%TYPE , boolean ) FROM public;
GRANT EXECUTE ON FUNCTION store.update_with_check( store.docs.id%TYPE ) TO service_functions;

Включение политики Row Level Secutiry для таблицы.
ENABLE ROW LEVEL SECURITY

ALTER TABLE store.docs ENABLE ROW LEVEL SECURITY ;

CREATE POLICY doc_select ON store.docs FOR SELECT TO service_functions USING ( (SELECT store.check_select(id)) );
CREATE POLICY doc_insert ON store.docs FOR INSERT TO service_functions WITH CHECK ( (SELECT store.check_insert(id)) );
CREATE POLICY docs_delete ON store.docs FOR DELETE TO service_functions USING ( (SELECT store.check_delete(id)) );

CREATE POLICY doc_update_using ON store.docs FOR UPDATE TO service_functions USING ( (SELECT store.update_using(id , is_del )) );
CREATE POLICY doc_update_check ON store.docs FOR UPDATE TO service_functions WITH CHECK ( (SELECT store.update_with_check(id , is_del )) );


Итог

Это работает.
Предложенная стратегия позволила перенести реализацию ролевой модели с уровня бизнес-функций на уровень хранения данных.
Функции могут быть использованы в качестве шаблона для реализации более изощренных моделей скрытия данных, если того требуют бизнес-требования.