Updated greylisting database cleanup script

[Thratchen]

After using the amazing greylisting/qmail patch by Martin Dempsey mjd@digitaleveryware.com (& patched into plesk by Brent Meshier) I have been keeping an eye on the database that this generates.

If like me some of your users subscribe to mailing lists the size of the database can swell to a very great size indeed and typically 40-80% of the entries can be from mailing lists delivery attempts.
Normally this would still not be a problem but on a very busy system this could slow down the initial lookups quite considerably.

mailing lists programs like mailman use one-time-use email addresses when forwarding mails to subscribers. I'v personally seen this account for 75% of the entries in my local mailservers greylist database.

So Iv taken to cleaning the database out more regularly with some slightly stronger SQL;

This is the initial SQL for cleaning the database. Which is pretty good but

DELETE FROM relaytofrom WHERE record_expires < NOW() - INTERVAL 1 HOUR AND origin_type = 'AUTO'

I have also added two further queries which VASTLY, and to my mind quite safely, reduce the number of records in the database.

DELETE FROM relaytofrom WHERE passed_count = 0 AND last_update < NOW() - INTERVAL 1 DAY AND origin_type = 'AUTO'
DELETE FROM relaytofrom WHERE mail_from LIKE '%-return-%' AND blocked_count = 1 AND passed_count = 1 AND origin_type = 'AUTO' AND create_time < NOW() - INTERVAL 1 DAY

The first removed entries that have led to no successful deliveries and are more than one day old.
The second is aimed at the mailing lists and removed entries older than one day with -return- somewhere in the mail_from and passed_count & origin_type both at 1.




So here below is the amended greylist clean up script which includes all of the above mentioned cleanups
!!Remember to add your own login details!!

#!/usr/bin/perl
use strict;
use warnings;

use constant DBD => 'DBI:mysql:DATABASE_NAME:localhost:3306';
use constant DBUSER => 'DATABASE_USERNAME';
use constant DBPASS => 'DATABASE_PASSWORD';

use DBI;

system ("cat /dev/null > /tmp/greylist_dbg.txt");

my $dbh = DBI->connect(DBD,DBUSER,DBPASS) or die "can't connect to db ", $DBI::errstr, ":$!";

$dbh->do("DELETE FROM relaytofrom WHERE record_expires < NOW() - INTERVAL 1 HOUR AND origin_type = 'AUTO'");

# Clean out entries for mails that were not delivered with the last day
$dbh->do("DELETE FROM relaytofrom WHERE passed_count = 0 AND last_update < NOW() - INTERVAL 1 DAY AND origin_type = 'AUTO'");

# Clean out entries from mailing lists older then 1 day.
# They typically have -return- somewhere in their from address and only get delivered once ever (hence the blocked_count & passed_count both at 1 )
# PLEASE CHECK THIS IS SUITABLE FOR YOUR SYSTEM BEFORE IMPLEMENTING
# -----------------------------------------------------------------
$dbh->do("DELETE FROM relaytofrom WHERE mail_from LIKE '%-return-%' AND blocked_count = 1 AND passed_count = 1 AND origin_type = 'AUTO' AND create_time < NOW() - INTERVAL 1 DAY");

$dbh->do("OPTIMIZE TABLE relaytofrom");

$dbh->disconnect;

exit;

Comments and other suggestions are always welcome

 

[ClausVB]

I want to translate (german) your thread and post it at http://clausvb.de/.

I think I need your and Brent's approval.

I would say:
"powered by Thratchen. Orginal script by Brent Meshier. All rights reserved."

And link to your thread here. Is this ok?

Regards,
Claus