Re: [Mimedefang] Getting Geylisting working

2006-10-10 Thread 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: [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 -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

Re: [Mimedefang] Getting Geylisting working

2006-10-03 Thread Andrew Watkins 


I am still having a few problems with the greylisting. It is working but 
I am getting a lot of "Couldnt insert tripple" in my syslog.


Looking at the logic I can see the problem but at this time not sure the 
best way to fix it.


tripple=RelayAddr/Sender/Recipients
now=current time=2006-10-03 15:02:38
timestamp=greylist time limit=current time - 5 minutes

1) First time e-mail is received, so it is inserted in database:
|RelayAddr/Sender/Recipients|messageID|2006-10-03 15:02:38|

2a) E-mail is delivered again in 4 minutes, so messageID is different 
and timestamp is (5 minutes before):

SELECT DISTINCT tripple, sessionid FROM greylisting
WHERE tripple  = "RelayAddr/Sender/Recipients"
AND timestamp <= "2006-10-03 15:01:38")

Which RETURNS no values, since time stored in database is greater than 
this time.


2b) It does an insert which it will fail due to a clash of tripples.

I just wonder should the logic be altered so that the SELECT retrieves 
tripples from the database and then the TIME is checked. I think this is 
what you do on your postgres and BerkeleyDB versions.


Thanks

Andrew
___
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

Re: [Mimedefang] Getting Geylisting working

2006-09-29 Thread Michael Lang 
On Fri, 2006-09-29 at 12:01 +0100, Andrew Watkins wrote:
> It is looking good.
> 
> - The missing "query->execute();" in the filter_recipient seems to do
> the trick. I will monitor it so check it is 100% working.
> 
> - GreyListCleanup.pl script only had the one error, since I added my own
> error ;-)
>   my ($timstamp, $period, $GreyList, $cleanup);
>
> Should read:
>   my ($timestamp, $period, $GreyList, $cleanup);
> 
> - The Create Database script seems to be OK. The error seems to appear a
> lot on the web, so I have no answer to that one, but I just replaced the
> "prepare" and "execute" with "do" statements.
> 
> Thanks for the quick response


I've tested it today with SQLite (v3) with copy & paste from the Wiki
and it worked even with the SQLite3 engine, maybe the folder permission 
as written before made troubles in your setup with SQLite3.

Kind regards
Michael Lang


> 
> Andrew
> 

-- 
Michael Lang <[EMAIL PROTECTED]>

___
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

Re: [Mimedefang] Getting Geylisting working

2006-09-29 Thread Andrew Watkins 


It is looking good.

- The missing "query->execute();" in the filter_recipient seems to do
the trick. I will monitor it so check it is 100% working.

- GreyListCleanup.pl script only had the one error, since I added my own
error ;-)
my ($timstamp, $period, $GreyList, $cleanup);
 
Should read:
my ($timestamp, $period, $GreyList, $cleanup);

- The Create Database script seems to be OK. The error seems to appear a
lot on the web, so I have no answer to that one, but I just replaced the
"prepare" and "execute" with "do" statements.

Thanks for the quick response

Andrew



Michael Lang wrote:
I've tryed the code in a new(clean) Xen Instance right now,
modifing only 


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

$query = $GreyList->prepare("SELECT DISTINCT tripple, sessionid FROM 
\"greylisting\"".
   " WHERE \"tripple\" = ? AND \"timestamp\" <= ?;") ||
   md_syslog('warning', 'Problem on greylist Query: '. $GreyList->errstr) && 
   return ('CONTINUE', 'OK');

while ( $row = $query->fetchrow_hashref )  {

to 
 # build the tripple

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

$query = $GreyList->prepare("SELECT DISTINCT tripple, sessionid FROM 
\"greylisting\"".
   " WHERE \"tripple\" = ? AND \"timestamp\" <= ?;") ||
   md_syslog('warning', 'Problem on greylist Query: '. $GreyList->errstr) && 
   return ('CONTINUE', 'OK');

$query->execute();
 # ^^
while ( $row = $query->fetchrow_hashref )  {

and everything works find (using perl-DBD-SQLite2) 
i will need another day to check it with SQLite3 as i dont have time left now ...


one or more maybe problem is write permission to the SQLiteDB Directory as the
SQLite Engine tries to create a journal file, what user defang isnt permitted for in 
/etc/mail 


I've corrected those and the cleanup typos in the wiki :)

Greetz mIke

___
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

Re: [Mimedefang] Getting Geylisting working

2006-09-28 Thread Michael Lang 
On Thu, 2006-09-28 at 13:08 +0100, Andrew Watkins wrote:
> I thought I would look at greylisting, and follow the example on the 
> wiki website using the SQLite version:
>   http://www.mimedefang.com/kwiki/index.cgi?Greylisting
> 
> Since, I have noticed a few bugs in the perl code and after fixing them 
> I still get a couple of errors. I wonder has any got a working version 
> of GreyListingSQLite then let us know.
> 
> Information I am after:

I'm sorry for this foobar on the code there's a missing 
 $query->execute();

I've tryed the code in a new(clean) Xen Instance right now,
modifing only 

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

$query = $GreyList->prepare("SELECT DISTINCT tripple, sessionid FROM 
\"greylisting\"".
   " WHERE \"tripple\" = ? AND \"timestamp\" <= ?;") ||
   md_syslog('warning', 'Problem on greylist Query: '. $GreyList->errstr) 
&& 
   return ('CONTINUE', 'OK');
while ( $row = $query->fetchrow_hashref )  {

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

$query = $GreyList->prepare("SELECT DISTINCT tripple, sessionid FROM 
\"greylisting\"".
   " WHERE \"tripple\" = ? AND \"timestamp\" <= ?;") ||
   md_syslog('warning', 'Problem on greylist Query: '. $GreyList->errstr) 
&& 
   return ('CONTINUE', 'OK');
$query->execute();
 # ^^
while ( $row = $query->fetchrow_hashref )  {

and everything works find (using perl-DBD-SQLite2) 
i will need another day to check it with SQLite3 as i dont have time left now 
...

one or more maybe problem is write permission to the SQLiteDB Directory as the
SQLite Engine tries to create a journal file, what user defang isnt permitted 
for in 
/etc/mail 

I've corrected those and the cleanup typos in the wiki :)

Greetz mIke

> 
> - Perl script which creates the Database - Version on web works but you 
> get an error with SQLlite 3.
> 
> DBI::db=HASH(0x8279b94)->disconnect invalidates 1 active statement 
> handle (either destroy statement handles or call finish on them before 
> disconnecting) at CreateDB.pl line 25.
> closing dbh with active statement handles at CreateDB.pl line 25.
> 
> - The filter_recipient code.
>   Since getting an error "couldn't insert tripple"
> 
> - GreyListCleanup.pl script:
>   Couple of typos in the original.
> 
> Thanks
> 
> Andrew
> ___
> 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
-- 
Michael Lang <[EMAIL PROTECTED]>

___
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

Re: [Mimedefang] Getting Geylisting working

2006-09-28 Thread Jonas Eckerman 

Andrew Watkins wrote:

I wonder has any got a working version 
of GreyListingSQLite then let us know.


I'm not using the code from the wiki, but my greylist implementation uses 
SQLite and works fine for me. It's in the filter at:
http://whatever.frukt.org/mimedefangfilter.text.shtml

/Jonas

--
Jonas Eckerman, FSDB & Fruktträdet
http://whatever.frukt.org/
http://www.fsdb.org/
http://www.frukt.org/


___
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