Thread: postgresql log parsing to report on user/db access

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.

On 04/25/2012 06:36 AM, Larry J Prikockis wrote:
> 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... 
> 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.
>
What is the etc? You should be able to parse it pretty quickly with awk
but if you want additional stats like length of time a user connected it
will involve additional work.

Have you considered logging to the csv format?
http://www.postgresql.org/docs/9.1/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-CSVLOG

This may be an easier approach as you can just pull the data into a
table and report from there. Of course you can pre-filter the CSV with
grep or similar to avoid importing uninteresting lines.

Cheers,
Steve