sasql - SpamAssassin+SQL DB plugin for SquirrelMail

Author: Randy Smith <perlstalker@falconsroost.alamosa.co.us>

The SASql SquirrelMail plugin lets users change a pre-defined set of
SpamAssassin settings when those settings are stored in a SQL DB
rather than a config file.

INSTALLATION

o Unpack in the plugins directory of your SM installation.
o Edit sasql_conf and change the DSN and table name to suit
  your database. (The schema used by SA is defined in
  http://www.spamassassin.org/dist/sql/README.)
o Enable the sasql plugin.

SUPPORTED SPAMASSASSIN SETTINGS

o [un]whitelist_from / [un]blacklist_from / whitelist_to
o required_hits / required_score
o use_terse_report
o rewrite_subject
o use_dcc
o use_razor1
o use_razor2
o always_add_headers
o ok_languages
o use_bayes
o auto_learn
o report_safe

Settings removed from SpamAssassin 2.50. Set $useSA2_43 to enable,
or add to $allowed_params.
o report_header
o defang_mime

ADDING ADDITIONAL SETTINGS

Additional settings can be added very easily in sa_opts.php.
To do so, add a new entry to $sa_params with the following fields:
- title: A short title for the setting. This is not the name of
the setting that SpamAssassin uses.
- descr: A description of the setting. HTML formatting is ok here.
- type: The type of widget to use for input. Currently allowed are:
	select: Creates drop-down box for input.
	checkbox: Creates a simple checkbox for input.
	text: Create a plain text box.
- multiple: Allow multiple selections. Only useful with the 'select' type.
- size: The size of the widget. If omitted, then the default size for that
	widget is used. Not used for 'checkbox' types.
- values: This varies depending on the type chosen above.
	select: This is an associative array of values and descriptions.
		See the 'required_hits' setting for an example.
	checkbox: This is the value for a selected checkbox.
	text: not used
- default: This is the default value for the setting. For settings
           with the 'select' type, the default should be one of the keys
           of the array.
- delete: Generally used on 'text' type settings to remove them from the
          database if the value is set to ''. Note: Setting the default
          value to some non-empty value for options with the 'delete' flag
          will probably fail in interesting ways. Most likely, the user
          will never be able to turn it off.

Example:
Let's say we wanted to add a setting named 'use_foo'. We need to
create 'title', 'descr', 'type', 'values' and 'default' fields.
Note: The first index MUST be the name of the setting as SpamAssassin
uses it.

$sa_params['use_foo']['title'] = "Use Foo";
$sa_params['use_foo']['descr'] = "Use Foo to check messages for spam.";

This will be a simple on/off switch so we will make it a checkbox.

$sa_params['use_foo']['type'] = 'checkbox';

SpamAssassin sees things as on if they have value of 1 and off if the
value is 0. So we'll set 'values' to 1 and the 'default' to 0;

$sa_params['use_foo']['values'] = 1;
$sa_params['use_foo']['default'] = 0;

The last thing we need to do is enable the setting in $allowed_params.

$allowed_params = array('required_hits', ..., 'use_foo');

If you create a settings entry for a standard SpamAssassin setting
that is not already included, please email me the entry and I'll see
about adding it to the standard package. Thanks!

ADDING ADDITIONAL SECTIONS

You can reorder, remove or add sections to the main options page by
changing $allowed_sections in sasql_conf.php. This will allow you to
easily add additional, local features to your setup. Included is an example
for adding a dspam opt-in/opt-out page.

To add a custom section, create a file called <section_name>.inc.php where
<section_name> is the name of the section (without <>'s). Then add
<section_name> to $allowed_sections.

ADDITIONAL FEATURES

o Read-only support for '@GLOBAL' settings. Users' settings will
default to value of the setting for the '@GLOBAL' user. '@GLOBAL'
settings must be set in the database "by hand." There is no way to
edit them through the plugin. Note: Global settings that are not in
the $allowed_params list are not shown by the plugin.

o The included process-spam.pl Perl script can be used with the 'x-spam-days'
setting to clean out users' spam folders. It should be reasonably easy to
modify to fit your server setup. Run 'pod2text process-spam.pl' for the
script's documentation.

o Add the from address to the white/blacklist from the message view.

FAQ

Q: How can I make sasql store the user name as 'foo@domain' instead
of 'foo'?
A: sasql gets the user name from SquirrelMail which, generally,
doesn't include the domain. You can use the 'vlogin' plugin to append
a default domain to the user name. You can also set a default domain
in sasql_conf.php.

Q: Why is SA ignoring my users' whitelist/blacklist preferences?
A: Make sure that spamd is invoked with '-q', otherwise, it will not even
attempt to connect to your database and will not retrieve any user
preferences, even if you have the correct dsn settings in your local.cf
SA configuration file. You can use the '-x' option to tell spamd to ignore
settings users' .spamassassin directories.

Q: Why do you have a dspam section in a SpamAssassin plugin?
A: The short answer is that I use both at the ISP I work at and wanted a
unified place to configure both. (My setup is doc'd at
http://perlstalker.amigo.net/courier/dspam.phtml.) It also provides a
good example of a way to include local spam filter customizations that
may or may not interact with SpamAssassin.

DEBUGGING NOTE

If you are getting strange DB errors from PEAR you might try this
suggestion from Paul Civati:

- To aid debugging I also changed some of the error statements
  from DB::errorMessage($dbh) to $res->getDebugInfo().  This doesn't
  appear to be a documented call so you probably won't want to add
  that in case it disappears from PEAR one day.  I found it useful
  because I had an incorrect table name in the config file and thus
  lots of PHP errors, but nothing more explanatory than a unknown
  DB error from PEAR.

BUGS

o None that I know of.

ACKNOWLEDGMENTS

This plugin is based on php-sa-mysql-0.5.tar.gz by Justin England
which can be downloaded from http://www.spamassassin.org/devel/.

Thanks to the SquirrelMail team for builing such a great app and
for all the work they do to keep it running.

Thanks to all those who have sent in patches and suggested
improvements.
