Before going into the details, thanks to the author of ‘login hook’ extension for developing and maintaining it.
Many times, in Oracle to Postgres migration exercise, I have seen the use of Oracle Database Event trigger – AFTER LOGON ON. Its one type of Oracle database/user event trigger(LOGON) that fires when a user connects to a database, typically its used to set the user environment and perform functions associated with secure application roles.
Let’s say, for example, that we have an application user where we wanted him to connect from the application ONLY and not from any other programs or clients(Oracle/SQL*Plus). This can be achieved by creating a database event trigger AFTER LOGON ON in Oracle.
Postgres supports most of the standard triggers, but there is no AFTER LOGON trigger. To workaround, I selected
login_hook extension that did the job pretty well.
Let’s see what we’re going to convert from Oracle to Postgres with the help of extension. There is a trigger in Oracle that prevents the application users from connecting to the database from other programs/clients(sqlplus).
CREATE OR REPLACE TRIGGER program_restrict AFTER LOGON ON DATABASE BEGIN FOR x IN (SELECT username, program FROM SYS.v_$session WHERE audsid = USERENV ('sessionid')) LOOP IF LTRIM (RTRIM (x.username)) = 'MIGUSER' AND UPPER(substr(x.program,1,7)) = 'SQLPLUS' THEN raise_application_error(-20999,'Not authorized to use in the Production environment!'); END IF; END LOOP; END program_restrict;
From the above code, it is clear that
MIGUSER(application user) is restricted to connect via
SQL*PLUS client and any attempt the user make will result in the following error:
[[email protected] ~]$ rlsqlplus miguser/miguser ... <trimmed banner> ERROR: ORA-04088: error during execution of trigger 'SYS.PROGRAM_RESTRICT' ORA-00604: error occurred at recursive SQL level 1 ORA-20999: Not authorized to use in the Production environment! ORA-06512: at line 6 ORA-06512: at line 6
To workaround the above requirement, we first need to compile the extension
login_hook in Postgres. Steps are very simple like any other extension compilation
--Download zip/Git clone the extension https://github.com/splendiddata/login_hook -- Set the pg_config in your path [[email protected] ~]# export PATH=/usr/pgsql-13/bin:$PATH -- change to login_hook directory and run make/make install [[email protected] ~]# cd login_hook [[email protected] login_hook]# make [[email protected] login_hook]# make install -- add the login_hook.so to session_preload_libraries and restart the database [[email protected] ~]# grep -i session_preload /var/lib/pgsql/13/data/postgresql.conf session_preload_libraries = 'login_hook' [[email protected] ~]# systemctl restart postgresql-13.service -- connect to the database and create the extension [[email protected] ~]$ psql psql (13.1) Type "help" for help. postgres=# create extension login_hook; CREATE EXTENSION
Now, we’re all set to use this extension. In our case, we will prevent the application user from using Postgres client psql. To do this, use the template function provided on the
login_hook extension page and modify it to capture the client’s application name from pg_stat_activity view and terminate it using pg_terminate_backend() system function. Note: You can use the same template function for several purpose to manage application user.
CREATE OR REPLACE FUNCTION login_hook.login() RETURNS VOID LANGUAGE PLPGSQL AS $$ DECLARE ex_state TEXT; ex_message TEXT; ex_detail TEXT; ex_hint TEXT; ex_context TEXT; rec record; BEGIN IF NOT login_hook.is_executing_login_hook() THEN RAISE EXCEPTION 'The login_hook.login() function should only be invoked by the login_hook code'; END IF; BEGIN for rec in select pid,usename,application_name from pg_stat_activity where application_name ilike 'psql%' loop if rtrim(rec.usename) = 'miguser' and rtrim(rec.application_name) = 'psql' then raise notice 'Application users(%) restricted to connect with any clients(%)',rec.usename,rec.application_name; perform pg_terminate_backend(rec.pid); end if; end loop; EXCEPTION WHEN OTHERS THEN GET STACKED DIAGNOSTICS ex_state = RETURNED_SQLSTATE , ex_message = MESSAGE_TEXT , ex_detail = PG_EXCEPTION_DETAIL , ex_hint = PG_EXCEPTION_HINT , ex_context = PG_EXCEPTION_CONTEXT; RAISE LOG e'Error in login_hook.login()\nsqlstate: %\nmessage : %\ndetail : %\nhint : %\ncontext : %' , ex_state , ex_message , ex_detail , ex_hint , ex_context; END ; END$$; -- Give exeuction grant on the function. GRANT EXECUTE ON FUNCTION login_hook.login() TO PUBLIC;
Now, let’s see if application user can use Postgres
psql to connect to the database.
[[email protected] ~]$ psql -U miguser -d postgres -p 5432 NOTICE: Application users(miguser) restricted to connect with any clients(psql) psql: error: FATAL: terminating connection due to administrator command CONTEXT: SQL statement "SELECT pg_terminate_backend(rec.pid)" PL/pgSQL function login_hook.login() line 20 at PERFORM SQL statement "select login_hook.login()
Cool. We are able to prevent the application users connections from any other program/client in Postgres.