Grepular

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.