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.
Thanks to the FreeBSD ports collection this part is amazingly simple once you actually have FreeBSD installed (if starting from scratch).
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.
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.
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).
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:
Now start MySQL:
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:
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.
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
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
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.
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 firstname.lastname@example.org
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.
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
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
Now that SA is fully configured, it's time to start it:
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.
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.
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 *.
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.
IT业界 | 邮件服务器 | 邮件趣闻 | 移动电邮
电子邮箱 | 反垃圾邮件|邮件客户端|网络安全
行业数据 | 邮件人物 | 网站公告 | 行业法规
邮件原理 | 网络协议 | 网络管理 | 传输介质
线路接入 | 路由接口 | 邮件存储 | 华为3Com
CISCO技术 | 网络与服务器硬件
Windows 9X | Linux&Uinx | Windows NT
Windows Vista | FreeBSD | 其它操作系统
程序与开发 | Exchange | Qmail | Postfix
Sendmail | MDaemon | Domino | Foxmail
KerioMail | JavaMail | Winwebmail |James
Merak&VisNetic | CMailServer | WinMail
金笛邮件系统 | 其它 |
Outlook | Foxmail | DreamMail| KooMail
The bat | 雷鸟 | Eudora |Becky! |Pegasus
电子邮箱: 个人邮箱 | 企业邮箱 |Gmail
移动电子邮件:服务器 | 客户端 | 技术前沿
软件漏洞 | 安全知识 | 病毒公告 |防火墙
攻防技术 | 病毒查杀| ISA | 数字签名
Email营销 | 网络营销 | 营销技巧 |营销案例
邮件人才:招聘 | 职场 | 培训 | 指南 | 职场
邮件系统|反垃圾邮件 |安全 |移动电邮 |招标
邮件系统 |反垃圾邮件 |邮箱 |安全 |客户端