get account activity in postgresql

I have a old DB with bunch of accounts. One of them is used for sure but for 5 of them or so there is no information.

Is there any info in the system table in Postgresql when was the last login or executed command for a user? Or may be who and when created the user.

I do not find any system table show the last login time of roles。 if you want to see the
last login time of a role and the executed command of a user ,I suggest you set log_connections
parameter in postgresql.conf ,and you can see those information in your database log files.

In addition, you can use "\s" meta command see history command ,as the following:

[pg90@redhatB ~]$ psql
psql (9.0.9)
Type "help" for help.

postgres=# \s /home/pg90/script/tf/history.sql
Wrote history to file ".//home/pg90/script/tf/history.sql".

But only can see the history of one client.

To see other roles's connections information ,you can see pg_stat_activity for more informations。

postgres=# select usename,datname,count(*) from pg_stat_activity group by usename,datname order by 3 desc;

After that , you can make decision where drop a role or not。I also suggest that you shoud check whether a role owner any objects.

StackExchange Database Administrators Q#36986, answer score: 3