Dbmail? A great Open Source email system, especially for LAMP/MySql administrators

A couple of weeks ago, I was thinking about how I may build an advanced search utility for my own email archive. One way to make complex queries on the archive seemed to be to put it all into a relational database. Since the Dbmail system stores email in that way, I asked its developers and Harald Reindl (an email administrator at The Lounge who already uses Dbmail: I found him in the PostFix Mailing list archives) if Dbmail could be used in that way.

The feedback I got made me change my mind about how to rebuild my own email search system, for the reasons explained below. At the same time, how and why Harald and his company use Dbmail seemed really interesting. Here’s the story.

About doing complex email searches with Dbmail

Harald explained to me that:

you should only do searches in the mail client, via IMAP. The Dbmail database is not nice to search for messages because they splitted in all their mime-parts and many db-records. Therefore, even if your search is successful, it would be hard to get a complete message without studying the Dbmail sources. So, since the search is running over IMAP and with the capabilities of Imap you can’t do more complex searches, even if the backend is a relational database. However, searches can be faster, just because the messages are splitted and indexed on the other side. Even if Dovecot in the latest versions builds an index too, so I would not expect any difference. It is surely possible to make a backend in PHP or what else language to search in the Dbmail database, but be careful about references to not display messages not owned by the user who starts the search

Why you may want to use Dbmail

Harald: I chose Dbmail because it has a 100% Mysql-backend configuration and the possibility to have a synchronized backup-slave in the network, which you can stop everytime to make consistent snapshots for offsite backups without interrupting the mail server. We are using Dovecot as proxy in front of Dbmail for several reasons:

• it supports more auth mechanisms than dbmail
• it supports TLS/SSL directly
• it supports replaces (% to @) since historically many users are configured with %
• postfix supports dovecot directly for SASL-Auth, so you have the same auth-mechs and encryption options for pop3, Imap and smtp
• security: I think it would be hard to exploit Dbmail through Dovecot (whereas exploiting directly dovecot seems harder, since it has only the user-logins)

We decide to migrate to Dbmail because we were running Apple servers (*brrr*) with Eudora mail server and I needed a replacement running on Linux/Vmware-hosts.

Since my main job is PHP/Mysql-developer, a full db-driven server gives me options to write special interfaces for all needs, doing cron jobs for notifies, cleanups, implementing auto-reply-backends and many nice things without touching text configurations.

Postfix is also nearly 100% Mysql-driven in our environment. There are great options for forwarders/aliases on both sides. If there is anything to do you only have to figure out which of both components can do what you want best, with the smallest side-effects on the whole system.

It was a really hard job to write a PHP-backend with 20.000 lines of code in few weeks, while learning much about mail servers. However, this has been running perfectly since the summer of 2009 with only a few “WTFs” and optimizations, but they are because of little know-how at the beginning.

This means after two months working day and night there was a complete solution, and for the second mailserver the whole virtual machine was cloned in 2010 and needed only minimal configuration. The Mysql replication is a big improvement for backups, here is how we use it:

• VMware-ESXi-Cluster
• Mailserver on one host
• Clone of the first machine on the second
• Replication between both of them
• Replication is a separate Mysql instance, read-only port 3307
• the replication can be used for Postfix as fallback, since readonly is enough for that
• on the backup-machine a normal instance with a copy of the db is running…
• …so you can start dbmail-imapd with this instance and directly access it via Thunderbird
• once a week both mysqld are stopped and rsync-ed from replication to backup
• before that happens the last backup goes to “mysql-last-week”
• once per day the replication is stopped and a offsite-backup per rsync done

So we have permanently access to the mailbox versions from last sunday and we can switch a week back with a simple script and restore a customer with imapsync between both machines and have a daily backup on the other end of the city. And through all the time it takes to do this, the mail services are not down for one second. I would not know how to do this with a file driven mail server, because there are permanently files changed and nobody knows if the backup is clean enough if it is ever needed.