mysql configuration

# advanced greylisting including statistics and manual whitelisting
# capability with exim 4 and mysql.
# Author: Johannes Berg <johannes@sipsolutions.net>
#
# see README for more information

# if you don't have another database defined, then define it here
hide mysql_servers = localhost/exim4/user/password

# options
# these need to be valid as xxx in mysql's DATE_ADD(..,INTERVAL xxx)
# not valid, for example, are plurals: use "2 HOUR" instead of "2 HOURS"
GREYLIST_INITIAL_DELAY = 1 HOUR
GREYLIST_INITIAL_LIFETIME = 4 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
GREYLIST_CLASS_TABLE=exim_greylist_classes

# comment out to the following line to disable greylisting (temporarily)
GREYLIST_ENABLED=

# uncomment the following to enable logging (need log table!)
#GREYLIST_LOG_ENABLED=

# below here, nothing should normally be edited

.ifdef GREYLIST_ENABLED
# database macros
GREYLIST_CLASS_LOOKUP = SELECT name FROM GREYLIST_CLASS_TABLE \
  WHERE '${quote_mysql:$sender_host_address}' RLIKE ip_regex \
  LIMIT 1

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:$acl_m7}' \
        OR (type='MANUAL' \
            AND (    relay_ip IS NULL \
                  OR relay_ip = substring('${quote_mysql:$acl_m7}',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) \
 VALUES ( '${quote_mysql:$acl_m7}', \
  '${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' \
)

GREYLIST_DEFER_HIT = UPDATE GREYLIST_TABLE \
                     SET blockcount=blockcount+1 \
                     WHERE id = $acl_m9

GREYLIST_OK_COUNT = UPDATE GREYLIST_TABLE \
                    SET passcount=passcount+1 \
                    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