Обсуждение: 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
2015-09-26 18:17 GMT+02:00 Nikolai Zhubr <n-a-zhubr@yandex.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
2015-09-26 19:53 GMT+02:00 Nikolai Zhubr <n-a-zhubr@yandex.ru>:
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?
I don't know. I am pretty sceptical - from my experience this request was related to unhappy designed projects.
Pavel
Thank you,
Nikolai
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
2015-09-26 19:59 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us>:
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.
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.
Regards
Pavel
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
2015-09-26 20:29 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us>:
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.
Probably I didn't start transaction.
I'll check it.
Regards
Pavel
regards, tom lane
On Saturday, September 26, 2015, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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.
Or even "reset session" as is often used by connection poolers depending on what semantics are trying to be captured.
David J.
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
> 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.Probably I didn't start transaction.I'll check it.
It is working. Patch attached
Regards
Pavel
Вложения
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 >
2015-09-27 13:33 GMT+02:00 Nikolai Zhubr <n-a-zhubr@yandex.ru>:
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?
no, unhandled exeception enforce FATAL error. So you cannot to login to this session
And maybe it could even be proposed for upstream?
It is extension, so it can live outside.
The accepting to upstream depends on community. I have not too free time to push this patch. But anybody can do it - or upload to PGXN
It is so wonderfully small and looks not too much intrusive.
It is really simple.
Regards
Pavel
Thank you,
Nikolai
Regards
Pavel
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
It should not work - see a doc
If the file includes a function named
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.)2015-09-27 13:33 GMT+02:00 Nikolai Zhubr <n-a-zhubr@yandex.ru>:
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.
updated patch - fixed error reporting
Regards
Pavel
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Вложения
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.)
Pavel
2015-09-27 22:51 GMT+02:00 Nikolai Zhubr <n-a-zhubr@yandex.ru>:
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)
This point can be too late. Probably it is good enough for releasing external sources, but the query execution engine can be broken in this moment ~ you cannot to run any SQL
Not tested
Regards
Pavel
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 > >