Обсуждение: Single Line Query Logging

Hi Emre,

Am 10.07.2013 12:16, schrieb Emre ÖZTÜRK:
> 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?

Do you log to syslog?

I had a look into src/backend/utils/error/elog.c and found the following
comment:

/*
* our problem here is that many syslog implementations don't handle long
* messages in an acceptable manner. while this function doesn't help that
* fact, it does work around by splitting up messages into smaller pieces.
*
* we divide into multiple syslog() calls if message is too long or if the
* message contains embedded newline(s).
*/

If you use a dedicated logfile for PostgreSQL these commands will not be
split.

Example:

Statement:
test=# select * from
test;

PostgreSQL logfile:
LOG:  statement: select * from
        test;

syslog:
Jul 10 21:13:17 db1 postgres[32714]: [3-1] LOG:  statement: select * from
Jul 10 21:13:17 db1 postgres[32714]: [3-2] #011test;


I hope it helps.

>
> Regards,
>
>
>
> /*Emre ÖZTÜRK*/

Regards

    - Adrian

Emre ÖZTÜRK wrote:

> I have sent below question months ago but I could not get any reply from you experts. I will very
> appreciated if you can help.
> 
> PS: I have tried debug_pretty_print = off but I did not work.

> 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?

There is no way to modify queries like that in the PostgreSQL log.

You can set log_destination = 'csvlog' to get a PostgreSQL log in CSV
format that can be parsed more easily.

I don't know what the "SIEM logs" are that you talk about.
If they log only part of the query, I'd say that they are broken
and should be fixed.

Yours,
Laurenz Albe

From: emreozturk@outlook.com
To: pgsql-general@postgresql.org
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@2ndQuadrant.it | www.2ndQuadrant.it