Re: user logging info

On Thu, Jun 2, 2016 at 7:03 PM, Dhandapani Shanmugam <postgresql95(at)gmail(dot)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