Virtual Domains with Exim + Courier-IMAP + MySQL

p5r.uk/blog/2002/virtual_domains.html, Version 0.9.1, 28 January 2004

I would like to thank James Harr for his suggestions and the contribution of the Exim 4 config file, David Buxton for the suggestion of using MySQL's ENCRYPT() utility function and the hints for compiling MySQL, and finally Michael Fischer v. Mollard for the Python scripts.

Overview

This document describes how to set up Exim and Courier IMAP on a MySQL database in order to handle virtual domains, which means here accepting mails for several domains which must not be local, but the MX record of the domain points to your server. All information about the domains and the users are stored in a SQL database. This article does not cover how to set up from scratch any of the above mentioned programs. It will be assumed that they are already working, and need to be adapted for our purpose.

I hope this guide is useful to somebody. Suggestions and critics are appreciated, and I will consider them for newer versions of this file.

Note: This article is based on the (outdated) version of Exim 3. There are, however, example files for Exim 4.

Preliminaries

Exim and Courier IMAP must be compiled with support for MySQL (or installed with the MySQL plugins of your distribution).

For Debian users: the Exim package provided by Debian has no MySQL support compiled in. If you want to use this setup on a Debian machine, I suggest to make a .deb package; see Automated Build and setup of exim-tls with virtual users for an example on how to compile your own Exim package.

Preparing MySQL

It will be assumed that there exists a database maildb, accessible to the user mail:

create database maildb;
grant select,insert,update,delete on maildb.* to mail@localhost identified by 'secret';
flush privileges;

with the table users:

use maildb;
CREATE TABLE users (
    id                    char(128) DEFAULT '' NOT NULL,
    crypt                 char(128) DEFAULT '' NOT NULL,
    clear                 char(128) DEFAULT '' NOT NULL,
    name                  char(128) DEFAULT '' NOT NULL,
    uid                   int(10) unsigned DEFAULT '65534' NOT NULL,
    gid                   int(10) unsigned DEFAULT '65534' NOT NULL,
    home                  char(255) DEFAULT '' NOT NULL,
    maildir               char(255) DEFAULT '' NOT NULL,
    quota                 char(255) DEFAULT '' NOT NULL,
    KEY id (id(128))
);

This is the structure of the table recommended by Courier-IMAP. The meaning of the fields are quite obvious, but if there are doubts, in the configuration file of authmysqlrc is a detailed description. In our example the home of the users belongs to the system-user mail and therefore the fields uid and gid must be filled with the right numbers. (Under Debian, the uid:gid of mail are 8:8.)

Setting up a test-Domain

In the examples to follow the mails of a virtual domain are stored in /usr/local/vdomains/. For example the home of the users of domain example.com will be situated in /usr/local/vdomains/example.com/users/user/. For every user there must exist a Maildir folder (which can be created with the maildirmake utility coming along with Courier IMAP, or with the shell commands mkdir -m 0700 Maildir; mkdir -m 0700 Maildir/{cur,new,tmp}). For simplicity all folders of the users belong to the system-user mail, which makes sure that Exim and Courier IMAP can read and write this folders. Beware that this choice is feasible if all users access their mailboxes through the IMAP server.

Finally, the domain must be inserted into a table domains which may be defined as:

CREATE TABLE domains (
    userid char(128) NOT NULL default '',
    KEY userid (userid)
);

INSERT INTO domains (userid) VALUES ("example.com");

Setting up a test-User

A user (e.g. kasperl@example.com) is created by adding his home directory, and inserting it into the database:

INSERT INTO users (id, crypt, clear, name, home, maildir) VALUES (
    "kasperl@example.com", "jnk.l3QByZvdk", "lrepsak", "Kasperl",
    "/usr/local/vdomains/example.com/users/kasperl",
    "/usr/local/vdomains/example.com/users/kasperl/Maildir/");

The crypt field can be generated by the following Perl script:

#!/usr/bin/perl

