Thursday, October 13, 2016

Setting Up A Mail Server - Part 1: MySQL, Postfix and Dovecot - Incomplete

I've spent a few days going through the ISPMail server (debian Jessie with dovecot and postfix) tutorial and I've found the experience somewhat frustrating.

The use of the database, for example, seems to introduce redundancy when the whole point of a relational database is to remove that risk. Nothing in the tutorial seems to scale all that well i.e. how do you separate out functions but allow them to keep communicating between different machines?

More frustrating for me was the fact that it attempts to tackled everything NOW NOW NOW rather than addressing things in a logical order (I had to pull out my whiteboard to make sense of it).

Within code snippets, any text in red should/could be changed. Blue indicates that the same data is going to be used for a whole chunk.

I'm making an assumption about the structure of the sort of network this is sitting on. The MySQL, File Server, Postfix, Dovecot and web server machines can all be set up on different machines BUT exist on a trusted network i.e. they sit behind a firewall making communication between the machines relatively trusted. Most of this should still work if this isn't the case BUT avoid anything to do with lmtp - it's not made for an untrusted environment.

Warning: This tutorial is incomplete and untested. I will be testing it within the next couple of days and figuring out the missing bits. It needs a test at the end to make sure that it is all working as expected. The SMTP server doesn't appear to have been configured yet (the ISPMail tutorial does this after setting up roundcube) and security on that to make sure the mail server isn't used for relaying spam.

So here goes...

The Structure of an E-mail Server

Very basically you need to be able to send emails, get your emails, which are handled by a 'mta server' (Mail Transport Agent) and retrieve our emails using either IMAP or POP3.

Because editing text files sucks a great big one to handle email addresses, a database is a damn good idea. And of course, we're going to want to do a whole lot of things around reducing the spam coming in and stop our server from being used to send lots of spam. We also want to do this securely so we're going to need some domain verification certificates. Oh and we have to set up DNS to tell the Internet where mail should go to. Eventually we're probably going to want to set up a web front end for the mail so that you can check it anywhere you like and/or configure an email client (ick).

Yep. It's messy.

If you need more information, go and have a look at this page.

We're going to use Postfix as our MTA, Dovecot for our IMAP/POP3 server (though we're going to disable POP3. I'll explain later). For our database we're going to use MySQL (although the instructions likely don't change for MariaDB).

Setting up the Infrastructure


I'm going to trust that you have purchased a domain name. In your DNS records you'll have something like:

Which is your A record for the base domain. Add another A record for something like or I'm going to stick to mx (Mail eXchange). Then add an MX record. It should look something like this:


This is basically saying "use to serve mail for". If you don't know who to use for your DNS, I've found Cloudflare to be pretty good.


This is something I was kind of annoyed about with most tutorials. They'd talk about using self signed certificates and there was little information on using the free certificates issued by Let's Encrypt. This is what I'm using.

Let's Encrypt state in their FAQ that the certificates they issue are for domain verification only and aren't suitable for email encryption.

My concern here is that there's an ambiguity. I care more about the encryption when transmitting the email than I do about encrypting the email itself. I hope to be able to trust the user to be able to worry about the encryption of the email if it is needed via PGP (Pretty Good Privacy) or some other means. In which case, I have no idea why I should be worried about my Certificate Authority being able to encrypt emails.

To get started, go to this site, and get instructions on how to download certbot. I'm not going to go into using this tool. You'll need to set up a web server (I recommend nginx myself. I was using Apache but found it horrendously slow) to get the certificates.

Sharing Files - add ssh-keygen and move to it's own tutorial

In my case, I want to keep my mail server well away from other functions such as my database and web server. Which means there's the potential of me needing to securely share some files, such as certificates, between machines (Let's Encrypt uses my webserver to verify my domain but I'm not sure if it needs it to renew. It's just easier for me be able to share the files).

While I have a firewall and my network seems to be reasonably secure, it's worthwhile having some security on the inside of your own network. In which case, I'm going to use SSH to share files and lock things down as much as humanly possible.

I'm going to assume you have ssh servers on all of your machines.

On the machine containing the files:

 apt-get install openssh-server  
Edit /etc/ssh/sshd_config:
Change the line starting:
 Subsystem sftp  
 Subsystem sftp internal-sftp
If you need to debug sshfs later on, change this to:
 Subsystem sftp internal-sftp -l DEBUG1  
At the end of this file (It must be at the end), add something along the following:
 Match User cert  
   ChrootDirectory /etc/letsencrypt  
   ForceCommand internal-sftp  
   AllowTCPForwarding no  
   X11Forwarding no  
   PasswordAuthentication no  

WARNING: When using ChrootDirectory, that folder MUST be owned by root and not be group writable.
What this does is allows a user named "cert" to access our certificates only. The cert user can not log in or do anything else except access the /etc/letsencrypt folder.

