From 7c089f71667a1a14cc508772ca289d4d1d2edd27 Mon Sep 17 00:00:00 2001
From: Guilhem Moulin <guilhem@fripost.org>
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/spamassassin.sql')

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

