stiefels.net

Recently I was flooded with e-mails a friend sent me. Mails with PowerPoint or PDF attachments of hundreds of kilobytes with jokes. Although I told him to stop this he ignored me and continued sending me this crap.
I could create a rule in Mail.app which deletes such mails but that wouldn’t help as the rule mechanism only works with received mails. But I don’t even want to have to download them.
A better solution is to let the mail server (here Exim) deny such mails while they are delivered.

Technorati Tags: Exim

To do that we first have to store the e-mail address of the sender in a database. This allows us to make changes to our blacklist without having to restart the mail server. As my greylisting implementation already uses MySQL I will use that for blacklisting too.

 CREATE TABLE blacklist (
   id int(11) PRIMARY KEY auto_increment,
   from_local_part varchar(100),
   from_domain varchar(100)
 );

This creates a new table in the database in which an ID (just for administration purposes), a local part (the part before the @) and the domain of the sender can be stored.

A new entry in the blacklist can be inserted with:

INSERT INTO blacklist
VALUES (NULL, 'someone', 'somedomain.com');

In the exim configuration file (exim.conf) we first create a macro which contains the controlling SQL statement:

# blacklist macro
BLACKLIST_TEST = SELECT CASE \
  WHEN COUNT(*) > 0 THEN 1 \
  ELSE 0 \
END \
FROM blacklist \
WHERE from_domain ='${quote_mysql:$sender_address_domain}' \
AND (from_local_part = '${quote_mysql:$sender_address_local_part}' \
     OR from_local_part = '*')

As you can see we want to be able to block complete domains by inserting a asterisk in the from_local_part column. The statement can return two different values. A 1 is returned if a entry is found and the mail should be blocked. A 0 is returned if the mail should not be blocked by the blacklist mechanism.

If you haven’t configured exim to use MySQL yet be sure it is compiled with MySQL support and add the following line to exim.conf in order to set the connection parameters:

hide mysql_servers = server/database/username/password

The last thing we have to do is to run the SQL statement every time a mail comes in. A Exim mail server normally uses access control lists in its configuration. We will build our blacklist mechanism in the acl_check_rcpt control list:

# access control lists
begin acl

acl_check_rcpt:
  ...
  warn  set acl_m3 = ${lookup mysql{BLACKLIST_TEST}{$value}}
  ...
  deny  message    = Blacklisted. Your mail has been blocked.
        condition  = ${if eq{$acl_m3}{1}}
  ...

The ACL configuration consists of two entries.
The first (beginning with warn) just runs the SQL statement and saves the result (1 or 0) in a variable called acl_m3.
Below the second entry (beginning with deny) uses that variable to decide whether to deny the mail or not. If denied the mail server of the sender gets the error message Blacklisted. Your mail has been blocked. Don’t forget to restart Exim to make the changes work.