Add the cert user (at BASH):
 useradd cert -p '!' -s /bin/false  

Give the cert user permissions to the files it actually needs:
 chgrp cert /etc/letsencrypt/live /etc/letsencrypt/archive  
 chmod 750 /etc/letsencrypt/live /etc/letsencrypt/archive  

On the machine that needs the files:

We're going to use a combination of sshfs and autofs just for robustness (read: we don't really want to have to worry too much about the order in which machines have to be booted in).

Install the needed software:
 apt-get install sshfs autofs  
Make a folder for autofs to control:
 mkdir /mnt/sshfs  
Edit /etc/auto.master. And the following line:
 /mnt/sshfs /etc/auto.sshfs uid=1000,gid=1000,--timeout=30,--ghost  
If only one user will be using the files from here, it's worthwhile setting the uid and gid to that user. I can get that information with:
 id dovecot  
Save and exit. Make the file /etc/auto.sshfs and put in the following:
 certs -fstype=fuse,ro,nodev,nonempty,noatime,max_read=65536 :sshfs\#cert@webserver1\:/  
I could change the 'ro' to 'rw' for readwrite access. I could also add "allow_other" which would give everyone on the system access to that mount.

The Database - MySQL

We want to use a database to store information about 3 things:
  • Domain(s) - we're setting things up to allow for scalability which means we should be able to easily add domains should we need to.
  • Mailboxes.
  • Aliases - virtual addresses that lead to mailboxes.
On the machine you're setting MySQL up on (this can be the same machine as anything else. It will create little branches throughout this tutorial), install the needed software:

 apt-get install mysql-server mysql-client 

We're going to rely on the command line to configure our database. The reasoning for this is that phpMyAdmin doesn't really abstract things away to become any more or less user friendly though does install a piece of web accessible software on your server that seems unnecessary.

If you don't know your mysql root password, you may need to reset it:

 sudo service mysql stop  
 sudo mysqld --skip-grant-tables &  
 mysql -u root mysql  
 sudo service mysql restart  

Log into mysql:
 mysql -u root -p  

Create and configure the user that Postfix and Dovecot will use to access the database:
 CREATE USER 'mailuser'@'' IDENTIFIED BY 'DBPassword';  
 GRANT SELECT,INSERT,UPDATE,DELETE ON mailserver.* TO 'mailuser'@'';  

Both Postfix and Dovecot need access to the database. If you're running MySQL on a different machine from postfix and dovecot, you need to change '' to the IP address of the machine running Postfix or Dovecot. If Postfix and Dovecot are running on different machines from each other, you need to create separate accounts for each of those users. They can be the same user name. It's just the host portion that needs to be different.

To test that you're able to log in from the computer you're going to need to access the database from, install mysql-client and attempt to login via:

 mysql --host -u mailuser -p  

Back to the database.... Make your database:
 CREATE DATABASE mailserver;  
 USE mailserver;

