Thread: user logging info

user logging info

From
Dhandapani Shanmugam
Date:
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

Re: user logging info

From
Glyn Astill
Date:
> 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
>
>


Re: user logging info

From
Dhandapani Shanmugam
Date:
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


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
>
>

Re: user logging info

From
hubert depesz lubaczewski
Date:
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



Re: user logging info

From
Venkata Balaji N
Date:

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

Re: user logging info

From
Dhandapani Shanmugam
Date:
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 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