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.
******