Guide to Postfix+Dovecot+MySQL Mail Server

Last week I spent a lot of hours researching and experimenting with Postfix (used to deliver mail, essentially an SMTP server) and Dovecot (POP and IMAP server, for handling the mails) trying to set them up properly with virtual users in a MySQL database. In this guide I'm going to go through the steps I ended up with in an easy-to-follow manner without going into too much detail. The systems I used are Slackware 12.1 and Slackware64 13.0 with Postfix 2.6.5 available at postfix.org, Dovecot 1.2.4 available at dovecot.org and MySQL v>5 available at mysql.com. I have yet to set up SSL and things like that, but once I do I might edit this guide or post a new one for that purpose.

Installing

First of all we need to install all the software we need. Note that I assume you will use root priviliges at the right times, such as when installing or starting/stopping a service. I won't explicitly say when you have to be root.

MySQL

I'm going to assume you have MySQL and it's libraries installed already, if not you'll have to find a separate guide for that (there should be plenty around).

Postfix

Download the tarball of the latest version (currently 2.6.5) from postfix.org and unpack with tar xvzf postfix-2.6.5.tar.gz. Go into the folder with cd postfix-2.6.5. Something that slightly annoys me here is that Postfix doesn't use flags to a configure script to compile with e.g. MySQL, but instead you have use a make command with unintelligible arguments before you do the compiling. To compile Postfix with TLS, MySQL support and Dovecot SASL (I think ;)) use this command:
make -f Makefile.init makefiles 'CCARGS=-DUSE_TLS -DHAS_MYSQL -I/usr/include/mysql -DUSE_SASL_AUTH -DDEF_SERVER_SASL_TYPE=\"dovecot\"' 'AUXLIBS=-L/usr/lib/mysql -lmysqlclient -lz -lm -lssl -lcrypto'
You might have to change the paths to what your system is using, those are what I used on Slackware and they seem to work just fine.

Before we can compile we have to make the user and groups Postfix will use. I made them with the following IDs and names:
groupadd postfix -g 2000
groupadd postdrop -g 2001
useradd postfix -u 2000 -g 2000

Now that we've done all that we can start compiling. Run make and make install. When it gets to the end it'll ask a lot of questions - you can just use the defaults (make sure the user and group is set correctly) unless you have any paths you want to change. After the make install is done Postfix should be installed and you can start it with postfix start to see if it works. Right about now it's probably a good idea to have a terminal with tail -f /var/log/maillog open. In there you will see all errors you get, in case you get any.

Dovecot

Download the tarball of the latest version (currently 1.2.4) from dovecot.org and unpack with tar xvzf dovecot-1.2.4.tar.gz. Go into the folder with cd dovecot-1.2.4. Fortunately Dovecot is kind enough to provide a configure script, which makes it easy to install with MySQL support. Run ./configure --with-mysql --with-sql-drivers. Now all you have to do is run make and make install and you should have Dovecot installed. You may want to create a dovecot user right about now:
groupadd dovecot -g 3000
useradd dovecot -u 3000 -g 3000
Try starting Dovecot with the command dovecot and check your terminal with maillog for any errors.

Configuring

Now that we've got all the software installed all that is left is to configure them! There's a lot more to do here than in the installing part, but it's nothing difficult so just follow along and it shouldn't be a problem.

MySQL

It's in MySQL we are going to store all the e-mail addresses and passwords and stuff we're going to have on our server. To get basic functionality what we need is a table for domains, a table for aliases and a table for mailboxes. We also need a user to connect with and a database to store the tables in. To create them log into mysql as root (mysql -u root -p) and issue the command CREATE DATABASE mail. To create a user called mail, with the password mail, that has full privileges on the database mail, run this command:
GRANT ALL ON mail.* TO 'mail'@'localhost' IDENTIFIED BY 'mail';

Without going into too many details I'll post the specifications of the tables below. You can just paste them into a file and use mysql -umail -pmail mail < mail.sql to have it create the tables for you.

domain

CREATE TABLE `domain` (
`id` int(11) NOT NULL auto_increment,
`domain` varchar(255) NOT NULL,
`transport` varchar(255) NOT NULL default 'virtual',
`active` tinyint(1) NOT NULL default '1',
PRIMARY KEY (`id`),
UNIQUE KEY `domain` (`domain`)
) ENGINE=InnoDB;

alias

