Need help with Spamassassin SQL per-user prefs

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:

1. 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.
2. 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.
3. 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):

Code:

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),
  INDEX (username)
) TYPE=MyISAM;

Then I added the appropriate information to /etc/mail/spamassassin/local.cf:

Code:

# SQL database settings for userperfs lookup

user_scores_dsn                       DBI:mysql:mail:localhost
user_scores_sql_username              mail_admin
user_scores_sql_password              xxxxxxxxxxx
#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:

Code:

# 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:

Code:

// DSN for DB
$SqlDSN = 'mysql://mail_admin:<password>@localhost/mail';

Here (I'm not looking to get Bayes or AWL working just yet):

Code:

// 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):

Code:

// 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):

Code:

// 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
// https://sourceforge.net/tracker/?func=detail&aid=1589520&group_id=311&atid=300311
$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 to "****SPAM!****".

Everything okay so far, I figured it was time to put it to the test. First:

Code:

[root@taylor-madeak ~]# cat sample-spam.txt | spamc -u [email protected]

The command line output isn't as important as the debug info in /var/log/spamassassin/spamd.log:

Code:

Mon Jul 26 14:14:42 2010 [11883] dbg: config: Conf::SQL: executing SQL: select preference, value from userpref where username = '[email protected]' or username = '@GLOBAL' order by username asc
Mon Jul 26 14:14:42 2010 [11883] dbg: config: retrieving prefs for [email protected] 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.

I'm stuck. 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!

 

As far as I know you can do this stuff with ISPConfig 3, but you can't install it on top of your existing setup (plus, Mandriva isn't supported).

 

Thanks for the reply, falko. Even if it were supported, I'd prefer not to use ISPConfig 3. My server needs just aren't big enough to require it and I'm used to just doing most of my server admin through Webmin. So, being the author of all those excellent guides that use amavisd-new, can you point me in the right direction for getting amavisd configured for what I described above? I really just want to give users an easy way to manage white/blacklists and adjust their required_score to their own liking. Things like Bayes and auto-whitelist can wait for another day.