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.
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.
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 (http://misc.netzarbeiter.de/debian/exim-tls-mysql, broken link) for an example on how to compile your own Exim package.
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.)
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");
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).
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.
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.
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.
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
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 be passed, 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.
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}}
Here are the official sites of Exim, Courier IMAP and MySQL.
The page Automated build and setup of exim-tls with virtual users (http://misc.netzarbeiter.de/debian/exim-tls-mysql, broken link) , 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 (http://www.bastard.net/~kos/mailrouter/, broken link) explains how to set up a virtual Domain with Exim, with LDAP authentication.
Other useful software: