[Mimedefang] Getting Geylisting working

[Andrew Watkins]

I have now a working greylist setup with SQLite v3, which was modified 
from the original Michael Lang version:

What was missing from the code was the timedelay setup which was there 
in the postges and DB versions, but not the SQLite version.
When I have the whitelisting working I will post it on a web page.

% cat CreateDB.pl
#!/usr/local/bin/perl -w
use DBI;
use strict;

my $dbh = DBI->connect('dbi:SQLite:/etc/mail/db/GreylistSQLite.db', '', 
'') ||
  die "Couldnt open Database: $@\n";

$dbh->do("BEGIN TRANSACTION;");
my $table = $dbh->prepare("CREATE TABLE \"greylisting\" ( ".
        "\"tripple\" CHAR(255) NOT NULL, ".
        "\"sessionid\" CHAR(50) NOT NULL, ".
        "\"timestamp\" CHAR(12) NOT NULL, ".
        "\"datestamp\" DATETIME NOT NULL, ".
        "PRIMARY KEY ( \"tripple\" ));");
$table->execute();
$table = $dbh->prepare("CREATE INDEX \"index_timestamp\" ".
  "ON \"greylisting\" ( \"timestamp\" );");
$table->execute();
$table = $dbh->prepare("CREATE UNIQUE INDEX \"pindex_tripple,sessionid\" ".
  "ON \"greylisting\" (   \"tripple\" );");
$table->execute();
undef($table);
$dbh->do("COMMIT;");
$dbh->disconnect;
#################################


sub filter_recipient {

     #We still need some whitelisting, which is being tested

     # do Greylisting check
     my $id = percent_encode_for_graphdefang($MsgID);
     # if we already passed the Greylisting torture for this Tripple 
continue
     if (defined($Greylistpassed->{$id})) {  return ('CONTINUE', 'OK'); }

     # define the Greylisting Timeout
     my $GreylistTimeout = 300;

     # SQLite2 Timestamp syntax YYYY-MM-DD HH:MM:SS
     my $datestamp = strftime "%Y-%m-%d %H:%M:%S", localtime(time());
     my $now = time;
     my $timestamp = $now - $GreylistTimeout;

     my $GreyList = 
DBI->connect('dbi:SQLite:/etc/mail/db/GreylistSQLite.db', '', '',
        {PrintError=>0, Taint=>0} ) or
        md_syslog('warning', 'Greylist: Couldnt connect GreylistDB') && 
return ('CONTINUE', 'OK');
     #

     # build the tripple
     my $tripple = "$RelayAddr/$Sender/" . $Recipients[0];

     md_syslog('warning', "greylist_in: $tripple, $id, $timestamp" );

     $query = $GreyList->prepare("SELECT DISTINCT tripple, timestamp, 
sessionid FROM \"greylisting\"".
                " WHERE \"tripple\" = ? ;") ||
        md_syslog('warning', 'Greylist: Problem on Query: '. 
$GreyList->errstr) &&
        return ('CONTINUE', 'OK');
     $query->execute($tripple);
     while ( $row = $query->fetchrow_hashref )  {
        if ($row->{'sessionid'} eq $id) {
                md_syslog('warning', 'Greylist: Session didnt end, 
reject again');
                $query->finish();
                undef($query);
                $GreyList->disconnect();
                return ('TEMPFAIL', 'Greylisting active, please try 
again later');
        } else {
                if ( int($row->{'timestamp'}) <= $timestamp) {
                   $query->finish();
                   md_syslog('warning', 'greylist_delete: '.$tripple );
                   $query= $GreyList->prepare("DELETE FROM 
\"greylisting\" WHERE".
                            " \"tripple\" = ?") ||
                        md_syslog('warning', 'Greylist: Problem on 
delete: '. $GreyList->errstr) &&
                        return ('CONTINUE', 'OK');
                   $query->execute($tripple);
                   $query->finish();
                   undef($query);
                   $GreyList->disconnect();
                   return ('CONTINUE', 'OK');
               } else {
                md_syslog('warning', "Greylist: come back later: $tripple");
                $query->finish();
                undef($query);
                $GreyList->disconnect();
                return ('TEMPFAIL', 'Greylisting active, please try 
again later');
               }
        }
     }
     $query->finish();
     undef($query);
     md_syslog('warning', "greylist_insert: $tripple, $id" );
     $insert = $GreyList->prepare("INSERT INTO \"greylisting\"".
        " (\"tripple\", \"sessionid\", \"timestamp\", \"datestamp\")".
        " VALUES (?, ?, ?, ?);") ||
        md_syslog('warning', 'Greylist: Problem inserting: '. 
$GreyList->errstr) &&
        return ('CONTINUE', 'OK');
     $insert->execute($tripple, $id, $now, $datestamp) ||
        md_syslog('warning', "Greylist: couldn't insert tripple 
$tripple, $id, $timestamp,".
$GreyList->errstr) && return ('CONTINUE', 'OK');
     $insert->finish();
     undef($insert);
     $GreyList->disconnect();

     return ('TEMPFAIL', 'Greylisting active, please try again later');
}