Two-node Graylisting (or Greylisting) setup with Postfix, MySQL and Perl

When using graylisting on mutiple MX hosts, you have to be sure that each of them knows about the graylisted combinations, otherweise too many delays occur.

So I did it like that:

• Each Postfix host has its own MySQL server running.
• Each MySQL server has the same table defined.
• When a new mail comes in, first the local database is searched for the graylisting combination.
• If the combination cannot be found, the remote database (on the other node) is searched. If the entry is found, it is automatically pushed/inserted in the local database.
• If the entry is found either in the local or remote database, the mail will be accepted.
  
• If the combination cannot be found on both databases, then it is INSERTed in both databases.
• If this is accomplished, the "You have been graylisted"-Prompt is given to the delivering mailserver.
• A random timeout value is also generated, stored so that retry times cannot be guessed.

A note on "gray" and "grey": The former is american english, the latter is the british variant. I'll use the british form in program names and the american form in description texts. I hope both sides are satisfied with that.

Personal data is NOT stored in the tables!


To recall greylisting:
Graylisting is a method to be sure that a delivering mailserver will make some efforts to send you a mail. Most bot networks won't to that so this measure is quite effective.
Whenever a mail comes in, there are three values known after RCPT TO stage:

1. The IP address of the mailserver which tries to deliver that mail.
2. The envelope sender (MAIL FROM:)
3. The recipient (RCPT TO:)

This is the moment where graylisting comes in. It checks in a database whether a mail with this data has already been seen. If not, it generates a timeout value and refuses further SMTP communication with a 4xx error code (temporary failure), giving the timeout value and the reason ("you have been graylisted"). Because of the temporary failure error number, that mailserver - if it is a real mailserver - will retry delivery some instants later. If the same mail is tried again to be delivered, and the timeout value has passed, the mail is accepted.

Users will notice this delay when getting mail from a new sender to their mailbox.

So after this background information, let's begin.

First, we define these database tables on each of the two Postfix nodes in MySQL:

use grey;

create table T_grey (
 hash CHAR(32) NOT NULL,
 PRIMARY KEY(hash),
 t0 INTEGER DEFAULT  0 ,
 t1 INTEGER DEFAULT  0 ,
 timeout INTEGER DEFAULT 900 ,
 INDEX(t1),
 INDEX(t0)
) TYPE=InnoDB;

It is important to set the type to InnoDB. It is nearly impossible to delete old entries at a nightly purge run without blocking the whole database when using MyISAM...

In our case, MySQL's data directory is on a ZFS volume, so we can turn off double writes (zfs guarantees that no partial writes will occur):

[mysqld]
datadir = /data/mysql
max_connections=1200
table_cache=1000
# InnoDB settings
innodb_data_file_path = ibdata1:100M:autoextend
innodb_buffer_pool_size=200M
innodb_log_file_size=100M
innodb_doublewrite=0
innodb_flush_log_at_trx_commit=0

As you can see, in our tables there is no personal information stored besides of the hash value, which is computated like that:

hash = md5_hex(client_address.\0.sender.\0.recipient)

\0 means the nullbyte. client_address is the IP address of the connecting mail server, sender and recipient are the envelope addresses (MAIL FROM and RCPT TO).

Last, we need a whitelist to be able to define mail server IPs which we trust, so that no delays occur (e.g. mailing list servers as acm, universities, ...).

How to implement such a graylisting service in Postfix?
Just use the Postfix policy interface. The API is simple and very ease to parse form Perl. Yes, I used Perl - no Java, no C - I wanted it simple and efficient. Perl DBI interfaces gives me maximum ease of use to access the database in a generalized manner.

Last, I use config files in the form "dsn.hostname", so that I may copy the whole greylist-perl-environment from one node to another and each node reads "its" config.

The two config files in this case were dsn.guanin and dsn.cytosin (as the two mail servers are named guanin and cytosin). An example for guanin:

