[Maia-users] HOW TO: Delete High Score SPAM (amavis $sa_quarantine_cutoff_level fix)

[Robert LeBlanc]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Sebastian A. Aresca wrote:
> I've testing Maia MailGuard for 2 weeks and I found the problem:
> "Too many emails on SPAM quarantine".
> So I built this little script and then cron it to remove records with score greater than X.

Yes, this is an item that will be dealt with in both 1.0.3 and 1.1.0,
albeit in different ways.  The solution for 1.0.3 will be a feature
incorporated into the maiadbtool.pl script, which can be run from the
command line and scheduled with cron.  The "proper" solution is of
course to avoid storing these unwanted items in the first place, which
is what 1.1.0 will do when it adds more flexible score thresholds and
actions.


> ###################################################################################################################################
> #!/bin/bash
> 
> echo -n "Deleting records ... "
> mysql -v -D maia -u root -B -e "DELETE FROM maia_mail_recipients WHERE mail_id IN (SELECT * FROM HighScore)"
> mysql -v -D maia -u root -B -e "DELETE FROM maia_sa_rules_triggered WHERE mail_id IN (SELECT * FROM HighScore)"
> mysql -v -D maia -u root -B -e "DELETE FROM maia_viruses_detected WHERE mail_id IN (SELECT * FROM HighScore)"
> mysql -v -D maia -u root -B -e "DELETE FROM maia_banned_attachments_found WHERE mail_id IN (SELECT * FROM HighScore)"
> mysql -v -D maia -u root -B -e "DELETE FROM maia_mail WHERE score > 20"
> echo "OK"
> 
> ###################################################################################################################################
> 
> This should remove all the spam with score greater than 20.

There's no need to invoke mysql separately for each query, of course.
If you put all of those queries together in a single SQL script, you can
have cron call mysql just once.  Just remember to terminate each of your
queries with a semicolon.

For safety's sake, though, you probably want to encapsulate all of those
statements in a consistent-snapshot transaction, to ensure that they all
operate on the same data set, so that new items that arrive in the
middle of the process don't mess things up. e.g.

Ahead of time create the index and the view as a temporary table (the
temporary table is necessary in order to be able to delete from the
maia_mail table, since it's referenced in the view itself):

 CREATE INDEX maia_mail_idx_score ON maia_mail (score);

 CREATE ALGORITHM = TEMPTABLE VIEW maia.HighScore AS
        SELECT maia_mail.id AS id
               FROM maia_mail
               WHERE maia_mail.score >= 20;

Then in your script, e.g. delete_high-scoring_spam.sql:

 START TRANSACTION WITH CONSISTENT SNAPSHOT;
   DELETE FROM maia_mail_recipients
          WHERE mail_id IN (SELECT * FROM maia.HighScore);
   DELETE FROM maia_sa_rules_triggered
          WHERE mail_id IN (SELECT * FROM maia.HighScore);
   DELETE FROM maia_viruses_detected
          WHERE mail_id IN (SELECT * FROM maia.HighScore);
   DELETE FROM maia_banned_attachments_found
          WHERE mail_id IN (SELECT * FROM maia.HighScore);
   DELETE FROM maia_mail
          WHERE id IN (SELECT * FROM maia.HighScore);
 COMMIT;

You can then call this SQL script as usual, e.g.

  mysql -u root -p maia < delete_high-scoring_spam.sql

Note that this sort of process is not without its share of side-effects,
since it bypasses Maia's normal deletion mechanisms.  In particularly it
will mean that your statistics will become inaccurate, since these
deleted items will not be recorded as such--it will be as if they were
never received at all.  A more thorough script would take care of that
bit of bookkeeping as well.

Another potential concern is that this sort of process could confuse
users if they login to the web interface and see that there are 300
items in their spam quarantine and then find only 70 items in there when
they click on the quarantine link, if the deletion script happens to run
in the meantime.  This will particularly affect digest subscribers, who
may receive an email digest that lists items that may very well be
deleted by the time they finally login to view them.  I would suggest
that you also modify the SELECT query in the send-quarantine-digests.pl
script to only list items with scores lower than your deletion
threshold, since higher-scoring items will ultimately be deleted when
the deletion script runs.

- --
Robert LeBlanc <rjl at renaissoft.com>
Renaissoft, Inc.
Maia Mailguard <http://www.maiamailguard.com/>

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.7 (GNU/Linux)

iD8DBQFGrrTQGmqOER2NHewRAqA+AJ94NGx/l0Dfx4SlN9/pR3ZV7PwtuQCgk1k9
Fp9CNpOW78KifmO7GzVTSlg=
=M9BT
-----END PGP SIGNATURE-----