Обсуждение: How to enable Logon Trigger in postgres?

Hi all,

I wanted to know whether we can store logon(user login/logout) information
for a database. In specific I want to know at what time which user had
logged in and what he performed on the database and the time which he logged
off?

Is it possible to get this level of information in Postgres?

Will be very happy if we can get this piece of information from the
postgres(inbuilt/any tool). Looking forward to hearing from you!

Regards,
Pavan



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html

Hi,
There is nom such trigger in postgres.
You have to set up some specific parameters  like log_connections or
log_disconnections to get those informations.
There is also pgaudit extension that can help.

Regards
PAscal



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html

Hi Pascal,

Thanks for your reply. But I think with pgaudit we can log only to the
Postgres log file. 

1) Can we log the same pgaudit information into a table automatically?

2) I want to implement a task such that I do have two servers one is
monitoring server and the other production server. So how will pgaudit will
help me in getting all production server audit data to table in monitoring
server automatically. Can we do it??

Looking forward to hear from you! Thanks in Advance.

Regards,
Pavan



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html

Hi Shreyansh,

Is there any alternate approach without logging into log file??


Regards,
Pavan



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html

Maybe you can load that log file using COPY or FILE_FDW as described in
https://blog.hagander.net/remote-log-reading-in-postgresql-91-192/

Regards
PAscal



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html

Hi all,

Thanks for your responses. But to my wonder why can't these logon, logout be
events for a postgres instance. If they are like any other
INSERT/UPDATE/DELETE we can go with the triggers(logon trigger like in SQL
server).

It will be great if postgres 11 includes this feature in its native release.


Regards,
Pavan



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html

Hi all,

Thanks for your responses. But to my wonder why can't these logon, logout be
events for a postgres instance. If they are like any other
INSERT/UPDATE/DELETE we can go with the triggers(logon trigger like in SQL
server).

It will be great if postgres 11 includes this feature in its native release.

On 21/08/18 17:46, pavan95 wrote:

> Hi all,
>
> Thanks for your responses. But to my wonder why can't these logon, logout be
> events for a postgres instance. If they are like any other
> INSERT/UPDATE/DELETE we can go with the triggers(logon trigger like in SQL
> server).
>
> It will be great if postgres 11 includes this feature in its native release.
>
>

It is certainly *possible*. But right now the event matrix - described here:

https://www.postgresql.org/docs/10/static/event-trigger-matrix.html

does *not* include logon/logoff events. Given that 11 is just about to 
be released, it won't include such a feature either. Now just to be 
clear, what you are asking for makes sense - having direct access to 
logon events rather than scraping the log file after then event - is a 
nice idea...we just don't have a patch to do that for version 11 (or 12) 
right now.

regards
Mark

Hi Mark/David,

Yeah Understood. But my point to say is it will be a nice audit feature if
it's included in the upcoming releases


regards,
Pavan



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html

Hi,

Development team is aware of this demand, If you search for "logon trigger"
in hacker list you will find many of them.

For exemple "Session Start/End hooks" (
https://commitfest.postgresql.org/15/1318/) in 
commitfest 2017/11 is very near of your need ;o)

But this is only (frozen) development work now ...

Regards
PAscal



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html