#!/usr/bin/perl

use DBI;

=for comment

1. create tables:

CREATE TABLE graylist (
  "ok"        BOOLEAN                     NOT NULL DEFAULT false,
  "ip"        INET                        NOT NULL,
  "from"      TEXT                        NOT NULL,
  "to"        TEXT                        NOT NULL,
  "ts_entry"  TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT now(),
  "ts_latest" TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT now(),
  "ts_ok"     TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT now(),
  "count"     BIGINT                      NOT NULL DEFAULT 1,
  UNIQUE ("ip", "from", "to"),
  PRIMARY KEY ("ip", "from", "to")
);

CREATE TABLE blackwhitelist (
  "ok"   BOOLEAN NOT NULL DEFAULT false,
  "ip"   INET    NOT NULL,
  "from" TEXT    NOT NULL,
  "to"   TEXT    NOT NULL,
  UNIQUE ("ip", "from", "to"),
  PRIMARY KEY ("ip", "from", "to")
);


2. install cronjob:

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

=cut

$| = 1;

### db connection
$dbhost = "/tmp";
$dbport = 5432;
$dbname = "XXX";
$dbuser = "XXX";
$dbpass = "XXX";

### connect to db
$dbh = DBI->connect("DBI:Pg: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_bw = undef;
  $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 blackwhitelist
  foreach $f($from, $from_dom, "") {
    foreach $t($to, $to_dom, "") {
      $sth_select_bw->execute($ip, $f, $t);
      if ($sth_select_bw->rows == 1) {
        $row = $sth_select_bw->fetchrow_hashref();
        return &choose_action($row->{ok});
      }
    }
  }

  ### 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 key violates unique constraint \"graylist_pkey\"";
    $dbh->{RaiseError} = 0;
    $sth_insert_gray->execute($ip, $from, $to);
    &handle_error() unless (!$dbh->err || ($dbh->errstr =~ /$key_err/o));
    $dbh->{RaiseError} = 1;
  }

  return &choose_action($ok);
}

sub prepare_statements ()
{
  $sql = <<EOF;
SELECT "ok" FROM blackwhitelist
WHERE "ip" >>= ? AND "from" = ? AND "to" = ? ORDER BY "ip" DESC LIMIT 1
EOF
  $sth_select_bw = $dbh->prepare($sql);

  $sql = <<EOF;
SELECT "ok" FROM graylist
WHERE "ip" = ? AND "from" = ? AND "to" = ?
EOF
  $sth_select_gray = $dbh->prepare($sql);

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

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

  $sql = <<EOF;
INSERT INTO graylist ("ip", "from", "to") VALUES (?, ?, ?)
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" = 't'
WHERE NOT "ok" AND now() - interval '3 minutes' >= "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("false");
  $cnt_halfopen = &dbdo("true AND ts_latest = ts_entry");
  $cnt_permit = &dbdo("true 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"
AND "ts_entry" = "ts_latest" AND now() - interval '12 hours' >= "ts_entry"
EOF
  $dbh->do($sql);
  return if $hour;

  # every day at midnight
  $sql = <<EOF;
DELETE FROM graylist WHERE now() - interval '35 days' >= "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);
}