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: [EMAIL PROTECTED]";
$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');
}
_______________________________________________
NOTE: If there is a disclaimer or other legal boilerplate in the above
message, it is NULL AND VOID. You may ignore it.
Visit http://www.mimedefang.org and http://www.roaringpenguin.com
MIMEDefang mailing list MIMEDefang@lists.roaringpenguin.com
http://lists.roaringpenguin.com/mailman/listinfo/mimedefang