our $dsn1="DBI:mysql:database=grey;host=guanin-10;mysql_connect_timeout=2";
our $dsn2="DBI:mysql:database=grey;host=cytosin-10;mysql_connect_timeout=2";
our $dsn1_user="U_grey";
our $dsn1_password="XXXXXXXX";
our $dsn2_user="U_grey";
our $dsn2_password="XXXXXXXX";

"guanin-10" and "cytosin-10" are the interface names to access he nodes via a private network shared by them.

So the node "guanin" will use itself as local database ($dsn1...) and cytosin as the remote database. In the config file dsn.cytosin the values are turned around:

our $dsn1="DBI:mysql:database=grey;host=cytosin-10;mysql_connect_timeout=2";
our $dsn2="DBI:mysql:database=grey;host=guanin-10;mysql_connect_timeout=2";
our $dsn1_user="U_grey";
our $dsn1_password="XXXXXXXX";
our $dsn2_user="U_grey";
our $dsn2_password="XXXXXXXX";

Yes, you have to create the user U_grey with access to the "grey" database on both MySQL instances, sure.

Example of a whitelist:

# ACM.org, US-Server
63.118.7.51     *

# Cyrus SASL, Cyrus IMAP
128.2.10.216    *

Be sure to include all known Google Gmail Mailservers in that list, as retries are not coming from the same IP! Neither Hotmail nor Yahoo experience this behaviour. Not putting Google Mail IP-Ranges in your whitelist will cause serious problems with your users getting mails from google mail users. We have > 300 Google Mail servers already in our whitelist.

Generate the whitelistdb with "postmap whitelist" (I used postfix compatible format for my mailgrey-policy script).

Here is the script to implement the MySQL-Two-Node-Greylisting-Policy for Postfix (mailgrey-policy.pl downloadable link):

#!/usr/local/bastion/bin/perl

# Pascal Gienger, 2008
# pascal@southbrain.com
# If no database can be accessed, OK is returned
# 60 seconds after the last database failure, a reconnect is
# tried.

use Fcntl;
use DB_File;
use DBI;
use Digest::MD5 qw(md5_hex);
use Sys::Hostname;
use Sys::Syslog;

my $whitelistdb="/usr/local/bastion/mailgrey/etc/whitelist.db";
my $configdir="/usr/local/bastion/mailgrey/etc";

our $dsn1;
our $dsn1_user;
our $dsn1_password;
our $dsn2;
our $dsn2_user;
our $dsn2_password;
require  $configdir."/dsn.".hostname();

my $d_timeout=280;

my %p;
my %w;

my $dbh1;
my $dbh2;
my $sth1;
my $sth1_1;
my $sth2;
my $sth2_1;
my $t0;
my $t1;
my $status;
my $ts;
my $waitperiod;
my $nl;
my $erg;
my $erg1;
my $erg2;
my $failure;
my $client_address;
my $sender;
my $recipient;
my $hashstring;
my $null=chr(0);

my $changedate;

sub getstat
{
  my $filename=$_[0];

  my ($dev,$ino,$mode,$nlink,$uid,$gid,$rdev,$size,$atime,$mtime,$ctime,$blksize,$blocks)
    =stat($filename);
  return $mtime;
}

sub initialize_db
{
  $failure=0;

  $dbh1=DBI->connect($dsn1,$dsn1_user,$dsn1_password,{
                          RaiseError => 0,
                          AutoCommit => 1,
                          PrintError => 0
                        });
  $dbh2=DBI->connect($dsn2,$dsn2_user,$dsn2_password,{
                          RaiseError => 0,
                          AutoCommit => 1,
                          PrintError => 0
                        });
  if ($dbh1)
  {
    $sth1  =$dbh1->prepare("SELECT t0,t1,timeout FROM T_grey WHERE hash=?");
    $sth1_1=$dbh1->prepare("INSERT INTO T_grey (hash,t0,t1,timeout) VALUES (?,?,?,?)");
    $sth1_2=$dbh1->prepare("UPDATE T_grey SET t1=UNIX_TIMESTAMP() WHERE hash=?");
  }
  if ($dbh2)
  {
    $sth2  =$dbh2->prepare("SELECT t0,t1,timeout FROM T_grey WHERE hash=?");
    $sth2_1=$dbh2->prepare("INSERT INTO T_grey (hash,t0,t1,timeout) VALUES (?,?,?,?)");
    $sth2_2=$dbh2->prepare("UPDATE T_grey SET t1=UNIX_TIMESTAMP() WHERE hash=?");
  }

  if ((!$sth1)||(!$sth1_1)||(!$sth1_2)||(!$sth2)||(!$sth2_1)||(!$sth2_2))
  {
    $failure=time();
  }
}

