Продолжаем серию «Адаптивное администрирование Sigla Vision». Разберем кейсы, где триггеры в базе FineDB помогают решать задачи администрирования Sigla Vision.
Привет, Хабр! Меня зовут Всеволод Коваленко. В Газпромбанке я занимаюсь развитием функционала BI-системы на базе Sigla Vision.
В предыдущей статье «Версионирование таблиц репозитория метаданных Sigla Vision» мы разобрали исторические таблицы, которые хранят данные о состояниях записей в БД. Версионирование таблиц мы тоже строили на триггерах FineDB.
Теперь покажем, как те же триггеры решают еще ряд задач администрирования Sigla Vision.
Как обычно, отметим, что подход не привязан только к Sigla Vision — он работает в любой аналитической системе, где метаданные хранятся во внешней СУБД с поддержкой триггеров.
Оглавление для навигации между материалами цикла
Как UI системы работает с БД репозитория
Пользовательский интерфейс Sigla Vision позволяет создавать метаданные системы и ограниченно ими управлять. Объекты метаданных лежат в таблицах репозитория, и каждое действие в интерфейсе запускает одну или несколько команд в репозитории. Например, удаление роли в интерфейсе Sigla Vision удалит строки сразу в двух таблицах репозитория — в таблице ролей и в таблице связей пользователь-роль, если эта роль кому-то назначалась.
Триггеры, о которых пойдет речь, позволяют дополнять или прерывать эти команды — те, что система выполняет в репозитории метаданных в ответ на действия в интерфейсе портала Sigla Vision. На схеме ниже — примерное расположение триггеров и их функций в составе нашей системы.
В нашей BI-системе есть ролевая модель, и иногда отдельным ролям нужно добавить ограничения, которых в стандартном интерфейсе (UI) Sigla Vision нет. Не все это удается задать дефолтными настройками поведения системы. Иногда нам требуется автоматически менять атрибуты в строках таблиц, которые отвечают за состояние объектов системы.
По умолчанию система создает объекты с определенными значениями атрибутов и в зависимости от них по-разному ведет себя с объектами в UI. Об изменении атрибутов у части объектов система узнает сразу, у остальных — считывает атрибуты только при перезапуске. Вручную поменяв такой атрибут, администратор может задать нужное поведение системы при работе с объектом. Разумеется, такие изменения нужно логировать и заранее понимать, как система на них отреагирует.
Если FineDB хранится во внешней СУБД с поддержкой триггеров, через них можно менять или ограничивать атрибуты объектов, а также добавлять свою логику обработки DML-команд (insert, update, delete) — дополнять или останавливать их.
Дальше на примерах покажем, как задавать такие ограничения триггерами в СУБД PostgreSQL.
Кейсы
Создание роли в системе
По умолчанию, когда новую роль создают через веб-портал Sigla Vision, система заводит объект в соответствующей таблице и помечает его как «созданный вручную» атрибутом creationtype=1. Если есть доступ к работе с ролями и пользователями, такие роли можно редактировать через интерфейс — добавлять или убирать сотрудников.
Допустим, модератору-администратору каталога нужно видеть сотрудников с простой ролью на просмотр отчетов того же каталога и работать с ними, но саму роль он редактировать не должен. Тогда роль на просмотр отчетов можно превратить из «созданной вручную» в «синхронизируемую» — сменив атрибут на creationtype=2. «Синхронизируемые» роли вручную не создают: их регулярно загружает штатный процесс синхронизации пользователей Sigla Vision через файл или SQL-запрос. Редактировать такие роли и менять их состав из интерфейса уже нельзя — это и защищает их от вмешательства пользователей.
Первая версия нашего решения сводилась к ручной смене атрибута creationtype=2 прямо в БД. И тут же вылез критический недостаток реализации: роли, вручную превращенные в «синхронизируемые», но не попавшие в файл/SQL-запрос синхронизации, удаляются этой самой синхронизацией вместе с записями о том, что роль есть у сотрудников.
Первой реакцией стало создание триггера в БД, запрещающего удалять роли и записи об их назначении сотрудникам. Роли в системе создаются по строгим правилам наименования, поэтому триггер несложно было настроить по шаблону их имен.
Решение оказалось временным: такой триггер заодно не давал и отбирать роли у сотрудников. Во вторую версию мы включили все подобные роли в файл/SQL-запрос синхронизации. Это оказалось нетривиальным решением — из-за особенностей реализации наших АС и необходимости собирать запрос синхронизации из разных БД и даже разных сред.
Запрет удаления роли
CREATE OR REPLACE FUNCTION public.f_custom_role_trigger () RETURNS trigger LANGUAGE plpgsql AS $function$ BEGIN if left(OLD.name,2) = ‘<шаблонная часть наименования роли на просмотр>’ or left(OLD.name,2) = ‘<шаблонная часть наименования роли на разработку>’ or left(OLD.name,2) = ‘<шаблонная часть наименования роли на модерирование>’ then return null; else return old; end if; END; $function$ ; // Установка триггера create or replace trigger t_custom_role_trigger before delete on public.fine_custom_role_test_trigger_bd for each row execute function public.f_custom_role_trigger ();
Запрет удаления связи роль-пользователь
CREATE OR REPLACE FUNCTION public.f_user_to_role () RETURNS trigger LANGUAGE plpgsql AS $function$ Declare role_name_to_preserve varchar(255); BEGIN select name into role_name_to_preserve from public.fine_custom_role where id = OLD.roleid; if left(role_name_to_preserve,2) = ‘<шаблонная часть наименования роли на просмотр>’ or left(role_name_to_preserve,2) = ‘<шаблонная часть наименования роли на разработку>’ or left(role_name_to_preserve,2) = ‘<шаблонная часть наименования роли на модерирование>’ then return null; else return old; end if; END; $function$ ; // Установка триггера create or replace trigger t_user_to_role before delete on public.fine_user_role_middle for each row execute function public.f_user_to_role ();
Запрет создания ролей с некорректным наименованием
На принципах наименования ролей в нашем решении держится много процессов — заявки на доступ к системе и интеграция с другими системами, формирование ролевой модели, заявки на создание каталога в АС, методы выдачи полномочий на объекты каталога внутри ролей и др. Один из принципов — и для основных ролей, и для вспомогательных — имя роли содержит полное название каталога. Создаем каталог на корневом уровне (дальше — «основной») — и вторым шагом система автоматически заводит «основные» роли на доступ к нему. Вспомогательные роли — на доступ к подкаталогам или отдельным отчетам / наборам данных — создаются вручную в интерфейсе.
В какой-то момент мы разрешили пользователям — модераторам каталогов самим создавать вспомогательные роли в системе. А вместе с этим понадобилось контролировать, как эти роли называют, чтобы они следовали принципам наименования вспомогательных ролей. Поэтому мы сделали триггер, который не дает создавать вспомогательные роли по шаблону имени основных.
При создании роли триггер смотрит, есть ли в ее имени название основного каталога: если имя построено по шаблону основной роли, а одноименного каталога в системе нет, — создание прерывается. Так пользователь не сможет создать вспомогательную роль, чей префикс совпадает с шаблоном основной.
Запрет создания основной роли вручную
CREATE OR REPLACE FUNCTION public.f_subrole_restrict_trigger () RETURNS trigger LANGUAGE plpgsql AS $function$ BEGIN if NOT exists (select from public.fine_authority_object where displayname = substring(new.name from 3)) and ( left(NEW.name,2) = ‘<шаблонная часть наименования роли на просмотр>’ or left(NEW.name,2) = ‘<шаблонная часть наименования роли на разработку>’ or left(NEW.name,2) = ‘<шаблонная часть наименования роли на модерирование>’ ) then return null; else return new; end if; END; $function$ ; // Установка триггера create or replace trigger t_subrole_restrict_trigger before insert on public.fine_custom_role for each row execute function public.f_subrole_restrict_trigger();
Открытие совместного управления вспомогательной ролью
Конфигурация здесь такая. Есть один основной каталог и основная роль на ведение его объектов — роль модератора. Эту роль держат сразу несколько модераторов, поэтому полномочия по ведению каталога у них одинаковые: каждый может открывать и закрывать доступ к объектам, находящимся внутри. Именно это равенство прав и делает кейс примечательным — на нем строится решение.
В нашем подходе такие модераторы могут создавать вспомогательные роли в АС, чтобы через них задавать дополнительные ограничения или разрешения конкретным сотрудникам или их подгруппам. Если один модератор создал такую роль, остальные по умолчанию ее не видят и не могут ей управлять, — а это проблема, когда первый модератор внезапно недоступен.
Поэтому мы сделали триггер в БД: он срабатывает на создание новой вспомогательной роли (ее можно определить по правилам наименования, а неправильно названную роль уже не даст создать триггер из второго кейса) и автоматически добавляет строку в таблицу, которая отвечает за полномочия внутри основной роли каталога. Все модераторы держат основную роль каталога (роль модератора), поэтому достаточно дать основной роли право видеть новую вспомогательную роль и управлять ею. Тогда любой модератор сможет управлять вспомогательными ролями других модераторов с той же основной ролью.
Видимость созданной роли внутри основной роли
CREATE OR REPLACE FUNCTION public.f_subrole_visibility_trigger () RETURNS trigger LANGUAGE plpgsql AS $function$ declare moder_roleid varchar(255); BEGIN moder_roleid = ''; if left(NEW.name,2) != ‘<шаблонная часть наименования роли на просмотр>’0 and left(NEW.name,2) != ‘<шаблонная часть наименования роли на разработку>’ and left(NEW.name,2) != ‘<шаблонная часть наименования роли на модерирование>’ and exists (select from public.fine_custom_role where name = ‘<шаблонная часть наименования роли на модерирование>’||left(new.name,LENGTH(new.name) - POSITION('_' IN REVERSE(new.name)) ) then select id into moder_roleid from public.fine_custom_role where name = ‘<шаблонная часть наименования роли на модерирование>’||left(new.name,LENGTH(new.name) - POSITION('_' IN REVERSE(new.name)) ); insert into public.fine_authority (id,tenantid,authority,authorityentityid,authorityentitytype,authoritytype,roleid,roletype) VALUES ('Artifici-alID-ForA-cces-'||right(cast(now() as varchar),12),'default',2,new.id,1,9,moder_roleid,2), ('Artifici-alID-ForA-utho-'||right(cast(now() as varchar),12),'default',2,new.id,1,2,moder_roleid,2); return new; else return new; end if; END; $function$ ; // Установка триггера create or replace trigger t_subrole_visibility_trigger after insert on public.fine_custom_role for each row execute function public.f_subrole_visibility_trigger ();
Как накладывать ограничения на уровне БД
Как мы показали во втором кейсе, часть ограничений на действия пользователей можно задавать на уровне БД через триггеры. Кроме триггера, запрещающего создавать некорректно названные роли в АС, мы сделали еще один — он не дает пользователям с правами, позволяющими вести каталоги источников данных удалять такие каталоги. Разработчикам мы выдали права на ведение объектов в каталогах источников — а вместе с ними появилась и техническая возможность эти каталоги удалять. Запретить это из интерфейса было нельзя, поэтому и появился триггер — защита от случайных действий сотрудников. Триггер ловит попытки удалить объекты из таблицы с существующими каталогами и останавливает удаление основных каталогов.
Запрет на удаление каталога
CREATE OR REPLACE FUNCTION public.f_restrict_delete_folder () RETURNS trigger LANGUAGE plpgsql AS $function$ declare res integer default 0; BEGIN --каталоги 1 уровня источников execute 'select count(distinct entity_key) as cnt from public.finebi_package_en where namespace = ''PackageCreate'' and entity_key = '''||old.entity_key||''' and entity_key in ( select distinct j.entity_key from ( select entity_key, entity_value::jsonb as content from public.finebi_package_en p where p.value_class = ''PackageParentPO'' ) j where replace((j.content -> ''pid'')::text,''"'','''') = '''' );' into res; if res = 1 then return null; else RETURN OLD; end if; END; // Установка триггера create or replace trigger t_ restrict_delete_folder before delete on public.finebi_package_en for each row execute function public.f_restrict_delete_folder ();
Отслеживание работы триггеров
Действия триггеров, которые создают новые строки, тоже нужно уметь проверять и отслеживать. Здесь пригодятся версионирование таблиц и исторические таблицы zst_ из предыдущей статьи. Кроме того, можно настроить дополнительное логирование событий триггеров.