SA per-user preferences

There are various web interfaces to SpamAssassin, my favorite being the SquirrelMail plugin. There is a page on the SpamAssassin wiki listing the available web interfaces. Instructions for a couple of them follow.

Install the Squirrelmail SpamAssassin plugin (SASQL)

The SpamAssassin SQL plugin (SASQL) enables users to modify their individual anti-spam preferences from within the Squirrelmail web interface. The official plugin page for sasql can be found here

1. Install the squirrelmail sasql plugin from ports:

cd /usr/ports/mail/squirrelmail-sasql-plugin && make clean install

2. Create the sasql configuration file:

cd /usr/local/www/squirrelmail/plugins/sasql
cp sasql_conf.php.dist sasql_conf.php

3. Edit the configuration file and set the DSN & proper userpref table:

a. The DSN line should look like:

$SqlDSN = 'mysql://spamassassin:dbpassword@localhost/spamassassin';

b. The table line should look like:

$SqlTable = 'userpref';

4. Run the Squirrelmail config utility:

cd /usr/local/www/squirrelmail
./configure

5. Install the sasql plugin:

a. Select Plugins (#8) from the main configuration menu.

b. Look for and select the sasql plugin (#17).

c. Verify that sasql is listed in the Installed Plugins list.

d. Press S and Enter to save.

e. Press Q and Enter to quit.

6. Test! Browse to http://full.dns.of.toaster/squirrelmail/src/configtest.php and verify that there are no errors.

7. To test if this works, follow the following steps:

a. In qmailadmin, check the user's "Spam Detection?" box.

b. Login to Squirrelmail as the user - this will create the necessary SPAM folders.

c. Logout of Squirrelmail and back in again to verify that the "Learn Ham", "Learn Spam" and "Spam" folders exist.

d. While in Squirrelmail, click on Options and then Spam Filters to be able to modify the user's anti-spam settings.

Enable Per-User Preferences

This step modifies the spamd flags to tell it to use SQL based spamassassin user preferences.

IMPORTANT: Do not skip this section or spamassassin won't use the database for your preferences.

1. Ensure that toaster-watcher.conf has the following settings:

install_spamassassin               = 1
install_spamassassin_flags         = -v -q -x   # Add -q for per user SQL prefs
filtering_spamassassin_method      = user       # site | user | domain

2. Verify that /etc/rc.conf has the following lines:

spamd_enable="YES"
spamd_flags="-v -q -x"

3. Restart spamd.

Old Stuff

Note: I moved a bunch of stuff to this section because the page flow was a mess. I didn't want to delete this stuff yet - I think it should be preserved somewhere, but I haven't got the time to figure out where. TL;DR: Disregard much of this section - Matt's automated it.

How do I set up per-user SpamAssassin preferences on Mail::Toaster?

To configure SpamAssassin, follow the SQL directions on the SpamAssassin site.

I've added a little bit of support to toaster_setup.pl to help you on your way. Edit toaster-watcher.conf and set the following values:

install_spamassassin_sql = 1 # use AWL, bayes, and per-user prefs from MySQL
install_spamassassin_dbuser = spamassassin
install_spamassassin_dbpass = assSPAMing

Don't forget to create the MySQL user and password for access to the spamassassin database. Here's the command to create a MySQL database, and user/password pair to access it:

mysql -h host -u adminuser-p
Enter password: adminpass
mysql> use mysql;
mysql> insert into user (Host, User, Password) values('localhost','user', password('pass'));
mysql> insert into db (Host, Db, User, Select_priv, Insert_priv, Update_priv, Delete_priv) values('localhost','dbname','user','Y');
mysql> create database spamassassin;
mysql> quit

What are some common values to stick in userpref?

insert into userpref (username, preference, value) VALUES ('@GLOBAL', 'required_score', '12.0');
insert into userpref (username, preference, value) VALUES ('@GLOBAL', 'whitelist_from', '*@tnpi.biz');
insert into userpref (username, preference, value) VALUES ('bob@example.com', 'required_score', '5.5');
insert into userpref (username, preference, value) VALUES ('bob@example.com', 'whitelist_from', 'alerts@dealnews.com');
insert into userpref (username, preference, value) VALUES ('bob@example.com', 'blacklist_from', '*@spammer.com');

How do I alter what prefs SpamAssassin uses?

You can control the order of preferences by re-writing the SQL query that SpamAssassin uses. You can do this by setting the user_scores_sql_custom_query value to a custom query. You can use the following variables in the SQL query:

_USERNAME_
_MAILBOX_
_DOMAIN_

Here are a few example SQL queries:

default query

SELECT preference, value FROM _TABLE_ WHERE username = _USERNAME_ OR username = '@GLOBAL' ORDER BY username ASC

global, then domain level

SELECT preference, value FROM _TABLE_ WHERE username = _USERNAME_ OR username = '@GLOBAL' OR username = '@~'||_DOMAIN_ ORDER BY username ASC

global overrides user prefs

SELECT preference, value FROM _TABLE_ WHERE username = _USERNAME_ OR username = '@GLOBAL' ORDER BY username DESC

from the SA SQL README

user_scores_sql_custom_query SELECT preference, value FROM _TABLE_ WHERE username = _USERNAME_ OR username = '$GLOBAL' OR username = CONCAT('%',_DOMAIN_) ORDER BY username ASC

External Links

Official Squirrelmail SASQL Plugin Page - squirrelmail.org