Take the 2-minute tour ×

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.

share|improve this question

migrated from stackoverflow.com Mar 19 '13 at 9:12

This question came from our site for professional and enthusiast programmers.

add comment

3 Answers

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.

share|improve this answer
thanks for the answer, 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 .. ? –  d.raev Mar 19 '13 at 8:13
yes , pg_stat_activity only show current connections, and no system tables can show history connections. As the preceding describe, you may need look the database log for history connections informations. –  francs Mar 19 '13 at 9:43
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

