Использование On-Logon триггеров в СУБД Postgres Pro Enterprise
Введение
Триггеры On-Logon хорошо знакомы разработчикам приложений для СУБД Oracle Database.
Они являются одним из видов триггеров событий базы данных, и автоматически срабатывают при подключении пользователя к БД.
Фактически, On-Logon триггер является блоком кода на языке программирования Oracle PL/SQL, который срабатывает на событие On-Logon (подключение пользователя к БД). Следует отметить, что при возникновении ошибки в On-Logon триггере, подключение пользователя к БД запрещается.
On‑Logon триггер удобно использовать для формирования контекста сессии, либо для проведения нестандартных автоматических проверок пользователя перед началом сессии.
В Данной статье я расскажу о функциональности PostgreSQL, аналогичной тому, что предоставляет Oracle On Logon Trigger.
Первоначально, On-Logon триггеры появились в Postgres Pro Enterprise версии 14. Компания Postgres Pro передала свою реализацию этой технологии сообществу PostgreSQL и скоро они войдут и в open source версию СУБД PostgreSQL, доступную всем — 17-ый релиз, который будет выпущен в 2024 году.
В силу различия принятой в Oracle и PostgreSQL терминологии для события входа в систему, в PostgreSQL этот триггер называется On‑Login триггер.
Данный пример ярко характеризует модель развития СУБД PostgreSQL.
Идеи и их реализации, апробированные компаниями в коммерческих форках, передаются в open source. С другой стороны, компании точно также получают наработки open source в свой коммерческий форк. Это формирует устойчивую ситуацию взаимовыгодного сотрудничества коммерческих компаний и open source сообщества. Существующая практика уже доказала, что обычно эта схема более надёжная и долговечная, чем отдельные коммерческие компании без open source сообщества, или открытые продукты без коммерческой поддержки и развития.
On-Logon триггеры в СУБД Oracle
Рассмотрим следующий пример On-Logon триггера в СУБД Oracle Database.
Этот пример будет решать следующую задачу: для данного пользователя SCOTT разрешать подключение пользователя к БД только в рабочие часы (с 9:00 до 18:00), а также запрещать использование для подключения БД любых исполняемых файлов приложения кроме «hr.exe».
On-Logon триггер, решающий вышеописанную задачу в СУБД Oracle Database, будет иметь следующий вид:
CREATE OR REPLACE TRIGGER hr_logon_trigger
AFTER LOGON ON DATABASE
DECLARE
v_cProgram constant varchar2(128 char) := 'hr.exe';
v_xProgram varchar2(128 char);
v_xHour int := (EXTRACT(hour FROM systimestamp));
BEGIN
IF user = 'SCOTT' THEN
-- Проверяем что выполняемый файл hr.exe
v_xProgram := sys_context('USERENV','CLIENT_PROGRAM_NAME');
IF v_xProgram != v_cProgram THEN
raise_application_error(-20000, 'Access denied from executable "' || v_xProgram || '"');
END IF;
-- Запретить вход в нерабочее время
IF v_xHour not between 9 and 18 THEN
raise_application_error(-20000, 'Access denied in NOT working hours');
END IF;
END IF;
END;
/
Trigger HR_LOGON_TRIGGER compiled
Проверяем наш триггер в СУБД Oracle, с помощью открытия соединения в утилите Oracle SQL*Plus:
C:\oracle\product\19.0.0\client_1\bin>sqlplus.exe scott/tiger@mydemosrv/orcl
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Dec 01 14:25:54 2023
Version 19.21.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
ERROR:
ORA-04088: error during execution of trigger 'SYS.HR_LOGON_TRIGGER'
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: Access denied from executable "sqlplus.exe"
ORA-06512: at line 10Все работает штатно: подключение к БД с помощью утилиты sqlplus запрещено.
Для целей тестирования скопируем файл sqlplus.exe в hr.exe:
C:\oracle\product\19.0.0\client_1\bin>copy sqlplus.exe hr.exe
1 file(s) copied. Конечно, на реальном рабочем месте бизнес-пользователя, у него отсутствуют права на переименование файлов на его рабочем ПК.
Снова пытаемся открыть соединения с помощью нашей «новой» утилиты hr.exe:
C:\oracle\product\19.0.0\client_1\bin>hr.exe scott/tiger@mydemosrv/orcl
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Dec 10 20:38:17 2023 Version 19.21.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Last Successful login time: Sun Dec 01 2023 14:28:42 +03:00
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.21.0.0.0
SQL> select to_char(sysdate,'hh24:mi') from dual;
TO_CH
12:38Итак, наш On-Logon триггер в СУБД Oracle, автоматически срабатывает в момент подключения к БД и осуществляет все определенные в нем действия.
On-Login триггеры в СУБД Postgres Pro Enterprise
On-Login триггер в СУБД Postgres Pro Enterprise, так же как и в Oracle Database, является одним из видов триггеров событий базы данных (https://www.postgrespro.ru/docs/enterprise/14/event-trigger-database-login-example).
Данная функциональность впервые была добавлена еще в Postgres Pro Enterprise 14, и, конечно, доступна в последующих релизах этой СУБД.
Определим наш On-Login триггер решающий туже самую задачу: для данного пользователя разрешать подключение пользователя к БД только в рабочие часы с 9:00 до 18:00, а также запрещать использование для работы с БД любых исполняемых файлов кроме «hr.exe», но уже в среде СУБД Postgres Pro Enterprise.
On-Login триггер, решающий вышеописанную задачу в СУБД Postgres Pro Enterprise, будет иметь следующий вид:
--Создаем функцию выполняемую в триггере:
CREATE OR REPLACE FUNCTION check_session() RETURNS event_trigger
SECURITY DEFINER
LANGUAGE plpgsql AS
$$
DECLARE
v_cProgram constant text := 'hr.exe';
v_xProgram text;
v_xHour int := EXTRACT('hour' FROM current_time);
BEGIN
IF upper(quote_ident(session_user)) != 'SCOTT' THEN
return;
END IF;
-- 1. Проверяем, что выполняемый файл hr.exe
SELECT
application_name
INTO
v_xProgram
FROM
pg_stat_activity
WHERE
pid = pg_backend_pid();
IF v_xProgram != v_cProgram THEN
RAISE EXCEPTION 'Access denied from executable "%"', v_xProgram;
END IF;
EXECUTE 'SET LOCAL TIME ZONE ''Europe/Moscow'';';
-- 2. Запретить вход в нерабочее время
IF v_xHour NOT BETWEEN 9 AND 18 THEN
RAISE EXCEPTION 'Access denied in NOT working hours';
END IF;
END;
$$
;
CREATE FUNCTION
-- Наконец, создаем сам On-Logon триггер:
CREATE EVENT TRIGGER check_session
ON login
EXECUTE FUNCTION check_session();
CREATE EVENT TRIGGERПроверяем наш триггер в среде Postgres Pro Enterprise, с помощью открытия соединения в утилите psql:
postgres@demosrv:~$ psql -U scott -d demodb
psql: ошибка: подключиться к серверу через сокет "/tmp/.s.PGSQL.5432" не удалось: ВАЖНО: Access denied from executable "psql"
КОНТЕКСТ: функция PL/pgSQL check_session(), строка 22, оператор RAISE
postgres@demosrv:~$
Все работает штатно: подключение к БД с помощью утилиты psql запрещено.
Для целей тестирования скопируем файл файл psql в hr.exe:
postgres@demosrv:/opt/pgpro/ent-15/bin$ cp ./psql hr.exeИ снова попытаемся открыть соединение с помощью «утилиты» hr.exe в нерабочее время:
postgres@demosrv:/opt/pgpro/ent-15/bin$ date
Ср дек 13 19:36:25 MSK 2023
postgres@demosrv:/opt/pgpro/ent-15/bin$ ./hr.exe -U scott -d demodb
hr.exe: ошибка: подключиться к серверу через сокет "/tmp/.s.PGSQL.5432" не удалось: ВАЖНО: Access denied in NOT working hours
КОНТЕКСТ: функция PL/pgSQL check_session(), строка 33, оператор RAISEНаконец, подключаемся с помощью «утилиты» hr.rxe в рабочее время:
postgres@demosrv:/opt/pgpro/ent-15/bin$ date
Пн дек 18 13:46:23 MSK 2023
postgres@demosrv:/opt/pgpro/ent-15/bin$ ./hr.exe -U scott -d demodb
hr.exe (15.5) Введите "help", чтобы получить справку.
demodb=>On-Login триггер успешно сработал в СУБД Postgres Pro и все проверки успешно были выполнены!
Заключение
Поддержка в Postgres Pro Enterrise On-Logon триггеров, то есть триггеров события входа пользователя в систему, является важной функциональной возможностью СУБД Postgres Pro Enterprise.
Она востребована как при миграции с СУБД Oracle Database и MS SQL Server, так и для приложений, которые разрабатываются для СУБД Postgres Pro Enterprise «с нуля».
Снова хотелось бы напомнить, что поддержка On-Logon триггеров будет добавлена в следующий мажорный релиз open source СУБД PostgreSQL — в версию 17.
Игорь Мельников
Postgres Pro