if( $#ARGV != 0 )
{
    print "usage: vcrypt password\n";
    exit 1;
}
my $salt = join '', ('.', '/', 0..9,'A'..'Z', 'a'..'z')[rand 64, rand 64];
print crypt($ARGV[0], $salt) ."\n";

You could also insert the encrypted version of the password using MySQL's ENCRYPT() function. It is only available if the underlying OS has a crypt utility function (so may not be available on a binary installation of MySQL on Win NT, for example).

Setting up Courier-IMAP

Starting from a running IMAP installation, the authmysql module has to be added to the authmodulelist in the configuration file of authdaemon (/etc/courier/authdaemonrc under Debian). (Note: If you need to compile MySQL 4, you may to have to replace every occurrence of mysql_connect with mysql_real_connect in the authlib 'configure' file.) Edit authmysqlrc; be sure to set up properly MYSQL_SERVER, MYSQL_USERNAME, MYSQL_PASSWORD and MYSQL_DATABASE. With our conventions, the entries are:

    MYSQL_SERVER            localhost
    MYSQL_USERNAME          mail
    MYSQL_PASSWORD          secret
    MYSQL_DATABASE          maildb
    MYSQL_USER_TABLE        users
    MYSQL_CRYPT_PWFIELD     crypt
    MYSQL_LOGIN_FIELD       id
    MYSQL_HOME_FIELD        home
    MYSQL_NAME_FIELD        name

Since this file contains the password for the SQL user mail in clear, it would be a good idea to make it readable only by root.

Testing the IMAP account

You can test your settings by telnetting to the IMAP server:

telnet my.server.com imap
* OK Courier-IMAP ready. Copyright 1998-2002 Double Precision, Inc.
A login kasperl@example.com lrepsak
A OK LOGIN Ok.
B select INBOX
* FLAGS (\Draft \Answered \Flagged \Deleted \Seen \Recent)
* OK [PERMANENTFLAGS (\Draft \Answered \Flagged \Deleted \Seen)] Limited
* 0 EXISTS
* 0 RECENT
* OK [UIDVALIDITY 1021381622] Ok
B OK [READ-WRITE] Ok
C logout
* BYE Courier-IMAP server shutting down
C OK LOGOUT completed

This seems to be OK. Kasperl can authenticate to the IMAP server and has read and write access to his mailbox.

Setting up Exim

General Maildir support for Exim

If you want to use Maildir as the default mailbox style, you have to change the local_delivery transport. (This transport must not have the same name on your machine, it is the one which is called for local deliveries by a director with driver = localuser.) Here is an example (from a default Debian config file, modified)

delivery:
    driver = appendfile
    group = mail
    mode = 0660
    mode_fail_narrower = false
    envelope_to_add = true
    return_path_add = true
    directory = /home/${local_part}/Maildir/
    maildir_format

This configuration makes Exim store the mails for system users in the Maildir directory within their home directory. Add a second transport, address_directory, which will be used to store redirected mails (redirected by a users .forward file, or, as in our case, by a entry in the database):

address_directory:
    driver = appendfile
    no_from_hack
    prefix = ""
    suffix = ""
    maildir_format

Now set up the appropriate directors (in the directors section) by adding a directory_transport = address_directory. (I added it everywhere I found a line like directory_transport = ...). Doing so, every file name (in the system-wide alias file, or in the user's .forward file or in the database) ending with a slash is treated as a Maildir.

Note: as with Exim 4.14 and later the pending slash may not be necessary any more, but it won't hurt to append it anyway.

SQL support for Exim

In order to set up a connection to the database, define the variable "mysql_servers" at the top of exim.conf:

hide mysql_servers = localhost/maildb/mail/secret

The hide statement is needed to protect the MySQL password from being printed when a user calls Exim with the -bP option. Now every domain Exim accepts mails for, must be inserted in the local_domains list. This can be done simply with a SQL statement:

local_domains = localhost:my.server.com:\
    mysql;SELECT userid FROM domains WHERE userid='$key';

In order to handle incoming mail, a new director has to be defined. Let's call it mysql_aliases:

mysql_aliases:
    driver = aliasfile
    file_transport = address_file
    directory_transport = address_directory
    pipe_transport = address_pipe
    search_type = mysql
    query = "SELECT maildir FROM users where id = '$local_part@$domain';"
    user = mail
    group = mail

Testing Exim

A sample test session may look:

telnet localhost smtp
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
220 my.server.com ESMTP Exim 3.35 #1 Wed, 05 Jun 2002 06:48:31 +0200
mail from: xx@yy.zz
250 <xx@yy.zz> is syntactically correct
rcpt to: kasperl@example.com
250 <kasperl@example.com> verified
data
354 Enter message, ending with "." on a line by itself
Subject: Test

1, 2, 3 test
.
250 OK id=17FSnP-0000Cl-00
quit
221 my.server.com closing connection
Connection closed by foreign host.

Should this test not pass, read the output of exim -d2 -bt kasperl@example.com and check the MySQL log. The Exim FAQ contains a section with various debug techniques, which I found very useful.

Some notes about Exim 4

The configuration of Exim 4 is quite similar to the above example. Here is an example for a router for virtual domains:

virtual_user:
    driver = redirect
    allow_fail
    allow_defer
    data = ${lookup mysql{ SELECT maildir FROM users \
            WHERE id='${local_part}@${domain}' }}
    directory_transport = address_directory

If you use /etc/aliases with unqualified user names, let's say root: kasperl@example.com then not only mails to root@local.domain.of.exim will be delivered to kasperl, but every mail which has root as local part. The option check_local_user doesn't look at the domain, so you must add a condition to the system_aliases router: The following line will do the job:

condition = ${if {eq{$domain}{$primary_hostname}} {yes}{no}}

Some considerations

Sample Config Files

Here are the official sites of Exim, Courier IMAP and MySQL.

The page Automated build and setup of exim-tls with virtual users, by Vinai Kopp, describes how to configure Exim with TLS support. This page contains also some hints on how to build your own Debian package of Exim.

Jani Reinikainen wrote a very pretty Exim, Amavis, Qpopper with TLS+MySQL Auth Mini How-To.

The Document Virtual Mail Router Design explains how to set up a virtual Domain with Exim, with LDAP authentication.

Other useful software: