My name is Philipp C. Heckel and I write about nerdy things.
This site moved here recently from blog.philippheckel.com!

How to: Postfix as mail relay with greylisting support using SQLgrey


Administration, Linux

How to: Postfix as mail relay with greylisting support using SQLgrey


Greylisting is a very efficient technique for fighting spam and can reduce the spam messages in your mailbox by more than 90%. It uses the fact that most spammers only try delivering their spam-mails once, whereas real mail transfer agents (such as the ones regular e-mail service providers are using) try delivering each message up to 4-5 days before they give up.

I have always wondered why most ESPs don’t offer greylisting for their mailboxes, but only rely on less effective and resource-hungry post-retrieval filter methods. Unfortunately, my e-mail provider is one of them so that I get at least a couple of spam mails a day …

Luckily, it is very easy to set up your own mail relay with greylisting support, i.e. a mail server that simply forwards the mail to your real provider once it passes the greylist-filter.

This little tutorial describes how to set up Postfix and SQLgrey as mail relay.


Contents


1. What you need

  • A dedicated or virtual private server with SSH root access.
  • Access to the DNS entries of your domain for adjusting the MX record; in this post called example.com

2. How it works

If you have outsourced all e-mail services to a service provider like I have, the MX record of your domain usually points to your provider’s mail server. That is, your mails go directly to the mail server of your provider, e.g. Google’s mail server → your provider’s mail server. That is, your DNS configuration looks something like this:

In order to pre-process mails with greylisting and blacklisting, your server will handle mails as intermediary, i.e., mails will always traverse your server first; in the above case something like Google’s mail server → your mail server → your provider’s mail server. Consequently, the MX record has to be changed to the IP address of your server:

But, first things first: we need to configure our server before we change the DNS records!

3. Installation & Configuration

If you have a Debian based system, install Postfix, SQLgrey and MySQL using apt-get:

This will install:

  • Postfix: a fully functioning MTA which will be configured as mail relay, i.e., instead of storing arriving mails on the system, it will just greylist them and then forward them to their real destination (your provider’s mail server).
  • SQLgrey: a SQL-based greylisting add-on for Postfix. Before accepting mails blindly, Postfix will ask the SQLgrey daemon whether to accept the mail or not. SQLgrey keeps track of mail delivery attempts and only replies with success if the foreign MTA tried delivering the mail at least twice.
  • MySQL: a RDBMS which will be used as back-end for storing Postfix’s routing tables and SQLgrey’s caching tables. Both Postfix and SQLgrey also support other back-ends such as PostgreSQL.

3.1. Configuring SQLgrey

SQLgrey’s config files reside in /etc/sqlgrey/, the main configuration happens in /etc/sqlgrey/sqlgrey.conf. The file is well documented and offers many possibilities.

The most important options are:

  • inet: IP address and port to bind the daemon to, default is 127.0.0.1:2501
  • db_*: database connection details, i.e., database, user and password
  • greymethod: defines which IP class to use for greylisting. Especially important for big e-mail service providers since the same mail might be delivered from two different IP addresses (Class C greylisting recommended!).
  • optmethod: defines if greylisting is enabled by default (optout), or has to be enabled specifically for each address or domain (optin).
3.1.1. Config file /etc/sqlgrey/sqlgrey.conf

My configuration looks like this:

3.1.2. Database

SQLgrey has a fixed database structure which is set up automatically when the script is started. All that needs to be done is to create a new database sqlgrey with a corresponding user. You can do this manually, or with a tool like phpMyAdmin:

3.1.3. Populating the database

SQLgrey automatically creates the required tables when it starts for the first time. So start the daemon using the provided init.d-script:

This creates a couple of tables in the sqlgrey-database. For our purpose and configuration, the tables optin_email and optin_domain are most interesting because only domains and e-mail addresses in these tables will be greylisted.

For our example, we will enable greylisting for the whole domain example.com:

That’s it for SQLgrey. Once we connect it to Postfix, it’ll provide us with the greylisting service we want.

3.2. Configuring Postfix

Postfix is a very flexible and powerful mail transfer agent (MTA) and can be used as final destination, or mail forwarder (mail relay). For this scenario, Postfix will be a mail relay which only forwards an e-mail if

  • its recipient is listed in the database
  • and it passes the greylisting-filter

The configuration files of Postfix reside in /etc/postfix/. The most interesting file for our purpose is /etc/postfix/main.cf.

In order to not be confused by all the more or less useful config parameters, the file shown here is minimal, i.e., you cannot remove any parameter without major consequences. Details to each of them can be found in the Postfix configuration man-page.

