Blog posts with tag postgresql

Greylisting using Exim v4 and PostgreSQL

Why another greylisting implementation for Exim?

I found Tollef Fog Heen's implementation really useful but decided I wanted more of the features from Evan Harris's greylisting whitepaper plus some extra local requirements.

  • Block & whitelist expiries set within the exim configuration file
  • Ability to use either single IP addresses or a netblock for relay_ip
  • Automatic whitelist entries created from outgoing email
  • Automatic whitelisting of IP addresses of high volume relays (Yahoo, etc)
  • Archiving of expired database records
  • Statistics about blocked delivery attempts and successful deliveries

Requirements

  • Exim v4 built with PostgreSQL support (available as exim4-daemon-heavy in Debian)
  • PostgreSQL running on the MTA system or some other local server

The database

The file createdb.sql can be used to create the database structure required. Make sure you change the usernames and passwords to those you have chosen before importing the script Controlling access to the database

/etc/postgresql/pg_hba.conf for PostgreSQL v7.3+ ...

local   exim        exim                                          password
host    exim        exim        <mta_ip>        255.255.255.255   password

Exim configuration changes

This first section with database and macro definitions needs to go in the 'main' section of the exim config file (somewhere before the first 'begin') with database parameters set appropriately

# define our database connection
# local database accessed via a UNIX socket
#hide pgsql_servers = (/var/run/postgresql/.s.PGSQL.5432)/<database>/<username>/<password>
# local or remote database accessed via the network
hide pgsql_servers = <database_server>/<database>/<username>/<password>

#------------------------------------------------------------------------------
# start greylisting macros
#------------------------------------------------------------------------------

# set the following to 32 to match individual addresses or to 24 to match
# class-C size netblocks (etc.)
GREYLIST_MASK = 24

# Initial delay before previously unknown triplets are allowed to pass
GREYLIST_RETRYMIN = 28 minutes

# Lifetime of triplets that have not been retried after initial delay
GREYLIST_RETRYMAX = 8 hours

# Lifetime of auto-whitelisted triplets that have allowed mail to pass
GREYLIST_EXPIRE = 60 days

# SQL WHERE clause common to 3 of the following queries
GREYLIST_WHERE = \ 
  WHERE '${quote_pgsql:$sender_host_address}' <<= relay_ip \ 
  AND mail_from='${quote_pgsql:$sender_address}' \ 
  AND rcpt_to='$acl_m8'

# greylist check
GREYLIST_TEST = SELECT CASE \ 
    WHEN now() > block_expires THEN 'white' \ 
    ELSE 'grey' \ 
  END \ 
  FROM relaytofrom GREYLIST_WHERE

# add a greylist entry
GREYLIST_ADD = INSERT INTO relaytofrom \  
    (relay_ip, mail_from, rcpt_to, block_expires, record_expires, origin_type) \ 
  VALUES ( \ 
    network('${quote_pgsql:$sender_host_address}/GREYLIST_MASK'), \ 
    '${quote_pgsql:$sender_address}', \ 
    '$acl_m8', \ 
    now() + interval 'GREYLIST_RETRYMIN', \ 
    now() + interval 'GREYLIST_RETRYMAX', \ 
    'auto')

# update a still blocked entry
GREYLIST_BLOCKED = UPDATE relaytofrom \ 
  SET blocked_count = blocked_count + 1, last_update = now() GREYLIST_WHERE

# update a whitelisted entry
GREYLIST_PASSED = UPDATE relaytofrom \  
  SET passed_count = passed_count + 1, last_update = now(), \  
    record_expires = now() + interval 'GREYLIST_EXPIRE' GREYLIST_WHERE

# check if outgoing sender/recipient pair already has whitelist entry
WHITELIST_CHECK = SELECT * from relaytofrom \ 
  WHERE relay_ip = '0.0.0.0/0' \ 
  AND mail_from='${quote_pgsql:$local_part@$domain}' \ 
  AND rcpt_to='${quote_pgsql:$sender_address}'

# add a whitelist entry - used for outgoing mail from local users
WHITELIST_ADD = INSERT INTO relaytofrom \ 
    (relay_ip, mail_from, rcpt_to, block_expires, record_expires, origin_type) \ 
  VALUES ( \ 
    '0.0.0.0/0', \ 
    '${quote_pgsql:$local_part@$domain}', \ 
    '${quote_pgsql:$sender_address}', \ 
    now(), \ 
    now() + interval 'GREYLIST_EXPIRE', \ 
    'auto')

WHITELIST_RESET_EXPIRE = UPDATE relaytofrom \ 
  SET last_update = now(), record_expires = now() + interval 'GREYLIST_EXPIRE' \ 
  WHERE rcpt_to='${quote_pgsql:$sender_address}' \ 
  AND mail_from='${quote_pgsql:$local_part@$domain}' \ 
  AND relay_ip='0.0.0.0/0'

# whitelist IP address
WHITELIST_IP_ADD = INSERT INTO relay_ip_log (relay_ip) \ 
  VALUES ('${quote_pgsql:$sender_host_address}')

And this little piece belongs at the end of your check rcpt ACL...

# auto-whitelist outgoing mail (if not already auto-whitelisted)
  warn set acl_m5 = ${lookup pgsql{WHITELIST_CHECK}{found}{missing}}
    acl           = check_relay
    domains       = ! +local_domains
  warn condition  = ${if eq{$acl_m5}{missing}{true}}
    acl           = check_relay
    domains       = ! +local_domains
    condition     = ${lookup pgsql{WHITELIST_ADD}{yes}{no}}
  warn condition  = ${if eq{$acl_m5}{found}{true}}
    acl           = check_relay
    domains       = ! +local_domains
    condition     = ${lookup pgsql{WHITELIST_RESET_EXPIRE}{yes}{no}}

  # accept anything from host we relay for
  accept  hosts = +relay_hosts

  # deny anything not to local domains or domains we relay for
  deny domains = ! +local_domains : ! +relay_domains
    message    = relay not permitted

  #
  # we have now performed relay checks so from here on we know that recipients
  # must be in local_domains or relay_domains
  #

  # accept mail if relay_ip is in the auto-generated relay_ip_whitelist table
  warn set acl_m6 = ${lookup pgsql{SELECT relay_ip FROM relay_ip_whitelist \ 
                    WHERE '${quote_pgsql:$sender_host_address}' = relay_ip}\ 
                    {yes}{no}}
  warn message    = X-Greylist: ip whitelist pass
    condition     = ${if eq{$acl_m6}{yes}{yes}}
    condition     = ${lookup pgsql{WHITELIST_IP_ADD}{yes}{no}}
  accept domains  = +local_domains : +relay_domains
    condition     = ${if eq{$acl_m6}{yes}{yes}

  # accept to abuse@<any local domain> and postmaster@<any local domain>
  accept local_parts = abuse : postmaster

  # block invalid recipients
  deny message = $local_part@$domain is not a vaild recipient - maybe the \ 
                 account has been closed or perhaps you have misspelled the \ 
                 recipient email address. Please contact postmaster@$domain if \ 
                 you have any queries
    log_message = recipient verification failed
    domains = +local_domains
    !verify = recipient

  #
  # greylisting....
  #

  # 1. set acl_m8 equal to recipient so still available in DATA acl
  # 2. check greylist status and set acl_m9 variable (not null-sender)
  # 3. if not in greylist then add (valid rcpt domain & not null sender bounces)
  # 4. defer if still greylisted and update blocked count 
  # 5. update passed count for whitelisted triplets + add record to relay_ip_log

  warn set acl_m8 = ${quote_pgsql:$local_part@$domain}

  warn !senders = :
    set acl_m9  = ${lookup pgsql{GREYLIST_TEST}{$value}{none}}

  defer message  = Temporary failure - please try again later.
    log_message  = greylisted.
    condition    = ${if eq{$acl_m9}{none}{true}}
    condition    = ${lookup pgsql{GREYLIST_ADD}{yes}{no}}

  defer message  = Temporary failure - please try again later.
    log_message  = greylisted retry.
    condition    = ${if eq{$acl_m9}{grey}{true}}
    condition    = ${lookup pgsql{GREYLIST_BLOCKED}{yes}{no}}

  warn condition = ${if eq{$acl_m9}{white}{true}}
    message      = X-Greylist: greylist pass
    condition    = ${lookup pgsql{GREYLIST_PASSED}{yes}{no}}
    condition    = ${lookup pgsql{WHITELIST_IP_ADD}{yes}{no}}

  # end of greylisting

  # accept anything that gets this far
  accept

And here is the first part of your check data ACL...

  # do not scan messages submitted from our own hosts
  accept  hosts = +relay_hosts

  # START greylisting for null sender (bounces)

  warn senders = : 
    set acl_m7 = ${lookup pgsql{GREYLIST_TEST}{$value}{none}}

  defer message  = Temporary failure - please try again later.
    log_message  = greylisted.
    condition    = ${if eq{$acl_m7}{none}{true}}
    condition    = ${lookup pgsql{GREYLIST_ADD}{yes}{no}}

  defer message  = Temporary failure - please try again later.
    log_message  = greylisted retry.
    condition    = ${if eq{$acl_m7}{grey}{true}}
    condition    = ${lookup pgsql{GREYLIST_BLOCKED}{yes}{no}}

  warn condition = ${if eq{$acl_m7}{white}{true}}
    message      = X-Greylist: greylist pass
    condition    = ${lookup pgsql{GREYLIST_PASSED}{yes}{no}}
    condition    = ${lookup pgsql{WHITELIST_IP_ADD}{yes}{no}}

  # END greylisting for null sender (bounces)

Database maintenance

Two scripts are used to maintain the database tables. Each script needs to be modified for your environment with appropriate database settings.

  • exim_greylist_maintenance should be run from cron every hour or so to keep the main relayiptofrom table pruned. This also moves expired triplets into relayiptofrom_archive.
  • exim_ipwhitelist_maintenance should be run from cron once or twice each day to rebuild the relay_ip_whitelist table and purge old records from relay_ip_log. Make sure you set $maxage and $whitecount for local policy.

Stats generation

I'm still working on the stats side of things :-)

References

Posted on 8 August 2009 by SysPete [Permalink]