
Using SpamAssassin Automatic Reputation With An SQL Database
-------------------------------------------------------

The TxRep plugin improves on the earlier Auto-Whitelist plugin.
The most common use for a system like this would be for tracking
the expected spam score (or reputation) of frequent senders. A
domain that sends frequent spam will lose reputation (or gain
spam score) over time.

In order to activate the SQL based reputation system you have to
configure spamassassin and spamd to use the appropriate storage
backend. This is done with the txrep_factory config variable,
like so:

txrep_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

These settings are identical to those for the AWL plugin, so you
do not need to change these if you are upgrading.

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 (the most common being
MySQL (driver is 'mysql'), PostgreSQL ('Pg') and SQLite ('SQLite')).
<database> must be the name of the database that you created to store
the txrep 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 tracking reputations.

One additional configuration option exists that allows you to set the
table name for the txrep table.

user_awl_sql_table           txrep

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.

Database Schema
---------------

The database must contain a table named by 'user_awl_sql_table' (default
setting: "txrep") with at least the fields specified in the accompanying
SQL files.

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

To install the table, use the following command:

mysql -h <hostname> -u <adminusername> -p <databasename> < txrep_mysql.sql
Enter password: <adminpassword>

For PostgreSQL, use the following:

psql -U <username> -f txrep_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 storage backend in the config file in order
for this to work and the current username must be passed to spamd.

Maintenance
---------------

It is recommended to keep user_awl_sql_table clear of stale data, for
performance reasons. A sample query that can be run on a regular
schedule is below:

DELETE FROM txrep WHERE last_hit <= (now() - INTERVAL 120 day);

For PostgreSQL, use the following:

DELETE FROM txrep WHERE last_hit <= (now() - INTERVAL '120 day');