Tag Archive: mail


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;

View full article »

I recently needed to do some testing with sending mail, and needed a host that would accept mails without asking questions, and just discard it. This turned out to be pretty easy with Postfix.   Starting with a fresh installation on Debian Lenny, add the following to /etc/postfix/main.cf:

# accept all mail
relay_domains = static:ALL
# then get rid of it
smtpd_end_of_data_restrictions = check_client_access static:discard

And before I even got to send a test mail, someone had beat me to it:

Jul  9 03:43:58 mx02 postfix/smtpd[13940]: 6BC681740FC: discard: END-OF-MESSAGE
from 114-45-59-8.dynamic.hinet.net[114.45.59.8]: <114-45-59-8.dynamic.hinet.net
[114.45.59.8]>: Client host triggers DISCARD action; from=<[email protected]>
to=<[email protected]> proto=SMTP helo=<98.129.169.123>

Yes, I probably should have firewalled out port 25…

Today I got tasked with removing duplicate mails from a mail folder with over 100,000 mails in it.  Doing this from a mail client is so impractical, it’s not even worth giving any thought at all.  Fortunately, the mailbox is on a mail server using Maildir style mailboxes, so I knew this could be done with minimum effort.

I discovered the ‘reformail’ utility, part of courier-imap, and after a few trial runs, I settled on the following:

# cd /path/to/mailbox/Maildir/cur

# for i in `find . -type f`; do reformail -D 10000000 /tmp/duplicates <$i && rm $i; done


-D looks for, and deletes duplicates.

10000000 is the length of the temporary file where a list of message IDs will be written

/tmp/duplicates is the aforementioned temporary file.

The temporary file needs to be big enough to accommodate the message ID of each mail.  In this particular case, I have found the average length to be 54 characters, but I would suggest using around double that to be safe.  So adjust to your needs.

In a big mail folder, and especially on ext3, this will take a long time to complete.

I recently decided to move my enormous mail archive from my trusty Courier-imap/Maildir setup to DBmail. The reason is simple.  I have several mail folders with 100,000+ mails in.  That means several directories with upwards of 100,000 files in.   And that means bad performance.    There is another reason:  I can execute far more powerful searches with an SQL query, than any mail client can allow me to do.

DBmail is a POP3/IMAP server that uses a regular database server (currently MySQL, PostgreSQL or SQLite) for its mail store.  Given the obvious advantages, I’m surprised this isn’t more popular.

There is one gotcha to the setup.  MySQL, being Swedish, has a default collation setting of ‘latin1_swedish_ci’ while DBmail assumes ‘utf8_general_ci’ will be set.  But the DBmail docs, and even the MySQL notes page, does not mention this* at all, and the included create_tables.mysql script does not set the correct collation either.   This results in the following error showing up in the logs:

Sep 14 03:00:01 hermes dbmail/maintenance[16708]: Error: dbmysql.c,db_mysql_check_collations(+138): collation mismatch, your MySQL configuration specifies a different charset than the data currently in your DBMail database.

This is easily fixed.  Assuming your database is called ‘dbmail’ do:

mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select * from SCHEMATA where SCHEMA_NAME = ‘dbmail’;

The ‘DEFAULT_COLLATION_NAME’ column will most likely show ‘latin1_swedish_ci’ – this is the problem.  Run the following:
mysql> alter database `dbmail` collate `utf8_general_ci`;
Query OK, 1 row affected (0.02 sec)

Run the select query again and check if the right collation is showing.  After doing this, dbmail should connect to MySQL without any problems.