sub cut_db1
{
  if ($failure==0) { $failure=time(); }
  undef $sth1,$sth1_1,$sth1_2,$dbh1;
}

sub cut_db2
{
  if ($failure==0) { $failure=time(); }
  undef $sth2,$sth2_1,$sth2_2,$dbh2;
}

$|=1;

openlog("mailgrey-policy","","local0");

&initialize_db;

tie %w,"DB_File",$whitelistdb,O_RDONLY,0400,$DB_HASH;
$changedate=getstat($whitelistdb);

while (true)
{
  if (getstat($whitelistdb)!=$changedate)
  {
    untie %w;
    tie %w,"DB_File",$whitelistdb,O_RDONLY,0400,$DB_HASH;
    $changedate=getstat($whitelistdb);
    syslog("info","$whitelistdb reloaded.");
  }
  undef %p;

  while ($nl==0)
  {
    my $input=<STDIN>;
    if (!$input)
    {
      closelog();
      exit;
    }
    chop $input;

    if (!($input =~ "="))
    {
      $nl=1;
    }
    else
    {
      my ($lh,$rh) = split /=/,$input;
      $p{$lh}=lc($rh);
    }
  }

  $ts=time();
  $client_address=substr($p{'client_address'},0,63);
  $sender=substr($p{'sender'},0,63);
  $recipient=substr($p{'recipient'},0,63);

# Zuerst Whitelist
  if ($w{$p{'client_address'}.$null} eq '*'.$null)
  {
    print "action=OK\n\n";
  }
  elsif ($w{$p{'client_address'}.$null} eq $sender.$null)
  {
    print "action=OK\n\n";
  }
# ansonsten Greylist
  else
  {
    $hashstring=md5_hex($client_address.$null.$sender.$null.$recipient);

    $status=0; $t0=0; $t1=0;
    $timeout=int(rand(300))+180;

    if ($sth1)
    {
      $sth1->bind_param(1,$hashstring);
      $erg1=$sth1->execute();
      $sth1->bind_columns(\$t0,\$t1,\$timeout);
      $sth1->fetch();

      if (!$erg1)
      {
        &cut_db1;
      }

      if ($erg1>0)
      {
        $status=1;
      }
    }
    if (($sth2)  && ($status==0))
    {
      $sth2->bind_param(1,$hashstring);
      $erg2=$sth2->execute();
      $sth2->bind_columns(\$t0,\$t1,\$timeout);
      $sth2->fetch();

      if (!$erg2)
      {
        &cut_db2;
      }

      if ($erg2>0)
      {
        $status=2;

        if ($sth1_1)
        {
          $sth1_1->bind_param(1,$hashstring);
          $sth1_1->bind_param(2,$t0);
          $sth1_1->bind_param(3,$t1);
          $sth1_1->bind_param(4,$timeout);
          $erg=$sth1_1->execute();
          if (! $erg)
          {
            &cut_db1;
          }
        }
      }
    }

    $waitperiod=$timeout;

    if ($status>0)
    {
      if (($t0+$timeout) > $ts)
      {
        $status=-1;
        $waitperiod=$t0-$ts+$timeout;
      }
    }

    if ($status==0)
    {
      if ($sth1_1)
      {
        $sth1_1->bind_param(1,$hashstring);
        $sth1_1->bind_param(2,$ts);
        $sth1_1->bind_param(3,0);
        $sth1_1->bind_param(4,$timeout);
        $erg=$sth1_1->execute();
      }
      if ($sth2_1)
      {
        $sth2_1->bind_param(1,$hashstring);
        $sth2_1->bind_param(2,$ts);
        $sth2_1->bind_param(3,0);
        $sth2_1->bind_param(4,$timeout);
        $erg2=$sth2_1->execute();
      }
      if ((!$erg1) && (!$erg2))
      {
        print "action=OK\n\n";
      }
      else
      {
        print "action=420 4.7.1 You have been graylisted, next try in $waitperiod sec ($hashstring) [http://southbrain.com/graylisting]\n\n";
      }
    }
    elsif ($status==-1)
    {
      print "action=420 4.7.1 You came again too early, next try in $waitperiod sec ($hashstring) [http://southbrain.com/graylisting]\n\n";
    }
    else
    {
      if ($sth1_2)
      {
        $sth1_2->bind_param(1,$hashstring);
        $sth1_2->execute();
      }
      if ($sth2_2)
      {
        $sth2_2->bind_param(1,$hashstring);
        $sth2_2->execute();
      }
      print "action=OK\n\n";
    }
  }

  if ( ($failure>0) && ( ($failure+60) < time()) )
  {
    &initialize_db;
  }
  $nl=0;
}


