#!/usr/bin/perl -w

use strict;
use DBI;

# how many days before we remove records from the whitelist relay log
# (value of maxage must be a valid PostgreSQL interval)
my $maxage = '30 days';

# how many whitelist records we need for an IP address before we whitelist it
my $whitecount = 50;

my $dbname = 'exim';
my $dbhost = 'localhost';
my $dbuser = 'eximadmin';
my $dbpass = 'some password';

my $logger = '/usr/bin/logger -p mail.info -t greylist';

my $dbh = DBI->connect(
  "dbi:Pg:dbname=$dbname;host=$dbhost", $dbuser, $dbpass
);
if (!$dbh) {
  system("$logger 'Error on initial connect to database: $DBI::errstr'");
}

# first drop old records from relay_ip_log
my $sth = $dbh->prepare("
  delete from relay_ip_log where create_time < now() - interval '$maxage'
");
$sth->execute;

# use a transaction to clear out old relay_ip_whitelist table and insert new
# records
$dbh->{AutoCommit} = 0;
$dbh->{RaiseError} = 1;
eval {
  # start with the delete
  my $sth = $dbh->prepare('delete from relay_ip_whitelist');
  $sth->execute();

  # now insert new records
  $sth = $dbh->prepare("
    insert into relay_ip_whitelist
      select relay_ip from relay_ip_log group by relay_ip
      having count(relay_ip) > $whitecount
  ");
  $sth->execute();

  # commit the changes
  $dbh->commit;
};
if ($@) {
  system("$logger 'ip whitelist transaction aborted because $@'");
  eval { $dbh->rollback };
} else {
  system("$logger 'ip whitelist updated'");
}
$dbh->disconnect;