Jump to content

SpamAssassin

From MattWiki

SpamAssassin Notes[edit | edit source]

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

Installing Required RPM's[edit | edit source]

yum -y install spamassassin perl-Mail-SPF-Query.noarch razor-admin

Configuring Spamassassin (Local Users)[edit | edit source]

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

# How many hits before a message is considered spam.
required_score           5.0

# Change the subject of suspected spam
rewrite_header Subject [SPAM]

# Encapsulate spam in an attachment (0=no, 1=yes, 2=safe)
report_safe             1

# Enable the Bayes system
use_bayes               1

# Enable Bayes auto-learning
bayes_auto_learn        1

# Enable or disable network checks
skip_rbl_checks         0
use_razor2              1
use_dcc                 1
use_pyzor               1

# Mail using languages used in these country codes will not be marked
# as being possibly spam in a foreign language.
ok_languages            en

# Mail using locales used in these country codes will not be marked
# as being possibly spam in a foreign language.
ok_locales              en

Configuring Spamassassin (MySQL & Virtual Users)[edit | edit source]

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[edit | edit source]

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` )
);

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[1] 

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[edit | edit source]

Now setup the configuration.

  • /etc/mail/spamassassin/local.cf
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

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

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[edit | edit source]

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[edit | edit source]

To Update Spam Assassin's Rules[edit | edit source]

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[edit | edit source]

Troubleshooting SpamAssassin[edit | edit source]

sa-update gpg error[edit | edit source]

Try updating the GPG keys by running: 

wget http://spamassassin.apache.org/updates/GPG.KEY
sa-update --import GPG.KEY

References[edit | edit source]

  1. ↑ http://svn.apache.org/repos/asf/spamassassin/trunk/sql/bayes_mysql.sql
  2. ↑ UsingDcc - Spamassassin Wiki
  3. ↑ AutoWhitelist - Spamassassin Wiki
Retrieved from "https://wiki.mattrude.com/index.php?title=SpamAssassin&oldid=7303"