Greylisting with MySQL and Exim

This is meant to be a very simple implementation. There's no whitelist, though it's easy to add, and very little error checking — though I've found no need so far. It is based on work by Evan Harris and more specifically, on an implementation using PostgreSQL by Tollef Fog Heen.

Step one

Create a table for the greylist:

CREATE TABLE exim_greylist (
  id integer NOT NULL auto_increment,
  relay_ip varchar(64),
  from_domain varchar(255),
  block_expires datetime NOT NULL,
  record_expires datetime NOT NULL,
  origin_type enum('MANUAL','AUTO') NOT NULL default 'AUTO',
  create_time datetime NOT NULL,

Give privileges as proper.

Step two

In the main part of your exim.conf file, add some macros:

   WHEN now() - block_expires > 0 THEN 2 \
   ELSE 1 \
 END \
 FROM exim_greylist \
 WHERE relay_ip = '${quote_mysql:$sender_host_address}' \
  AND from_domain = '${quote_mysql:$sender_address_domain}'

GREYLIST_ADD = INSERT INTO exim_greylist (relay_ip, from_domain, \
  block_expires, record_expires, create_time) \
VALUES ( '${quote_mysql:$sender_host_address}', \
  '${quote_mysql:$sender_address_domain}', \
  DATE_ADD(now(), INTERVAL 7 DAY), \
  now() \

And an SQL server login if you don't have one:

hide mysql_servers = [server1]/[db]/[user]/[password]:\

Step three

In the ACL controlling the response to the RCPT command, put this line near the top, since it has no action but to set a variable.

warn set acl_m2 = ${lookup mysql{GREYLIST_TEST}{$value}{0}}

And after rules for all mail that you always accept, like postmaster addresses or mail from trusted relays, add the following.

defer message = Greylisted - please try again a little later.
  condition = ${if eq{$acl_m2}{0}{1}}
  condition = ${lookup mysql{GREYLIST_ADD}{yes}{no}}
defer message = Greylisted - please try again shortly.
  condition = ${if eq{$acl_m2}{1}{1}}