Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.