spamassassin chokes when using PostgreSQL as Bayes store

[Martin Emrich]

Binary package hint: spamassassin

Hi!

Since moving to Postgres as the storage backend for the bayes database, I get lots of these in my syslog:

May 12 18:36:17 sauron spamd[4803]: HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
May 12 18:36:18 sauron spamd[4807]: WARNING: nonstandard use of \\ in a string literal
May 12 18:36:18 sauron spamd[4807]: LINE 1: select put_tokens(3, '{"\\\\001\\\\316\\\\152\\\\330\\\\076"...

Please consider backporting this fix to hardy:
https://issues.apache.org/SpamAssassin/show_bug.cgi?id=5730

Martin

[Scott Kitterman (kitterman)]

I'm working on getting this fix into the next Ubuntu release (Intrepid). If you think it's worth fixing for the current release, please provide a detailed test case and impact statement so that others (without a lot of sa or pg experience) can replicate the problem and validate the solution.

[Launchpad Janitor (janitor)]

This bug was fixed in the package spamassassin - 3.2.4-1ubuntu2

---------------
spamassassin (3.2.4-1ubuntu2) intrepid; urgency=low

  * Add change from upstream to work with Postgresql 8.1 and later bayes store
    (LP: #229646)

 -- Scott Kitterman <email address hidden> Mon, 12 May 2008 14:28:27 -0400

[Martin Emrich (emme)]

Hi!

Try this to reproduce the bug:

Install hardy server i386, without any extras.

Install Packages:
• postgresql-8.3
• spamassassin
• libdbd-pg-perl
• libpg-perl

Create spamassassin user+db on postgres:
• $ sudo -i
• # su - postgres
• $ createuser -S -D -R -P sa
• enter passwort "sa" twice
• $ createdb -O sa sa
• $ exit

Create spamassassin Database tables, etc.
• # psql -h localhost -U sa -d sa
• > create language plpgsql;
• > \i /usr/share/doc/spamassassin/sql/bayes_pg.sq
* [CTRL-D]

Configure Spamassassin to use postgres: Add this to /etc/spamassassin/local.cf:

use_bayes 1
bayes_store_module Mail::SpamAssassin::BayesStore::PgSQL
bayes_sql_dsn DBI:Pg:dbname=sa
bayes_sql_override_username sa
bayes_sql_username sa
bayes_sql_password sa
bayes_auto_learn 1

Now, try to learn a mail:
# sa-learn --spam /usr/share/doc/spamassassin/examples/sample-spam.txt

Now you'll see the PostgreSQL error messages.

Ciao

Martin

[Scott Kitterman (kitterman)]

Looks reasonable. I'll give it a try and see what I can do.

[John Dong (jdong)]

The patch linked from the upstream bug seems reasonable. There seems to be concrete examples to reproduce the bug in the comment above and also on the linked bug report, but please include them in the bug description. Otherwise, MOTU-SRU says go ahead :)

https://issues.apache.org/SpamAssassin/attachment.cgi?id=4193