Thread: user logging info
Hi All,
Good Day!
Please clarify me on below points :)
-Can we see user connections on any system tables(active + past) in postgreSQL, Please share me the SQL if it is possible.
-.I assume postgresql.conf can be modified so that user connections are logged in a log file.
Is this best approach or how can we do to check for the user connection details? any advise is really helpful. Thanks in Advance
-Dhandapani
> From: Dhandapani Shanmugam <postgresql95@gmail.com>
>To: pgsql-admin@postgresql.org
>Sent: Thursday, 2 June 2016, 7:21
>Subject: [ADMIN] user logging info
>
>
>
>Hi All,
>
>
>Good Day!
>
>
>Please clarify me on below points :)
>
>
>-Can we see user connections on any system tables(active + past) in postgreSQL, Please share me the SQL if it is
possible.
>
Can you clarify what you want to see here? You can see the current and last queries of connections with "SELECT * FROM
pg_stat_activity". If you want auditing or logging of all activity then depending on what you need it's possible with
triggersor an extension like pg_audit.
>
>-.I assume postgresql.conf can be modified so that user connections are logged in a log file.
>
Yes turn on log_connections and log_disconnections, see the "what to log" section here:
https://www.postgresql.org/docs/current/static/runtime-config-logging.html
>>Is this best approach or how can we do to check for the user connection details? any advise is really helpful. Thanks
inAdvance
>
>
>-Dhandapani
>
>
Thank for your response Glyn,
>-Can we see user connections on any system tables(active + past) in postgreSQL, Please share me the SQL if it is possible.
Can you clarify what you want to see here? You can see the current and last queries of connections with "SELECT * FROM pg_stat_activity". If you want auditing or logging of all activity then depending on what you need it's possible with triggers or an extension like pg_audit.
Can you clarify what you want to see here? You can see the current and last queries of connections with "SELECT * FROM pg_stat_activity". If you want auditing or logging of all activity then depending on what you need it's possible with triggers or an extension like pg_audit.
I want to see the list of user who logged into XXX database in last 90 days. How can we do this please
On Thu, Jun 2, 2016 at 2:17 PM, Glyn Astill <glynastill@yahoo.co.uk> wrote:
> From: Dhandapani Shanmugam <postgresql95@gmail.com>
>To: pgsql-admin@postgresql.org
>Sent: Thursday, 2 June 2016, 7:21
>Subject: [ADMIN] user logging info
>
>
>
>Hi All,
>
>
>Good Day!
>
>
>Please clarify me on below points :)
>
>
>-Can we see user connections on any system tables(active + past) in postgreSQL, Please share me the SQL if it is possible.
>
Can you clarify what you want to see here? You can see the current and last queries of connections with "SELECT * FROM pg_stat_activity". If you want auditing or logging of all activity then depending on what you need it's possible with triggers or an extension like pg_audit.
>
>-.I assume postgresql.conf can be modified so that user connections are logged in a log file.
>
Yes turn on log_connections and log_disconnections, see the "what to log" section here:
https://www.postgresql.org/docs/current/static/runtime-config-logging.html
>>Is this best approach or how can we do to check for the user connection details? any advise is really helpful. Thanks in Advance
>
>
>-Dhandapani
>
>
On Thu, Jun 02, 2016 at 02:33:34PM +0530, Dhandapani Shanmugam wrote: > I want to see the list of user who logged into XXX database in last 90 > days. How can we do this please You can't really. Unless you will modify postgresql.conf (or any other means of setting config variables), and you'll set log_connections. You might want to read https://www.postgresql.org/docs/current/static/runtime-config-logging.html or https://www.depesz.com/2011/05/06/understanding-postgresql-conf-log/ Best regards, depesz
On Thu, Jun 2, 2016 at 7:03 PM, Dhandapani Shanmugam <postgresql95@gmail.com> wrote:
Thank for your response Glyn,>-Can we see user connections on any system tables(active + past) in postgreSQL, Please share me the SQL if it is possible.
Can you clarify what you want to see here? You can see the current and last queries of connections with "SELECT * FROM pg_stat_activity". If you want auditing or logging of all activity then depending on what you need it's possible with triggers or an extension like pg_audit.I want to see the list of user who logged into XXX database in last 90 days. How can we do this please
Yes, you need to enable log_connections and if you wish to know the time spent by each connection on the database, then you would need to enable log_disconnections and log_duration parameters as well. What information is exactly logged into the logfiles will also depend on your log_line_prefix parameter settings. You can choose the options for log_line_prefix from postgresql.conf itself.
Once you enable the said parameters, the information will be logged into the logfiles which is pretty informative and hard to analyze. You perform analysis on the log files using tools like pgBadger which helps you generate a html report with various statistical information.
Regards,
Venkata B N
Fujitsu Australia
Awesome. Thanks balaji
On Sun, Jun 5, 2016 at 7:22 AM, Venkata Balaji N <nag1010@gmail.com> wrote:
On Thu, Jun 2, 2016 at 7:03 PM, Dhandapani Shanmugam <postgresql95@gmail.com> wrote:Thank for your response Glyn,>-Can we see user connections on any system tables(active + past) in postgreSQL, Please share me the SQL if it is possible.
Can you clarify what you want to see here? You can see the current and last queries of connections with "SELECT * FROM pg_stat_activity". If you want auditing or logging of all activity then depending on what you need it's possible with triggers or an extension like pg_audit.I want to see the list of user who logged into XXX database in last 90 days. How can we do this pleaseYes, you need to enable log_connections and if you wish to know the time spent by each connection on the database, then you would need to enable log_disconnections and log_duration parameters as well. What information is exactly logged into the logfiles will also depend on your log_line_prefix parameter settings. You can choose the options for log_line_prefix from postgresql.conf itself.Once you enable the said parameters, the information will be logged into the logfiles which is pretty informative and hard to analyze. You perform analysis on the log files using tools like pgBadger which helps you generate a html report with various statistical information.Regards,Venkata B NFujitsu Australia