Re: FW: Single Line Query Logging

[Giuseppe Broccolo <giuseppe.broccolo@xxxxxxxxxxxxxx>]

From: emreozturk@xxxxxxxxxxx
To: pgsql-general@xxxxxxxxxxxxxx
Subject: Single Line Query Logging
Date: Wed, 10 Jul 2013 13:16:13 +0300

Hello all,

Is there a parameter to log any SQL query as a single line in audit logs? I have some problems in my SIEM application. If a DBA sends the query as a single line I can gather the whole query, but if he enters like

UPDATE x  .......
          y=Z ......
          where ......

I only get the line starts with UPDATE then I can not see what is really changed in my SIEM logs. I have heard that there is a parameter do what I mean. Do you agree?

No. There's no parameter to be set for log parsing in a simple way, specially for multi-raws query. A possible solution is to use syslog instead of stderr: syslog stores each log element as an independent item.
You can try setting

                log_destination = 'syslog'
                redirect_stderr = off

Then you have to setup syslog by editing your syslog conf (On Debian: /etc/rsyslog.d/50-default.conf): add this new line (supposing your log directory is /var/log/pgsql/, and 'local0' is set in "syslog_facility" parameter in your postgres.conf - do a check)

                LOCAL0.*            -/var/log/pgsql

and in the "catch all log files" area add

                LOCAL0.none

then restart syslog (sudo /etc/init.d/rsyslog restart). I've tried it, and it works!

Giuseppe.

-- 
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
giuseppe.broccolo@xxxxxxxxxxxxxx | www.2ndQuadrant.it