postgresql log parsing to report on user/db access - Mailing list pgsql-general

Is anyone aware of a quick solution for producing user/db access reports
from pgsql syslog format logs?

in other words, I have a bunch of lines such as:

> Apr 22 06:39:04 147283-db3 postgres[13252]: [1800-1] user=database1_remote,db=sqm_remote_database1 LOG:  connection
authorized:user=database1_remote database=sqm_remote_database1 
> Apr 22 06:39:04 147283-db3 postgres[13252]: [1801-1] user=database1_remote,db=sqm_remote_database1 LOG:
disconnection:session time: 0:00:00.01 user=database1_remote 
> Apr 22 06:39:04 147283-db3 postgres[13254]: [1799-1] user=[unknown],db=[unknown] LOG:  connection received:
host=172.25.150.172port=58088 
> Apr 22 06:39:04 147283-db3 postgres[13254]: [1800-1] user=database1_remote,db=sqm_remote_database1 LOG:  connection
authorized:user=database1_remote database=sqm_remote_database1 
> Apr 22 06:39:04 147283-db3 postgres[13254]: [1801-1] user=database1_remote,db=sqm_remote_database1 LOG:
disconnection:session time: 0:00:00.02 user=database1_remote 
> Apr 22 06:39:12 147283-db3 postgres[13259]: [1799-1] user=[unknown],db=[unknown] LOG:  connection received:
host=172.25.150.162port=40768 
> Apr 22 06:39:12 147283-db3 postgres[13259]: [1800-1] user=database3_remote,db=sqm_remote_database3 LOG:  connection
authorized:user=database3_remote 
> Apr 22 06:39:12 147283-db3 postgres[13259]: [1801-1] user=database3_remote,db=sqm_remote_database3 LOG:
disconnection:session time: 0:00:00.01 user=database3_remote 
> Apr 22 06:39:16 147283-db3 postgres[13263]: [1799-1] user=[unknown],db=[unknown] LOG:  connection received:
host=172.25.150.69port=37739 
> Apr 22 06:39:16 147283-db3 postgres[13263]: [1800-1] user=database2_remote,db=sqm_remote_database2 LOG:  connection
authorized:user=database2_remote database=sqm_remote_database2 
> Apr 22 06:39:16 147283-db3 postgres[13263]: [1801-1] user=database2_remote,db=sqm_remote_database2 LOG:
disconnection:session time: 0:00:00.01 user=database2_remote 
> Apr 22 06:39:18 147283-db3 postgres[13264]: [1799-1] user=[unknown],db=[unknown] LOG:  connection received:
host=172.25.150.69port=37740 

and I'd like to be able to generate weekly reports that provide rolled
up statistics on which user connected to which db, how many times, etc.

I've used pg_fouine for doing reporting on queries, but it doesn't
really deal with tracking user/db access.

Before I try to whip up something myself with perl or something, I
wanted to see if anyone's already done something similar.

thanks in advance--

--
Larry J. Prikockis
System Administrator
240-965-4597 (direct)
lprikockis@vecna.com
http://www.vecna.com
http://www.vecnamedical.com
http://vecnamed.blogspot.com/


Vecna
6404 Ivy Lane
Suite 500
Greenbelt, MD 20770
Phone: 240-965-4500
Fax: 240-547-6133
Better Technology, Better World (TM)

The contents of this message may be privileged and confidential.
Therefore, if this message has been received in error, please delete it
without reading it. Your receipt of this message is not intended to
waive any applicable privilege. Please do not disseminate this message
without the permission of the author.