From ebe51a8ebbf2518b7cbd656f2a87ddd239c7014c Mon Sep 17 00:00:00 2001 From: Guilhem Moulin Date: Thu, 6 Dec 2012 17:15:34 +0100 Subject: How to store the bayes filter in a MySQL database. --- fripost-docs.org | 145 ++++++++++++++++++++++++++++++++++++++++++++++++++----- 1 file 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 - - -- cgit v1.2.3