My SpamAssassin MySQL How-To

From Wistful.net

Contents [hide] 1 About 2 Requirements 3 Installing the software 3.1 FreeBSD 3.2 joe 3.3 sudo 3.4 MySQL 3.5 SpamAssassin 4 The Database 4.1 Create the database and user 4.2 Create the tables 5 Configure SpamAssassin 5.1 User Defaults 5.2 User Settings 5.3 Auto-Whitelisting 5.4 Bayesean 5.5 Start SpamAssassin 6 Maintenance 6.1 Script and SQL query 6.2 Kicking it off 7 The End

About

This is how to set up SpamAssassin for use with a MySQL server for storing user preferences, the AWL data, and bayesean data. This how-to has no information on how to interface SA with your SMTP server or to edit user preferences. That information will be covered in a different how-to at a later date.

It is also assumed that you have the very basics of Unix administration, and in particular familiarity with FreeBSD which is my OS of choice.

Feel free to contact me but be aware that if you have a problem with making this work on Linux you're pretty much on your own. This document will help you quite a lot but you'll still need to know how to do many of the installation type things on your own and where the various files are to make the appropriate changes to them.

Requirements

• FreeBSD 6.x.x - Obviously you can do this on other BSDs or Linux but I'll be writing this for FreeBSD.
• MySQL 4.x.x - Currently I'm using 4.0.x and have so far not run in to any issues, but I've read that using less than version 4.1.x can cause problems. You should not even consider trying this with 3.x.x. Using version 5.x.x might work but I haven't tried it yet.
• SpamAssassin 3.1.x - It's the latest and greatest (right now), baby.

Installing the software

Thanks to the FreeBSD ports collection this part is amazingly simple once you actually have FreeBSD installed (if starting from scratch).

FreeBSD

You should use nothing less than version 6.0-RELEASE, and preferably should be using the latest 6.x release. Once installed make sure to use CVSUP to get the latest world sources as well as an updated ports tree, etc. Once done build and install the world. Next use ports to install the latest versions of perl, OpenSSL, OpenSSH, etc. And don't forget to setup a firewall.

joe

Installing joe is optional. I myself prefer joe over vi, though vi is on every single decent OS right out of the box.

cd /usr/local/editors/joe
make install && make clean
rehash

Now that joe is installed, let's set it up for use as our preferred editor for some system commands. We can do this by using "joe ~/.cshrc" and adding the following:

setenv  VISUAL          /usr/local/bin/joe
setenv  EDITOR          $VISUAL

Actually, you may be changing the EDITOR line instead of adding it. Once you've made these changes hit Ctrl-K X (Ctrl-K, then X) to save the file and exit joe in one move. You can now either enter those commands at the prompt or just log out and back in for them to take effect. The file ~/.cshrc is for the csh and tcsh shells - if you are using bash or zsh you're on your own and/or will just have to skip that bit. Be aware that joe leaves backup files around with a ~ (tilde) on the end of the file, such as .cshrc~ so be aware of that. Those backups can save you a lot of headaches, but they can also lead to major trouble if they're of particular files.

sudo

You're going to want to install and use sudo:

cd /usr/ports/security/sudo
make install && make clean
rehash

Once installed, run visudo as root and use the line for root as a template for your normal user account. On a stock system visudo will use vi as the editor, but will use whatever is specified in the VISUAL environment variable which has now been set to joe in the above section (if you elected to do that).

MySQL

If you already have MySQL installed then you can skip this step, otherwise do the following to install MySQL 4.0.x:

cd /usr/ports/databases/mysql40-server/
make install && make clean
rehash

Set the system to start MySQL at boot by adding the following to /etc/rc.conf:

mysql_enable="yes" 

Now start MySQL:

/usr/local/etc/rc.d/mysql-server.sh start

SpamAssassin

If you don't already have SA installed, then install the latest SA, which is currently 3.1.4 :

cd /usr/ports/mail/p5-Mail-SpamAssassin
make install && make clean
rehash

If you don't have the latest SA then you should upgrade at this point. You can use portupgrade to easily upgrade the SA system itself, but some config options will need to be manually updated. In that case you'll have to read the SA updating file for details on that part of the operation.

Set the system to start SA at boot time and which what options to use by adding the following to /etc/rc.conf:

spamd_enable="yes"
spamd_flags="--daemonize --sql-config --nouser-config --username=nobody"

Since we haven't configured SA yet we don't want to start it yet, but when it is time to do so, use the following:

/usr/local/etc/rc.d/sa-spamd.sh start

The Database

Assuming a fresh install of MySQL you won't have any databases we can use for this, so we'll have to create one before we can create the tables to populate it with. But first you'll need to have access to the MySQL daemon. By default it has a root user with a blank password. For now that's fine, but you really really need to change that to something else.

Create the database and user

First we need to create the SA user and database in MySQL. This will also set the permissions needed in order for SA to do everything it'll be doing. First the command to connect/login to MySQL:

mysql -h localhost -u root -p

Now the commands for MySQL do do what we want:

use mysql;
insert into user (Host, User, Password) values('localhost','spamassassin',password("SApassword"));
insert into db (Host, Db, User, Select_priv, Insert_priv, Update_priv, Delete_priv) values('localhost','spamassassin','spamassassin','Y','Y','Y','Y');
create database spamassassin;
quit

Notice the SApassword in the password clause? You need to pick a password and substitute it for that at every spot in this how-to where SApassword appears.

Now you need to reload MySQL so it will recognise the user we created:

mysqladmin -h localhost -u root -p reload

Create the tables

There are three files we're going to have to use in order to create all of the tables we'll need.

