How to store the bayes filter in a MySQL database.

1 files changed, 133 insertions, 12 deletions

diff --git a/fripost-docs.org b/fripost-docs.org
index a1037bb..24aae1b 100644
--- a/fripost-docs.org
+++ b/fripost-docs.org
@@ -226,12 +226,9 @@ sudo aptitude install logcheck syslog-summary
 
     run_daemon="true"
 
-
 * Next Steps
 ** Configuring the backup solution
-
 *** Bacula configuration
-
 *** Simple rsync solution
 
 General idea [[http://wikis.sun.com/display/BigAdmin/Using+rdist+rsync+with+sudo+for+remote+updating][from here]].  This is just a basic setup for now, will need to be
@@ -1235,6 +1232,139 @@ sudo /etc/init.d/dovecot restart
 sudo update-rc.d -n dovecot stop 2 3 4 5 .
 sudo update-rc.d -n postfix stop 2 3 4 5 .
 
+*** Configuring an Anti-SPAM on the Mail Delivery Agent (MDA)
+We will use Amavisd-new v2.7 (earlier version do not support per-user bayes database).
+The WhiteList and the Bayes filter are stored in a MySQL database, managed
+directly by SpamAssassin. Viruses are detected by ClamAV.
+The use of Amavis allows us to have a per-user configuration (for instance, everyone
+can opt-in/-out the Virus scaning or fine-tune the sensibility of the Anti-SPAM).
+
+**** Install packages
+  apt-get install clamav clamav-daemon clamav-freshclam
+  apt-get install mysql-server spamassassin razor spamc
+  apt-get install -t squeeze-backports amavisd-new
+
+The user 'clamav' needs to be added to the group 'amavis':
+  adduser clamav amavis
+
+We need to remove '/etc/cron.daily/amavisd-new', now subsumed by '/etc/cron.d/amavisd-new':
+  rm /etc/cron.daily/amavisd-new
+
+**** Install the MySQL database
+
+  sudo mysql -p
+  mysql> CREATE DATABASE spamassassin CHARSET utf8 COLLATE utf8_unicode_ci;
+  mysql> use spamassassin;
+
+A SQL script to create tables to store the WhiteList and Bayes filter can be found
+in '/usr/share/doc/spamassassin/sql/awl_mysql.sql', and
+'/usr/share/doc/spamassassin/sql/bayes_mysql.sql', however it does not work well with
+the unicode character set. We use a custom script instead:
+
+CREATE TABLE awl (
+  username  VARCHAR(100)   CHARACTER SET latin1 NOT NULL DEFAULT '',
+  email     VARBINARY(255)                      NOT NULL DEFAULT '',
+  ip        VARCHAR(40)    CHARACTER SET latin1 NOT NULL DEFAULT '',
+  count     INT(11)                             NOT NULL DEFAULT 0,
+  totscore  FLOAT                               NOT NULL DEFAULT 0,
+  signedby  VARCHAR(255)   CHARACTER SET latin1 NOT NULL DEFAULT '',
+  PRIMARY KEY (username,email,signedby,ip)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+CREATE TABLE bayes_global_vars (
+  variable VARCHAR(30)  CHARACTER SET latin1 NOT NULL DEFAULT '',
+  value    VARCHAR(200) CHARACTER SET latin1 NOT NULL DEFAULT '',
+  PRIMARY KEY (variable)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO bayes_global_vars VALUES ('VERSION','3');
+
+CREATE TABLE bayes_vars (
+  id                 INT(11)                           NOT NULL AUTO_INCREMENT,
+  username           VARCHAR(200) CHARACTER SET latin1 NOT NULL DEFAULT '',
+  spam_count         INT(11)                           NOT NULL DEFAULT 0,
+  ham_count          INT(11)                           NOT NULL DEFAULT 0,
+  token_count        INT(11)                           NOT NULL DEFAULT 0,
+  last_expire        INT(11)                           NOT NULL DEFAULT 0,
+  last_atime_delta   INT(11)                           NOT NULL DEFAULT 0,
+  last_expire_reduce INT(11)                           NOT NULL DEFAULT 0,
+  oldest_token_age   INT(11)                           NOT NULL DEFAULT 2147483647,
+  newest_token_age   INT(11)                           NOT NULL DEFAULT 0,
+  PRIMARY KEY (id),
+  UNIQUE KEY bayes_vars_idx1 (username)
+) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;
+
+CREATE TABLE bayes_expire (
+  id      INT(11),
+  runtime INT(11) NOT NULL DEFAULT 0,
+  KEY bayes_expire_idx1 (id),
+  FOREIGN KEY (id) REFERENCES bayes_vars(id) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+CREATE TABLE bayes_seen (
+  id    INT(11),
+  msgid VARCHAR(200) CHARACTER SET latin1 NOT NULL DEFAULT '',
+  flag  CHAR(1)      CHARACTER SET latin1 NOT NULL DEFAULT '',
+  FOREIGN KEY (id) REFERENCES bayes_vars(id) ON DELETE CASCADE ON UPDATE CASCADE,
+  PRIMARY KEY (id,msgid)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+CREATE TABLE bayes_token (
+  id         INT(11),
+  token      BINARY(5) NOT NULL DEFAULT '',
+  spam_count INT(11)   NOT NULL DEFAULT 0,
+  ham_count  INT(11)   NOT NULL DEFAULT 0,
+  atime      INT(11)   NOT NULL DEFAULT 0,
+  FOREIGN KEY (id) REFERENCES bayes_vars(id) ON DELETE CASCADE ON UPDATE CASCADE,
+  PRIMARY KEY (id,token),
+  KEY bayes_token_idx1 (id,atime)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+
+Note that the 'InnoDB' engine needs to be allowed by the MySQL configuration. We
+can now create our users and grant minimal rights:
+
+mysql> create user 'sa_awl'@'localhost' IDENTIFIED BY '...';
+mysql> create user 'sa_bayes'@'localhost' IDENTIFIED BY '...';
+mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON spamassassin.awl               TO 'sa_awl'@'localhost';
+mysql> GRANT SELECT, INSERT,         DELETE ON spamassassin.bayes_seen        TO 'sa_bayes'@'localhost';
+mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON spamassassin.bayes_token       TO 'sa_bayes'@'localhost';
+mysql> GRANT SELECT                         ON spamassassin.bayes_global_vars TO 'sa_bayes'@'localhost';
+mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON spamassassin.bayes_vars        TO 'sa_bayes'@'localhost';
+mysql> GRANT SELECT, INSERT,         DELETE ON spamassassin.bayes_expire      TO 'sa_bayes'@'localhost';
+
+We're now ready to configure SpamAssassin:
+  touch /etc/spamassassin/sql.cf
+  chown root:amavis /etc/spamassassin/sql.cf
+  chmod 0644 /etc/spamassassin/sql.cf
+
+  :: /etc/spamassassin/sql.cf
+    bayes_store_module          Mail::SpamAssassin::BayesStore::MySQL
+    bayes_sql_dsn               DBI:mysql:spamassassin:localhost
+    bayes_sql_username          sa_bayes
+    bayes_sql_password          ...
+
+    auto_whitelist_factory      Mail::SpamAssassin::SQLBasedAddrList
+    user_awl_dsn                DBI:mysql:spamassassin:localhost
+    user_awl_sql_username       sa_awl
+    user_awl_sql_password       ...
+
+
+**** Configure Amavis
+
+  :: /etc/amavis/conf.d/05-domain_id
+    $mydomain = "fripost.org";
+    @local_domains_acl = ( ".$mydomain" );
+    @local_domains_maps = ( ".$mydomain" );
+
+  :: /etc/amavis/conf.d/50-user
+    @sa_username_maps = (
+      new_RE ( [ qr'^([^@]+@.*)'i => '${1}' ] ),
+      'amavis'
+    );
+
+# TODO: use LDAP lookups for per-user configuration
+# http://www.ijs.si/software/amavisd/LDAP.schema
+
 *** Configuring a new smarthost to relay e-mail to the main IMAP server
 **** Overview
 
@@ -1327,7 +1457,6 @@ default is then to allow read access to anyone).
 TODO: add the necessary configuration files
 
 *** Configuring the outgoing SMTP (MTA)
-
 **** Install packages
 
 sudo aptitude install postfix
@@ -2543,21 +2672,15 @@ For the webmail, we only log messages of priority warn or higher.
         endscript
     }
 
-
 ** Necessary stuff to fix for security
 *** Bacula for backups
 Also has tripwire-like capabilities.
 *** OSSEC
-
 *** Firewall rules
 TODO: Add nice rules.
-
 ** Ideas for improved security
-
 *** Monitoring
 
-
-
 * Hardening
 ** Overview
 
@@ -2661,5 +2784,3 @@ We have written some tools to make administration tasks easier. They can be
 found at:
 
     git clone http://git.fripost.org/pub/fripost-tools.git
-
-