Amavisd Quarantine with amavisnewsql squirrelmail plugin

This is article explains how to install amavisnewsql to your existing amavisd sytem. Amavisd-new (http://www.ijs.si/software/amavisd/) is an incredibly powerful application that sits between your MTA and various anti-virus and spam detection software. I’m currently using Amavisd-new with Postfix (http://www.postfix.org/), Spamassassin (http://spamassassin.apache.org/)and ClamAV (http://www.clamav.net/) all wrapped up with a MySQL backend to manage virtual users.

In the past, I was using Amavis to coordinate between Spamassassin and ClamAV for SPAM and virus protection. I was not quarantining any messages (with the exception of viruses). I was ‘tagging’ SPAM so the subject line contained ***SPAM***. This was putting the focus on the user to create filters with their mail client software. I wanted to implement a method where SPAMs were never delivered, but still give the user the ability to ‘release’ messages in case of false positives, etc.

I recently discovered the SquirrelMail (http://www.squirrelmail.org/) plugin called amavisnewsql (http://www.squirrelmail.org/plugin_view.php?id=224). This great plugins creates a MySQL backend where Amavis can query per user SPAM levels and handling. This will cause Amavis to quarantine the message into the /var/amavis/quarantine folder. Every 5 minutes a cron job will parse those messages and insert them into the database. Digest report emails are sent off once a day, where users can log into their webmail accounts to release mails. They’ll also have the ability to delete messages, whitelist recipients and control their spamassassin settings.

This howto will describe the process of installing this plugin and configure amavisd. I will follow the README provided, but fill in additional instruction that were discovered during the install on my Gentoo server.

This howto assumes that you have the following

This howto assumes that you have the following
– Gentoo server
– Amavisd-new, Squirrelmail, postfix, mysql, spamassassin already configured and working to some level

Step one:
Create the directory .notstored under your spamquarantine directory
(usually /var/virusmails) and make it owned by the amavis user. When
a message is not able to be processed it will be placed in this directory
and an error written to the mail log.

mkdir /var/amavis/quarantine/.notstored chown amavis:amavis /var/amavis/quarantine/.notstored/

Step two:
Install PEAR modules –

emerge -pv PEAR-Net_SMTP PEAR-Log PEAR-DB

Step three:
Unpack in the plugins directory of your SM installation.

cp /usr/local/src/amavisnewsql /var/www/domain.com/webmail/plugins/. -R cd /var/www/domain.com/webmail/plugins/ rm CHANGELOG UPGRADE README

Step four:
Copy config.php.dist to config.php and edit config.php to change the
DSN and domain names for your webmail URL and digest mailings.

cp config.php.dist config.php mv config.php.dist config.dist.php vi config.php change -> $CONFIG["dsn"] = 'mysql://amavis:password@localhost/amavis'; change -> $CONFIG["default_domain"] = "domain.com"; change -> $CONFIG["webmail_url"] = "https://domain.com/webmail/"; change -> $CONFIG["digest_subject"] = "Mail Server Quarantine Report"; change -> $CONFIG["digest_from"] = '"Domain Spam Quarantine" <support@domain.com>';

Step five:
Create the database called amavis.
Create the amavis user account and apply permissions to the amavis database only
I gave him SELECT INSERT UPDATE and DELETE only

Step six:
mport the schema file in the utils directory appropriate for your DB
– Default schema files are provided for Postgres and MySQL

cd utils/<br> mysql -u root -p amavis < schema.mysql

Step seven:
Edit cleanquarantine.php and set the path info.

vi cleanquarantine.php change -> #!/usr/local/bin/php -q to #!/usr/bin/php change -> DEFINE ("BASEINCLUDE", "/htdocs/squirrel/plugins/amavisnewsql/");

Step eight:
Edit generatedigest.php and set the path info.

vi generatedigest.php change -> #!/usr/local/bin/php -q to #!/usr/bin/php change -> DEFINE ("BASEINCLUDE", "/htdocs/squirrel/plugins/amavisnewsql/"); change -> include(BASEINCLUDE."htmlMimeMail/htmlMimeMail.php");

Step nine:
Edit the process_bsmtp.php script and change the BASEINCLUDE setting to
match your directory. This script must be located on the same system with your
amavis-new install since it picks up quarantined messages and places
them in the database.

vi process_bsmtp.php change -> #!/usr/local/bin/php -q to #!/usr/bin/php change -> DEFINE ("BASEINCLUDE", "/htdocs/squirrel/plugins/amavisnewsql/"); change -> DEFINE ("QUARANTINEDIR", "/var/virusmails"); change -> } else if (preg_match("/^X-Spam-Status:.*hits=([0-9]+\.[0-9]+)/", $line, $match)) { to } else if (preg_match("/^X-Spam-Status:.*score=([0-9]+\.[0-9]+)/", $line, $match)) {

Step ten:
Set up cron jobs for cleanquarantine.php, process_bsmtp.php and
generatedigest.php (suggested entries listed below)

*/5 * * * * /webmail/plugins/amavisnewsql/utils/process_bsmtp.php 0 15 * * * /webmail/plugins/amavisnewsql/utils/cleanquarantine.php 30 16 * * * /webmail/plugins/amavisnewsql/utils/generatedigest.php

Step eleven:
In amavisd.conf change the default sql statement for
$sql_select_white_black_list to the following:

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

vi /etc/amavisd.conf

Step twelve:
Also in amavisd.conf be sure to enable sql lookups with
@lookup_sql_dsn to match your database settings.

@lookup_sql_dsn = ( ['DBI:mysql:database=amavis;host=localhost', 'amavis', 'password'] );

Step thirteen:
Also in amavisd.conf Use the bsmtp methods:

$virus_quarantine_method = "bsmtp:$QUARANTINEDIR/virus-%i-%n.bsmtp"; $spam_quarantine_method = "bsmtp:$QUARANTINEDIR/spam-%b-%i-%n.bsmtp";


  $virus_quarantine_to = 'virus-quarantine';

$final_virus_destiny = D_DISCARD; # (defaults to D_DISCARD) $final_banned_destiny = D_DISCARD; # (defaults to D_BOUNCE) $final_spam_destiny = D_DISCARD; # (defaults to D_BOUNCE) $final_spam_destiny = D_DISCARD; $final_bad_header_destiny = D_PASS; # (defaults to D_PASS), D_BOUNCE suggested

At this point, I figured I was rolling, but I was having some problems. The problem I ran into, Amavis was not quarantining emails. After subscribing to the amavis-users mailing list (https://lists.sourceforge.net/lists/listinfo/amavis-user) and setting high debug levels, we discovered that there were some incorrect values in the ‘policy’ table for spam_quarantine_to. These values need to be set to ‘NULL’. Once this was set, I was seeing quarantined emails.

Next, I was noticing bad-header emails being quarantined. To prevent this, add the following to your amavid.conf

$final_bad_header_destiny = D_PASS; $bad_header_quarantine_method = 'local:badh-%m'; $bad_header_quarantine_to = undef;

I also had an existing system using postfix and postfixadmin to manage my virtmail users. For quarantining to start working they need to be added to the ‘users’ table in amavis. This occurs automatically upon logging into webmail. But I need to get users in first to start handling their spam. I wrote this simple CLI-PHP script to suck users from my postfix table and insert them into my amavis table using addamavisuser.php script.

vi addusers.php


#!/usr/bin/php

<?

$DBserver       = "localhost";

$DBname         = "postfix";

$DBuser         = "postfixadmin";

$DBpassword     = "password";

// put it all together

if(!($connect = mysql_connect($DBserver, $DBuser, $DBpassword))){

        echo "Error Connecting to the Database.";

        //echo mysql_error();

        exit();

}

$DB             = mysql_select_db($DBname);

$sql            = "SELECT *

                                FROM mailbox

                                WHERE username <> ''";

if(!$rs = mysql_query($sql,$connect)){

        echo "Database Error";

        exit();

}

while($row = mysql_fetch_object($rs)){

        $email  = $row->username;

        system("./addamavisuser.php $email ' ' $email");

}

mysql_free_result($rs);

?>

chmod 700 addusers.php ./addusers.php

I also wanted to make a simple query to see who my SPAM hogs were on the server. I’ll probably create a cron job, but this is something that I’ll want to run every once and awhile.. not all the time.

mysql -u root -p mysql> use amavis; Database changed mysql> mysql> SELECT COUNT(msg.id) AS SpamCount, users.username -> FROM msg -> INNER JOIN msgowner ON msg.id = msgowner.msgid -> INNER JOIN users ON msgowner.rid = users.id -> GROUP BY username -> ORDER BY SpamCount DESC;

This will give you a nice read out of numbers of spam with each email address that’s associated in the quarantine.