That's it. You have to define a policy server now to run this script. I did that via a "spawn" entry in Postfix master.cf:

127.0.0.1:2910 inet n n n - 0 spawn user=nobody argv=/usr/local/bastion/mailgrey/bin/mailgrey-policy.pl

On localhost, Port 2910 the policy service will listen for requests. argv is the path to the above greylisting script.

In Postfix main.cf, you can turn on greylisting as a smtpd_recipient_restriction, e.g.

smtpd_recipient_restrictions = permit_sasl_authenticated,
                               permit_mynetworks,
                               reject_unauth_destination,
                               check_sender_access hash:/usr/local/bastion/etc/postfix/access,
                               reject_unlisted_recipient,
                               check_policy_service inet:127.0.0.1:2910

In this case the Graylisting algorithm becomes effective when all other rules did not apply. So SASL authenticated senders still can send whatever mail they want (in my case above).


The MySQL databases get VERY stressed, giving 170-300 questions per second. The InnoDB tablespace is using approx. 3 GB and I am seeing 8-10 million inserts per day.

At last, define a nightly purge job like this:

#!/usr/local/bastion/bin/perl

use DBI;
use Digest::MD5 qw(md5_hex);
use Sys::Hostname;
use Sys::Syslog;

my $configdir="/usr/local/bastion/mailgrey/etc";

our $dsn1;
our $dsn1_user;
our $dsn1_password;
our $dsn2;
our $dsn2_user;
our $dsn2_password;
require  $configdir."/dsn.".hostname();

$failure=0;

openlog("mailgrey-periodic","pid","local0");

$dbh1=DBI->connect($dsn1,$dsn1_user,$dsn1_password,{
                          RaiseError => 0,
                          AutoCommit => 1,
                          PrintError => 0
                        });
if ($dbh1)
{
  $sth1  =$dbh1->prepare("DELETE FROM T_grey WHERE (t1=0) AND (t0<?)");
  $sth1_1=$dbh1->prepare("DELETE FROM T_grey WHERE (t1!=0) AND (t1<?)");
}

my $timestamp=time();

my $rate_inactive=$timestamp-(2*86400);
my $rate_active  =$timestamp-(90*86400);
my $nr1=0;
my $nr2=0;

if ($sth1)
{
  $sth1->bind_param(1,$rate_inactive);
  $sth1->execute();
  $nr1=$sth1->rows;

  $sth1_1->bind_param(1,$rate_active);
  $sth1_1->execute();
  $nr2=$sth1_1->rows;
}

my $timestring=localtime();
my $log=sprintf("Inactive deleted: %8s Active deleted: %8s",$nr1,$nr2);
syslog("info",$log);
closelog();

This will time out graylist entries after 2 days for inactive ones, and after 90 days for active ones.