0

I have rsyslog 4.6.4 configured to write mail logs to a PostgreSQL database. It all works fine, until the log message contains a backslash, as in this example:

Jun 12 11:37:46 dc5 postfix/smtp[26475]: Vk0nYDKdH3sI: to=<[email protected]>, relay=----.---[---.---.---.---]:25, delay=1.5, delays=0.77/0.07/0.3/0.35, dsn=4.3.0, status=deferred (host ----.---[---.---.---.---] said: 451 4.3.0 Error writing to file d:\pmta\spool\B\00000414, status = ERROR_DISK_FULL in "DATA" (in reply to end of DATA command))

The above is the log entry, as written to /var/log/mail.log. It is correct. The trouble is that the backslash characters in the file name are interpreted as escapes when sent to the following SQL recipe:

$template dcdb, "SELECT rsyslog_insert(('%timereported:::date-rfc3339%'::TIMESTAMPTZ)::TIMESTAMP,'%msg:::escape-cc%'::TEXT,'%syslogtag%'::VARCHAR)",STDSQL
:syslogtag, startswith, "postfix" :ompgsql:/var/run/postgresql,dc,root,;dcdb

As a result, the rsyslog_insert() stored procedure gets the following value for as msg:

Vk0nYDKdH3sI: to=<[email protected]>, relay=----.---[---.---.---.---]:25, delay=1.5, delays=0.77/0.07/0.3/0.35, dsn=4.3.0, status=deferred (host ----.---[199.85.216.241] said: 451 4.3.0 Error writing to file d:pmtaspoolB

The \p, \s, \B and \0 in the file name are interpreted by PostgreSQL as literal p, s, and B followed by a NULL character, thus early-terminating the string. This behavior can be easiily confirmed with:

dc=# SELECT 'd:\pmta\spool\B\00000414';
   ?column?   
--------------
 d:pmtaspoolB
(1 row)

dc=# 

Is there a way to correct this problem? Is there a way I'm not finding in the rsyslog docs to turn \ into \\?

1 Answer 1

2

First, you should REALLY be using parameterized queries & prepared statements when passing arbitrary strings.
(This probably isn't your fault -- rsyslog is almost certainly responsible for that horror).

If you can't switch to a better query structure the Postgres encode function can probably help you out (see the documentation here) -- Specify an encoding of escape and Postgres will conveniently double all your backslashes in the string you pass to it.


Note that if you're feeling particularly pedantic you can enable standard_conforming_strings, which makes Postgres treat the \ character in a string as a literal backslash rather than its historical (unix-y) behavior of treating it as an escape character.
Whether such a change is viable in your environment depends on a lot of factors...

2
  • I tried your encode() suggestion, but the input is escape-processed too soon. The end result is the same. standard_conforming_strings seems to be a reasonable solution. It's unfortunate that with rsyslog I can't just set that as a session variable. But in my case, it should work to make it system-wide without much fallout. Thanks for the suggestions.
    – Flimzy
    Jun 19, 2012 at 20:53
  • To limit fallout I believe you can set it on a per-database level (ALTER DATABASE foo SET standard_conforming_strings=true) -- never tried this myself, but there's a promising Postgres mailing list post about it
    – voretaq7
    Jun 19, 2012 at 21:13

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .