I had to solve a problem for a customer where they need to delegate management of backup MX servers to their clients. Of course, they don’t want to give their clients root access to their server, but they can write an app that gives users access to certain entries in a database, based on their privileges. This means I can join my two favourite server apps in the world: Postfix + MySQL

First, we need to create a database and some tables. These are quick and dirty and only meant to be a proof of concept. The fields a pretty straight forward: id is just a number, domain is the domain being relayed, and destination is the primary MX or where ever you need the mail to go. The syntax is as per the Postfix transport documentation. :domain.tld will do an mx lookup on domain.tld, and smtp:host.domain.tld will deliver directly to the host specified. My database is called ‘backupmx’ 

CREATE TABLE `domains` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `domain` varchar(128) NOT NULL DEFAULT '',
  `destination` varchar(128) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `domain` (`domain`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

CREATE TABLE `recipients` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `address` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `address` (`address`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;


Let’s make a two MySQL users. The first one is for the web app to write to the database to, and the second one is for Postfix to read from it:

mysql> grant usage on backupmx.* to 'mxadmin'@'localhost'
      identified by 'acrazypassword';
Query OK, 0 rows affected (0.00 sec)

mysql> grant select on backupmx.* to 'mxreadonly'@'localhost'
      identified by 'anothercrazypassword';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Then, create three files for postfix to read the MySQL configuration:

/etc/postfix/mysql_relay_domains.cf

hosts = 127.0.0.1
dbname = backupmx
user = mxreadonly
password = anothercrazypassword
query = SELECT domain FROM domains WHERE domain='%s';

/etc/postfix/mysql_relay_recipients.cf

hosts = 127.0.0.1
dbname = backupmx
user = mxreadonly
password = anothercrazypassword
query = SELECT * FROM recipients WHERE address = '%s';

/etc/postfix/mysql_transport.cf

hosts = 127.0.0.1
dbname = backupmx
user = mxreadonly
password = anothercrazypassword
query = SELECT destination FROM domains WHERE domain = '%s';

Populate your database. Here my example:

mysql> select * from domains;
+----+-----------------+------------------------+
| id | domain          | destination            |
+----+-----------------+------------------------+
|  1 | example1.com    | :example1.com          |
|  2 | example2.com    | smtp:host.example2.com |
+----+-----------------+------------------------+
3 rows in set (0.00 sec)

mysql> select * from recipients;
+----+----------------------------+
| id | address                    |
+----+----------------------------+
|  1 | [email protected]         |
|  2 | [email protected]         |
|  3 | [email protected]         |
|  4 | [email protected]         |
+----+----------------------------+
12 rows in set (0.00 sec)

Lastly, lets configure postfix. You need just the following three lines:

relay_domains = mysql:/etc/postfix/mysql_relay_domains.cf
relay_recipient_maps = mysql:/etc/postfix/mysql_relay_recipients.cf
transport_maps = mysql:/etc/postfix/mysql_transport.cf

Now, I know what you’re thinkging – recipient lists on a backup MX? Well, yes. the idea of a backup MX was designed in the last century, and it shows. Today, a backup MX is a delicious target for spammers. If your primary MX does recipient verification (as it should), and your backup MX accepts mail indiscriminately, your backup MX is now stuck with that spam, and it’s going to try to bounce it, making *you* the spammer. And if your using a production mail server as a backup MX for another domain, you’ll find yourself fighting to get your IP off RBLs sooner or later.

« »