Re: Server-side hooks for user session start and session end

2015-09-26 18:17 GMT+02:00 Nikolai Zhubr <n-a-zhubr(at)yandex(dot)ru>:

> Hi all,
>
> I'm trying to find a soultion to automatically execute something
> (preferrably a function or at least some pure sql statements) at the
> beginning and at the end of a user session. As an example, imagine just
> storing of all login and logout timestamps (though the real task is a bit
> more complicated than that)
>
> I would not like it to be somehow explicitely client-side initiated (like
> e.g. trivially calling some certain do_at_sess_begin(), do_at_sess_end())
> because first, a malicious client could then mess it up easily, and
> furthermore, in the case that the session somehow ended abnormally (due to
> say temporary network fault) client-side finalizer function would not be
> called anyway.
>
> My first try was to create an event trigger for sql drop events, then
> create a temporary table and watch for its drop at the end of a session.
> However, it seems drop events are just not fired for temporary tables
> (although documentation does not state this anywhere, as far as I can see,
> maybe I'm wrong about that)
>
> It would seem like one of the simplest things ever, but I'm now totally
> stuck out of ideas.
>
> As a partial solution, handling just session _end_ would already be OK.

> Any thoughts?
>

This cannot be solved without patching PostgreSQL source code :( . There
are not good hooks for custom extension. Patch is relative simple, but I
cannot to publish it.

You have to modify main loop in src/backend/tcop/postgres.c file. Login
point can be immediately before loop. Logout point can be joined to
'X',EOF message.

Attention - there are some corner case, what are necessary to solve -
mainly how to handle exceptions in login/logout procedures.

Regards

Pavel

p.s. I understand so this hack is necessary for porting older application
from other databases, but I am not sure if it is a good idea to use it. Not
all tasks can be solved well in database.

>
>
> Thank you,
> Nikolai
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>