From 7c089f71667a1a14cc508772ca289d4d1d2edd27 Mon Sep 17 00:00:00 2001 From: Guilhem Moulin Date: Mon, 9 Dec 2013 08:11:16 +0100 Subject: Configure the content filter. Antispam & antivirus, using ClamAV and SpamAssassin through Amavisd-new. Each user has his/her amavis preferences, and own Bayes filter (to maximize privacy). One question remains, though: how to set spamassassin's trusted_networks / internal_networks / msa_networks? It seems not obivious to get it write with IPSec and dynamic IPs. (Cf. https://wiki.apache.org/spamassassin/AwlWrongWay) --- roles/IMAP/files/tmp/spamassassin.sql | 57 +++++++++++++++++++++++++++++++++++ 1 file changed, 57 insertions(+) create mode 100644 roles/IMAP/files/tmp/spamassassin.sql (limited to 'roles/IMAP/files/tmp') diff --git a/roles/IMAP/files/tmp/spamassassin.sql b/roles/IMAP/files/tmp/spamassassin.sql new file mode 100644 index 0000000..ed2e641 --- /dev/null +++ b/roles/IMAP/files/tmp/spamassassin.sql @@ -0,0 +1,57 @@ +-- Sources: https://svn.apache.org/repos/asf/spamassassin/trunk/sql/awl_mysql.sql +-- https://svn.apache.org/repos/asf/spamassassin/trunk/sql/bayes_mysql.sql + +CREATE TABLE awl ( + username VARCHAR(100) NOT NULL DEFAULT '', + email VARBINARY(255) NOT NULL DEFAULT '', + ip VARCHAR(40) NOT NULL DEFAULT '', + count INT(11) NOT NULL DEFAULT 0, + totscore FLOAT NOT NULL DEFAULT 0, + signedby VARCHAR(255) NOT NULL DEFAULT '', + PRIMARY KEY (username,email,signedby,ip) +) ENGINE=InnoDB; + +CREATE TABLE bayes_expire ( + id INT(11) NOT NULL DEFAULT 0, + runtime INT(11) NOT NULL DEFAULT 0, + KEY bayes_expire_idx1 (id) +) ENGINE=InnoDB; + +CREATE TABLE bayes_global_vars ( + variable VARCHAR(30) NOT NULL default '', + value VARCHAR(200) NOT NULL default '', + PRIMARY KEY (variable) +) ENGINE=InnoDB; +INSERT INTO bayes_global_vars VALUES ('VERSION','3'); + +CREATE TABLE bayes_seen ( + id INT(11) NOT NULL DEFAULT 0, + msgid VARCHAR(200) BINARY NOT NULL DEFAULT '', + flag CHAR(1) NOT NULL DEFAULT '', + PRIMARY KEY (id,msgid) +) ENGINE=InnoDB; + +CREATE TABLE bayes_token ( + id INT(11) NOT NULL DEFAULT 0, + 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, + PRIMARY KEY (id, token), + INDEX bayes_token_idx1 (id, atime) +) ENGINE=InnoDB; + +CREATE TABLE bayes_vars ( + id INT(11) NOT NULL AUTO_INCREMENT, + username VARCHAR(200) 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 bayes_vars_idx1 (username) +) ENGINE=InnoDB; -- cgit v1.2.3