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