Re: On login trigger: take three

From: Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru>
To: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
Cc: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: On login trigger: take three
Date: 2020-09-11 10:42:17
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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:
> 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:
> 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 $$
  insert into connects (who) values (current_user());
  raise notice 'You are welcome!';
$$ language plpgsql;
create event trigger on_login_trigger on session_start execute procedure
alter event trigger on_login_trigger enable always;

Attachment Content-Type Size
on_connect_event_trigger-6.patch text/x-patch 15.5 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Masahiko Sawada 2020-09-11 11:06:52 Re: Transactions involving multiple postgres foreign servers, take 2
Previous Message Amit Langote 2020-09-11 10:20:56 Re: making update/delete of inheritance trees scale better