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.
pg_stat_activity shows only active connections. I didn't get the history part .. does this file exist ? and you must be logged with the user I suppose .. ?
The best solution is to log connections, and then periodically parse the log files and store the resulting data somewhere.
From there, you can then make some decisions about which roles to drop. All this being said there are some key difficulties that arise that make this worth standing as a standalone answer.
The first is that you can't just query pg_roles and merge it with your data. The basic problem is that roles may be granted to other roles. You'd need to start merging it with other information, and this is actually relatively complex. The key system tables to look at are pg_auth_id and pg_auth_members. You should also check for whether a role can even login. Note further that superusers can assume roles not granted to them so you don't even have any guarantees there. You should log which roles you are deleting so you can quickly recreate them as needed if something breaks.
Have you looked at the pg_stat_statements extension? It won't give you the role logon time (although setting the log_connections parameter as @francs suggests would deal with that) but it will show you which roles are running queries (and what those queries are).
