#!/usr/bin/perl

use DBI;

=for comment

1. create tables:

CREATE TABLE graylist (
  ok        BOOL            NOT NULL DEFAULT 0,
  ip        LONGTEXT        NOT NULL,
  src       LONGTEXT        NOT NULL,
  dst       LONGTEXT        NOT NULL,
  ts_entry  DATETIME        NOT NULL,
  ts_latest DATETIME        NOT NULL,
  ts_ok     DATETIME        NOT NULL,
  count     BIGINT UNSIGNED NOT NULL DEFAULT 1,
  UNIQUE (ip(133), src(133), dst(133)),
  PRIMARY KEY (ip(133), src(133), dst(133))
);

CREATE INDEX graylist_ok_idx ON graylist(ok);
CREATE INDEX graylist_ts_entry_idx ON graylist(ts_entry);
CREATE INDEX graylist_ts_latest_idx ON graylist(ts_latest);
CREATE INDEX graylist_ts_ok_idx ON graylist(ts_ok);


2. install cronjob:

* * * * * /usr/local/libexec/mygraypol.pl cron

=cut

$| = 1;

### db connection
$dbhost = "";
$dbport = 3306;
$dbname = "XXX";
$dbuser = "XXX";
$dbpass = "XXX";

### connect to db
$dbh = DBI->connect("DBI:mysql:dbname=$dbname;host=$dbhost;port=$dbport",
                    $dbuser, $dbpass, { RaiseError => 1, PrintError => 0,
                    HandleError => \&handle_error });

if ($ARGV[0] eq "cron") {
  ### we are called as cronjob
  use RRDs;
  ($sec, $min, $hour, @rest) = localtime(time());
  &cronjobs();
}
else {
  ### called from postfix
  &prepare_statements();

  ### suck in parameters
  while (<>) {
    s/\r|\n//sgio;
    if (/^$/o) {
      ### empty line, end of parameters
      print &check_policy();
      %h = ();
    }
    else {
      ### save parameter
      ($key, $value) = split(/=/, $_, 2);
      $h{$key} = $value;
    }
  }

  ### undefine statement handles (sigsegv otherwise)
  $sth_select_gray = undef;
  $sth_update_gray = undef;
  $sth_update_gray_ok = undef;
  $sth_insert_gray = undef;
}

### exit
$dbh->disconnect();


### local subroutines ###
sub check_policy ()
{
  ### ensure that we are called at the correct stage
  return &myok() if ($h{request} ne "smtpd_access_policy");
  return &myok() if ($h{protocol_state} ne "RCPT");

  ### the primary key
  $ip = lc($h{client_address});
  substr($ip, rindex($ip, ".") + 1) = "0";
  $from = lc($h{sender});
  $to = lc($h{recipient});
  ($from_user, $from_dom) = split(/\@/, $from, 2);
  ($to_user, $to_dom) = split(/\@/, $to, 2);

  ### check for entry in graylist
  $sth_select_gray->execute($ip, $from, $to);
  if ($sth_select_gray->rows == 1) {
    ### found an entry
    $row = $sth_select_gray->fetchrow_hashref();
    $ok = $row->{ok};
    ### update timestamp(s)
    if ($ok) {
      $sth_update_gray_ok->execute($ip, $from, $to);
    }
    else {
      $sth_update_gray->execute($ip, $from, $to);
    }
  }
  else {
    ### did not found an entry -> create it
    $ok = 0;
    $key_err = "Duplicate entry";
    $sth_insert_gray->{RaiseError} = 0;
    $sth_insert_gray->{HandleError} = undef;
    $sth_insert_gray->execute($ip, $from, $to);
    &handle_error() unless (!$dbh->err || ($dbh->errstr =~ /$key_err/o));
    $sth_insert_gray->{HandleError} = \&handle_error;
    $sth_insert_gray->{RaiseError} = 1;
  }

  return &choose_action($ok);
}

