Re: On login trigger: take three

Hi

čt 3. 9. 2020 v 15:43 odesílatel Konstantin Knizhnik <
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

Pavel

Example
>
> --
> Konstantin Knizhnik
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
>