Обсуждение: Server-side hooks for user session start and session end

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?


Thank you,
Nikolai

Hi Pavel,
26.09.2015 19:26, Pavel Stehule wrote:
[...]
> 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.

Ok, I see. Creating such a patch might be not very hard actually.
But living with custom-patched server could cause massive pain later...

> 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.

Thanks for the hint, I've noticed this loop while searching for some
more 'regular' methods.

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

Right. And I'd guess that might be the reason for not implementing such
thing in upstream (yet) - the lack of proper context for exceptions and
such.

> 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.

No, the application in question has been running on top of postgres
almost from its very start and for quite some years successfully now, it
definitely does not contain anything foreign to postgres.
And the events of session start and session end would seem quite generic
and usefull anyway?


Thank you,
Nikolai

Pavel Stehule <pavel.stehule@gmail.com> writes:
> 2015-09-26 19:53 GMT+02:00 Nikolai Zhubr <n-a-zhubr@yandex.ru>:
>> And the events of session start and session end would seem quite generic
>> and usefull anyway?

> I don't know. I am pretty sceptical - from my experience this request was
> related to unhappy designed projects.

A session-end hook seems pretty problematic to me: you simply cannot
guarantee that it will run at all.  (Consider process crash or server
abort cases.)  So anything built on the assumption that it gets to run
at session end is going to be inherently unreliable.

A session-start hook is already possible at the C-code level, using
session_preload_libraries.  It wouldn't be hard to write an extension
that exposed that in some useful way to SQL code.

            regards, tom lane

Pavel Stehule <pavel.stehule@gmail.com> writes:
> 2015-09-26 19:59 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us>:
>> A session-start hook is already possible at the C-code level, using
>> session_preload_libraries.  It wouldn't be hard to write an extension
>> that exposed that in some useful way to SQL code.

> years ago I tried it, if I remember well. I had a problems with SPI calls,
> because some caches was not initialized. I am not sure, and I didn't test
> last time.

You'd have to start your own transaction if you wanted one, and any
uncaught error would effectively be FATAL because it would terminate the
session, but otherwise I don't see why that wouldn't work.

            regards, tom lane

Pavel Stehule <pavel.stehule@gmail.com> writes:
> 2015-09-26 19:53 GMT+02:00 Nikolai Zhubr <n-a-zhubr@yandex.ru>:
>> And the events of session start and session end would seem quite generic
>> and usefull anyway?

> I don't know. I am pretty sceptical - from my experience this request was
> related to unhappy designed projects.

A session-end hook seems pretty problematic to me: you simply cannot
guarantee that it will run at all.  (Consider process crash or server
abort cases.)  So anything built on the assumption that it gets to run
at session end is going to be inherently unreliable.

26.09.2015 20:59, Tom Lane wrote:
[...]
> A session-end hook seems pretty problematic to me: you simply cannot
> guarantee that it will run at all.  (Consider process crash or server
> abort cases.)  So anything built on the assumption that it gets to run
> at session end is going to be inherently unreliable.

Yes, I understand that.
For such cases (of e.g. abnormal shutdown) it would also be nice if a
database could 'autorun' some specified function just after server has
performed whetever startup/replay/cleanup procedures necessary and
immediately before it is ready for normal operation. This would allow to
perform some checks and cleanups to restore consistency. And actually,
such 'autorun' function could be interesting regardless of this session
begin/end thing.
And there are already quite some hooks in use for session-end cleanups
at C-level, like e.g. for disposing temp tables, just none of them
expose anything to SQL level. I'd guess this technique is available for
use by extensions, so potentially SQL-level hook could also be
implemented. Though I think it is a bit beyond my capability at the
moment...

Regards,


Thank you,
Nikolai

Hi,
27.09.2015 8:29, Pavel Stehule:
>     I'll check it.
>
>
> It is working. Patch attached

Oh, brilliant! This is a _huge_ help actually!

If I understand it correctly, any unhandled SQL-level exceptions will
essentially be ignored there, so that the session will continue regardless?

And maybe it could even be proposed for upstream?
It is so wonderfully small and looks not too much intrusive.


Thank you,
Nikolai

>
> Regards
>
> Pavel
>

On 9/26/15 7:12 PM, Nikolai Zhubr wrote:
> 26.09.2015 20:59, Tom Lane wrote:
> [...]
>> A session-end hook seems pretty problematic to me: you simply cannot
>> guarantee that it will run at all.  (Consider process crash or server
>> abort cases.)  So anything built on the assumption that it gets to run
>> at session end is going to be inherently unreliable.
>
> Yes, I understand that.
> For such cases (of e.g. abnormal shutdown) it would also be nice if a
> database could 'autorun' some specified function just after server has
> performed whetever startup/replay/cleanup procedures necessary and
> immediately before it is ready for normal operation. This would allow to
> perform some checks and cleanups to restore consistency. And actually,
> such 'autorun' function could be interesting regardless of this session
> begin/end thing.
> And there are already quite some hooks in use for session-end cleanups
> at C-level, like e.g. for disposing temp tables, just none of them
> expose anything to SQL level. I'd guess this technique is available for
> use by extensions, so potentially SQL-level hook could also be
> implemented. Though I think it is a bit beyond my capability at the
> moment...

Actually, there's probably some other hooks you could use for this purpose.

BTW, if you want to extend Pavel's code to also handle logout, you just
need to create a _PG_fini function. See the end of this section in the
docs: http://www.postgresql.org/docs/9.4/static/xfunc-c.html#XFUNC-C-DYNLOAD
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

Hi,
27.09.2015 22:59, Pavel Stehule:
>
> 2015-09-27 21:40 GMT+02:00 Jim Nasby <Jim.Nasby@bluetreble.com
> <mailto:Jim.Nasby@bluetreble.com>>:
>
>     _PG_fini
>
>
> It should not work - see a doc

I'd rather consider registering with on_shmem_exit -- as per the
insightfull note placed in postgresql.c:4290.
(Haven't tried - just thinking)


Thank you,
Nikolai

> If the file includes a function named |_PG_fini|, that function will be
> called immediately before unloading the file. Likewise, the function
> receives no parameters and should return void. Note that |_PG_fini| will
> only be called during an unload of the file, not during process
> termination. (Presently, unloads are disabled and will never occur, but
> this may change in the future.)

Hi,
27.09.2015 23:22, Pavel Stehule wrote:
[...]
> updated patch - fixed error reporting

Wow! I'll definitely borrow it :)


Thank you,
Nikolai

>
> Regards
>
> Pavel
>
>
>     --
>     Sent via pgsql-general mailing list (pgsql-general@postgresql.org
>     <mailto:pgsql-general@postgresql.org>)
>     To make changes to your subscription:
>     http://www.postgresql.org/mailpref/pgsql-general
>
>