Re: On login trigger: take three

On 03.09.2020 17:18, Pavel Stehule wrote:
> Hi
>
> čt 3. 9. 2020 v 15:43 odesílatel Konstantin Knizhnik
> <k(dot)knizhnik(at)postgrespro(dot)ru <mailto:k(dot)knizhnik(at)postgrespro(dot)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;