MySQL-based greylisting Implementation for Exim

Exim configuration

  1. At the beginning of the configuration file:

    hide mysql_servers = localhost/database/user/password
    
    # options
    # these need to be valid as xxx in mysql's DATE_ADD(..,INTERVAL xxx)
    # not valid, for example, are plurals: "2 HOUR" instead of "2 HOURS"
    GREYLIST_INITIAL_DELAY = 20 MINUTE
    GREYLIST_INITIAL_LIFETIME = 8 HOUR
    GREYLIST_WHITE_LIFETIME = 36 DAY
    GREYLIST_BOUNCE_LIFETIME = 0 HOUR
    
    # you can change the table names
    GREYLIST_TABLE=exim_greylist
    GREYLIST_LOG_TABLE=exim_greylist_log
    
    # comment out to the following line to disable greylisting (temporarily)
    GREYLIST_ENABLED=
    
    # uncomment the following to enable logging
    #GREYLIST_LOG_ENABLED=
    
    # below here, nothing should normally be edited
    
    .ifdef GREYLIST_ENABLED
    # database macros
    GREYLIST_TEST = SELECT CASE \
       WHEN now() > block_expires THEN "accepted" \
       ELSE "deferred" \
     END AS result, id \
     FROM GREYLIST_TABLE \
     WHERE (now() < record_expires) \
       AND (sender      = '${quote_mysql:$sender_address}' \
            OR (type='MANUAL' \
                AND (    sender IS NULL \
                      OR sender = '${quote_mysql:@$sender_address_domain}' \
                    ) \
               ) \
           ) \
       AND (recipient   = '${quote_mysql:$local_part@$domain}' \
            OR (type = 'MANUAL' \
                AND (    recipient IS NULL \
                      OR recipient = '${quote_mysql:$local_part@}' \
                      OR recipient = '${quote_mysql:@$domain}' \
                    ) \
               ) \
           ) \
       AND (relay_ip    = '${quote_mysql:$sender_host_address}' \
            OR (type='MANUAL' \
                AND (    relay_ip IS NULL \
                      OR relay_ip = substring('${quote_mysql:$sender_host_address}',1,length(relay_ip)) \
                    ) \
               ) \
           ) \
     ORDER BY result DESC LIMIT 1
    
    GREYLIST_ADD = INSERT INTO GREYLIST_TABLE \
      (relay_ip, sender, recipient, block_expires, \
       record_expires, create_time, type, blockcount ) \
     VALUES ( '${quote_mysql:$sender_host_address}', \
      '${quote_mysql:$sender_address}', \
      '${quote_mysql:$local_part@$domain}', \
      DATE_ADD(now(), INTERVAL GREYLIST_INITIAL_DELAY), \
      DATE_ADD(now(), INTERVAL GREYLIST_INITIAL_LIFETIME), \
      now(), 'AUTO', 1 )
    
    GREYLIST_DEFER_HIT = UPDATE GREYLIST_TABLE \
                         SET blockcount=blockcount+1 \
                         WHERE id = $acl_m9
    
    GREYLIST_OK_COUNT = UPDATE GREYLIST_TABLE \
                        SET passcount=passcount+1, last_passed=now() \
                        WHERE id = $acl_m9
    
    GREYLIST_OK_NEWTIME = UPDATE GREYLIST_TABLE \
                          SET record_expires = DATE_ADD(now(), INTERVAL GREYLIST_WHITE_LIFETIME) \
                          WHERE id = $acl_m9 AND type='AUTO'
    
    GREYLIST_OK_BOUNCE = UPDATE GREYLIST_TABLE \
                         SET record_expires = DATE_ADD(now(), INTERVAL GREYLIST_BOUNCE_LIFETIME) \
                         WHERE id = $acl_m9 AND type='AUTO'
    
    GREYLIST_LOG = INSERT INTO GREYLIST_LOG_TABLE \
                   (listid, timestamp, kind) \
                   VALUES ($acl_m9, now(), '$acl_m8')
    .endif
  2. Just after begin acl

    ###################################### GREYLIST ACL
    
    .ifdef GREYLIST_ENABLED
    # this acl returns either deny or accept
    # since we use it inside a defer with acl = greylist_acl,
    # accepting here makes the condition TRUE thus deferring,
    # denying here makes the condition FALSE thus not deferring
    greylist1_acl:
      # For regular deliveries, check greylist.
    
      # check greylist tuple, returning "accepted", "deferred" or "unknown"
      # in acl_m8, and the record id in acl_m9
    
      warn set acl_m8 = ${lookup mysql{GREYLIST_TEST}{$value}{result=unknown}}
           # here acl_m8 = "result=x id=y"
    
           set acl_m9 = ${extract{id}{$acl_m8}{$value}{-1}}
           # now acl_m9 contains the record id (or -1)
    
           set acl_m8 = ${extract{result}{$acl_m8}{$value}{unknown}}
           # now acl_m8 contains unknown/deferred/accepted
    
      # check if we know a certain triple, add and defer message if not
      accept
           # if above check returned unknown (no record yet)
           condition = ${if eq{$acl_m8}{unknown}{1}}
           # then also add a record
           condition = ${lookup mysql{GREYLIST_ADD}{yes}{no}}
    
      # now log, no matter what the result was
      # if the triple was unknown, we don't need a log entry
      # (and don't get one) because that is implicit through
      # the creation time above.
    #.ifdef GREYLIST_LOG_ENABLED
    #  warn condition = ${lookup mysql{GREYLIST_LOG}}
    #.endif
    
      # check if the triple is still blocked
      accept
           # if above check returned deferred then defer
           condition = ${if eq{$acl_m8}{deferred}{1}}
           # and note it down
           condition = ${lookup mysql{GREYLIST_DEFER_HIT}{yes}{yes}}
    
      deny
    
    # Final phase, to be used after RBL checks
    
    greylist2_acl:
    
      # use a warn verb to count records that were hit
      warn condition = ${lookup mysql{GREYLIST_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 = : postmaster@*
           condition = ${lookup mysql{GREYLIST_OK_NEWTIME}}
      warn senders = : postmaster@*
           condition = ${lookup mysql{GREYLIST_OK_BOUNCE}}
    
      accept
    .endif
  3. In check_recipient:, just before the spam blocking rules:

    .ifdef GREYLIST_ENABLED
      defer !senders = : postmaster@*
            !authenticated = *
            acl      = greylist1_acl
            message  = greylisted - try again later
    .endif
  4. in check_message:

    .ifdef GREYLIST_ENABLED
    
      # First phase for NULL sender case
      defer senders  = : postmaster@*
            acl      = greylist1_acl
            message  = greylisted - try again later
    
      # Message accepted, second greylist phase: update greylist DB
    
      # First phase was after the RCPT command (sender not NULL)
      accept !senders = : postmaster@*
             !authenticated = *
             acl = greylist2_acl
    
      # First phase was after the DATA command (sender NULL)
      accept senders = : postmaster@*
             acl = greylist2_acl
    
    .endif

Cron jobs

Required for proper self-maintenance!

Set & verify credentials

# vi /root/.my.cnf
# mysql
...

Eventually fix database to use

# vi /root/bin/greylist_purge
# vi /root/bin/greylist_addwhite

set cron jobs

# vi /etc/cron.d/greylist
11 * * * * root /root/bin/greylist_purge
22 1 * * * root /root/bin/greylist_addwhite

Troubleshooting

Tips

Maintenance

Purge many rows

Procedure to use instead of greylist_purge whan many rows = very slow

insert into copy_greylist
select *
from exim_greylist
where record_expires > now();


RENAME TABLE exim_greylist TO old_greylist, copy_greylist TO exim_greylist;

DROP TABLE old_greylist;

See also

Migration

Greylisting (last edited 2010-01-04 00:50:50 by JaumeSola)