cd /usr/local/share/doc/p5-Mail-SpamAssassin/sql
mysql -h localhost -u root -p spamassassin < userpref_mysql.sql
mysql -h localhost -u root -p spamassassin < awl_mysql.sql
mysql -h localhost -u root -p spamassassin < bayes_mysql.sql

Configure SpamAssassin

SpamAssassin saves most of its site config options in a file called local.cf. You can find this file and others in /usr/local/etc/mail/spamassassin/ on FreeBSD when installing SA from the ports collection.

User Defaults

These are the initial settings used for all users w/o any other settings. I feel that these defaults should work very well for most people, but obviously you can change them to suit your needs.

required_hits                   5
rewrite_header                  Subject [SPAM _HITS_]
report_safe                     0
report_contact                  postmaster@domain.tld

• Setting required_hits to 5 seems to mark most spam properly.
• The rewrite_header line above will cause messages marked as spam to have their subjects start off like "[SPAM 8.2]". This will allow users to see that the message is (or most likely is) spam and how likely it is to be. A lower score is less likely to be spam whereas a higher is almost certainly spam.
• The report_safe option leaves the message body alone. In the case of a false-positive the user will still be able to read and use the message as it, but could still fall prey to a bug/flaw in their mail client, etc. Changing the option to 1 will cause the message to be encapsulated in an attatchment, and changing it to 2 does the same but also changes the type to text/plain which can render the message pretty broken.
• The report_contact option is for specifying the email address which will be used by SA in various messages. This should be your postmaster account.

User Settings

In your local.cf add the following:

user_scores_dsn                 DBI:mysql:spamassassin:localhost
user_scores_sql_username        spamassassin
user_scores_sql_password        SApassword
user_scores_sql_custom_query    SELECT preference, value FROM userpref WHERE username = _USERNAME_ OR username = '@GLOBAL' OR username = '@~'||_DOMAIN_ ORDER BY username ASC

With this custom SQL query you can place some or even all of your default system-wide SA settings for users w/o those settings. I use this query just in case I decide to place the default user preferences in the database, but mostly I use SAs local.cf file instead. The upside to using the database for this is that changes happen right away with no downtime to the server.

Auto-Whitelisting

In your local.cf add the following:

auto_whitelist_factory          Mail::SpamAssassin::SQLBasedAddrList
user_awl_dsn                    DBI:mysql:spamassassin:localhost
user_awl_sql_username           spamassassin
user_awl_sql_password           SApassword
user_awl_sql_table              awl

Bayesean

In your local.cf add the following:

bayes_store_module              Mail::SpamAssassin::BayesStore::MySQL
bayes_sql_dsn                   DBI:mysql:spamassassin:localhost
bayes_sql_username              spamassassin
bayes_sql_password              SApassword

Start SpamAssassin

Now that SA is fully configured, it's time to start it:

/usr/local/etc/rc.d/sa-spamd.sh start

Maintenance

As time goes on and mail flows in/out of your server, your AWL data will begin to grow. How much it grows and at what rate all depends on how much mail your server handles. As such there will be some entries which are not needed and should be cleaned out. Entries which should be cleaned out are ones which have only one hit. This will remove some very recent entries, but on the whole it'll do a fair job. Some of the entries removed will be spam which you'll often only get one of from each address.

Script and SQL query

Create a new file called /usr/local/etc/awl-trim.sh and populate it with the following:

#!/bin/sh
/usr/local/bin/mysql -uspamassassin -pSApassword < /usr/local/etc/awl-trim.sql
exit 0

Now we need to create the file our SQL query will reside in for input to our script. The file will be /usr/local/etc/awl-trim.sql and should have the following content:

USE spamassassin;
DELETE FROM awl WHERE count="1";

Be sure to leave an extra line at the end of both files so the exit command and query are both properly executed.

Next we need to set ownership and permissions:

chown root:wheel /usr/local/etc/awl-trim.sh
chown root:wheel /usr/local/etc/awl-trim.sql
chmod ug+x,o-rwx /usr/local/etc/awl-trim.sh
chmod o-rwx /usr/local/etc/awl-trim.sql

Since we have a critcal password in the query script we want to make sure it's a little hard to get at it which is why we chmod o-rwx the file, and we don't need anyone else seeing or running the script itself. It is not fool-proof by any means so make sure you keep and eye on your system, don't run or install anything on that doesn't need to be, etc.

Go ahead and try to run the awl-trim.sh script now in order to verify that it executes cleanly and no errors are output. If any are then you need to resolve them before continuing.

Kicking it off

Next we need to get our script run on a weekly basis, and to do that we'll have cron run the script as root at 1am on Sunday. Use this command to add our entry to roots crontab file:

crontab -u root -e

And add the following lines:

#minute hour    mday    month   wday    command
0       1       *       *       6       /usr/local/etc/awl-trim.sh

The comment line is one I like to add just to remind me what each of the fields represent so you can feel free to keep it or leave it out. If you want to run the script once every two weeks (about), you could set the mday to 1,15 and wday to *. Or how about once a month? Set mday to 1 and wday to *.

The End

And that should be it. All of your user preferences, AWL and bayesean data will now be pulled from your MySQL server. Check your logs to make sure no errors are being logged by SA while its running to make sure it's working properly.

All of the above information is freely available in many places on the internet, but if you found this to be helpful then drop me a line at (image), or if you really really found it helpful then feel free to visit my Amazon.com wishlist. By the way, this page was created by me and is therefore copyrighted by me. If you copy it you must link back to this page and give me credit for it. Derivative works must also do the same.