lazarus's Journal: GDSA (Greylisting Domain Sender Analysis)

The following is a variant of greylisting. You can comment on it from your soapbox if you wish but I've been running it for about three years now and it works great. I put it together for my own use and I have no desire to document it, support it, or in any way promote it. I'm posting it here because I'm tiring of hearing people whine about spam. It uses Exim and mysql to get around some of the inherent limitations of greylisting as it was originally defined (specifically the mandatory "delay" in receiving e-mail from a new source and the requirement to roll-up large senders (like google) into an IP range. Everything is automatic and I don't have problems with mail delays.

Let me be clear. I don't care if you like it or not, or use it or not. It's just data if you want it or are interested.

You need Exim compiled with mysql support in it. Get an RPM or whatever your favourite package manager is. Oh, and you need a resolver. I used a local one for speed and use iptables to block any external use of it.

In /etc/exim you need three files:

exim.conf
gdsa-acl
gdsa-mysql

This is what they look like. First /etc/exim/exim.conf (some of this you don't need -- depends on what you want your mailer to do for you):

# Exim Config

# Host and SQL config
primary_hostname = host.domain.com
hide mysql_servers = localhost/gdsa/root/

# Domain and Host Lists
domainlist local_domains = mysql;SELECT DISTINCT rd_name FROM rd WHERE rd_name='$domain' AND rd_type='local'
domainlist relay_to_domains = mysql;SELECT DISTINCT rd_name FROM rd WHERE rd_name='$domain' AND rd_type='relay'
hostlist relay_from_hosts = mysql;SELECT DISTINCT rh_name from rh WHERE rh_name='$sender_host_address' AND rh_type='relay'

# Misc MX Configs
acl_smtp_rcpt = acl_check_rcpt
never_users = root
host_lookup = *
rfc1413_hosts = *
rfc1413_query_timeout = 30s
smtp_enforce_sync = false
ignore_bounce_errors_after = 2d
timeout_frozen_after = 7d
#auth_advertise_hosts =
qualify_domain = domain.com
smtp_accept_max = 75
helo_allow_chars = _

# GDSA Config .include /etc/exim/gdsa-mysql

# ACL Config
begin acl .include /etc/exim/gdsa-acl

        acl_check_rcpt:
                deny message = Restricted characters in address
                                domains = +local_domains
                                local_parts = ^[.] : ^.*[@%!/|]
                deny message = Restricted characters in address
                                domains = !+local_domains
                                local_parts = ^[./|] : ^.*[@%!] : ^.*/\\.\\./
                deny message = No sender information
                                senders = :
                accept authenticated = *
                                control = submission
                deny message = Rejected because $sender_host_address is in zen.spamhaus.org blacklist
                                dnslists = zen.spamhaus.org
                accept hosts = +relay_from_hosts
                                control = submission
                defer acl = gdsa_acl
                                message = GDSA Deferred.
                accept domains = +local_domains
                                endpass
                                message = Unknown user
                                verify = recipient
                accept domains = +relay_to_domains
                                endpass
                deny message = Relay not permitted.

# Routers
begin routers

# MySQL loading of all routers
route_gdsa:
        driver = manualroute
        domains = +relay_to_domains
        route_data = ${lookup mysql{SELECT rd_ip FROM rd WHERE rd_name='$domain' AND rd_type='relay'}{$value}}
        transport = remote_smtp
        no_more

dnslookup:
    driver = dnslookup
    domains = !+local_domains
    transport = remote_smtp
    ignore_target_hosts = 0.0.0.0 : 127.0.0.0/8
    no_more

userforward:
    driver = redirect
    check_local_user
    file = $home/.forward
    no_verify
    no_expn
    check_ancestor
    allow_filter
    directory_transport = address_directory
    file_transport = address_file
    pipe_transport = address_pipe
    reply_transport = address_reply

localuser:
    driver = accept
    check_local_user
    transport = local_delivery
    cannot_route_message = Unknown user

# Transports
begin transports

remote_smtp:
        driver = smtp

local_delivery:
    driver = appendfile
    group = mail
    mode = 0660
    directory = /home/${local_part}/mail/
    maildir_format

# Retry Config
begin retry
* * F,2h,15m; G,16h,1h,1.5; F,4d,6h

# Authentication
begin authenticators

plain:
      driver = plaintext
      public_name = PLAIN
      server_prompts = :
      server_condition = "${if saslauthd{{$2}{$3}{smtp}} {1}}"
      server_set_id = $2

login:
      driver = plaintext
      public_name = LOGIN
      server_prompts = "Username:: : Password::"
      server_condition = "${if saslauthd{{$1}{$2}{smtp}} {1}}"
      server_set_id = $1

Next is the /etc/exim/gdsa-acl where most of the magic happens. You'll see what I am doing from the comments (in theory):

# GDSA ACL

gdsa_acl:

        # Our process goes as follows:
        # We attempt to get the full host name of the sending host
        # If we fail to get it, we apply a penalty
        # Then we break down the sending host into either a TLD or an IP address
        # We check to see if the sender is existent
        # If there is no sender we add a penalty
        # We do the DSA test
        # If it fails, we add a penalty
        # Then we check to see if a record for this host/sender/receiver exists
        # If it does not, then we add it with the appropriate penalties
        # We then do a GDSA check as always
        # If the delay requirement passes and the hit requirement passes, then the e-mail gets through
        # Otherwise it is deferred

        # Our variables look like the following:
        # Note: Are these persistent? Documentation says acl_m0..acl_m19 are thrown out after connection. May be good idea to use them.
        # acl_m0 = the full DNS-resolved name of the host, or null if lookup failed
        # acl_m1 = the TLD of the host or it's IP address if a DNS lookup failed
        # acl_m2 = the result of the GDSA test on the record
        # acl_m3 = the id of the record that matched (if any)
        # acl_m4 = the record exists in the database already (true, false)
        # acl_m5 = placeholder for the resut of adding a new record
        # acl_m6 = penalty count
        # acl_m7 = sender address domain for testing
        # acl_m8 = sender ip

        # Clear penalty count
        warn set acl_m6 = 0

        # Get the sener ip address (this is for tracking botnets)
        warn set acl_m8 = $sender_host_address

        # Try to get host name
        warn set acl_m0 = $sender_host_name

        # Get the sender address domain
        warn set acl_m7 = ${if def:sender_address_domain {$sender_address_domain}{$sender_helo_name}}

        # If we could not, then we apply a penalty
        warn set acl_m6 = ${eval:$acl_m6 + ${if def:acl_m0 {0}{1}}}

        # Now extract the TLD from the host name (by stripping off the host part) or set it to an IP address if we didn't get one
        # How do we get the domain? The domain we want is the domain part of the sender address if it exists in the host address.
        # So we check to see if the domain portion of the sender address is in the host.
        # If it is, then we set the TLD to the domain part of the sender address
        # If it is not, then we create the domain part by stripping off the host part of the sending host
        # If domain is NOT in host name then return stripped off host, otherwise return the sending address domain portion
        warn set acl_m1 = ${if def:acl_m0 {${if eq{${lookup mysql{SELECT LOCATE('$acl_m7','$acl_m0')}}}{0}{${lookup mysql{SELECT IF((LENGTH('$ac
l_m0')-LENGTH(REPLACE('$acl_m0','.','')))/LENGTH('.')=1,SUBSTRING_INDEX('$acl_m0','.',-2),SUBSTRING_INDEX('$acl_m0','.',-(LENGTH('$acl_m0')-LENG
TH(REPLACE('$acl_m0','.','')))/LENGTH('.')))}}}{$acl_m7}}}{$sender_host_address}}

        # Check to see if we got a sender address and apply a penalty if we didn't
        warn set acl_m6 = ${eval:$acl_m6 + ${if def:sender_address {0}{1}}}

        # Do the DSA (TLD, sender domain match) test
        warn set acl_m6 = ${eval:$acl_m6 + ${if eq{$acl_m1}{$acl_m7}{0}{1}}}

        # Check if the record exists
        warn set acl_m4 = ${lookup mysql{RECORD_EXISTS}{$value}{-1}}

        # If the record does not exist the we add it with the appropriate penalties
        warn set acl_m5 = ${if eq{$acl_m4}{false}{${lookup mysql{RECORD_ADD}}}}

        # At this point either a record exists or we have added it, so we always do a GDSA test.
        # Note that we should NEVER get an 'unknown' here...
        warn set acl_m2 = ${lookup mysql{GDSA_TEST}{$value}{result=unknown}}

        # now extract the record id (or -1)
        set acl_m3 = ${extract{gl_id}{$acl_m2}{$value}{-1}}

        # now set acl_m2 to contain unknown/deferred/accepted
        set acl_m2 = ${extract{result}{$acl_m2}{$value}{unknown}}

        # check if the record is still blocked
        accept
                # if above check returned deferred then defer
                condition = ${if eq{$acl_m2}{deferred}{1}}
                # and note it down
                condition = ${lookup mysql{GDSA_DEFER_HIT}{yes}{yes}}

        # use a warn verb to count records that were hit
        warn condition = ${lookup mysql{GDSA_OK_COUNT}}

        # use a warn verb to set a new expire time on automatic records,
        # but only if the mail was not a bounce, otherwise set to now().
        warn !senders = :
                condition = ${lookup mysql{GDSA_OK_NEWTIME}}
        warn senders = :
                condition = ${lookup mysql{GDSA_OK_BOUNCE}}

        deny

Elegant? Okay maybe not, but hey, I was scripting in an ACL definition language... Here, finally is the /etc/exim/gdsa-mysql:

# GDSA SQL Definitions

# Greylisting Options
GDSA_DELAY = 15
GDSA_RETRIES = 4
GDSA_INITIAL_LIFETIME = 12 HOUR
GDSA_WHITE_LIFETIME = 90 DAY
GDSA_BOUNCE_LIFETIME = 0 HOUR
GDSA_TABLE = gl

# GDSA SQL Macros

RECORD_EXISTS = SELECT IF (COUNT(*) > 0, 'true', 'false') \
        AS result \
        FROM GDSA_TABLE \
        WHERE gl_host='${quote_mysql:$acl_m1}' AND \
                        gl_sender='${quote_mysql:$sender_address}' AND \
                        gl_recipient='${quote_mysql:$local_part@$domain}'

GDSA_TEST = SELECT CASE \
        WHEN now() >= gl_blocked AND gl_retries = gl_blockcount THEN "accepted" ELSE "deferred" END \
        AS result, gl_id \
        FROM GDSA_TABLE \
        WHERE now() gl_expires AND \
                gl_host = '${quote_mysql:$acl_m1}' AND \
                gl_sender = '${quote_mysql:$sender_address}' AND \
                gl_recipient = '${quote_mysql:$local_part@$domain}' \
        ORDER BY result DESC LIMIT 1

RECORD_ADD = INSERT INTO GDSA_TABLE \
        (gl_ip, gl_host, gl_sender, gl_recipient, gl_created, gl_blocked, gl_expires, gl_retries) \
        VALUES ('${quote_mysql:$acl_m8}', \
                '${quote_mysql:$acl_m1}', \
                '${quote_mysql:$sender_address}', \
                '${quote_mysql:$local_part@$domain}', \
                now(), \
                DATE_ADD(now(), INTERVAL ($acl_m6 * GDSA_DELAY) MINUTE), \
                DATE_ADD(now(), INTERVAL GDSA_INITIAL_LIFETIME), \
                $acl_m6 * GDSA_RETRIES )

GDSA_DEFER_HIT = UPDATE GDSA_TABLE \
                                          SET gl_blockcount=gl_blockcount+1 \
                                          WHERE gl_id = $acl_m3

GDSA_OK_COUNT = UPDATE GDSA_TABLE \
                                        SET gl_passcount=gl_passcount+1 \
                                        WHERE gl_id = $acl_m3

GDSA_OK_NEWTIME = UPDATE GDSA_TABLE \
                                            SET gl_expires = DATE_ADD(now(), INTERVAL GDSA_WHITE_LIFETIME) \
                                            WHERE gl_id = $acl_m3

GDSA_OK_BOUNCE = UPDATE GDSA_TABLE \
                                          SET gl_expires = DATE_ADD(now(), INTERVAL GDSA_BOUNCE_LIFETIME) \
                                          WHERE gl_id = $acl_m3

Of course you're going to need the table definitions:

-- MySQL dump 10.11
--
-- Host: localhost Database: gdsa
-- ------------------------------------------------------
-- Server version 5.0.77 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `bl`
--

DROP TABLE IF EXISTS `bl`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `bl` (
    `bl_sender` varchar(128) NOT NULL default '',
    `bl_recipient` varchar(128) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;

--
-- Table structure for table `gl`
--

DROP TABLE IF EXISTS `gl`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `gl` (
    `gl_id` bigint(20) NOT NULL auto_increment,
    `gl_ip` varchar(128) default NULL,
    `gl_host` varchar(128) default NULL,
    `gl_sender` varchar(128) default NULL,
    `gl_recipient` varchar(128) default NULL,
    `gl_created` datetime NOT NULL default '0000-00-00 00:00:00',
    `gl_blocked` datetime NOT NULL default '0000-00-00 00:00:00',
    `gl_expires` datetime NOT NULL default '9999-12-31 00:00:00',
    `gl_retries` bigint(20) NOT NULL default '0',
    `gl_passcount` bigint(20) NOT NULL default '0',
    `gl_blockcount` bigint(20) NOT NULL default '0',
    PRIMARY KEY (`gl_id`)
) ENGINE=MyISAM AUTO_INCREMENT=4897 DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;

--
-- Table structure for table `rd`
--

DROP TABLE IF EXISTS `rd`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `rd` (
    `rd_name` varchar(128) NOT NULL default '',
    `rd_ip` varchar(128) default NULL,
    `rd_type` enum('local','relay') NOT NULL default 'local'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Relay Domains';
SET character_set_client = @saved_cs_client;

--
-- Table structure for table `rh`
--

DROP TABLE IF EXISTS `rh`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `rh` (
    `rh_name` varchar(128) NOT NULL default '',
    `rh_type` enum('relay') NOT NULL default 'relay'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Relay Hosts';
SET character_set_client = @saved_cs_client;

--
-- Table structure for table `wl`
--

DROP TABLE IF EXISTS `wl`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `wl` (
    `wl_sender` varchar(128) NOT NULL default '',
    `wl_recipient` varchar(128) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

That's it (I think). I can't remember if I ever implemented black or white lists in this. There is probably a good way to do that in the context of everything else. I don't seem to need it. If you do make changes or find something that you think could be done better, I'd be happy to hear about it. Large parts of this were probably stolen from other SQL-based Greylisting solutions. It's been so long I can't remember where I got everything.

Enjoy.