A child-safe SMTP whitelist with Postfix and MySQL

Worried about your children receiving adult-oriented spam while on the Internet? I was. Here’s how I solved the problem by protecting their email addresses using Postfix and MySQL.

My kids have been using the Internet now for over a year, mostly playing Flash-based games at a variety of websites. At first my wife or I would sit with the kids to supervise their activity online. As they mastered navigating their favorite sites we granted them more and more autonomy during their Internet sessions. This was easy enough because the kids’ computers were not connected to our home network, so any time they wanted to use the Internet they had to ask us permission to use our computers.

When the kids wanted to play online more and more, we knew it was time to connect their computers to the network, and to allow them Internet access. I installed the Squid proxy, added the kids’ favorite sites to a list of authorized destinations, and configured the kids’ browsers to use the proxy. It’s certainly not a fool-proof configuration, but my wife and I felt comfortable allowing the kids to use the Internet in their rooms without watching everything they did. This worked great: Squid ensured that the kids only went to destinations that my wife and I had approved ahead of time. We could add new destinations easily enough. And as an unexpected benefit, almost all of the advertising on the kids’ websites was blocked by Squid, too, since it was coming from domains that were not in our whitelist.

I felt pretty confident that my kids were going to be protected from most inappropriate content they might accidentally stumble upon. Then, without warning, this confidence was completely shattered in a most unexpected way: Grandma wanted to send the kids email!

I get about one hundred pieces of junk mail every day. I don’t expect my kids to get that much, but even one piece of unsolicited pornographic mail is too much. I run SpamAssassin on my mail server, so I briefly considered using SA’s whitelist feature to help me filter messages. “I could send all whitelisted email to the kids’ inboxes and send everything else elsewhere…” I thought to myself. I finally decided that I really don’t want the kids to get any mail from anyone I don’t know. Filtering after the fact is not solving the problem, it’s merely dealing with the symptoms. Not filtering, but outright rejection of unapproved senders was what I wanted. Thankfully, Postfix supports exactly this!

A Simple Whitelist

The easiest solution is a single whitelist. Identify local addresses you want protected, and they can only receive messages from email addresses in the whitelist. Any local address not identified as protected can receive email from anyone.

I use a MySQL database to store the list of protected users and authorized senders, but you could just as easily use PostgreSQL, dbm, or hash files. Configuring Postfix to support MySQL lookup tables is not difficult, and is well documented both at the official Postfix website, and also in the documentation bundled with the source. For the rest of this document I’ll assume you have successfully configured Postfix to talk to MySQL.

In order to establish our whitelist, we need to create two tables — one for protected recipients and one for authorized senders — and tell Postfix to check these tables for incoming mail.

First, let’s create the tables that will define our list of protected users and the list of whitelisted addresses. Use your MySQL tool of choice (mysql command line, MySQL Control Center, phpMySQL, or whatever). I use a database called postfix to hold all the lookup tables I use, adjust to your configuration accordingly.

USE postfix;

CREATE TABLE `protected_users` (
`recipient` VARCHAR( 50 ) NOT NULL ,
`class` VARCHAR( 10 ) NOT NULL,
UNIQUE ( `recipient` )
);

CREATE TABLE `whitelist` (
`sender` VARCHAR( 50 ) NOT NULL ,
`action` VARCHAR( 2 ) NOT NULL ,
UNIQUE ( `sender` )
);

Next, we need to grant SELECT permission on these tables to a user. This user only needs SELECT privileges.


GRANT SELECT on postfix.protected_users, postfix.whitelist TO postfix@localhost identified by 'postfix';


For each user you want protected, add them to the protected_users table with a class of ‘whitelist’:


INSERT INTO protected_users (recipient, class) VALUES ('vague@imprecise.info', 'whitelist');


Now for the allowed addresses. For each address that you want to permit email from, add them to the whitelist table with an action of ‘OK’:


INSERT INTO whitelist (sender, action) VALUES ('grandma@example.com', 'OK');


Now we need to create two files which tell Postfix how to access the database. /etc/postfix/protected_users.cf defines which users are protected by the whitelist. It looks like this:

