spamassassing userpref SQL lookup by amavis

[Henning]

Hi!

This setup worked, well. However, I experience a problem, since I 
introduced user_pref:
Spamassassin does not honor my $spam_tag2_level anymore.

Other preferences, set by amavis work as before. I searched my whole 
system and commented every single require_score. No success. Also, if I 
comment the user_pref stuff in local.cf and amavisd.conf again, only 
require_score values are recognized by SA, not $spam_tag2_level.

Please help me with this!

best regards
Henning

Am 11.09.2014 14:14, schrieb Henning:
> Hi,
>
> Finally I succeeded setting up an an SQL userpref lookup for
> spamassassin through amavis and I have to share it!
>
> With spamassassin an SQL lookup for user based preferences is easily
> done by using these lines in SA's local.cf
>
> ----- snip -----
> user_scores_dsn                  DBI:mysql:DATABASE:localhost:3306
> user_scores_sql_password         PASSWORD
> user_scores_sql_username         USER
> user_scores_sql_custom_query     SELECT preference, value FROM _TABLE_
> WHERE username = _USERNAME_ OR username = '$GLOBAL' OR username =
> CONCAT('%',_DOMAIN_) ORDER BY username ASC
> ----- snip -----
>
> https://wiki.apache.org/spamassassin/UsingSQL
> http://laclaro.wordpress.com/2014/02/22/fighting-spam-with-amavis/
>
> 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 often use spamassassin and not amavis, see
>
> https://github.com/JohnDoh/Roundcube-Plugin-SpamAssassin-User-Prefs-SQL
>
> Using the options @sa_userconf_maps and @sa_username_maps has to be used
> to do the recipient-based SQL lookup through amavis
>
> http://lists.amavis.org/pipermail/amavis-users/2011-May/000326.html
> http://de.postfix.org/pipermail/amavis-users/2011-August/000708.html
>
> I therefore added the following to my amavis config:
>
> ----- snip -----
> # use userpref SQL connection from SA local.cf for ALL recipients
> @sa_userconf_maps = ({
>    '.' => 'sql:'
> });
> # use recipient email address as "username" in "userpref" mySQL table
> @sa_username_maps = new_RE (
>    [ qr'^([^@]+ at .*)'i => '${1}' ]
> );
> ----- snip -----
>
> Read more about my configuration here:
> https://laclaro.wordpress.com
>
> regards,
> Henning
>