use MySQL for Spamassassin prefs

Published on Author righterLeave a comment

If you want tou use a MySQL DB for different Spamassassin prefs:

Create DB and global Prefs:

mysql

create database spamassassin;
CREATE TABLE userpref ( username varchar(100) NOT NULL default '', preference varchar(30) NOT NULL default '', value varchar(100) NOT NULL default '', prefid int(11) NOT NULL auto_increment, PRIMARY KEY (prefid), KEY username (username) );
grant all on spamassassin.userpref to 'spamassassin'@'localhost' identified by 'PASSWORD';
insert into userpref (username, preference, value) values ('$GLOBAL', 'required_hits', '4.00');
insert into userpref (username, preference, value) values ('$GLOBAL', 'score USER_IN_WHITELIST', '-10');
insert into userpref (username, preference, value) values ('$GLOBAL', 'score USER_IN_BLACKLIST', '10');
insert into userpref (username, preference, value) values ('$GLOBAL', 'report_safe', '0');
insert into userpref (username, preference, value) values ('$GLOBAL', 'use_razor2', '1');
insert into userpref (username, preference, value) values ('$GLOBAL', 'use_pyzor', '1');
insert into userpref (username, preference, value) values ('$GLOBAL', 'use_bayes', '1');
insert into userpref (username, preference, value) values ('$GLOBAL', 'rewrite_header Subject', '[SPAM-Score]-');

Create config /etc/mail/spamassassin/sql.cf

user_scores_dsn DBI:mysql:spamassassin:localhost:3306
user_scores_sql_password PASSWORD
user_scores_sql_username spamassassin
user_scores_sql_custom_query SELECT preference, value FROM _TABLE_ WHERE username = _USERNAME_ OR username = '$GLOBAL' OR username = CONCAT('%',_DOMAIN_) ORDER BY username ASC

Add –nouser-config –sql-config to /etc/default/spamassassin

OPTIONS="--create-prefs --max-children 5 --helper-home-dir -x -u vmail --virtual-config-dir=/var/vmail/.spamassassin --nouser-config --sql-config"

Restart spamasssssin

service spamassassin restart

Now test and you should see the score which you have configured globally:

echo -e "From: user\nTo:user\Subject: Test\n\n" | spamc -u '$GLOBAL'

Now you can add specific domain config:

insert into userpref (username, preference, value) values ('%nmgi.com', 'whitelist_from', '*@hp.com');

or you can use user specific config:

insert into userpref (username, preference, value) values ('dallase@nmgi.com', 'required_hits', '4.10');

In my config spamassin showed only:

spamd: processing message for m:5000

The domain was missing, I had to put -e domain.ch in /etc/default/spamass-milter

OPTIONS="-u spamass-milter -i 127.0.0.1 -e domain.ch

Leave a Reply

Your email address will not be published. Required fields are marked *