CREATE TABLE `alias` (
`id` int(11) NOT NULL auto_increment,
`alias` varchar(255) NOT NULL,
`target` varchar(255) NOT NULL,
`domain` varchar(255) NOT NULL,
`active` tinyint(1) NOT NULL default '1',
PRIMARY KEY (`id`),
KEY `domain` (`domain`),
CONSTRAINT `alias_ibfk_1` FOREIGN KEY (`domain`) REFERENCES `domain` (`domain`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

mailbox

CREATE TABLE `mailbox` (
`id` int(11) NOT NULL auto_increment,
`username` varchar(255) NOT NULL,
`password` char(32) NOT NULL,
`maildir` varchar(255) NOT NULL,
`domain` varchar(255) NOT NULL,
`active` tinyint(1) NOT NULL default '1',
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`),
KEY `domain` (`domain`),
CONSTRAINT `mailbox_ibfk_1` FOREIGN KEY (`domain`) REFERENCES `domain` (`domain`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

Postfix

The default Postfix configuration file has got a ton (I really mean A LOT) of comments that we don't really need. The final configuration file I ended up with is only about 15 rows. I removed pretty much everything and wrote one not far from scratch, you might want to do the same. If you prefer going through those hundreds of lines of comments, be my guest, though. :) The file in question is /etc/postfix/main.cf. Use your favorite editor (such as vim or emacs) and open it up. I'll just post my main.cf and leave any additional configuring you might want (such as different delays or a different greeting etc.) to you. The lines you have to change are the ones with myhostname = server.domain.tld and mydomain = domain.tld. Additionally you might need to add an SMTP relay since almost all ISPs block port 25 (and instead provide an SMTP server for you to use). I've commented out the line, remove the hash sign and add the URL to your ISPs SMTP relay server if you need it. Note that I removed a lot of lines where the setting was the default, see /etc/postfix/main.cf.defaults.

main.cf

myhostname = server.domain.tld
mydomain = domain.tld
myorigin = $mydomain
mydestination =
alias_maps = hash:/etc/postfix/aliases
alias_database = hash:/etc/postfix/aliases
#relayhost = smtp.isp.com
smtpd_recipient_restrictions = permit_mynetworks, reject_non_fqdn_hostname, reject_non_fqdn_sender, reject_non_fqdn_recipient, reject_unauth_destination, reject_unauth_pipelining, reject_invalid_hostname
disable_vrfy_command = yes
virtual_mailbox_base = /var/mail/virtual
virtual_minimum_uid = 2000
virtual_uid_maps = static:2000
virtual_gid_maps = static:2000
virtual_alias_maps = mysql:/etc/postfix/mysql_alias.cf
virtual_mailbox_maps = mysql:/etc/postfix/mysql_mailbox.cf
virtual_mailbox_domains = mysql:/etc/postfix/mysql_domains.cf

Next we need to create the MySQL configuration files for Postfix (the three listed above). They tell Postfix where to look for aliases and users and their directories.

mysql_domains.cf

user = mail
password = mail
dbname = mail
hosts = localhost
table = domain
select_field = domain
where_field = domain
additional_conditions = and active = 1

mysql_alias.cf

user = mail
password = mail
dbname = mail
hosts = localhost
table = alias
select_field = target
where_field = alias

mysql_mailbox.cf

user = mail
password = mail
dbname = mail
hosts = localhost
table = mailbox
select_field = maildir
where_field = username
additional_conditions = and active = 1

That's all there is to configuring Postfix. :)
Try restarting to see if the new configuration works.

Dovecot

The Dovecot configuration file is similar to Postfix in the way that it's got a whole ton of comments. I got rid of them and I'll simply post my configuration file here. It should work without any changes. Note that mine was located in /usr/local/etc/dovecot.conf, not /etc/dovecot.

dovecot.conf

base_dir = /var/run/dovecot/
listen = *
syslog_facility = mail
ssl = no
disable_plaintext_auth = no
login_dir = /var/run/dovecot/login
login_chroot = yes
login_user = dovecot
first_valid_uid = 2000
first_valid_gid = 2000
protocols = imaps imap pop3s pop3
mail_location = maildir:/var/mail/virtual/%d/%n
auth default {
mechanisms = plain
user = root
userdb sql {
args = /etc/dovecot/dovecot-mysql.conf
}
passdb sql {
args = /etc/dovecot/dovecot-mysql.conf
}
socket listen {
master {
path = /var/run/dovecot/auth-master
mode = 0600
user = dovecot
}
client {
path = /var/run/dovecot/auth-client
mode = 0660
user = dovecot
group = dovecot
}
}
}

The longest configuration file so far. I haven't optimised it by utilising default values or anything, could be why. Next we have to set Dovecot up to know how to use the MySQL tables. It's done with /etc/dovecot/dovecot-mysql.conf as such:

dovecot-mysql.conf

driver = mysql
connect = host=localhost dbname=mail user=mail password=mail
default_pass_scheme = PLAIN-MD5
password_query = SELECT password FROM mailbox WHERE username = '%u'
user_query = SELECT maildir, 2000 AS uid, 2000 AS gid FROM mailbox WHERE username = '%u' AND active = 1

That should be all the configuring you have to do, so try restarting Dovecot with pkill -HUP dovecot and see if it'll start and connect to MySQL properly!

Setting up users

If everything is working all that's left is to add some accounts to your database and test it!

To insert a domain into the database connect with mysql -u mail -p mail and enter:
INSERT INTO domain (domain) VALUES ('domain.tld');

To add a mailbox (an actual inbox on the computer) issue the following query in MySQL:
INSERT INTO mailbox (username, password, maildir, domain) VALUES ('user@domain.tld', md5('password'), 'domain.tld/user/', 'domain.tld');

Additionally I always add an alias for users, even if it points to the same address. In the alias table you can create aliases that points to an external mail, for example user2@domain.tld can be set to point to mymail@gmail.com. You can set up an alias to send to several mail addresses by adding several aliases that are the same but points to different targets. To create a catch-all (i.e. where any mails sent to an address of your domain that doesn't have an alias already ends up) enter the alias as @domain.tld. Here are some examples:
INSERT INTO alias (alias, target, domain) VALUES ('user@domain.tld', 'user@domain.tld', 'domain.tld');
INSERT INTO alias (alias, target, domain) VALUES ('user2@domain.tld', 'mymail@gmail.com', 'domain.tld');
INSERT INTO alias (alias, target, domain) VALUES ('user2@domain.tld', 'user2@domain.tld', 'domain.tld');
INSERT INTO alias (alias, target, domain) VALUES ('@domain.tld', 'catch-all@domain.tld', 'domain.tld');

To make the administering of this easier I put together my own administration interface in PHP in which you can easily add, edit and delete domains, aliases and mailboxes using the database I've posted here. It doesn't have any error-checking or security, but it does it's job trusting the user not to screw things up. It's probably a pretty good idea to password-protect it though, if you intend to have it on a server public to the Internet. .htaccess and .htpasswd will do the job just fine, I guess, so Google that.
Here is a download link: nmailadm-0.1.tar.gz

Testing

Now that you've got your mail server set up, it's about time you try sending and reading some mails using Telnet. If it works, you can try using a mail client (such as using POP to fetch mails with Gmail or Outlook to both send and recieve mails).

The easiest way to test is to use telnet. Here's how to send a mail using SMTP:
telnet localhost 25
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
220 server.domain.tld ESMTP Postfix
mail from: user@domain.tld
250 2.1.0 Ok
rcpt to: user2@domain.tld
250 2.1.5 Ok
data
354 End data with .
This is a test mail. :)
.
250 2.0.0 Ok: queued as **********
quit
221 2.0.0 Bye
Connection closed by foreign host.

Now, check your maillog and you should see a couple of lines following you through the process you just did. Here is an example:
Sep 13 17:20:27 server postfix/smtpd[11333]: connect from localhost[127.0.0.1]
Sep 13 17:21:33 server postfix/smtpd[11333]: **********: client=localhost[127.0.0.1]
Sep 13 17:22:31 server postfix/cleanup[12236]: **********: message-id=<20090913152133.**********@server.domain.tld>
Sep 13 17:22:31 server postfix/qmgr[30203]: **********: from=, size=355, nrcpt=2 (queue active)
Sep 13 17:22:31 server postfix/virtual[13036]: **********: to=, relay=virtual, delay=81, delays=81/0.01/0/0.02, dsn=2.0.0, status=sent (delivered to maildir)
Sep 13 17:23:22 server postfix/smtpd[11333]: disconnect from localhost[127.0.0.1]

Now that we've sent a message, let's try connecting with POP to list the messages we have!
telnet localhost 110
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
+OK Dovecot ready.
user user@domain.tld
+OK
pass password
+OK Logged in.
list
+OK 1 messages.
1 450
.
quit
+OK Logging out.
Connection closed by foreign host.

Yep, that's about it. If you followed this and everything worked you should have a fully functional mail server with virtual users running! Next is to connect using a mail client such as Outlook so you won't have to send mails using telnet.

If you have any questions or problems, feel free to send me an e-mail or post a comment and I will do my best to help. :)