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.

Create your Web database applications with DaDaBIK

DaDaBik is Free Software that you can use (writing little or no code by hand!) to create PHP-based Web applications that even users with little previous experience can use to manage several types of (already existing) relational databases.
DaDaBik is Free Software that you can use (writing little or no code by hand!) to create PHP-based Web applications that even users with little previous experience can use to manage several types of (already existing) relational databases.

I’m happy to report that, after a quite long stop, development of DaDaBIK recently restarted. The result, which you can see in the screenshots in this page, is version 4.3: the images come by a demo accounting application created using DaDaBIK without writing any code manually.

Customer table, with support for images and other files
Customer table, with support for images and other files

With the Web database applications developed with DaDaBIK you can directly insert images or any other type of file in the database. The applications are also themable, so it is easy to visually integrate them in your already existing website.

Invoice detail
Invoice detail

DaDaBIK is developed in Italy by Eugenio Tacchini, but the interface and documentation for users and developers are also available in English and other languages.

DaDaBIK is NOT PhPMyAdmin!

An important thing that you should not overlook is that DaDabik is not an alternative to programs like PHPMyAdmin. I asked Eugenio to explain the difference, and this is how he summarizes it:

The goals of PhPMyAdmin and DaDaBIK are different and in a sense they complement each other. PhPMyAdmin is a ready-to-use DB administration tool, while DaDaBIK is software to create, in the simplest possible way, generic Web interfaces aimed at unexperienced end users. Another very important difference between the two programs is that DaDaBIK is database agnostic. Today it already supports MySQL, PostgreSQL, Oracle and SQL Server and I plan to add support for even more databaseS in the future, starting with SQLite and IBM DB2.