Set-up SQL quarantine with Amavisd-new and ISPConfig

1. Prerequisites

A working Postfix+Amavis stack with ISPConfig
A working SQL (PostgreSQL, MySQL...) database
Optional : a working mail server with PHP (for Mailzu)

Jump to Installation if you know what you are doing.

2. Off-subject generic explanations

2.1 Amavis and ISPConfig policies

In a default ISPConfig installation per-user ISPConfig policies are loaded. The configuration file for Amavis, written by ISPConfig contains :

@lookup_sql_dsn =
   ( ['DBI:mysql:database=dbispconfig;host=127.0.0.1;port=3306', 'ispconfig', 'xxxx'] );
$sql_select_policy =
   'SELECT *,spamfilter_users.id'.
   ' FROM spamfilter_users LEFT JOIN spamfilter_policy ON spamfilter_users.policy_id=spamfilter_policy.id'.
   ' WHERE spamfilter_users.email IN (%k) ORDER BY spamfilter_users.priority DESC';
$sql_select_white_black_list = 'SELECT wb FROM spamfilter_wblist'.
    ' WHERE (spamfilter_wblist.rid=?) AND (spamfilter_wblist.email IN (%k))' .
    ' ORDER BY spamfilter_wblist.priority DESC';

It means that whatever you would set as $sa_spam_subject_tag, $sa_tag_level_deflt, $sa_tag2_level_deflt, $sa_kill_level_deflt, $sa_dsn_cutoff_level, it will be overridden by per-user policies.

The ISPConfig policies can be changed in tab Email => Spamfilter => Policy in ISPConfig panel. If you struggle wondering why your message keeps getting smashed at level 4.5, look at the sa_tag_level in policies. We will have to change values in that policies, to make the SQL quarantine working.

2.2 Lookup DSN and Storage DSN

DSN (Data Source Name) are the connection strings with host, username, and password, used to connect to databases.

Amavis can set two DSN : one for Policies lookup (used to retrieve ISPConfig policies from Panel), and one for storage of mail meta informations and quarantine. We will use the Storage DSN to set up a secondary database for quarantine storage, to not mess with existing ISPConfig database.

2.3 Levels and cutoffs

Amavis uses Spamassassin to score the mail, in order to decide what to do with it. The category of test (spam test, antivirus, etc) and the score along with levels determines the actions Amavis will trigger, and the final destiny where the mail belongs.

Spamassassin levels are :

tag_level : a message above that score will be tagged with X-Spam-Status, X-Spam-Score and X-Spam-Level headers.
tag2_level : a message above that score will be marked as X-Spam-Status: Yes and the subject is changed if sa_spam_modifies_subj is set to true.
kill_level : a message above that score is taken to the final_spam_destiny, and quarantined, it will not be delivered unless D_PASS is set to final_spam_destiny.
dsn_cutoff_level : a message above that level will never trigger a bounce or a reject, whatever spam_destiny is.
quarantine_cutoff_level : a message above that level will not be quarantined.

2.4 Final destinations

Once the message is categorized by Amavis tests (through SpamAssassin, ClamAV, etc), Amavis decides if it should be delivered to user mailbox or not, and if a bounce will be issued.

This is the purpose of $final_virus_destiny, $final_spam_destiny, $final_banned_destiny, $final_bad_header_destiny.

They can take the following values :

D_PASS : mail will be delivered to inbox.
D_BOUNCE : mail will not be delivered, and a delivery status notification will be returned by Postifx to sender (except if the score exceeds the dsn_cutoff level)
D_REJECT : Postfix will answer REJECT to the distant mail server, and the distant mail server may produce a delivery status notification to the user
D_DISCARD : forgive and forget : the mail will not be delivered and the sender is not informed. The mail may be quarantined if the quarantine_cutoff level is not exceeded.

3. Installation

For Amavis : Nothing ! Amavis comes out-of-the-box with SQL storage.
For Mailzu : see Mailzu section.

4. Configuration

4.1 Database

Create an user and a database for quarantine storage :

# mysql -u root -p
mysql> CREATE DATABASE amavis_storage;
mysql> CREATE USER 'amavis_storage'@'localhost' IDENTIFIED BY 'xxxx';
mysql> GRANT ALL PRIVILEGES ON amavis_storage.* TO 'amavis_storage'@'localhost';
mysql> FLUSH PRIVILEGES;

Load the initial schema from Amavis docs (usually located in /usr/share/doc/amavisd-new/ ).

Delete unnecessary tables, as we will be using this database only for mail storage and not for lookups :

# mysql -u amavis_storage -p amavis_storage
mysql> DROP TABLE users;
mysql> DROP TABLE mailaddr;
mysql> DROP TABLE policy;
mysql> DROP TABLE wblist;

Nota Bene : while executing DROP TABLE users, don't be silly, and do not remove mysql users database.

4.2 Amavis

Update your Amavis configuration /etc/amavis/conf.d/50_user :

@storage_sql_dsn = ( ['DBI:mysql:database=amavis_storage;host=127.0.0.1;port=3306', 'amavis_storage', 'xxxx'] );  # none, same, or separate database

# Quarantine SPAM into SQL server.
$spam_quarantine_to = 'spam-quarantine';
$spam_quarantine_method = 'sql:';

# Quarantine VIRUS into SQL server.
$virus_quarantine_to = 'virus-quarantine';
$virus_quarantine_method = 'sql:';

# Quarantine BANNED message into SQL server.
$banned_quarantine_to = 'banned-quarantine';
$banned_files_quarantine_method = 'sql:';

# Quarantine Bad Header message into SQL server.
$bad_header_quarantine_method = 'sql:';
$bad_header_quarantine_to = 'badheader-quarantine';

