standalone3.0.tar
必须改掉require("../common/admin_layout.php
参考
Install awl
1、
auto_whitelist_factory Mail::SpamAssassin::SQLBasedAddrList
2、
user_awl_dsn DBI: mysql:awl:localhost
user_awl_sql_username spam
user_awl_sql_password spamd
3、
mysql –uroot –p
create database awl;
grant select,delete,update,insert,drop,create ON awl.* to identified by ‘spamd’;
msyql –uroot –p < awl_mysql.sql
install bayes_mysql.sql
1、
bayes_store_module Mail::SpamAssassin::BayesStore::MySQL
bayes_sql_dsn DBI:mysql:bayes:localhost
bayes_sql_username spam
bayes_sql_password spamd
3、
mysql –uroot –p
create database bayes;
grant select,delete,update,insert,drop,create ON bayes.* to identified by ‘spamd’;
mysql –uroot –p < bayes_mysql.sql
install userpref_mysql.sql
user_scores_dsn DBI:mysql:spamd:localhost
user_scores_sql_username spam
user_scores_sql_password spamd
user_scores_sql_custom_query SELECT preference, value FROM _TABLE_ WHERE username = _USERNAME_ OR username = OR username = CONCAT('%',_DOMAIN_) ORDER BY username ASC
mysql –uroot –p
create database spamd;
grant select,delete,update,insert,drop,create ON spamd.* to identified by ‘spamd’;
mysql –uroot –p < userpref_mysql.sql
UsingSQL
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 `userpref` (
`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 userpref 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 user prefs 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 userpref.sql and run
# cat userpref.sql | mysql
where 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 spamassassin;
> exit;
and then
# cat userpref.sql | mysql spamassassin
Once you have the table active, you need to assign access to it. All SpamAssassin needs is SELECT access to the userpref table. Running
# mysql spamassassin
> GRANT SELECT ON spamassassin.userpref 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 userpref 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 _TABLE_ WHERE username = _USERNAME_ OR
username = '$GLOBAL' OR username = CONCAT('%',_DOMAIN_) ORDER BY username ASC
Please avoid line wrapping on user_scores_sql_custom_query.. copying and pasting the above line into a .cf will not work.
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 userpref WHERE
(username='$GLOBAL' OR username='%nmgi.com' OR
) 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 | |
| $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 | |
| $GLOBAL | use_auto_whitelist | 1 |
| $GLOBAL | rewrite_header Subject | [SPAM-_HITS_]- |
| %nmgi.com | use_bayes | 1 |
| %nmgi.com | whitelist_from | |
| %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 |
| | use_razor2 | 1 |
| | use_bayes | 1 |
| | use_pyzor | 1 |
| | use_dcc | 1 |
| | skip_rbl_checks | 0 |
| | whitelist_from | |
| | ok_languages | en |
| | ok_locales | en |
| | score USER_IN_WHITELIST | -10 |
| | score USER_IN_BLACKLIST | 10 |
| | required_hits | 4.10 |
| | report_safe | 0 |
| | use_auto_whitelist | 1 |
| | 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 userpref (username,preference,value) VALUES ('$GLOBAL','required_hits','5.0');
> INSERT INTO userpref (username,preference,value) VALUES ('%nmgi.com','required_hits','4.5');
> INSERT INTO userpref (username,preference,value) VALUES ();
> 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
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
Loading SpamAssassin User Preferences From An SQL Database
----------------------------------------------------------
SpamAssassin can now load users' score files from an SQL database. The concept
here is to have a web application (PHP/perl/ASP/etc.) that will allow users to
be able to update their local preferences on how SpamAssassin will filter their
e-mail. The most common use for a system like this would be for users to be
able to update the white list of addresses (whitelist_from) without the need
for them to update their $HOME/.spamassassin/user_prefs file. It is also quite
common for users listed in /etc/passwd to not have a home directory, therefore,
the only way to have their own local settings would be through an RDBMS system.
Note that this will NOT look for test rules, only local scores,
whitelist_from(s), and required_score.
In addition, any config options marked as Admin Only will NOT be parsed from
SQL preferences.
SpamAssassin will check the global configuration file (ie. any file matching
/etc/mail/spamassassin/*.cf) for the following settings:
user_scores_dsn DBI:driver:connection
user_scores_sql_username dbusername
user_scores_sql_password dbpassword
The first option, user_scores_dsn, describes the data source name that will be
used to create the connection to your SQL server. It MUST be in the format
as listed above. should be the DBD driver that you have installed
to access your database. can differ depending on which
database you are using.
For MySQL, connection should take the format
database:hostname[:port]
must be the name of the database that you created to store the user
preference table. is the name of the host that contains the SQL
database server. is the optional port number where your database server
is listening.
user_scores_dsn DBI:mysql:spamassassin:localhost
Would tell SpamAssassin to connect to the database named spamassassin using
MySQL on the local server, and since
is omitted, the driver will use the
default port number.For PostgreSQL, connection should take the following format:
dbname=database;[host=hostname;[port=port;]
user_scores_dsn DBI:Pg:dbname=spamassassin;host=localhost
would do the same as the previous example.
For additional information, please refer to the DBD::* documentation
for your particular driver.
The spamd server will not pay attention to SQL preferences by default,
even with user_scores_dsn set in the config files. You must startup
spamd with the proper options (ie -q or -Q, see perldoc spamd for more
information). If the user_scores_dsn option does not exist,
SpamAssassin will not attempt to use SQL for retrieving users'
preferences.
While scanning a message if spamd is unable to connect to the server
specified in user_scores_dsn or an error occurs when querying the SQL
server then spam checking will not be performed on that message.
The user_scores_sql_username and user_scores_sql_password options are
required if your database server requires a username and password to
be sent on connect.
If you have a table layout that differs from the default, please
review the documentation for user_scores_sql_custom_query for
information on how deal with a custom layout.
Requirements
------------
In order for SpamAssassin to work with your SQL database, you must have
the perl DBI module installed, AS WELL AS the DBD driver/module for your
specific database. For example, if using MySQL as your RDBMS, you must have
the Msql-Mysql module installed. Check CPAN for the latest versions of DBI
and your database driver/module.
We are currently using:
DBI-1.20
Msql-Mysql-modules-1.2219
perl v5.6.1
But older and newer versions should work fine as the SQL code in SpamAssassin
is as simple as could be.
Database Schema
---------------
The database must contain a table, default name "userpref", with at
least three fields:
username varchar(100) # this is the username whose e-mail is being filtered
preference varchar(30) # the preference (whitelist_from, required_score, etc.)
value varchar(100) # the value of the named preference
You can add as many other fields you wish as long as the above three fields are
contained in the table.
Note that you can either use just the mail recipient's username for the
"username" field, in which case a varchar(8) should suffice. Alternatively,
you can use the entire recipient's email address, e.g. "", and
use the full varchar(100).
Included is a default table that can be safely used in your own setup. To use
the default table, you must first create a database, and a username/password
that can access that database.
If you wish to use a table that differs from the included default you
should review the user_scores_sql_custom_query config option for
information on making it work correctly.
To create a database, if one does not already exist, see "Creating A Database"
below.
To install the table to a mysql database, use the following command:
mysql -h
-u -p < userpref_mysql.sql
Enter password: This will create the following table:
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;
For PostgreSQL, use the following command:
psql -U -f userpref_pg.sql
This will create a table similar to above.
Once you have created the database and added the table, just add the required
lines to your global configuration file (local.cf). Note that you must be
running spamc/spamd in order for this to work, and the current username must
be passed to spamd. This can be done from spamc using the following
.procmailrc recipe:
:0fw
| /usr/local/bin/spamc -f
(watch out; spamc could be installed as /usr/bin/spamc instead.)
If you are using this from /etc/procmailrc, you must include DROPPRIVS=yes
before spamc. An example /etc/procmailrc:
DROPPRIVS=yes
:0fw
| /usr/local/bin/spamc -f
Also note that spamd may need the "-q" switch so it knows to look up users in
the SQL table instead of /etc/passwd. See "man spamd".
Creating A Database
-------------------
Here's the command to create a MySQL database, and user/password pair to access
it:
mysql -h
-u -p
Enter password:
mysql> use mysql;
mysql> insert into user (Host, User, Password) values('localhost','', password(''));
mysql> insert into db (Host, Db, User, Select_priv) values('localhost','','','Y');
mysql> create database ;
mysql> quitNOTE: If you intend to use this database for Bayes and/or AWL data you
may need to grant additional privs (ie Insert_priv, Update_priv and
Delete_priv). Please refer to the MySQL documentation for the proper
method of adding these privs.
To create the database for PostgreSQL, with a username/password:
psql -U
template1
template1=# CREATE USER PASSWORD '';
template1=# CREATE DATABASE OWNER = ;
Testing SpamAssassin/SQL
------------------------
To test your SQL setup, and debug any possible problems, you should start
spamd with the -D option, which will keep spamd in the foreground, and will
output debug message to the terminal. You should then test spamd with a
message by calling spamc. You can use the sample-spam.txt file with the
following command:
cat sample-spam.txt | spamc
Watch the debug output from spamd and look for the following debug line:
retrieving prefs for from SQL server
If you do not see the above text, then the SQL query was not successful, and
you should see any error messages reported. should be the user
that was passed to spamd and is usually the user executing spamc.
Note that under the default configuration any prefs stored under the
username are used as defaults for all users.
This code has only been tested using MySQL as the RDMS, but it has been written
with the utmost simplicity using DBI, and any database driver that conforms to
the DBI interface should work without problems.
******
NB: This should be considered BETA, and the interface, schema, or overall
operation of SQL support may change at any time with future releases of SA.
******
Web Interfaces
--------------
Several web interfaces have been created for per user configurations.
You can find more information about these on the SpamAssassin wiki:
