# Trouble with backslash characters and rsyslog writing to postgres

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=<-----@----.--->, 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=<-----@----.--->, 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 \\?

-

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

-
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 '12 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 '12 at 21:13
Thanks for the additional tip. –  Flimzy Jun 19 '12 at 21:13