Re: On login trigger: take three - Mailing list pgsql-hackers

Hi

čt 3. 9. 2020 v 15:43 odesílatel Konstantin Knizhnik <k.knizhnik@postgrespro.ru> napsal:

Hi hackers,

Recently I have asked once again by one of our customers about login
trigger in postgres. People are migrating to Postgres from Oracle and 
looking for Postgres analog of this Oracle feature.
This topic is not new:

https://www.postgresql.org/message-id/flat/1570308356720-0.post%40n3.nabble.com#4748bcb0c5fc98cec0a735dbdffb0c68
https://www.postgresql.org/message-id/flat/OSAPR01MB507373499CCCEA00EAE79875FE2D0%40OSAPR01MB5073.jpnprd01.prod.outlook.com#ed50c248be32be6955c385ca67d6cdc1

end even session connect/disconnect hooks were sometimes committed (but
then reverted).
As far as I understand most of the concerns were related with disconnect
hook.
Performing some action on session disconnect is actually much more
complicated than on login.
But customers are not needed it, unlike actions performed at session start.

I wonder if we are really going to make some steps in this directions?
The discussion above was finished with "We haven't rejected the concept
altogether, AFAICT"

I have tried to resurrect this patch and implement on-connect trigger on
top of it.
The syntax is almost the same as proposed by Takayuki:

CREATE EVENT TRIGGER mytrigger
AFTER CONNECTION ON mydatabase
EXECUTE {PROCEDURE | FUNCTION} myproc();

I have replaced CONNECT with CONNECTION because last keyword is already
recognized by Postgres and
make ON clause mandatory to avoid shift-reduce conflicts.

Actually specifying database name is redundant, because we can define
on-connect trigger only for self database (just because triggers and
functions are local to the database).
It may be considered as argument against handling session start using
trigger. But it seems to be the most natural mechanism for users.

On connect trigger can be dropped almost in the same way as normal (on
relation) trigger, but with specifying name of the database instead of
relation name:

DROP TRIGGER mytrigger ON mydatabase;

It is possible to define arbitrary number of on-connect triggers with
different names.

I attached my prototype implementation of this feature.
I just to be sure first that this feature will be interested to community.
If so, I will continue work in it and prepare new version of the patch
for the commitfest.

I have a customer that requires this feature too. Now it uses a solution based on dll session autoloading.  Native solution can be great.

+1

I realized that on connect trigger should be implemented as EVENT TRIGGER.
So I have reimplemented my patch using event trigger and use session_start even name to make it more consistent with other events.
Now on login triggers can be created in this way:

create table connects(id serial, who text);
create function on_login_proc() returns event_trigger as $$
begin
  insert into connects (who) values (current_user());
  raise notice 'You are welcome!';
end;
$$ language plpgsql;
create event trigger on_login_trigger on session_start execute procedure on_login_proc();
alter event trigger on_login_trigger enable always;