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