
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 (driver is 'mysql'), PostgreSQL ('Pg') and SQLite ('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 (DBD::mysql) module installed.  Check CPAN for the latest
versions of DBI and your database driver/module. 

We are currently using:

DBI-1.60.9
DBD-mysql-4.012
perl v5.10.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(40)       # this is the ip key (fits IPv4 or IPv6)
  msgcount int(11)        # this is the message counter
  totscore float          # this is the total calculated score
  signedby varchar(255)   # a DKIM or DomainKeys signing domain(s)

You can add as many other fields you wish as long as the above fields are
contained in the table.

The 'signedby' field was introduced in version 3.3.0 and is only needed
if auto_whitelist_distinguish_signed is true, e.g. (in local.cf):
  auto_whitelist_distinguish_signed 1
and is only useful if a plugin DKIM is enabled. If the setting is off
the field is not used, but it does no harm to have it in a table.
The new field makes AWL keep separate records for author addresses with
valid DKIM or DomainKeys signatures, and separate records for unsigned mail,
which does a good job for popular domains such as gmail.com and yahoo.com
where most of the spam claiming to be from such domain does not come from
a freemail provider and therefore can not carry a valid signature.

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(255) NOT NULL default '',
  ip varchar(40) NOT NULL default '',
  msgcount int(11) NOT NULL default '0',
  totscore float NOT NULL default '0',
  signedby varchar(255) NOT NULL default '',
  PRIMARY KEY (username,email,signedby,ip)
) TYPE=MyISAM;


For PostgreSQL, use the following:

psql -U <username> -f awl_pg.sql <databasename>


To add a field 'signedby' to an existing table and to modify a primary key:
under MySQL:
  ALTER TABLE awl
    DROP PRIMARY KEY,
    ADD signedby varchar(255) NOT NULL DEFAULT '',
    ADD PRIMARY KEY (username,email,signedby,ip);
under PostgreSQL:
  DROP INDEX awl_pkey;
  ALTER TABLE awl
    ADD signedby varchar(255) NOT NULL DEFAULT '',
    ADD PRIMARY KEY (username,email,signedby,ip);
then add the following to local.cf to let SpamAssassin start using the
newly added field 'signedby' :
  auto_whitelist_distinguish_signed 1

To extend a field awl.ip on an existing table to be able to fit
an IPv6 addresses (39 characters would suffice) or an IPv4 address:
under MySQL:
  ALTER TABLE awl MODIFY ip varchar(40);
under PostgreSQL:
  ALTER TABLE awl ALTER ip TYPE varchar(40);


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.