sub prepare_statements ()
{
  $sql = <<EOF;
SELECT ok FROM graylist
WHERE ip = ? AND src = ? AND dst = ?
EOF
  $sth_select_gray = $dbh->prepare($sql);

  $sql = <<EOF;
UPDATE graylist SET ts_latest = now(), ts_ok = now(), count = count + 1
WHERE ip = ? AND src = ? AND dst = ?
EOF
  $sth_update_gray = $dbh->prepare($sql);

  $sql = <<EOF;
UPDATE graylist SET ts_latest = now()
WHERE ip = ? AND src = ? AND dst = ?
EOF
  $sth_update_gray_ok = $dbh->prepare($sql);

  $sql = <<EOF;
INSERT INTO graylist (ip, src, dst, ts_entry, ts_latest, ts_ok)
VALUES (?, ?, ?, now(), now(), now())
EOF
  $sth_insert_gray = $dbh->prepare($sql);
}

sub dbdo ()
{
  $sth = $dbh->prepare("SELECT COUNT(*) AS cnt FROM graylist WHERE ok = $_[0]");
  $sth->execute();
  $row = $sth->fetchrow_hashref();
  $sth = undef;
  return $row->{cnt};
}

sub cronjobs ()
{
  # every minute
  $sql = <<EOF;
UPDATE graylist SET ok = 1
WHERE ok = 0 AND now() - interval 3 minute >= ts_entry
EOF
  $dbh->do($sql);
  return if ($min % 5);

  # every 5 minutes
  $rrd_path = "/var/db/graylist.rrd";
  $png_path = "/usr/local/www/data/graylist";
  if (!-e "$rrd_path") {
    RRDs::create("$rrd_path", "--start", "1000000000", "--step", 300,
                 "DS:deny:GAUGE:600:U:U", "DS:halfopen:GAUGE:600:0:U",
                 "DS:permit:GAUGE:600:U:U", "RRA:MAX:0:1:210240");
    $err = RRDs::error;
    &handle_error("$rrd_path: $err") if $err;
  }

  $cnt_deny = &dbdo("0");
  $cnt_halfopen = &dbdo("1 AND ts_latest = ts_entry");
  $cnt_permit = &dbdo("1 AND NOT (ts_latest = ts_entry)");

  RRDs::update("$rrd_path", "-t", "deny:halfopen:permit",
               "N:$cnt_deny:$cnt_halfopen:$cnt_permit");
  $err = RRDs::error;
  &handle_error("$rrd_path: $err") if $err;

  $jetzt = localtime(time());
  $jetzt =~ s/\:/\\\:/sgio;
  foreach ("1h", "1d", "1w", "1m", "1y") {
    $fn = "$png_path$_.png";
    RRDs::graph("$fn", "-s", "end-$_", "-l", "0",
                "DEF:deny=$rrd_path:deny:MAX",
                "DEF:halfopen=$rrd_path:halfopen:MAX",
                "DEF:permit=$rrd_path:permit:MAX",
                "COMMENT:[$jetzt]\\r",
                "AREA:permit#00FF00:permit\\: $cnt_permit\\n",
                "STACK:halfopen#0000FF:half open\\: $cnt_halfopen\\n",
                "STACK:deny#FF0000:deny\\: $cnt_deny");
    $err = RRDs::error;
    &handle_error("$fn: $err") if $err;
  }
  return if $min;

  # every hour
  $sql = <<EOF;
DELETE FROM graylist WHERE ok = 1
AND ts_entry = ts_latest AND now() - interval 12 hour >= ts_entry
EOF
  $dbh->do($sql);
  return if $hour;

  # every day at midnight
  $sql = <<EOF;
DELETE FROM graylist WHERE now() - interval 35 day >= ts_latest
EOF
  $dbh->do($sql);
}

sub myaction ()
{
  return "action=$_[0]\n\n";
}

sub mydefer ()
{
  return &myaction("defer_if_permit $_[0]");
}

sub myok ()
{
  return &myaction("dunno");
}

sub choose_action ()
{
  return ($_[0] ? &myok() : &mydefer("Please try again in several minutes."));
}

sub handle_error ()
{
  my $err = " ($_[0])" if $_[0];
  print &mydefer("Technical problems." . $err);
  $dbh->disconnect() if $dbh;
  exit 1;
}

### just for debugging
sub mylog
{
  open(FH,">>/tmp/fjopol.log");
  print FH localtime(time()), "\n",
           join("", map { "$_:\t$h{$_}\n" } sort keys %h), "===\n";
  close(FH);
}