SpamAssassin

SpamAssassin Notes
sa-learn -u  --dump magic for a in `echo "SELECT username FROM bayes_vars;" |mysql -N spamassassin`; do echo "Starting $a"; \ sa-learn --showdots -u $a --force-expire; done echo " select username, spam_count, ham_count, last_expire, last_expire_reduce from bayes_vars;" \ |mysql -u postfix -ppostfix spamassassin -t
 * To display the current status of the Bayes database per user.
 * To Expire your database per user
 * Status of the Bayes Users

Installing Required RPM's
yum -y install spamassassin perl-Mail-SPF-Query.noarch razor-admin

Configuring Spamassassin (Local Users)
Inorder to enable the Languages & DCC check you must enable them in /etc/mail/spamassassin/v310.pre vim /etc/mail/spamassassin/v310.pre Then for the Config File vim /etc/mail/spamassassin/local.cf

required_score          5.0 rewrite_header Subject [SPAM] report_safe            1 use_bayes              1 bayes_auto_learn       1 skip_rbl_checks        0 use_razor2             1 use_dcc                1 use_pyzor              1 ok_languages           en ok_locales              en
 * 1) How many hits before a message is considered spam.
 * 1) Change the subject of suspected spam
 * 1) Encapsulate spam in an attachment (0=no, 1=yes, 2=safe)
 * 1) Enable the Bayes system
 * 1) Enable Bayes auto-learning
 * 1) Enable or disable network checks
 * 1) Mail using languages used in these country codes will not be marked
 * 2) as being possibly spam in a foreign language.
 * 1) Mail using locales used in these country codes will not be marked
 * 2) as being possibly spam in a foreign language.

Configuring Spamassassin (MySQL & Virtual Users)
With this setup you will be saving the user preferences and bayes tokens in a MySQL data source. But, while scanning a message if spamd is unable to connect to the server specified in user_scores_dsn (below) or an error occurs when querying the SQL server then spam checking will not be performed on that message.

Building the Database
First we need to build the database table. You may download mine below. Then import the file into the spamassassin table on your MySQL server. wget http://svn.apache.org/repos/asf/spamassassin/trunk/sql/bayes_mysql.sql wget http://wiki.mattrude.com/images/a/a7/Spamassassin_userpref.sql mysql -h localhost -u postfix -ppostfix spamassassin < bayes_mysql.sql mysql -h localhost -u postfix -ppostfix spamassassin < Spamassassin_userpref.sql Or just create it your self, here's the users's tables CREATE TABLE IF NOT EXISTS userpref (    username VARCHAR( 100 ) NOT NULL,     preference VARCHAR( 100 ) NOT NULL ,     value VARCHAR( 100 ) NOT NULL ,     prefid INT( 11 ) NOT NULL AUTO_INCREMENT ,     PRIMARY KEY ( `prefid` ) ,     INDEX ( `username` ) );
 * [[Media:Spamassassin.sql|SpamAssassin Bayes tables]]
 * [[Media:Spamassassin_userpref.sql‎|Spamassassin User Preferences table]]

The Auto White List CREATE TABLE IF NOT EXISTS awl (    username varchar(100) NOT NULL,     email varchar(200) NOT NULL,     ip varchar(16) NOT NULL,     count int(11) default '0',     totscore float default '0',     PRIMARY KEY  (username,email,ip) );

