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.  <driver> should be the DBD driver that you have installed
to access your database. <connection> can differ depending on which
database you are using.

For MySQL, connection should take the format

  database:hostname[:port]

<database> must be the name of the database that you created to store the user
preference 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_scores_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.

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(50)  # 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. "user@example.com", 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 <hostname> -u <adminusername> -p <databasename> < userpref_mysql.sql
Enter password: <adminpassword>

This will create the following table:

CREATE TABLE userpref (
  username varchar(100) default NOT NULL,
  preference varchar(50) 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 <username> -f userpref_pg.sql <databasename>

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 <hostname> -u <adminusername> -p
Enter password: <adminpassword>
mysql> use mysql;
mysql> insert into user (Host, User, Password) values('localhost','<username>', password('<password>'));
mysql> insert into db (Host, Db, User, Select_priv) values('localhost','<databasename>','<username>','Y');
mysql> create database <databasename>;
mysql> quit

NOTE: 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 <adminuser> template1
template1=# CREATE USER <username> PASSWORD '<password>';
template1=# CREATE DATABASE <databasename> OWNER = <username>;


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 <username> 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. <username> 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 '@GLOBAL' 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.

Web Interfaces
--------------

Several web interfaces have been created for per user configurations.
You can find more information about these on the SpamAssassin wiki:
http://wiki.apache.org/spamassassin/WebUserInterfaces

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.

Using A SQL Database for Bayesian Storage Module
-------------------------------------------------------

SpamAssassin can now store users' bayesian filter data in a SQL
database.  The most common use for a system like this would be for
users to be able to have per user bayesian filter data on systems
where users may not have a home directory to store the data.

In order to activate the SQL based bayesian storage you have to
configure spamassassin and spamd to use a different bayes storage
module.  This can be done via a setting in the global configuration
file.

The directives required to turn on the SQL based bayesian storage are:

bayes_store_module		   Mail::SpamAssassin::BayesStore::SQL

This directive is used by the Bayes module to determine which storage
module should be used. If not set it will default to:
Mail::SpamAssassin::BayesStore::DBM

The storage module Mail::SpamAssassin::BayesStore::SQL is an older generic
SQL module which can be also be used with versions of MySQL which did not
have support for an InnoDB engine and transactions. If choosing this module
consider replacing the InnoDB engine with MyISAM (explicitly or defaulted)
in the schema (files bayes_mysql.sql and awl_mysql.sql). Note that old
versions of MySQL expect syntax TYPE=MyISAM instead of ENGINE=MyISAM,
while newer versions throw a syntax error on TYPE and only allow ENGINE.
In short: replace ENGINE=InnoDB with TYPE=MyISAM (or just leave it out)
in the bayes_mysql.sql and awl_mysql.sql schemas if ENGINE=InnoDB is not
accepted.

There is also a MySQL specific storage driver available to provides a
small boost in performance.  It requires version 4.1 or above of the
MySQL database software to work properly.  In addition, it provides
rollback on error functionality if you create your bayes database table
using the InnoDB storage engine. WARNING: Using this module with a version
of MySQL < 4.1 could have unexpected results.  To use the MySQL 4.1+
specific module set your bayes_store_module directive accordingly:
  bayes_store_module               Mail::SpamAssassin::BayesStore::MySQL

PostgreSQL users will want to use the PostgreSQL specific storage
module:
  bayes_store_module               Mail::SpamAssassin::BayesStore::PgSQL
This module provides a slightly different interface to makes better
use of the resources that PostgreSQL offers.  In addition, please make
sure that you follow the instructions below for loading the proper
procedural language and installing the tables and stored procedure.

Additional configuration directives provided by BayesSQL:

bayes_sql_dsn			   DBI:driver:database:hostname[:port]
bayes_sql_username		   dbusername
bayes_sql_password		   dbpassword

The bayes_sql_dsn directive 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 bayes data tables. <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.

For an example of connection to PostgreSQL, see the main README file.

In addition to the global configuration directives there is a user
preference:

bayes_sql_override_username	   someusername

This directive, if used, will override the username used for storing
data in the database.  This could be used to group users together to
share bayesian filter data.  You can also use this config option to
trick sa-learn to learn data as a specific user.


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 DBD::mysql module installed, a PostgreSQL
database requires DBD::Pg.  Check CPAN for latest versions of DBI
and your database driver/module.

The BayesStore::SQL module was tested with:

DBI-1.38
DBD-mysql-2.9002
perl v5.8.0

But older (and newer) versions should work fine as the SQL code in
SpamAssassin is as simple as could be.

NOTE: Some users have reported problems using DBD::Pg v1.22. There appears
to be a bug in how parameters are quoted in that version, we recommend you
upgrade to at least v1.31.

In addition, there appears to be a quote bug in some versions of PostgreSQL.
It's unclear when the bug was fixed.  7.4.2 seems to work just fine, however
some have reported problems with 7.3.6.  Your mileage may vary with versions
less than 7.4.2.  If you happen to know when the specific bug was fixed please
feel free to notify the dev team so they can update this documentation.

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

The database schema for storage of the bayesian filter data contains
several different tables.  Several sample SQL schemas have been
included in to help in setting up your database.  The schemas contain
the minimum tables and columns necessary to work with the code as
written.  You are free to add other columns as needed for your local
implementation.  Presently there is no way to override the table and
column names used by the BayesStore::SQL code, this feature may be added
in the future.

Example setup of bayes tables for MySQL:

This assumes that you have already created a database for use with
spamassassin and setup 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 tables using the included example, use the following command:

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

To install the tables for PostgreSQL, use:

You need to install the plpgsql procedural language (assuming it
hasn't already been installed in template1). You can do this by
running the following command:

createlang plpgsql <databasename>

and then:

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

From a security viewpoint, it is wise to grant the minimum privileges
needed to the user on the bayes tables.  These grants largely depend
on your system policies but here are some example grants to get you
started, please review carefully before putting into production:

GRANT SELECT, UPDATE, DELETE, INSERT ON TABLE bayes_token TO <username>;
GRANT SELECT, UPDATE, DELETE, INSERT ON TABLE bayes_vars TO <username>;
GRANT SELECT, UPDATE, DELETE, INSERT ON TABLE bayes_seen TO <username>;
GRANT SELECT, DELETE, INSERT ON TABLE bayes_expire TO <username>;
GRANT SELECT ON TABLE bayes_global_vars TO <username>;
GRANT USAGE, SELECT, UPDATE ON SEQUENCE bayes_vars_id_seq TO <username>;

Once you have created the database and added the tables, just add the
required lines to your global configuration file (local.cf).

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:

spamc < sample-spam.txt

Watch the debug output from spamd and look for the following debug
line:

debug: bayes: Database connection established
debug: bayes: Using username: <username>

If you do not see the above text, then the SQL query was not
successful, and you should see any error messages reported.

This code has been tested using MySQL as the RDMS, with basic tests
against PostgreSQL and SQLite.  It does require a database that allows
you to refer to a column on the right hand side of an expression (ie
update foo set bar = bar + 1).  Any database driver that allows for
that usage should work with the BayesStore::SQL code.  NOTE: You may
find that some implementations do not provide a significant advantage
over using the default DBM implementation.  If you find a driver that
should work and has issues, please report them to the SADev list.

Converting Bayes Data From a DBM Database
-----------------------------------------

Converting your bayes database data from Berkeley (DBM) based storage
to SQL based storage is as simple as a backup and then restore.

If you are upgrading from a previous version of SpamAssassin you
should first follow any recommended upgrade instructions for that
release, in most cases this will be as simple as running an
sa-learn --sync

Once you have performed this upgrade, for each bayes database follow
this procedure:

o Run 'sa-learn --backup > backup.txt' which will backup your bayes
  data into a text file.
o Optionally you can run 'sa-learn --clear' to remove the DBM based
  bayes files.
o Modify your local.cf file according to the directions above.
o Run 'sa-learn --restore backup.txt' to restore your bayes data to
  the SQL database.

NOTE: sa-learn must be run as the user who's data you are loading, or
      you must make use of the bayes_sql_override_username config
      option.

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');