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
- 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.

" These values need to be set to 'NULL'. Once this was set, I was seeing quarantined emails."

I think I found the post.. that was the change spam_quarantine_to o "NULL" right?

I'm still not actually seeing anything in the quarantine. I see files in $myhome/quarantine, but the PHP scripts/etc aren't showing me anything in the squirrelmail quarantine.

Did you run into this? How did you resolve it? I did try to contact the plugin author, but haven't heard back yet. :-)

So you're saying that the files are in the physical quarantine, but not making it to the database? If so.. can you verify that no files ever get removed from the folder. If so, we have a couple of possibilities.

The first one that I would look for is to see if the cron job is running.

process_bsmtp.php should be run every 5 minutes to take those physical messages and dump them into the database. If this job is running, but mails are being put into the database that leads to the second possibility.... the mails are NOT in bsmtp format.

You can verify this in your amavisd.conf. have the following in my config
$virus_quarantine_method = 'bsmtp:virus-%i-%n';
$spam_quarantine_method = 'bsmtp:spam-%b-%i-%n';
$banned_files_quarantine_method = 'bsmtp:banned-%i-%n';

I believe if you don't have this set.. it will act similar to what you're experiencing.
Hope this helps
hanji

doh. i forgot the $CONFIG["dsn"] in front of the db.
sorry :(

in webmin i created a db 'amavis'
in the amavisnewsql plugin dir config.php
i have this
'mysql://amavis:mypassw0rd@127.0.0.1/amavis';
i have tried replacing 127.0.0.1 with localhost.
when i log into SM i get this error at the bottom of the page:
Error: DB Error in connect Message = 'DB Error: not found'
also if i click on the Quarantine link i get this error:
Fatal error: Call to undefined method DB_Error::query() in /var/www/localhost/htdocs/squirrelmail/plugins/amavisnewsql/amavisnewsql.class.php on line 1199

thanks for the guide!
i'm stuck at the last step.

mysql> use amavis;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

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;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql> SELECT COUNT(msg.id) AS SpamCount, users.username
FROM msg
INNER JOIN msg' at line 1
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;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql> SELECT COUNT(msg.id) AS SpamCount, users.username FROM msg INNER JOIN msg' at line 1
mysql> quit
Bye

Ooops.. my bad. You have an extra mysql>

The query should be:
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;

HTH