And the bayes tables CREATE TABLE bayes_expire (  id int(11) NOT NULL default '0',   runtime int(11) NOT NULL default '0',   KEY bayes_expire_idx1 (id) ) ENGINE=InnoDB; CREATE TABLE bayes_global_vars (  variable varchar(30) NOT NULL default ,   value varchar(200) NOT NULL default ,   PRIMARY KEY  (variable) ) ENGINE=InnoDB; INSERT INTO bayes_global_vars VALUES ('VERSION','3'); CREATE TABLE bayes_seen (  id int(11) NOT NULL default '0',   msgid varchar(200) binary NOT NULL default ,   flag char(1) NOT NULL default ,   PRIMARY KEY  (id,msgid) ) ENGINE=InnoDB; CREATE TABLE bayes_token (  id int(11) NOT NULL default '0',   token binary(5) NOT NULL default '',   spam_count int(11) NOT NULL default '0',   ham_count int(11) NOT NULL default '0',   atime int(11) NOT NULL default '0',   PRIMARY KEY  (id, token),   INDEX bayes_token_idx1 (id, atime) ) ENGINE=InnoDB; CREATE TABLE bayes_vars (  id int(11) NOT NULL AUTO_INCREMENT,   username varchar(200) NOT NULL default '',   spam_count int(11) NOT NULL default '0',   ham_count int(11) NOT NULL default '0',   token_count int(11) NOT NULL default '0',   last_expire int(11) NOT NULL default '0',   last_atime_delta int(11) NOT NULL default '0',   last_expire_reduce int(11) NOT NULL default '0',   oldest_token_age int(11) NOT NULL default '2147483647',   newest_token_age int(11) NOT NULL default '0',   PRIMARY KEY  (id),   UNIQUE bayes_vars_idx1 (username) ) ENGINE=InnoDB; To set the version number in the database, run the following. INSERT INTO bayes_global_vars VALUES ('VERSION','3'); INSERT INTO userpref (username, preference, value, prefid) VALUES ('$GLOBAL', 'required_score', '5.0', NULL); INSERT INTO userpref (username, preference, value, prefid) VALUES ('$GLOBAL', 'rewrite_header Subject', '[SPAM]', NULL); INSERT INTO userpref (username, preference, value, prefid) VALUES ('$GLOBAL', 'report_safe', '1', NULL); INSERT INTO userpref (username, preference, value, prefid) VALUES ('$GLOBAL', 'fold_headers', '1', NULL); INSERT INTO userpref (username, preference, value, prefid) VALUES ('$GLOBAL', 'add_header all Level', '_STARS(*)_', NULL); INSERT INTO userpref (username, preference, value, prefid) VALUES ('$GLOBAL', 'remove_header all', '0', NULL); INSERT INTO userpref (username, preference, value, prefid) VALUES ('$GLOBAL', 'use_bayes', '1', NULL); INSERT INTO userpref (username, preference, value, prefid) VALUES ('$GLOBAL', 'bayes_auto_expire', '1', NULL); INSERT INTO userpref (username, preference, value, prefid) VALUES ('$GLOBAL', 'bayes_auto_learn', '1', NULL); INSERT INTO userpref (username, preference, value, prefid) VALUES ('$GLOBAL', 'bayes_auto_learn_threshold_spam', '9', NULL); INSERT INTO userpref (username, preference, value, prefid) VALUES ('$GLOBAL', 'bayes_auto_learn_threshold_nonspam', '1', NULL); INSERT INTO userpref (username, preference, value, prefid) VALUES ('$GLOBAL', 'skip_rbl_checks', '0', NULL); INSERT INTO userpref (username, preference, value, prefid) VALUES ('$GLOBAL', 'use_razor2', '1', NULL); INSERT INTO userpref (username, preference, value, prefid) VALUES ('$GLOBAL', 'use_pyzor', '1', NULL); INSERT INTO userpref (username, preference, value, prefid) VALUES ('$GLOBAL', 'use_dcc', '1', NULL); INSERT INTO userpref (username, preference, value, prefid) VALUES ('$GLOBAL', 'ok_languages', 'en', NULL); INSERT INTO userpref (username, preference, value, prefid) VALUES ('$GLOBAL', 'ok_locales', 'en', NULL);

Building the Configuration file
Now setup the configuration. loadplugin     Mail::SpamAssassin::Plugin::AWL loadplugin     Mail::SpamAssassin::Plugin::Bayes loadplugin     Mail::SpamAssassin::Plugin::DCC loadplugin     Mail::SpamAssassin::Plugin::TextCat user_scores_dsn                DBI:mysql:spamassassin:localhost user_scores_sql_username       postfix user_scores_sql_password       postfix bayes_store_module             Mail::SpamAssassin::BayesStore::MySQL bayes_sql_dsn                  DBI:mysql:spamassassin:localhost bayes_sql_username             postfix bayes_sql_password             postfix
 * /etc/mail/spamassassin/local.cf

DCC & AutoWhite lists are alos enabled in v310.pre, but not in version 3.3+ of SpamAssassin.

The spamd server will not pay attention to SQL preferences by default, even with user_scores_dsn set in the config files. You must startup spamd with the proper options (ie -q or -Q). If the user_scores_dsn option does not exist, SpamAssassin will not attempt to use SQL for retrieving users' preferences.

Starting SpamAssassin with the new configuration
SpamAssassin needs to be ran with the options similar to this: /usr/bin/spamd -d -x -q -Q -u nobody -r /var/run/spamd.pid I believe the best way of doing this is modify your /etc/init.d/spamassassin init file and change SPAMDOPTIONS to: SPAMDOPTIONS="-d -m5 -x -q -Q -u nobody" make sure /etc/sysconfig/spamassassin dosn't override your settings run the below command to confirm spamassassin is running correctly ps -eaf |grep spamd

SpamAssassin with MySQL Notes and Links

 * http://wiki.apache.org/spamassassin/UsingSQL
 * http://sourceforge.net/projects/php-sa-mysql
 * http://svn.apache.org/repos/asf/spamassassin/tags/spamassassin_current_release_3.2.x/sql/README
 * http://squirrelmail.org/plugins_category.php?category_id=3

To Update Spam Assassin's Rules
sa-update --updatedir /etc/mail/spamassassin && /etc/init.d/spamassassin restart You can add an entry like below to your root users crontab. 13 */2 * * * /usr/bin/sa-update --updatedir /etc/mail/spamassassin && /sbin/service spamassassin restart Note: This entry will only update Spam Assassin's rules once per hour.

Testing & Rules

 * http://spamassassin.apache.org/gtube/
 * http://www.rulesemporium.com/rules.htm
 * SpamAssassin Configuration Generator

sa-update gpg error
Try updating the GPG keys by running: wget http://spamassassin.apache.org/updates/GPG.KEY sa-update --import GPG.KEY