Postgresql – Trouble with backslash characters and rsyslog writing to postgres

postgresqlrsyslog

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

Best Answer

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

Related Topic