spamassassing userpref SQL lookup by amavis

[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