Go Back   HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials > Linux Forums > Server Operation

Do you like HowtoForge? Please consider supporting us by becoming a subscriber.
Thread Tools Display Modes
Old 27th July 2010, 11:24
taylor-made taylor-made is offline
Junior Member
Join Date: Feb 2008
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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):
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)
Then I added the appropriate information to /etc/mail/spamassassin/local.cf:
# 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:

# 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
// 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:
[root@taylor-madeak ~]# cat sample-spam.txt | spamc -u repairman@taylor-madeak.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 [11883] dbg: config: Conf::SQL: executing SQL: select preference, value from userpref where username = 'repairman@taylor-madeak.org' or username = '@GLOBAL' order by username asc
Mon Jul 26 14:14:42 2010 [11883] dbg: config: retrieving prefs for repairman@taylor-madeak.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.

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!
Reply With Quote
Sponsored Links
Old 28th July 2010, 15:49
falko falko is offline
Super Moderator
Join Date: Apr 2005
Location: Lneburg, Germany
Posts: 41,701
Thanks: 1,900
Thanked 2,749 Times in 2,579 Posts

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).
Download the ISPConfig 3 Manual! | Check out the ISPConfig 3 Billing Module!

FB: http://www.facebook.com/howtoforge

nginx-Webhosting: Timme Hosting | Follow me on:
Reply With Quote
Old 28th July 2010, 21:39
taylor-made taylor-made is offline
Junior Member
Join Date: Feb 2008
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts

Originally Posted by falko View Post
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.
Reply With Quote


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Statistic not working mzo Installation/Configuration 49 20th April 2011 13:19
Can't access to my website - after install problem pallermo Installation/Configuration 18 4th June 2010 14:29
spamassassin use the same sql prefs database gigizmo Installation/Configuration 4 18th May 2010 15:23
The system is currently updating the configuration files. warlock General 8 21st February 2009 19:15
ISP Config hesitation when opening web pages frankb Installation/Configuration 7 15th December 2008 14:06

All times are GMT +2. The time now is 01:40.

Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.