Обсуждение: 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,
You can enable login connection related parameters in a postgresql.conf file to get the user login/logout time.
Due to enabling the logging parameters the log file size will increase.
You can enable login connection related parameters in a postgresql.conf file to get the user login/logout time.
Due to enabling the logging parameters the log file size will increase.
On Mon, Aug 20, 2018 at 3:11 PM, pavan95 <pavan.postgresdba@gmail.com> wrote:
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 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
Use pgbouncer and leverage auth_query option. Can capture logins to table, trigger if necessary. No logout / disconnect equivalent tho.
-Greg
On Mon, Aug 20, 2018, 5:08 AM pavan95 <pavan.postgresdba@gmail.com> wrote:
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
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
On Monday, August 20, 2018, pavan95 <pavan.postgresdba@gmail.com> 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.
11 has been feature frozen for months, so that would be a no. It seems unlikely that the described feature will make it into any core PostgreSQL release at this time. Figuring out how to fit the existing log file based auditing setup into your process will be the path of least resistance.
David J.
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