IgorM23 27 дек 2023 в 12:09

Использование On-Logon триггеров в СУБД Postgres Pro Enterprise

Средний

5 мин

1.9K

Блог компании Postgres ProfessionalPostgreSQL*Администрирование баз данных*

Введение

Триггеры 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 Enterprise On-Logon триггеров, то есть триггеров события входа пользователя в систему, является важной функциональной возможностью этой СУБД.
Она востребована как при миграции с СУБД Oracle Database и MS SQL Server, так и для приложений, которые разрабатываются для СУБД Postgres Pro Enterprise "с нуля".

Снова хотелось бы напомнить, что поддержка On-Logon триггеров будет добавлена в следующий мажорный релиз open source СУБД PostgreSQL - в версию 17.

Игорь Мельников
Postgres Pro

Теги:

Хабы:

Использование On-Logon триггеров в СУБД Postgres Pro Enterprise

Введение

On-Logon триггеры в СУБД Oracle

On-Login триггеры в СУБД Postgres Pro Enterprise

Заключение

Публикации

Информация