-
Notifications
You must be signed in to change notification settings - Fork 70
/
README.awl
144 lines (104 loc) · 5.41 KB
/
README.awl
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
Using SpamAssassin Auto-Whitelists With An SQL Database
-------------------------------------------------------
SpamAssassin can now load users' auto-whitelists from a SQL database.
The most common use for a system like this would be for users to be
able to have per user auto-whitelists on systems where users may not
have a home directory to store the whitelist DB files.
In order to activate the SQL based auto-whitelist you have to
configure spamassassin and spamd to use a different whitelist factory.
This is done with the auto_whitelist_factory config variable, like
so:
auto_whitelist_factory Mail::SpamAssassin::SQLBasedAddrList
SpamAssassin will check the global configuration file (ie. any file
matching /etc/mail/spamassassin/*.cf) for the following settings:
user_awl_dsn DBI:driver:database:hostname[:port]
user_awl_sql_username dbusername
user_awl_sql_password dbpassword
The first option, user_awl_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. <driver> should be the DBD driver that
you have installed to access your database (initially tested with
MySQL, PostgreSQL and SQLite). <database> must be the name of the
database that you created to store the auto-whitelist table.
<hostname> is the name of the host that contains the SQL database
server. <port> is the optional port number where your database server
is listening.
user_awl_dsn DBI:mysql:spamassassin:localhost
Would tell SpamAssassin to connect to the database named spamassassin using
MySQL on the local server, and since <port> is omitted, the driver will use the
default port number. The other two required options tells SpamAssassin to use
the defined username and password to establish the connection.
If the user_awl_dsn option does not exist, SpamAssassin will not attempt
to use SQL for the auto-whitelist.
One additional configuration option exists that allows you to set the
table name for the auto-whitelist table.
user_awl_sql_table awl
For an example of connecting to a PostgreSQL database, see the README file.
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 versions should work fine.
Database Schema
---------------
The database must contain a table named by 'user_awl_sql_table' (default
setting: "awl") with at least these fields:
username varchar(100) # this is the username whose e-mail is being filtered
email varchar(200) # this is the address key
ip varchar(10) # this is the ip key
count int(11) # this is the message counter
totscore float # this is the total calculated score
You can add as many other fields you wish as long as the above fields
are contained in the table.
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. (See "Creating A Database", in "sql/README", if
you don't have a suitable database ready.)
To install the table, use the following command:
mysql -h <hostname> -u <adminusername> -p <databasename> < awl_mysql.sql
Enter password: <adminpassword>
This will create the following table:
CREATE TABLE awl (
username varchar(100) NOT NULL default '',
email varchar(200) NOT NULL default '',
ip varchar(10) NOT NULL default '',
count int(11) default '0',
totscore float default '0',
PRIMARY KEY (username,email,ip)
) TYPE=MyISAM;
For PostgreSQL, use the following:
psql -U <username> -f awl_pg.sql <databasename>
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 specify the proper whitelist factory in the config file in order
for this to work and the current username must be passed to spamd.
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:
SQL Based AWL: Connected to <your dsn>
If you do not see the above text, then the SQL query was not successful, and
you should consult any error messages reported.
This code has been tested using MySQL as the RDBMS, with basic tests
against PostgreSQL and SQLite. It has been written with the utmost
simplicity using DBI, and any database driver that conforms to the DBI
interface and allows you to refer to a column on the right hand side
of an expression (ie update foo set bar = bar + 1) should work with
little or no problems. If you find a driver that has issues, please
report them to the SADev list.
******
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.
******