As we saw in the first part of the guide, all spamassassin data is system-wide so, if a user wants to lower the minimum score for a spam message, he can’t.

We are going to generate a lot of information, first create a new database, and grant access to a new user with generated password:

$ pwgen # To generate a password
$ mysql -u root -p*********** localhost
mysql> CREATE SCHEMA SpamAssassin;
Query OK, 1 row affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON SpamAssassin.* TO 'spamassassin'@'localhost' IDENTIFIED BY '4ubnIt5Jn16U'; 
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `SpamAssassin`.`userpref` (
  `id` int(8) unsigned NOT NULL auto_increment,
  `username` VARCHAR(255) NOT NULL default '',
  `preference` VARCHAR(64) NOT NULL default '',
  `value` VARCHAR(128) default NULL,
  `description` VARCHAR(255) DEFAULT NULL,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `author` VARCHAR(128) NULL,
  `modified` TIMESTAMP NULL,
  UNIQUE KEY `id` (`id`),
  KEY `type` (`preference`),
  KEY `author` (`author`),
  KEY `preference` (`preference`),
  KEY `username` (`username`)
) ENGINE=MyISAM COMMENT='Spamassassin Preferences';

Now, before installing the basic bayesian SQL, in version 3.3.2 SQL code is not compatible with MySQL 5.5.2 (uses TYPE=MyISAM when creating tables instead of ENGINE=MyISAM), so we have to convert all appearances:

$ sed 's/TYPE=MyISAM/ENGINE=MyISAM/' /usr/share/doc/spamassassin/sql/bayes_mysql.sql > bayes_mysql_tmp.sql

Or download the file here (in sql.gz format). Then, run this file into mysql:

$ mysql -uroot -p****** SpamAssassin < bayes_mysql_tmp.sql

Now, all we have to do is to tell SpamAssassin to use the MySQL database, and, as all our users are from sasl (not system users), and all of them also, are e-mail addresses, we must make sure the system takes de recipient e-mail address as the username.

We will start configuring sql, to do so, let’s create /etc/spamassassin/sql.cf, and put these contents there:

user_scores_dsn                  DBI:mysql:SpamAssassin:localhost:3306
user_scores_sql_username         spamassassin
user_scores_sql_password         4ubnIt5Jn16U
user_scores_sql_custom_query     SELECT preference, value FROM _TABLE_ WHERE username = _USERNAME_ OR username = '$GLOBAL' OR username = CONCAT('%',_DOMAIN_) ORDE$

bayes_store_module               Mail::SpamAssassin::BayesStore::SQL
bayes_sql_dsn                    DBI:mysql:SpamAssassin:localhost:3306
bayes_sql_username               spamassassin
bayes_sql_password               4ubnIt5Jn16U

Here we’re setting up ser scores database and bayes database, we use in the dsn the following format:

DBI:[engine, for us, mysql]:[database, for us, SpamAssassin]:[host, for us, localhost]:[port, 3306]

Then, we fill the user name and password, and, in user_scores_sql_custom_query we put the MySQL query necessary to get the user settings. In this case, we will get preference and value from userpref table matching the username, the domain, or the keyword ‘$GLOBAL’, so we will read all this configuration.

The rest of the fields I’ve added to the table are for internal control (for us), just to track when was the setting created, when was it modified and who did it. It could be a good idea also to have an ‘active’ field to make users (now, the administrator) enable and disable settings.

Now, we have to change /usr/local/bin/spamfilter.sh to pass the user to spamc, but, now, we have a little problem:

• arguments passed to spamfilter.sh , as /etc/postfix/master.cf says are: -f sender@domain.ext — rcpt1@domain.ext rcpt2@domain.ext rcpt3@domain.ext …
• some of the recipients may be not users of our systems, for example, if the message has several recipients, some of them may be in our domains, and some of them not.
• we must choose one recipient of one of our domains.

Then, we have to read arguments string and choose one e-mail of our domains. The only problem now will be that if two users in our system are recipients of the same e-mail, it would only apply the rules of one user, the e-mail will be spam-processed only once, with the preferences on just one of our users. But it won’t be a big problem, since this mailing will probably be spam for both or a legitimate e-mail for both users.

To choose the e-mail from the list of arguments (remember, it must be one of our users email). I’ve made a simple C++ program (we can use some scripting for that, but I think it would be faster to process a huge amount of e-mails):

user_selector.cpp (or download here):

Let’s compile it:

$ g++ -o user_selector user_selector.cpp

And place it in /usr/local/bin/user_selector

Now edit /usr/local/bin/spamfilter.sh

#!/bin/bash
USER=`echo $@ | /usr/local/bin/user_selector`
/usr/bin/spamc -u "$USER" | /usr/sbin/sendmail -i "$@"
exit $?

Now, we will have per-user settings for antispam, and we could use as username, for example:

• info@mylittlecloud.com : To match this single user, notice that if we send an e-mail to any of the aliases of this user, info@mylittlecloud.com will be the destination of the e-mail so this will be the user used. So, don’t worry about the aliases, like if they didn’t exist.
• %mylittlecloud.com : Will affect all e-mails in this domain
• $GLOBAL : Will affect all users in our system

Now, let’s see some possible preferences:

• use_razor2 : Uses razor2 database, it can be 0 or 1
• use_bayes : Uses bayesian classifier for this user (0 or 1)
• use_pyzor : Uses pyzor database (0 or 1)
• skip_rbl_checks :  Don’t check online blacklists (0 or 1)
• required_hists : Score required to match mail as spam
• whitelist_from : E-mail or mask to whitelist (myuser@mydomain.com or *@mydoman.com to match all mails from this domain)
• blacklist_from : The same, but for blacklists
• rewrite_header : Rewrite header of the e-mail. Here we can change the subject of the e-mail for example.
• score USER_IN_WHITELIST : Initial score of a user in whitelist
• score USER_IN_BLACKLIST : Initial score of a user in blacklist
• score [TEST] : Sets the score of any spamassassin test

Of course there are much more settings, you can find them all in the documentation and configuration files. But it’s easy to create a web-based interface to make users change these settings.