The most important parameters for the configuration as mail relay are:

  • myhostname: this defines your hostname, i.e., in this case relay.example.com. Regarding the hostname, two things must be considered very thoroughly:
    1. The hostname must resolve to the IP address of your server, i.e., make sure you don’t state a fake host here. Postfix uses this value as HELO/EHLO identification, and some mail servers might reject your mails if this value doesn’t resolve to your server’s IP address.
    2. If you use your top level domain here, e.g. example.com, some mail servers might additionally perform a MX lookup and match your server’s IP address with the one of the MX record. In case the MX record points to a different IP address than the A record of the TLD, foreign servers might also reject all your mails. In my case, this resulted in log entries like this:

      As you can see, the foreign hosts suspected me of forging the HELO name, and denied relaying my mails.

  • relay_domains: this option links to the database table relay_domains and defines the domains managed by this mail server. If a recipient-domain is not in this table, Postfix will reject the mail. For this example, the domain example.com must be added to this DB table.
  • relay_recipient_maps: this option links to the database table relay_recipients and defines the e-mail addresses managed by this mail server. If a recipient address is not in this table, Postfix will reject the mail. This option is closely linked to relay_domains and will not work without it!

    In this case, only one address will be added to this table: john.doe@example.com.

  • transport_maps: this option links to the database table transport and defines to which mail server incoming mails will be forwarded. Routing can happen address- or domain-based.

    In this case, mails for example.com shall be forwarded to our provider’s mail server, i.e., this table must have an entry of the form example.comsmtp:[mx.my-esp.tld].

    For details on the value format, read the transport man page.

  • smtpd_recipient_restrictions: this is where the actual magic happens. This option checks the RCPT TO field of each incoming mail, i.e., the recipient, and then queries the greylisting service. Its options closely relate to the relay_*-tables from above:
    • permit_mynetworks allows local applications to send e-mails. If you have web sites running on localhost that may use e-mail, do not remove this option.
    • reject_unauth_destination queries the relay_domains SQL table, i.e., it checks whether a the incoming recipient domain is relayed by our server.
    • reject_unlisted_recipient queries the relay_recipients SQL table to find out if the exact address is relayed.
    • check_policy_service queries the SQLgrey daemon which in turn either allows or rejects the mail.
3.2.1 Config file /etc/postfix/main.cf

Here’s the minimal main.cf config file to make Postfix a mail relay with greylisting support:

3.2.2. Database

Postfix is very flexible when it comes to address and route handling. In fact, its configuration doesn’t need a database back-end at all. However, using a SQL database makes everything much easier. I decided to use a very straight forward database structure which directly relates to Postfix’ configuration options.

First, create a database postfix and a corresponding read-only user:

Note: It is important that you use 127.0.0.1 as host, and not localhost, because Postfix runs in a chroot-environment and wouldn’t be able to access localhost.

After setting up the database, add the following three tables:

In order to connect Postfix with the database, we need to create the three config files specified above: /etc/postfix/mysql_*.cf:

Before we can now start testing our server, we need to compile these config files to Postfix compatible lookup tables. Do that by running the following command:

3.2.3. Populate the database

Now we have to fill Postfix’ database with the domains and addresses we’d like to relay. In particular, that means we have to add example.com to relay_domains and transport, and the full addresses to relay_recipients:

The entry structure for each table is different. Please refer to the Postfix manual for details (cp. transport, relay_domains, and relay_recipient_maps).

Note: the database structure above is not optimal since it requires redundant entries in three different tables. Even though the structure is not perfect, I have chosen this layout to make it easily understandable!

4. Test your server

After this short configuration period it’s now time to finally start the Postfix server:

To make sure you didn’t make any mistakes in the configuration, you should now check the log files. Postfix and SQLgrey both use syslog so that you should be able to determine the system’s status like this:

If the log doesn’t show any errors, we can now try if everything works as expected. To do so, simply connect to the server from your home computer via telnet:

If Postfix replies with a 450 error code, i.e., relay temporarily denied, everything works just fine. On the server side, the log should output something like this:

Wait 5 minutes and try connecting again via telnet. This time, SQLgrey will detect that this is your second delivery attempt and add the sender e-mail and its IP address to the automatic white list (AWL). Postfix will accept your mail and forward it to your provider’s mail server (according to the transport-table):

5. Go live: change the DNS record

Play around a little and make sure that everything works as expected. If it does, change the DNS record like described above, i.e., set the MX record of the domains to be relayed to your server’s IP address.

Note: For the first few mails, you should definitely watch the logs. If anything goes wrong, you can always change back the MX record. But be aware that DNS changes might take up to 48h!

If you have any questions, please comment below. I am open for suggestions!

2 Comments

  1. Sniper

    Hi. Thank you for the How-To. Works like a charm.

    Is there a way of allowing all relay_recipients to avoid loading them one by one? Some of my clients have over a 1000 users… Ouch.

    For example:
    INSERT INTO `postfix`.`relay_recipients` (`email` ,`active`)
    VALUES (‘*@example.com’, ‘y’);

    I am new to MySQL and actually all of this.

    Or is there another way of adding many users at once?


  2. Philipp C. Heckel

    Not sure exactly what you mean. Here are two possible answers that might help.

    1) Well there is the relay_domains table which is checked first; only after that returns negative, postfix checks the relay_recipients table. So if you want maximum efficiency when checking the tables at runtime, use relay_domains by adding ‘@example.com” like in the example above:

    INSERT INTO `postfix`.`relay_domains` (`domain` ,`active`)
    VALUES (‘example.com’, ‘y’);

    2) If it’s just about how to load the MySQL tables initially, i.e. how to add many users to the tables, simply use a script to add them, e.g. a php script:

    < ?php mysql_connect("localhost", "mysqluser", "mysqlpass"); mysql_select_db("mysqldb"); $active_recipients = array("john@example.com", "mary@example.org", ...); foreach ($active_recipients as $recipient) { $query = "INSERT INTO `postfix`.`relay_recipients` (`email` ,`active`) VALUES ('$recipient', 'y');"; echo "$query\n"; mysql_query($query); } ?>

    Note: This script is not tested.
    Hope this helps a little. :-)

    Cheers
    Philipp


Leave a comment

I'd very much like to hear what you think of this post. Feel free to leave a comment. I usually respond within a day or two, sometimes even faster. I will not share or publish your e-mail address anywhere.