Using SQL
spamd can use SQL user_prefs by calling it with the -q or -Q flags. the following configuration options will be required in local.cf to point to the correct database.
These configuration options below are specific to SA 3.0. 2.63 does not use user_scores_sql_custom_query but does have other items to reference table names and such.
[root@mailgw /]# cd /etc/mail/spamassassin/ [root@mailgw spamassassin]# cat sql.cf user_scores_dsn DBI:mysql:logs:localhost:3306 user_scores_sql_password password user_scores_sql_username username user_scores_sql_custom_query SELECT preference, value FROM _TABLE_ WHERE username = _USERNAME_ OR username = '$GLOBAL' OR username = CONCAT('%',_DOMAIN_) ORDER BY username ASC
Make sure your user_scores_sql_custom_query is ALL ON ONE LINE! I wrapped it for display purposes only.
See sql/README in your SpamAssassin tarball for more information on SQL configuration options.
SQL Table Structure for User Prefs
Personally, I use the following table structure for my user prefs... This allows my web interface to add additional information to the items via the descript field, and also has added, added_by, and modified fields.
CREATE TABLE `userprefs` ( `id` int(8) unsigned NOT NULL auto_increment, `username` varchar(128) NOT NULL default '', `preference` varchar(64) NOT NULL default '', `value` varchar(128) default NULL, `descript` varchar(128) default NULL, `added` datetime NOT NULL default '2003-01-01 00:00:00', `added_by` varchar(128) NOT NULL default '', `modified` timestamp(14) NOT NULL, UNIQUE KEY `id` (`id`), KEY `type` (`preference`), KEY `added_by` (`added_by`), KEY `preference` (`preference`), KEY `username` (`username`) ) TYPE=MyISAM COMMENT='Spamassassin Preferences';
The userprefs table as recommended by sql/userpref_mysql.sql in your SA tarball is..
CREATE TABLE userpref ( username varchar(100) NOT NULL default '', preference varchar(30) NOT NULL default '', value varchar(100) NOT NULL default '', prefid int(11) NOT NULL auto_increment, PRIMARY KEY (prefid), KEY username (username) ) TYPE=MyISAM;
Not alot of difference... use whatever you want. If you are planning on having 1000's of userprefs via SQL, i would scale it back and not have all the extra fields.
To get this table active in your mysql database, save the syntax above to a file named userprefs.sql and run
# cat userprefs.sql | mysql <DB>
where <DB> is the database you want to store this table. If you have not already created it, you will want to run something like..
# mysql > CREATE DATABASE spamassasin; > exit;
and then
# cat userprefs.sql | mysql spamassassin
Once you have the table active, you need to assign access to it. All SpamAssassin needs is SELECT access to the userprefs table. Running
# mysql spamassassin > GRANT SELECT ON spamassassin.userprefs TO username IDENTIFIED BY 'password'; > exit;
will allow connections from spamd to connect to the database as user username and a password of password... please change those to something SANE!
Global, Per-Domain, and Per-User Preferences via SQL
SpamAssassin 3.0 supports the config option user_scores_sql_custom_query - Using this feature, one can accomplish any number of custom configuration for supporting tiered user preferences.
However, for proper sorting of userprefs when using Per-Domain settings, one must be careful on how SQL sorts the results. To make this work properly, you may be required to change how you reference your GLOBAL and PER-DOMAIN config preferences. To acheive proper sorting of SQL prefs, I use the following custom_query:
user_scores_sql_custom_query SELECT preference, value FROM sa_rules WHERE username = '$GLOBAL' OR username = CONCAT('%',_DOMAIN_) OR username = _USERNAME_ ORDER BY username ASC
The reason I do it this way, as opposed to the standard vpopmail way (@GLOBAL), is so the preferences in my user_pref database order correctly...
Here is what the query produces...
mysql> select username,preference,value from sa_rules WHERE (username='$GLOBAL' OR username='%nmgi.com' OR username='dallase@nmgi.com') ORDER by username ASC; +------------------+-------------------------+------------------------+ | username | preference | value | +------------------+-------------------------+------------------------+ | $GLOBAL | required_hits | 4.00 | | $GLOBAL | subject_tag | [SPAM-_HITS_]- | | $GLOBAL | score USER_IN_WHITELIST | -10 | | $GLOBAL | whitelist_from | *@sonicwall.com | | $GLOBAL | score USER_IN_BLACKLIST | 10 | | $GLOBAL | report_safe | 0 | | $GLOBAL | use_razor2 | 1 | | $GLOBAL | use_pyzor | 1 | | $GLOBAL | use_dcc | 1 | | $GLOBAL | skip_rbl_checks | 1 | | $GLOBAL | use_bayes | 1 | | $GLOBAL | ok_locales | en | | $GLOBAL | ok_languages | en | | $GLOBAL | whitelist_from | *@nai.com | | $GLOBAL | use_auto_whitelist | 1 | | $GLOBAL | rewrite_header Subject | [SPAM-_HITS_]- | | %nmgi.com | use_bayes | 1 | | %nmgi.com | whitelist_from | *@hp.com | | %nmgi.com | score USER_IN_WHITELIST | -10 | | %nmgi.com | score USER_IN_BLACKLIST | 10 | | %nmgi.com | ok_locales | en | | %nmgi.com | ok_languages | en | | %nmgi.com | use_razor2 | 1 | | %nmgi.com | use_pyzor | 1 | | %nmgi.com | use_dcc | 1 | | %nmgi.com | skip_rbl_checks | 1 | | %nmgi.com | report_safe | 0 | | %nmgi.com | required_hits | 5 | | dallase@nmgi.com | use_razor2 | 1 | | dallase@nmgi.com | use_bayes | 1 | | dallase@nmgi.com | use_pyzor | 1 | | dallase@nmgi.com | use_dcc | 1 | | dallase@nmgi.com | skip_rbl_checks | 0 | | dallase@nmgi.com | whitelist_from | *@paypal.com | | dallase@nmgi.com | ok_languages | en | | dallase@nmgi.com | ok_locales | en | | dallase@nmgi.com | score USER_IN_WHITELIST | -10 | | dallase@nmgi.com | score USER_IN_BLACKLIST | 10 | | dallase@nmgi.com | required_hits | 4.10 | | dallase@nmgi.com | report_safe | 0 | | dallase@nmgi.com | use_auto_whitelist | 1 | | dallase@nmgi.com | rewrite_header Subject | [SPAM-_HITS_]- | +------------------+-------------------------+------------------------+
Doing it this way guarantees proper sorting of prefs, so the last required_hits found would be that of the user if defined, or that of the domain if defined... And if neither exist, it will assume global required_hits.
Obviously, you'd need to rewrite a couple lines in your web administration packages that allows users to modify their SQL prefs with proper GLOBALS ($GLOBAL) and DOMAIN (%nmgi.com) references...
Testing your Setup
# mysql spamassassin; > INSERT INTO userprefs (username,preference,value) VALUES ('$GLOBAL','required_hits','5.0'); > INSERT INTO userprefs (username,preference,value) VALUES ('%nmgi.com','required_hits','4.5'); > INSERT INTO userprefs (username,preference,value) VALUES ('dallase@nmgi.com','required_hits','4.0'); > exit;
make sure spamd is running with the -q flag and that your SQL preferences are correct in your local.cf.
To debug it, make sure spamd is not running, and from the command line, start spamd in foreground (no -d flag) mode with full debugging enabled (-D), and watch for the SQL debug info...
# spamd -D -q
From another console, run
# echo -e "From: user\nTo:user\Subject: Test\n\n" | spamc -u '$GLOBAL' From: user To:user Subject: Test X-Spam-Status: No, hits=1.3 required=5.0
and you should see required hits is set to 5.0
# echo -e "From: user\nTo:user\Subject: Test\n\n" | spamc -u '%nmgi.com' From: user To:user Subject: Test X-Spam-Status: No, hits=1.3 required=4.5
should display required hits of 4.5
# echo -e "From: user\nTo:user\Subject: Test\n\n" | spamc -u 'dallase@nmgi.com' From: user To:user Subject: Test X-Spam-Status: No, hits=1.3 required=4.0
should display required hits of 4.0
As you can see, the 'required' hits changed on each of them because it pulled the SQL preferences. How you get spamc to call -u with the proper information is up to you. In qmail-scanner, I do the following.. in the sub spamassassin function before it opens up the spamc call -
if ($recips =~ m/\,/) { $sa_domain = &recips_in_same_domain($recips); if (defined $sa_domain && $sa_domain ne "") { $sa_user = "\%" . $sa_domain; } else { $sa_user = "\$GLOBAL"; } } else { $sa_user = $recips; } $sa_user=~s/[^0-9a-z\.\_\-\=\+\@\$\%]/_/gi; $sa_user=~/^([0-9a-z\.\_\-\=\+\@\$\%]+)$/i; $sa_user=tolower($1); open(SA,"$spamc_binary $spamc_options -u \"$sa_user\" < $scandir/$wmaildir/new/$file_id|") ||&tempfail("cannot run $spamc_binary < $scandir/$wmaildir/new/$file_id - $!");
my recips_in_same_domain function looks like this... (i'm sure someone can write it much smarter)
sub recips_in_same_domain { my $recips = shift; my @recip=split(/\,/,$recips); my (%doms,$user,$domain); my $count=0; if ($recips !~ m/\,/) { return; }; foreach my $r (@recip) { if ($r =~ m/\@/) { ($user,$domain) = split(/\@/,$r); } else { $user=$r; $domain='localhost'; } $doms{$domain} = (int $doms{$domain} + 1); } foreach my $key (keys %doms) { $count++; } if ($count == 1) { return $domain; } else { return; } }
Just add that function at the bottom of qmail-scanner-queue.pl somewhere so sub spamassassin can call it.
Fun Fun! Thats all for now.. Hopefully I'll add a little information on a web interface soon.
content by DallasEngelken