spamassassing userpref SQL lookup by amavis

Henning henne.gwath at
Thu Sep 11 14:14:36 CEST 2014


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

----- 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 = 
----- snip -----

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

Using the options @sa_userconf_maps and @sa_username_maps has to be used 
to do the recipient-based SQL lookup through amavis

I therefore added the following to my amavis config:

----- snip -----
# use userpref SQL connection from SA 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:


More information about the amavis-users mailing list