AMaViS, SpamAssassin, roundcube and mySQL – a wedding story

Some time ago I blogged about fighting spam with amavis for the Kolab community. Now the story continues by means of the roundcube integration with amavis.

As earlier mentioned spamassassin is able to store recipient-based preferences in a mysql table with some settings in its local.cf (see spamassassin wiki)

# Spamassassin for Roundcubemail
# http://www.tehinterweb.co.uk/roundcube/#pisauserprefs
user_scores_dsn DBI:mysql:ROUNDCUBEMAILDBNAME:localhost:3306
user_scores_sql_password ROUNCUBEMAILPASSWORD
user_scores_sql_username ROUNDCUBEMAILDBUSERNAME
user_scores_sql_custom_query SELECT preference, value FROM _TABLE_ WHERE username = _USERNAME_ OR username = '$GLOBAL' OR username = CONCAT('%',_DOMAIN_) ORDER BY username ASC

However, accessing this with amavis is a real bis problem for many users. Amavis has it’s own user-based configuration policies, but email-plugins as the roundcubemail plugin sauserprefs often only use spamassassin and not amavis. Originally, SA was only called once per message by amavis and therefore recipient-based preferences were not possible at all. This has changed. Now you can use the options @sa_userconf_maps and @sa_username_maps to perform such lookups. Unfortunately these options are still poorly documented. We use them anyway.

The values in @sa_userconf_maps define where amavis has to look for the user preferences. I use mySQL lookups for all recipient addresses.

# use userpref SQL connection from SA local.cf for ALL recipients
@sa_userconf_maps = ({
  '.' => 'sql:'
});

The variable @sa_username_maps tells amavis what to pass to spamassassin as _USERNAME_ (see above) for the mySQL lookup. By default the amavis system user is used. In my setup with Kolab and sauserprefs I use here a regexp which is supposed to match the recipient email-address:

# use recipient email address as _USERNAME_ in userpref mySQL table (_TABLE_)
@sa_username_maps = new_RE (
  [ qr'^([^@]+@.*)'i => '${1}' ]
);

With these additional bits sauserprefs should work. However it seems to me that the string “*** Spam ***”, which should be added to the subject does not work (maybe it does in the most recent version). The thresholds do, though, but better check it carefully.

Did you succeed? Comments are appreciated!

About

Peter Pan. Kann fliegen mit Feenstaub.

Tagged with: , , ,
Posted in Technik
7 comments on “AMaViS, SpamAssassin, roundcube and mySQL – a wedding story
  1. Anonymous says:

    thanks for your useful tips, I follow your setting,it is work but only for blacklist and whitelist,
    userprefs required_score and is not correct, it is alway use sa_tag2_level_deflt ,
    it is bug or my wrong config? thanks

  2. Anonymous says:

    I also has this issue,
    I using amavisd 2.9.1 with SA 3.3.1 ,
    I setting Score threshold = 9 and Subject tag =”*****SPAAM*****”
    all not work,

    • Anonymous says:

      below for the source hear
      X-Spam-Status: Yes, score=13.351 tagged_above=2 required=6.2

      Subject: ***Spam*** Fw: Please Treat As Confidential

  3. laclaro says:

    In my setup, increasing the threshold 10 leads to the expected behavior:

    X-Spam-Status: No, score=5.51 required=10 tests=[BAYES_99=5.308,
    BAYES_999=0.2, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001]
    autolearn=disabled

    The mysql userpref table within the roundcube database:

    CREATE TABLE IF NOT EXISTS `userpref` (
    `id` int(8) unsigned NOT NULL AUTO_INCREMENT,
    `username` varchar(128) NOT NULL DEFAULT ”,
    `preference` varchar(64) NOT NULL DEFAULT ”,
    `value` varchar(128) DEFAULT NULL,
    `descript` varchar(128) DEFAULT NULL,
    `added` datetime NOT NULL DEFAULT ‘2014-01-01 00:00:00′,
    `added_by` varchar(128) NOT NULL DEFAULT ”,
    `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `id` (`id`),
    KEY `type` (`preference`),
    KEY `added_by` (`added_by`),
    KEY `preference` (`preference`),
    KEY `username` (`username`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT=’Spamassassin Preferences’ AUTO_INCREMENT=25;

    And notice that the lines in you spamassassin config local.cf cannot contain linebreaks. One variable, one line. This hold especially for the SQL query user_scores_sql_custom_query.

    Although, the threshold works I’m not sure which other options work. Just try it.

    Best regards
    Henning

    • Oliver says:

      Hello Henning,

      thanks for helping with your post on configuring this but I’m still stuck on understanding part of the process. What is working so far: Roundcube plugin is writing rules to the roundcube database table userpref, if I call spamd from command line to test with articifical messages then spamassassin is actually looking at that database and pulls the rules from it if the user exists. So that is all fine but I can not get amavis to do the same.

      When using sa_userconf_maps where is the actual lookup of the user data taking place, is amavis doing the connection? if so how can it know which database to connect, I would have to tell amavis the roundcube db access data and I have not done that yet but I find this part in no tutorial so far. Please help

      Thanks

      • laclaro says:

        Amavis also is capable to do the user preferences lookups following a different table structure scheme than spam assassin does. Since we want to use the roundcubemail plugin which was written to use SA’s scheme, we only tell amavis, that SA takes care of sql-lookups.

        I think this is only to make amavis accept, that there could be missing options in the config, since those are pulled from sql. But I’m not sure about that.

        regards,
        Henning

  4. laclaro says:

    I recently noticed, that thresholds do not work anymore. I do not know why. Updated something? Do you experience the same?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Posts by topic…
…by month
Have a look at…


%d bloggers like this: