You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@spamassassin.apache.org by sp...@incubator.apache.org on 2004/06/29 22:25:37 UTC
[SpamAssassin Wiki] New: UsingSQL
Date: 2004-06-29T13:25:37
Editor: 67.67.32.129 <>
Wiki: SpamAssassin Wiki
Page: UsingSQL
URL: http://wiki.apache.org/spamassassin/UsingSQL
no comment
New Page:
= 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