Our first table is going to contain information about the domain names we're providing email for. A domain name can be a maximum size of around 255 characters long.
 CREATE TABLE domains (  
   id    INT(11)       NOT NULL AUTO_INCREMENT,  
   name  VARCHAR(255)  NOT NULL,  
   PRIMARY KEY ( id )  

 INSERT INTO domains( id, name ) VALUES( 1, '' ); # test data

The next table is all about our mailboxes:
 CREATE TABLE mailboxes(  
   id         INT             NOT NULL AUTO_INCREMENT,  
   domain_id  INT             NOT NULL,  
   name       VARCHAR(65)     NOT NULL,  
   password   VARCHAR(128)    NOT NULL,  
   PRIMARY KEY( id ),  
   UNIQUE( name, domain_id ),  
   FOREIGN KEY(domain_id) REFERENCES domains(id)  

 INSERT INTO mailboxes( domain_id, name, password )
 VALUES( 1, 'test', 'b109f3bbbc244eb82441917ed06d618b9008dd09b3befd1b5e07394c706a8bb980b1d7785e5976ec049b46df5f1326af5a2ea6d103fd07c95385ffab0cacbc86' );

For password we're going to use SHA512 which should be available on most Linux systems. SHA512 hashes passwords into 128 hexidecimal characters i.e. the length doesn't need to be variable.

We don't need to store the email address because that's a concatenation between the mailbox name and domain name and given that the domain name is already stored in the domains table, and we've got a link to the domain using a foreign key, it can be considered a calculated field.

And finally, aliases...
 CREATE TABLE aliases(  
   id          INT           NOT NULL AUTO_INCREMENT,  
   domain_id   INT           NOT NULL,  
   source      VARCHAR(65)   NOT NULL,  
   destination VARCHAR(320)  NOT NULL,  
   PRIMARY KEY( id ),  
   FOREIGN KEY( domain_id ) REFERENCES domains( id ),  
   UNIQUE( domain_id, source, destination )

 INSERT INTO aliases( domain_id, source, destination )
 VALUES( 1, 'alias', '' );

You'll notice that source and destination have completely different lengths. The source can be calculated by the source and domain name whereas the destination can potentially be sent to an entirely different domain.


 apt-get install postfix postfix-mysql  

We're going to put our configuration in its own folder just because it's a touch cleaner. Then we need to create 3 files which tell Postfix how to access our database.

 mkdir /etc/postfix/config  

Make a file called /etc/postfix/config/ and populate it with the following:
 user = mailuser  
 password = DBPassword  
 hosts =  
 dbname = mailserver  
 query = SELECT 1 FROM domains WHERE name='%s'  

You can test the query in mysql (substituting %s for a domain name). If the domain exists, it returns 1 (true). Otherwise it returns an empty set (false).

Enable the configuration in Postfix:
 postconf virtual_mailbox_domains=mysql:/etc/postfix/config/  

And finally, test this configuration setting:
 postmap -q mysql:/etc/postfix/config/  

Make a file called /etc/postfix/config/ and populate it with the following:
 user = mailuser  
 password = DBPassword  
 hosts =  
 dbname = mailserver  
 query = SELECT 1 FROM mailboxes JOIN domains ON WHERE CONCAT_WS('@',, )='%s'  

What's happening here is that we're joining the domains table so that we can get the domain name to form the email address. The reason for doing this is that it leads to less errors. i.e. if I'm delivering emails for '' but accidentally put in '', using this configuration theres only one place I could have made that mistake - in the domains table and fixing it for one fixes it for all. Whereas if I store the entire email address in a single field in the mailboxes table, I can make that mistake in a bunch of different places.

Enable the configuration in Postfix:
 postconf virtual_mailbox_maps=mysql:/etc/postfix/config/  

And test it...
 postmap -q mysql:/etc/postfix/config/  

And finally, create a file called /etc/postfix/config/ and populate it with the following:
 user = mailuser  
 password = DBPassword  
 hosts =  
 dbname = mailserver  
 query = SELECT aliases.destination FROM aliases JOIN domains ON aliases.domain_id = WHERE CONCAT_WS('@',aliases.source,'%s'

This query string is much like the last.

Enable the configuration:
 postconf virtual_alias_maps=mysql:/etc/postfix/config/  

And test it...
 postmap -q mysql:/etc/postfix/config/   

Set your permissions on the files:
 chgrp postfix /etc/postfix/config/*  
 chmod u=rw,g=r,o= /etc/postfix/config/*  

Enabling outgoing mail
1. Get postfix to use dovecot for authentication
At the command line run:
 postconf smtpd_sasl_type=dovecot  
 postconf smtpd_sasl_path=private/auth  
 postconf smtpd_sasl_auth_enable=yes  

2. Enable encryption
At the command line run:
 postconf smtpd_tls_security_level=may  
 postconf smtpd_tls_auth_only=yes  
 postconf smtpd_tls_cert_file=/etc/ssl/certs/mailserver.pem  
 postconf smtpd_tls_key_file=/etc/ssl/private/mailserver.pem  

If Postfix and Dovecot are going to run on the same machine:
 postconf virtual_transport=lmtp:unix:private/dovecot-lmtp  

If Postfix and Dovecot are going to run on different machines:
 postconf virtual_transport =  
Use the address of the dovecot machine here. The port specified here is one that's reserved for private mail use.


 apt-get install dovecot-mysql dovecot-imapd dovecot-managesieved dovecot-lmtpd  

Dovecot handles how we get our emails and is also going to be responsible for storing emails. The line above is missing POP3 support. If you need it, then do this:
 apt-get install dovecot-pop3d   

POP3 just isn't great with Spam Assassin. POP3 can't grab folders. Instead it just grabs emails from the inbox. Very simple. The problem with this is that you then can't put emails that have been marked as spam  into their own folder. In a POP3 system, the user never gets these emails. In an imap world, email folders are stored on the server.

First things first: Set up a user (and group) for the dovecot service to run under:
 groupadd -g 5000 vmail  
 useradd -g vmail -u 5000 vmail -d /var/vmail -m  

Create a place to store emails in /var/vmail. For me, I'm going to be using the sshfs stuff because my file storage is on a different machine from where my mail server is being run. I'm not going to step you through how to do this as there's (hopefully) enough detail in the sshfs section to do this along with using 'mount -o bind' to get the folder accessible from the right place.

Set your permissions:
 chown -R vmail.vmail /var/vmail  

Dovecot, by default on Debian, stores its configuration files in:
The files are processed in order so files starting with 99 are processed after files starting with 01. Most of the files are commented out and mostly contain examples so most of what we're doing here is appending to the relevant files.


Make sure that this line is uncommented.
 auth_mechanisms = plain  

If you're using Outlook Express on Windows XP or Windows Vista (though there's really no good reason you should be), that line needs to be:
 auth_mechanisms = plain login  

Plain may look dangerous though by default dovecot does not accept passwords sent via plain text i.e. TLS encrypted passwords only.

Uncomment (remove the '#') the following line.
 !include auth-sql.conf.ext  

Comment out the all of the '!include auth-' lines. They're well out of scope for this hastily thrown together tutorial.


Comment out the userdb section entirely and append the following to the bottom:
 userdb {  
  driver = static  
  args = uid=vmail gid=vmail home=/var/vmail/%d/%n  

This tells the dovecot daemon to run as the user 'vmail', group 'vmail' and where to place our emails (/var/vmail/[domain_name]/[mailbox_name]).


Change the line that reads:
 mail_location = mbox:~/mail:INBOX=/var/mail/%u  

 mail_location = maildir:/var/vmail/%d/%n/Maildir  


This file deals with what services are available. We need to concern ourselves with 2 things here:
  • Allowing postfix to communicate with dovecot for authentication.
  • Allowing postfix to send emails to dovecot (lmtp)

If postfix and dovecot are running on the same machine: then look for the line that starts with:
 #unix_listener /var/spool/postfix/private/auth {  

Uncomment that line and change the whole stanza to look like the following:
 unix_listener /var/spool/postfix/private/auth {  
  mode = 0660  
  user = postfix  
  group = postfix  

Change the stanza that looks like:
 unix_listener lmtp {  
  #mode = 0666  
 service lmtp {  
  unix_listener /var/spool/postfix/private/dovecot-lmtp {  
   group = postfix  
   mode = 0600  
   user = postfix  

If postfix and dovecot are running on different machines: 


Look for the stanza that starts with:
 service lmtp {  

and make it look like the following:
 inet_listener lmtp {  
  address = ::1  
  port = 24  
The address is the IP address of the NIC to listen on. You may want to use a firewall on this machine to limit access further i.e. look at the address of traffic coming in on port 24 and limit access to that port to very specific (the machine running postfix) machines.

Look for the block that starts with:
 service auth {  

Within that block, add the following stanza:
 inet_listener {  
   port = 12345  

Choose a random port between 1024 and 65535.


On the command line enter:
 smtpd_sasl_path =  
 smtpd_sasl_type = dovecot  

Where it reads "", change it to the address (ip or host name) of the machine running dovecot. Use the port number chosen above where it reads 12345.


If you've got a certificate for your mail server domain name ( from let's encrypt, the certificates are probably somewhere along the times of /etc/letsencrypt/live/ The 2 files we care about are cert.pem and privkey.pem.

Look for the line that starts with:
 ssl =   
And make sure it says:
 ssl = yes  

Look for the lines that say:
 #ssl_cert = </etc/dovecot/dovecot.pem  
 #ssl_key = </etc/dovecot/private/dovecot.pem  

Uncomment them and change them to read:
 ssl_cert = </etc/letsencrypt/live/  
 ssl_key = </etc/letsencrypt/live/  


We need to configure a couple of folders to exist by default. Look for the section that says:
 mailbox Drafts {  
  special_use = \Drafts  
 mailbox Junk {  
  special_use = \Junk  
 mailbox Trash {  
  special_use = \Trash  

For each of those stanzas, add auto = subscribe. It should now look like this:
 mailbox Drafts {  
  auto = subscribe
  special_use = \Drafts  
 mailbox Junk {  
  auto = subscribe
  special_use = \Junk  
 mailbox Trash {  
  auto = subscribe
  special_use = \Trash  

This makes it so that your users can't remove these folders. They're all special use folders and users generally expect them to exist anyway.


And finally, we need to tell dovecot how to talk to our database. To the bottom of dovecot-sql.conf.ext, add the following:
 driver = mysql  
 connect = host= dbname=mailserver user=mailuser password=DBPassword
 default_pass_scheme = SHA512-CRYPT  
 password_query = SELECT AS username, AS domain, CONCAT_WS('@',, , mailboxes.password FROM mailboxes JOIN domains ON mailboxes.domain_id = WHERE'%n' AND'%d'  

Change the host to the ip address of the server running the database server and the password to the password of your database for the mailuser user.

Make sure the permissions for dovecot-sql.conf.ext don't allow for users to get your mysql authentication details:

 chown root:root /etc/dovecot/dovecot-sql.conf.ext  
 chmod go= /etc/dovecot/dovecot-sql.conf.ext  


We want to enable the sieve plugin. This allows us to apply rules to email on the server (filtering and the like). Change the line that reads:
 #mail_plugins = $mail_plugins  
 mail_plugins = $mail_plugins sieve  

Running the new configuration:

On the command line run:
 service dovecot restart