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.

Review, Linux E-Mail,set up, maintain, and secure a small office e-mail server

(this is a review that i originally posted somewhere on Slashdot, IIRC)

Linux E-mail, Second Edition is a book written for Packt Publishing by I. Haycox, A. McDonald, M. Back, R. Hildebrandt, P.B.Koetter, D. Rusenko and C. Taylor. Linux E-mail contains Continue reading Review, Linux E-Mail,set up, maintain, and secure a small office e-mail server

Comments to, Wanted, Virtual Personal Email Servers

These are the comments I got when I wrote Wanted: Virtual Personal Email Servers:

(me:) Today this article was announced on LinuxToday and this is the first comment it got:

I didn’t feel like creating an account on his site just to make this comment (where’s OpenID please?) but in case he reads the comments here – the obvious solution is Citadel, which is a nice compact installation that does pretty much everything he’s looking for in a single package.

Here is my answer:

Art, thanks for your feedback. I agree that creating an account is bothering. It is definitely my intention to set up as soon as possible OpenID and/or some captcha system so that even unregistered users can contribute feedback directly on the website. I only have to find the time :-(

With respect to Citadel: personally, I don’t need it. I already built and use my own, fully customized VPES almost two years ago, it’s doing great and I am very happy with it. The point of this article is that:

  1. the majority of email users will simply refuse to switch to anything that isn’t complete and manageable from the first minute from one web control panel. They won’t install and configure stuff from the command line as I already did
  2. As good as it is, Citadel is NOT “pretty much everything” I am suggesting. Far from it. For example, the Citadel page about spam says: “Citadel has the ability to easily and seamlessly integrate with SpamAssassin. Here’s how to make it work for you. First, of course, you must install SpamAssassin. The steps for installing SpamAssassin are beyond the scope of this document. Go to the SpamAssassin web site…”
    In other words, to build what I call a complete VPES you have to do quite some work today, even if you start from Citadel. Not a problem for me, again, but not what I am suggesting.
  3. therefore, the point of my article was to suggest to developers and hosting providers (since I am no developer, have no interest or skills to be an hosting provider AND I already have my VPES anyway) to do the extra step to integrate all these things in ONE bundle to get more users and paying customers.

Ciao,
Marco

C. D. Rigby wrote:
Dynamic DNS

Excellent article, and for me, quite timely. I am in the process of setting up Postfix for myself on my home “server” (an old laptop running ArchLinux). So, the article provides good pointers for my roll-ones-own solution.

More advanced users may wish to run their own VPES at home. Many people have a dynamic IP address from their ISP. This configuration is almost always cheaper than an account with a fixed, routable IP address. So, I would add to your list, conditional on a self-managed, at-home VPES, the need for a Dynamic DNS service. Web searching for “dynamic DNS” will turn up lots of options.

Me: About timeliness and VPESs at home

about the “timely” part: as I wrote at the end, a VPES is to email what diaspora is to social networking with Facebook: an ideal/necessary complement.

I have not gone too much into the “VPES at home” part for several reasons. Speaking only of price, in many countries ISPs don’t allow running any server off cheap residential access contracts (even when they tolerate it but only until they have a bad day): in those cases (which is mine too) a VPES in a data center is actually cheaper than running a VPES at home. Even if it is more expensive than it should for the reasons explained in my piece, that is lack of properly sized hosting packages.

Wanted, Virtual Personal Email Servers

(update 2010/05/29: here’s another article about VPES legal, management and economics issues)

The way email is normally used today has several serious limits that I recently explained in another article. I also pointed out that one of the biggest obstacles to personal email management is lack of user demand for Virtual Personal Email Server (VPES) software and hosting packages. A VPES may run into any computer in your home or in some external datacenter, but that is another issue. Here I only want to look at the software side, that is to explain what are, in my opinion, the technical requirements and features of the perfect VPES. You are welcome to add your own in the comments and, if you’re a hosting provider already offering VPES, to add a link to your offer in the same way.

Web hosting providers only offer… web packages

Today, hosting providers (including those who “sell” virtual machines with full root access) offer packages that are optimized for easy set up and management of websites: bandwidth, virtual RAM, disk space and therefore price of those offers are only dimensioned for people who want to publish online some content. The only choice you have is between “cheap but slow and small website and “very expensive, but very big and fast website, plus some intermediate offer of the same kind. However, email hosting has different characteristics than web hosting: for example, it is not a real time service, so it has lower CPU, bandwidth (and probably disk space) requirements than most websites, but it must not relay spam and must block what the user (not its delegates or any third parties!) considers spam. So, even if turn-key VPES software packages already existed (see next paragraph) one obstacle to their large scale deployment is lack of corresponding hosting offers: sure, you could buy a (Virtual Server dimensioned for) web hosting account and use it for your VPES, but it wouldn’t be an efficient use of your money, just underutilization of something built and sold to do something else. Besides, even 10 Euro a month, that is the starting price for many reliable hosting packages, would be too expensive for many users.

Where is my email control panel?

Let’s now see what a VPES should be and look like. There’s very little or no software to develop to build it. All the free software necessary to run a VPES already exists today and is usable without being a programmer! I already run my own email server, and to do it I never had to write, modify or compile any software program: I only had to read a few tutorials in order to give the right value to some configuration options.

This said, in order to build my own VPES, I had to manually edit configuration files in a character terminal. I’m perfectly fine with that, but it is a fact of life that many people who’d like to have a VPES and pay a few bucks for it, will simply refuse to work in that way. Even if they have no problem whatever to set the same options through a web form.

Therefore, the other big obstacle (=business opportunity) to real personal email management is the lack of Linux-based distributions that are VPESs, that is distributions that contain all and only the software needed for a VPES plus an integrated web-based interface to configure all its parts. Something like this already exists today, as the screenshots in this tutorial on Postfix configuration with Webmin prove: what’s missing is bundling everything together so that you go from installation to a configuration panel like that without intermediate steps, and never needs to use anything else for configuration.

VPES software and feature list

Here is a list, by no means complete. When present, package names in parenthes indicate what I use on my own server, but you’re welcome to suggest alternatives. Just keep in mind, however, that the typical VPES user couldn’t care less of debating whether PostFix is better than Exim, or even to know which MTA server is actually running under the hood:

Software components:

  • MTA server: this is the program that actually receives your email from the Internet and forwards your messages to the Internet (Postfix)
  • Imap server: manages the mailboxes that contain received email, serving their content to the webmail or mail program chosen by the user (Dovecot)
  • Webmail interface, to read and write email from any web browser (Squirrelmail)
  • Backup utility to duplicate configuration info and mailboxes on other computers
  • Backup restore utility
  • Antispam system (SpamAssassin, bogofilter)
  • DNS configuration interface to set up the domain name part of your desired email address(es) and email identification functions like SPF or DKIM
  • Web interface to check if the server looks like a spam source
  • OpenSSL to encrypt web connections, with web interface to generate a certificate
  • database to store user and domain names
  • web based interface to manage user and domain names
  • password strength checking utility
  • GPG to digitally sign and/or encrypt email
  • Calendaring and address book functionality for each user of the VPES
  • Webmin or similar to manage through a web browser all of the above plus updates of software and antispam rules
  • Apache web server to run all the web interfaces mentioned above

Configuration

  • secure default configuration: no open relay, default set of SpamAssassin rules enabled, enforcement of secure passwords, only crypted HTTP connections allowed, mandatory SMTP authorization, software updates possible with signed packages from trusted sources…
  • support for multiple virtual domains, so one VPES can be used both for you @yourfamily.com, your.son @yourfamily.com, you @yourbusiness.com and so on
  • (why not?) wrapping everything in a virtual machine (a-la TurnKey Linux) so the whole thing can be installed in a snap even on Windows or Mac system or in the cloud.

What else? Please tell!

I am sure that the lists above are incomplete and that they can be improved. Your contributions are welcome, just register to add them in the comments or send them directly to me. What matters is to start serious discussion on how to build a really integrated VPES entirely out of Free Software. I also welcome general feedback about making Personal Email Management really popular, especially because it could (should?) become the email part of the Freedom Box.