dbname = postfix
hosts = localhost
user = postfix
password = postfix
table = protected_users
select_field = class
where_field = recipient

The second file — /etc/postfix/whitelist.cf — defines which addresses are whitelisted, and thus allowed to send mail to our protected users:

dbname = postfix
hosts = localhost
user = postfix
password = postfix
table = whitelist
select_field = action
where_field = sender

These files don’t need any particular permission restrictions, since we’ve granted only SELECT privilege to the postfix@localhost user. You will want to restrict access to these files if for any reason you give the postfix@localhost user more than just SELECT privileges.

Now we configure Postfix’s main.cf to use these lookup tables by adding this:

smtpd_recipient_restrictions = reject_non_fqdn_sender,
        reject_non_fqdn_recipient,
        reject_unauth_pipelining,
        permit_mynetworks,
        permit_tls_clientcerts,
        warn_if_reject reject_invalid_hostname,
        warn_if_reject reject_non_fqdn_hostname,
        reject_unauth_destination,
        check_helo_access hash:/etc/postfix/helo_checks
        mysql:/etc/postfix/protected_users.cf

smtpd_restriction_classes = whitelist
whitelist = check_sender_access mysql:/etc/postfix/whitelist.cf, reject

If you’re concerned about Postfix hammering your MySQL server, you can use Postfix’s proxy mechanism to cache the lookup results to spare a few queries:

smtpd_recipient_restrictions = reject_non_fqdn_sender,
        ...
        proxy:mysql:/etc/postfix/protected_users.cf
whitelist = check_sender_access proxy:mysql:/etc/postfix/whitelist.cf, reject

If you do this, you’ll need to also tell the proxy service that it is permitted to cache these lookups:


proxy_read_maps = proxy:mysql:/etc/postfix/whitelist.cf, proxy:mysql:/etc/postfix/protected_users.cf


Execute `postfix reload` and check the log files for any error messages. If there are no errors, you’re done!

Now, for each incoming message Postfix will examine whether the recipient is defined in the protected_users table. If they are, Postfix will check whether the message sender is included in the whitelist table. If the sender is listed, the mail is delivered. If the sender is not listed in the whitelist, the message is rejected with an error code of 554, “Recipient address rejected: Access denied (in reply to RCPT TO command)”.

It’s important to note that this is an all-or-nothing proposition. Any local user listed in the protected_users table can only receive mail from senders listed in the whitelist table. Further, any sender listed in the whitelist table can send email to any user listed in the protected_users table. It’s extremely easy to forge email headers, so if someone really wanted to send email to a whitelisted user they could certainly do so.

If you need something more complicated, like per-user whitelists for more precise controls, you can extend this basic whitelist in several ways. The action column in the whitelist table can be any of:

• OK
• 4NN text
• 5NN text
• REJECT optional text…
• DEFER_IF_REJECT optional text…
• DEFER_IF_PERMIT optional text…
• restriction…
• DISCARD optional text…
• DUNNO
• FILTER transport:destination
• HOLD optional text…
• PREPEND headername: headervalue
• REDIRECT user@domain
• WARN

If you decide to use something other than the simple whitelist I created, you’ll want to increase the size of the action VARCHAR field.

Whitelist Management

In order to make the whitelist as easy as possible for my wife and I, I wrote the following PHP script I called whitelist.php.

Save whitelist.php somewhere, and protect it with the following addition to your Apache httpd.conf, to ensure that only you can manage the whitelist:

     AllowOverride None
     Options None
     AuthName "Private"
     AuthType Basic
     AuthUserFile /etc/apache/htpasswd
     Require valid-user

For more information on Apache auth, see this.

Next, grant SELECT, INSERT and DELETE privileges to the whitelist@localhost user:

GRANT SELECT,INSERT,DELETE ON postfix.protected_users,
   postfix.whitelist TO whitelist@localhost IDENTIFIED BY 'whitelist';

I include only the local user portion –the user@ — of email addresses in my protected users because my kids’ email accounts are actually virtual accounts that forward to their real skippy.net account. I don’t want anyone sneaking past the whitelist by addressing mail directly to their skippy.net account. If you’re not using virtual domains or virtual accounts, you could just protect the full email address(es) as needed.