pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to enable Logon Trigger in postgres?
12+ messages / 6 participants
[nested] [flat]
* How to enable Logon Trigger in postgres?
@ 2018-08-20 09:41 pavan95 <pavan.postgresdba@gmail.com>
0 siblings, 2 replies; 12+ messages in thread
From: pavan95 @ 2018-08-20 09:41 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: How to enable Logon Trigger in postgres?
@ 2018-08-20 10:43 legrand legrand <legrand_legrand@hotmail.com>
parent: pavan95 <pavan.postgresdba@gmail.com>
1 sibling, 1 reply; 12+ messages in thread
From: legrand legrand @ 2018-08-20 10:43 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: How to enable Logon Trigger in postgres?
@ 2018-08-20 11:00 Shreeyansh Dba <shreeyansh2014@gmail.com>
parent: pavan95 <pavan.postgresdba@gmail.com>
1 sibling, 1 reply; 12+ messages in thread
From: Shreeyansh Dba @ 2018-08-20 11:00 UTC (permalink / raw)
To: pavan95 <pavan.postgresdba@gmail.com>; +Cc: pgsql-admin
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.
<http://www.shreeyansh.com;
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
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: How to enable Logon Trigger in postgres?
@ 2018-08-20 11:06 pavan95 <pavan.postgresdba@gmail.com>
parent: legrand legrand <legrand_legrand@hotmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: pavan95 @ 2018-08-20 11:06 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: How to enable Logon Trigger in postgres?
@ 2018-08-20 11:07 pavan95 <pavan.postgresdba@gmail.com>
parent: Shreeyansh Dba <shreeyansh2014@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: pavan95 @ 2018-08-20 11:07 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: How to enable Logon Trigger in postgres?
@ 2018-08-20 12:10 legrand legrand <legrand_legrand@hotmail.com>
parent: pavan95 <pavan.postgresdba@gmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: legrand legrand @ 2018-08-20 12:10 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: How to enable Logon Trigger in postgres?
@ 2018-08-20 13:45 Greg Spiegelberg <gspiegelberg@gmail.com>
parent: pavan95 <pavan.postgresdba@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Greg Spiegelberg @ 2018-08-20 13:45 UTC (permalink / raw)
To: pavan95 <pavan.postgresdba@gmail.com>; +Cc: pgsql-admin
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
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: How to enable Logon Trigger in postgres?
@ 2018-08-21 05:46 pavan95 <pavan.postgresdba@gmail.com>
parent: Greg Spiegelberg <gspiegelberg@gmail.com>
0 siblings, 2 replies; 12+ messages in thread
From: pavan95 @ 2018-08-21 05:46 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: How to enable Logon Trigger in postgres?
@ 2018-08-21 06:16 David G. Johnston <david.g.johnston@gmail.com>
parent: pavan95 <pavan.postgresdba@gmail.com>
1 sibling, 0 replies; 12+ messages in thread
From: David G. Johnston @ 2018-08-21 06:16 UTC (permalink / raw)
To: pavan95 <pavan.postgresdba@gmail.com>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: How to enable Logon Trigger in postgres?
@ 2018-08-21 10:58 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
parent: pavan95 <pavan.postgresdba@gmail.com>
1 sibling, 1 reply; 12+ messages in thread
From: Mark Kirkwood @ 2018-08-21 10:58 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: How to enable Logon Trigger in postgres?
@ 2018-08-21 13:01 pavan95 <pavan.postgresdba@gmail.com>
parent: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
0 siblings, 1 reply; 12+ messages in thread
From: pavan95 @ 2018-08-21 13:01 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: How to enable Logon Trigger in postgres?
@ 2018-08-21 13:18 legrand legrand <legrand_legrand@hotmail.com>
parent: pavan95 <pavan.postgresdba@gmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: legrand legrand @ 2018-08-21 13:18 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2018-08-21 13:18 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-08-20 09:41 How to enable Logon Trigger in postgres? pavan95 <pavan.postgresdba@gmail.com>
2018-08-20 10:43 ` legrand legrand <legrand_legrand@hotmail.com>
2018-08-20 11:06 ` pavan95 <pavan.postgresdba@gmail.com>
2018-08-20 12:10 ` legrand legrand <legrand_legrand@hotmail.com>
2018-08-20 11:00 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
2018-08-20 11:07 ` pavan95 <pavan.postgresdba@gmail.com>
2018-08-20 13:45 ` Greg Spiegelberg <gspiegelberg@gmail.com>
2018-08-21 05:46 ` pavan95 <pavan.postgresdba@gmail.com>
2018-08-21 06:16 ` David G. Johnston <david.g.johnston@gmail.com>
2018-08-21 10:58 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2018-08-21 13:01 ` pavan95 <pavan.postgresdba@gmail.com>
2018-08-21 13:18 ` legrand legrand <legrand_legrand@hotmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox