Grepular

Whitelist Recipients in Exim

Written 8 years ago by Mike Cardwell

It’s my opinion that if you send an email to somebody, your MSA should record that knowledge and make it available for the spam filtering on your MX‘s. Specifically, I automatically whitelist all email addresses that my users send email to. Here’s how I do it in Exim, using MySQL 5 as the data store:

  1. Create the MySQL database

  2. Create the table:

    CREATE TABLE recipient (
        local_part VARCHAR(64)  NOT NULL,
        domain     VARCHAR(255) NOT NULL,
        ctime      DATETIME     NOT NULL,
        atime      DATETIME     NOT NULL,
        hits       INT UNSIGNED NOT NULL DEFAULT 1,
        UNIQUE( local_part, domain )
    );
    

    You might find the ctime, atime and hits columns to be redundant, but they could be used for expiring old or unused entries from the database. “ctime” is set to the date the email address was first emailed. “atime” is the date it was last emailed, and “hits” is the number of times it has been emailed.

  3. I use a stored function in MySQL to make the Exim configuration more straight forward when adding entries to the database. Here’s how to create it:

    DELIMITER ;;
    CREATE FUNCTION exim_record_recipient ( VAR_email TEXT ) RETURNS BOOLEAN
    BEGIN
      DECLARE _HITS INT;
    
      SET @local_part = SUBSTRING_INDEX( VAR_email, '@', 1 );
      SET @domain     = SUBSTRING_INDEX( VAR_email, '@', -1 );
    
      SELECT hits INTO _HITS FROM recipient
       WHERE local_part = @local_part
         AND domain     = @domain;
    
      IF _HITS > 0 THEN
          UPDATE recipient SET atime = NOW(), hits = hits+1
           WHERE local_part = @local_part
             AND domain     = @domain;
      ELSE
          INSERT INTO recipient
             SET local_part = @local_part,
                 domain     = @domain,
                 ctime      = NOW(),
                 atime      = NOW();
      END IF;
      RETURN TRUE;
    END;
    ;;
    DELIMITER ;
    
  4. Configure Exim so it knows how to access the database. Example:

    hide mysql_servers = localhost/email/admin/secret-password
    
  5. Create two macros in Exim. One for updating the database, and one for checking it:

    ADD_TO_WHITELIST = ${lookup mysql{\
     SELECT exim_record_recipient('${quote_mysql:$local_part@$domain}')\
    }{true}}
    
    WHITELISTED = ${lookup mysql{\
                  SELECT hits FROM recipient \
                   WHERE local_part = '${quote_mysql:$sender_address_local_part}' \
                     AND domain     = '${quote_mysql:$sender_address_domain}' \
               }{true}{false}}
    
  6. Now to update the database, add the following to the beginning of your rcpt acl:

    warn authenticated = *
       condition     = ADD_TO_WHITELIST
    
  7. Now, if you want to prevent certain types of filtering from happening, you can for example do the following in your acl_smtp_data acl:

    deny !condition = WHITELISTED
       spam       = nobody
    
  8. If you want to check a header, such as the From header against the whitelist, rather than or as well as the envelope sender, your WHITELISTED macro would look like this:

    WHITELISTED = ${lookup mysql{\
      SELECT hits FROM recipient \
       WHERE local_part = '${quote_mysql:${local_part:${address:$h_From:}}}' \
         AND domain     = '${quote_mysql:${domain:${address:$h_From:}}}' \
    }{true}{false}}
    

I would recommend that you skip spam filtering for your whitelisted email addresses, but that you don’t skip virus scanning for any reason.

Looking to hire somebody like me? I'm open to offers of full time employment and small contract jobs. Check out my hiring page. You can follow this Blog using RSS or . To read more, visit my blog index.

Feeling generous?BitcoinMoneroZcashPaypal