Использованная стратегия подразумевает использование концепции «Бизнес-логика в БД», что было чуть подробнее описано здесь — Этюд по реализация бизнес-логики на уровне хранимых функций PostgreSQL
Теоретическая часть отлично описана в документации Postgres Pro — Политики защиты строк. Ниже рассмотрена практическая реализация конкретной бизнес задачи — ролевая модель доступа к данным.
В статье ничего нового, нет скрытого смысла и тайных знаний. Просто зарисовка о практической реализации теоретической идеи. Если кому интересно — читайте. Кому не интересно — не тратьте свое время зря.
Постановка задачи
Необходимо разграничить доступ на просмотр/вставку/изменение/удаление документа в соответствии с ролью пользователя приложения. Под ролью подразумевается запись в таблице 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 строки
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 строки
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 строки
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 строки.
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;
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 для таблицы.
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 )) );
Итог
Это работает.
Предложенная стратегия позволила перенести реализацию ролевой модели с уровня бизнес-функций на уровень хранения данных.
Функции могут быть использованы в качестве шаблона для реализации более изощренных моделей скрытия данных, если того требуют бизнес-требования.
ссылка на оригинал статьи https://habr.com/ru/post/516040/
Добавить комментарий