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

Поиск
Список
Период
Сортировка

How to enable Logon Trigger in postgres?

От
pavan95
Дата:
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


Re: How to enable Logon Trigger in postgres?

От
legrand legrand
Дата:
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


Re: How to enable Logon Trigger in postgres?

От
Shreeyansh Dba
Дата:
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.



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


Re: How to enable Logon Trigger in postgres?

От
pavan95
Дата:
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


Re: How to enable Logon Trigger in postgres?

От
pavan95
Дата:
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


Re: How to enable Logon Trigger in postgres?

От
legrand legrand
Дата:
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


Re: How to enable Logon Trigger in postgres?

От
Greg Spiegelberg
Дата:
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

Re: How to enable Logon Trigger in postgres?

От
pavan95
Дата:
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


Re: How to enable Logon Trigger in postgres?

От
"David G. Johnston"
Дата:
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.

Re: How to enable Logon Trigger in postgres?

От
Mark Kirkwood
Дата:
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


Re: How to enable Logon Trigger in postgres?

От
pavan95
Дата:
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


Re: How to enable Logon Trigger in postgres?

От
legrand legrand
Дата:
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