# Do not store non-quarantined messages info
# You can set it to 1 (the default) to test if Amavis is filling correctly the tables maddr, msgs, and msgcrpt
$sql_store_info_for_all_msgs = 0;

#
# SQL Select statements
#

$sql_select_policy =
   'SELECT *,spamfilter_users.id'.
   ' FROM spamfilter_users LEFT JOIN spamfilter_policy ON spamfilter_users.policy_id=spamfilter_policy.id'.
   ' WHERE spamfilter_users.email IN (%k) ORDER BY spamfilter_users.priority DESC';

$sql_select_white_black_list = 'SELECT wb FROM spamfilter_wblist'.
    ' WHERE (spamfilter_wblist.rid=?) AND (spamfilter_wblist.email IN (%k))' .
    ' ORDER BY spamfilter_wblist.priority DESC';

#
# Quarantine settings
#

$final_virus_destiny = D_BOUNCE;
$final_spam_destiny = D_DISCARD;
$final_banned_destiny = D_BOUNCE;
$final_bad_header_destiny = D_PASS;

# Default settings, we st this very high to not filter aut emails accidently
$sa_spam_subject_tag = '[SPAM] ';
$sa_tag_level_deflt  = 20.0;  # add spam info headers if at, or above that level
$sa_tag2_level_deflt = 60.0; # add 'spam detected' headers at that level
$sa_kill_level_deflt = 60.0; # triggers spam evasive actions
$sa_dsn_cutoff_level = 100;   # spam level beyond which a DSN is not sent
#$sa_debug = 1;

#
# Disable spam and virus notifications for the admin user.
# Can be overridden by the policies in mysql
#

$virus_admin = undef;
$spam_admin = undef;

#
# Enable Logging
#

$DO_SYSLOG = 1;
$LOGFILE = "/var/log/amavis.log";  # (defaults to empty, no log)

# Set the log_level to 5 for debugging
$log_level = 0;                # (defaults to 0)

4.3 ISPConfig policies

Remember that ISPconfig policies are overriding a lot of our configuration in 50_user. In order to majke the quarantine work, you have to reconfigure all the available policies in ISPConfig Panel.

Look at your policies list, you have to change the quarantine settings for every policies :

When editing a policy, on the Quarantine tab, set the destinations :

ISPConfig Mail Spamfilter Policy Quarantine destinations

If you do not fill something in these fields, Amavis will not store quarantined mails in SQL database, and will just discard it !

These fields correspond to the virus_quarantine_to, spam_quarantine_to, banned_quarantine_to, bad_header_quarantine_to variables in Amavis configuration, and an empty value is overriding those we set in Amavis configuration.

4.4 Test

Send some spam to your server, check if the tables are populated :

mysql> SELECT * FROM maddr;

Check if meta informations are populated :

mysql> SELECT * FROM msgs;
mysql> SELECT * FROM msgrcpt;

And if quarantine is filling :

mysql> SELECT * FROM quarantine;

5. Cleanup !

You should not "setup and forget" your quarantine SQL storage. Messages has to be deleted periodically, otherwise your database will grow forever. Look at the documentaion in /usr/share/docs/amavisd-new to make a cronjob like this :

#!/bin/bash

SQL_HOST="localhost";
SQL_LOGIN="amavis_storage"
SQL_PASSWORD="xxxx"
SQL_DB="amavis_storage"

mysql --user="$SQL_LOGIN" --password="$SQL_PASSWORD" --host="$SQL_HOST" $SQL_DB -e " \
  DELETE FROM msgs WHERE time_num < UNIX_TIMESTAMP() - 30*24*3600; \
  DELETE FROM msgrcpt WHERE NOT EXISTS (SELECT 1 FROM msgs WHERE mail_id=msgrcpt.mail_id); \
  DELETE FROM quarantine WHERE NOT EXISTS (SELECT 1 FROM msgs WHERE mail_id=quarantine.mail_id); \
  DELETE FROM maddr WHERE NOT EXISTS (SELECT 1 FROM msgs WHERE sid=id) AND NOT EXISTS (SELECT 1 FROM msgrcpt WHERE rid=id); \
"

6. Mailzu

I have to admit, Mailzu seems a bit obsolete as I had to patch to make it working with Amavis 3.3 tables. But it still works pretty well for a simple task like reading and releasing quarantine mails.

6.1 Installation

Download the source files at http://sourceforge.net/projects/mailzu/.

62. Patch

The existing Mailzu source code is quite old, and the schema of Amavis SQL tables changed. Download and apply this patch in to make Mailzu work.

6.3 Configuration

I suppose that you know how to spawn PHP with CGI to serve the Mailzu files.

Configure your database login and password in config/config.php :

$conf['db']['dbType'] = 'mysql';
$conf['db']['dbUser'] = 'amavis_storage';
$conf['db']['dbPass'] = 'xxxx';
$conf['db']['dbName'] = 'amavis_storage';
$conf['db']['hostSpec'] = 'localhost:3306';

I am using IMAP login to authenticate in Mailzu. Unfortunately, I had to turn off SSL authentication, as it wasn't working. Here is my configuration :

$conf['auth']['serverType'] = 'imap';
$conf['auth']['imap_hosts'] = array( 'localhost:143' );
$conf['auth']['imap_type'] = 'imaptls';
$conf['auth']['imap_domain_name'] = 'example.com';

Don't forget to set yourself "super" :

$conf['auth']['s_admins'] = array ('me@example.com');

And to set your web uri :

$conf['app']['weburi'] = 'https://example.com/mailzu';

6.4 Configure in-app release

Mailzu can also release quarantined mail. I did not implement this function, but you have to set up the amavisd-release internface on an inet socket on port 9998, instead of the existing unix socket located at /var/lib/amavis/amavisd.sock . Read more.

# uname -a