I wonder if any of you guys would mind helping me figure this out. I have a working Virtual Users and Domains With Postfix, Courier, MySQL, and Squirrelmail
installation (been using falko's excellent
guides for years) that I would like to tweak in the following ways:
- Give users the ability to manage some limited SpamAssassin settings (white/blacklisting, required score, rewrite subject, etc.) through the Squirrelmail interface, preferably with a plugin like SpamAssassin+SQL.
- Store those settings in a MySQL database where it can be retrieved and used by Amavis (or SpamAssassin directly, if that's easier to work out) for mail filtering.
- Make sure that these useprefs are used even when an e-mail is sent to an address in the forwards table.
To this end, I've been digging around and reading up on SpamAssassin and how it uses SQL, and I think I have a pretty good grasp of the basics. I've even tried to wrap my head around how Amavisd-new uses SQL, but the documentation was just too general and cryptic and I got lost very quickly.
So, following the guidelines in SpamAssassin's docs
, I attempted to get SpamAssassin to use SQL directly for userprefs. This is what I tried:
First, I added the userprefs table to my mail database (simpler than creating a new one just for SA):
CREATE TABLE userpref (
username varchar(100) default NOT NULL,
preference varchar(30) default NOT NULL,
value varchar(100) default NOT NULL,
prefid int(11) NOT NULL auto_increment,
PRIMARY KEY (prefid),
Then I added the appropriate information to /etc/mail/spamassassin/local.cf:
# SQL database settings for userperfs lookup
#user_scores_sql_custom_query SELECT preference, value FROM _TABLE_ WHERE username = _USERNAME_ OR username = '@GLOBAL' ORDER BY username ASC
After that, it's a matter of giving spamd the right command line options. In Mandriva, this is done in /etc/sysconfig/spamd:
# Customized settings for spamassassin
# Commandline options for spamd (-Q added for SQL userperfs lookup) (-D for testing)
SPAMDOPTIONS="-D -d -c -Q -m5 -H --syslog=/var/log/spamassassin/spamd.log"
After that, I went on to set up the SA+SQL Squirrelmail plugin, changing sasql_conf.php here:
// DSN for DB
$SqlDSN = 'mysql://mail_admin:<password>@localhost/mail';
Here (I'm not looking to get Bayes or AWL working just yet):
// If you plan on using the process-spam.pl script or your own home grown
// version, then setting this to True will allow the storage of emails
// on your server for later processing through sa_learn.
$sasql_use_learn = False;
Here (no reason to have folders that don't do anything):
// Set to '' to disable.
// The included process-spam.pl script provides a way to learn messages
// moved into these folders.
$learn_spam_folder = '';
$learn_fp_folder = '';
And here (I like to see this sort of stuff):
// Show a spam report in the message headers.
// This will only show something if the 'X-Spam-Status' header
// is present in the message.
// This option only works with SM 1.4.9 or with the patch at
$show_spam_report = True;
// Show the message's score in the spam report.
// $show_spam_report must also be set.
$show_report_score = True;
Then I enabled the plugin with conf.pl, reset spamd, and logged into Squirrelmail to populate the table. The SA+SQL plugin worked fine in Squirrelmail, encountering no errors of any kind. To differentiate between the old SA configuration and the new one, I set rewrite_header subject
Everything okay so far, I figured it was time to put it to the test. First:
[root@taylor-madeak ~]# cat sample-spam.txt | spamc -u firstname.lastname@example.org
The command line output isn't as important as the debug info in /var/log/spamassassin/spamd.log:
Mon Jul 26 14:14:42 2010  dbg: config: Conf::SQL: executing SQL: select preference, value from userpref where username = 'email@example.com' or username = '@GLOBAL' order by username asc
Mon Jul 26 14:14:42 2010  dbg: config: retrieving prefs for firstname.lastname@example.org from SQL server
So far, so good, right? To test the actual e-mail system - which is using Amavis and not
spamc - I simply sent the GTUBE to myself...
...and that's where I hit "ARGH!" status. The amavisd.conf file has rewrite_header subject set to "***SPAM***" to give me something easy to configure Thunderbird filters off of. When the GTUBE hit my Inbox, sure enough that's what the subject line had in it! NOT the subject rewrite I set in the userperfs table.
I did a lot more troubleshooting, but in the end I deactivated the SA+SQL Squirrelmail plugin and removed my edits from the /etc/sysconfig/spamd file. All other changes I left in place as they have no effect on how SpamAssassin operates without that -Q switch.
Evidently configuring SA itself for SQL userprefs just doesn't work when your system is set up to have Amavis call SA for you, and I don't have the slightest idea how to go about getting Amavis set up for the kind of per-user preferences I want to use (the documentation
is over my head). I saw a setting in amavisd.conf for SQL-based white/blacklisting, but it's set to 'undef' and the default query doesn't work for the database in its current configuration.
If some of you experts would care to work with me to find a solution to this dilemma, or even just point me toward a good howto that may already have been written (I searched both this forum and the main website before posting and didn't find what I'm looking for), I'd be mighty grateful
Heck, I'll take any advice I can get at this point!