How-To: Virtual emails accounts with Postfix and Dovecot

This tutorial will explain how to run a mail server with virtual domains and users using a MySQL backend to store email informations.
Postfix will be our SMTP server, Dovecot will be handling IMAP (optionally one could configure POP3 also) so that users can retrieve their email.

Another tutorial will cover SPAM fighting using DSPAM based on this implementation (yet to be done).

This tutorial was done on Debian Etch using postfix 2.3.8-2etch4 and 1.0.rc15-2etch4.

By using Virtual domains and users, we are able to set up unlimited email accounts without creating a system user. This does not make sense if you have only one user on your mail server, but can become pretty handy if you start hosting quite a fair amount of email accounts/domains.

In order to set this implementation up, we are first going to proceed as follow:

Set the emails accounts repository (where emails will be stored)
Set the users/domains databases (where user informations will be kept)
Set our SMTP server (postfix)
Set our IMAP server (dovecot)

1. Installing the required packages

Run this command to install all the required packages for this tutorial:

# apt-get install postfix postfix-mysql openssl ssl-cert dovecot-imapd mysql-server

Optionally, you might want to install dovecot-pop3d if you wish to activate POP3 service.

2. Creating the Virtual Email user

All our emails will be handled by a unique system user on the system: vmail, postfix and dovecot will take care of handling authentication.

To create the vmail user proceed as follow:

groupadd -g 5000 vmail
useradd -g vmail -u 5000 vmail -d /var/vmail
mkdir /var/vmail
chown vmail:vmail /var/vmail

3. Creating the database

Now, let's create the database that will hold our user emails and passwords. Log into MySQL as root and type:

CREATE DATABASE virtual_email;
GRANT USAGE ON *.* TO vemailuser@'localhost' IDENTIFIED BY 'vemailpass';
GRANT ALL PRIVILEGES ON virtual_email.* TO vemailuser@'localhost';

Then use the following script, name it create_emaildb.sql:

DROP TABLE IF EXISTS `virtual_domains`;
CREATE TABLE `virtual_domains` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `UNIQUE_NAME` (`name`)
) ENGINE=InnoDB ;

DROP TABLE IF EXISTS `virtual_aliases`;
CREATE TABLE `virtual_aliases` (
  `id` int(11) NOT NULL auto_increment,
  `domain_id` int(11) NOT NULL,
  `source` varchar(40) NOT NULL,
  `destination` varchar(80) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `domain_id` (`domain_id`),
  CONSTRAINT `virtual_aliases_ibfk_1` FOREIGN KEY (`domain_id`) REFERENCES `virtual_domains` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

DROP TABLE IF EXISTS `virtual_users`;
CREATE TABLE `virtual_users` (
  `id` int(11) NOT NULL auto_increment,
  `domain_id` int(11) NOT NULL,
  `user` varchar(40) NOT NULL,
  `password` varchar(32) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `UNIQUE_EMAIL` (`domain_id`,`user`),
  CONSTRAINT `virtual_users_ibfk_1` FOREIGN KEY (`domain_id`) REFERENCES `virtual_domains` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

and inject it into the database:

mysql -u root -p virtual_email < create_emaildb.sql

Finally, create some sample users:

# mysql -u vemailuser -p virtual_email
INSERT INTO virtual_domains (id, name) VALUES (1, 'example.com');
INSERT INTO virtual_users (id, domain_id, user, password) VALUES (1, 1, 'chantra', MD5('chantra'));
INSERT INTO virtual_users (id, domain_id, user, password) VALUES (2, 1, 'chantra2', MD5('chantra'));
INSERT INTO virtual_aliases (id, domain_id, source, destination) VALUES (1, 1, 'foo', 'another@email.foo'), (2, 1, '', 'chantra2@example.com');

Well, this is is for the database, you now have some sample users to play with.

Let's now configure Postfix.

How-To: Virtual emails accounts with Postfix and Dovecot -- page 2

4. Configuring Postfix

4.1. main.cf and master.cf

Your postfix main.cf file should look like:

smtpd_banner = $myhostname ESMTP $mail_name (Debian/GNU)
biff = no

# appending .domain is the MUA's job.
append_dot_mydomain = no

# Uncomment the next line to generate "delayed mail" warnings
#delay_warning_time = 4h

# TLS parameters
smtpd_tls_cert_file=/etc/ssl/certs/ssl-cert-snakeoil.pem
smtpd_tls_key_file=/etc/ssl/private/ssl-cert-snakeoil.key
smtpd_use_tls=yes
smtpd_tls_session_cache_database = btree:${queue_directory}/smtpd_scache
smtp_tls_session_cache_database = btree:${queue_directory}/smtp_scache

#vhost with mysql params
#virtual_alias_domains needs to be unset
virtual_alias_domains =

virtual_mailbox_domains = mysql:/etc/postfix/virtual/mysql-virtual-mailbox-domains.cf
virtual_mailbox_maps = mysql:/etc/postfix/virtual/mysql-virtual-mailbox-maps.cf
virtual_alias_maps = mysql:/etc/postfix/virtual/mysql-virtual-alias-maps.cf, mysql:/etc/postfix/virtual/mysql-virtual-email2email.cf
virtual_uid_maps = static:5000
virtual_gid_maps = static:5000
virtual_transport = dovecot
dovecot_destination_recipient_limit = 1


myhostname = <your hostname here>
alias_maps = hash:/etc/aliases
alias_database = hash:/etc/aliases
myorigin = /etc/mailname
mydestination = <your hostname here>, localhost
relayhost =
mynetworks = 127.0.0.0/8
mailbox_command = procmail -a "$EXTENSION"
mailbox_size_limit = 0
recipient_delimiter = +
inet_interfaces = all
smtpd_sasl_type = dovecot
smtpd_sasl_path = private/auth
smtpd_sasl_auth_enable = yes
smtpd_recipient_restrictions = permit_mynetworks,permit_sasl_authenticated,reject_unauth_destination

And at the end of /etc/postfix/master.cf add:

dovecot   unix  -       n       n       -       -       pipe
  flags=DRhu user=vmail:vmail argv=/usr/lib/dovecot/deliver -d ${recipient}

Do not restart/reload yet, the access to the users/domains informations in the database still need to be set, also, dovecot needs to be configured.

4.2. Access to the database

As per the main.cf configuration, the database configuration files will be kept in /etc/postfix/virtual so you need to create this directory:

# mkdir /etc/postfix/virtual

Then we will create the virtual mailbox maps database information:

# vi /etc/postfix/virtual/mysql-virtual-mailbox-maps.cf

and add the following:

user = vemailuser
password = vemailpass
hosts = 127.0.0.1
dbname = virtual_email
query = SELECT 1 FROM virtual_users AS U LEFT JOIN virtual_domains AS D ON U.domain_id=D.id WHERE CONCAT(U.user, '@',D.name)='%s'

Now, the virtual_mailbox_domains:

# vi /etc/postfix/virtual/mysql-virtual-mailbox-domains.cf

user = vemailuser
password = vemailpass
hosts = 127.0.0.1
dbname = virtual_email
query = SELECT 1 FROM virtual_domains WHERE name='%s'

and finally, for the aliases (virtual_alias_maps), we need 2 files:

# vi /etc/postfix/virtual/mysql-virtual-alias-maps.cf

user = vemailuser
password = vemailpass
hosts = 127.0.0.1
dbname = virtual_email
query = SELECT destination FROM virtual_aliases AS A LEFT JOIN virtual_domains AS D ON A.domain_id=D.id WHERE CONCAT(A.source, '@', D.name)='%s'

and

# vi /etc/postfix/virtual/mysql-virtual-email2email.cf

user = vemailuser
password = vemailpass
hosts = 127.0.0.1
dbname = virtual_email
query = SELECT CONCAT(U.user, '@',D.name) FROM virtual_users AS U LEFT JOIN virtual_domains AS D ON U.domain_id=D.id WHERE CONCAT(U.user, '@',D.name)='%s'

Now, we have to set the rights properly for those files:

# chown root:postfix -R /etc/postfix/virtual
# chmod 640 -R /etc/postfix/virtual

Now, we are done with the postfix side, let's carry on with dovecot.

How-To: Virtual emails accounts with Postfix and Dovecot -- page 3

5. Configuring Dovecot

Dovecot has its SQL configuration gathered in a separated file: /etc/dovecot/dovecot-sql.conf, so let's edit edit with our settings:

# vi /etc/dovecot/dovecot-sql.conf

driver = mysql
connect = host=127.0.0.1 dbname=virtual_email user=vemailuser password=vemailpass
default_pass_scheme = PLAIN-MD5
password_query = SELECT password FROM virtual_users AS V LEFT JOIN virtual_domains AS D ON V.domain_id=D.id WHERE V.user='%n' AND D.name='%d'

this will take care of getting the password from the database for a given user@domain.

Now, comes the biggest part of the config in /etc/dovecot/dovecot.conf. In this case, we are going to only enable IMAPS and the emails will be stored in /var/vmail/domain/user/Maildir.

You need to edit your conf file so it looks like:

# vi /etc/dovecot/dovecot.conf

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

## uncomment this if you already have email from
## courier for instance.

#namespace private {
#  separator = .
#  prefix = INBOX.
#  inbox = yes
#}
## change section "protocol lda" to:
protocol lda {
  # Address to use when sending rejection mails.
  postmaster_address = postmaster@example.com
  log_path = /var/vmail/dovecot-deliver.log
  # Hostname to use in various parts of sent mails, eg. in Message-Id.
  # Default is the system's real hostname.
  #hostname = 

  # Support for dynamically loadable plugins. mail_plugins is a space separated
  # list of plugins to load.
  #mail_plugins = 
  #mail_plugin_dir = /usr/lib/dovecot/modules/lda

  # Binary to use for sending mails.
  #sendmail_path = /usr/lib/sendmail

  # UNIX socket path to master authentication server to find users.
  auth_socket_path = /var/run/dovecot/auth-master

  # Enabling Sieve plugin for server-side mail filtering
  # handy for storing spam in their folders
  mail_plugins = cmusieve
  global_script_path = /var/vmail/globalsieverc
}


## in section auth default
## change :
mechanisms = plain login
## comment out "passdb pam"
## and make sure the following is in
## to look for users in the DB
  passdb sql {
    # Path for SQL configuration file, see /etc/dovecot/dovecot-sql.conf for example
    args = /etc/dovecot/dovecot-sql.conf
  }

## and add this so dovecot does not deal with uid/gid
## we use uid and gid 5000 for everybody
  userdb static {
    args = uid=5000 gid=5000 home=/home/vmail/%d/%n allow_all_users=yes
  }

## next make sure the section "socket listen" looks like this
## so dovecot and postfix work happily together
  socket listen {
     master {
       path = /var/run/dovecot/auth-master
       mode = 0600
       user = vmail # User running Dovecot LDA
       #group = mail # Or alternatively mode 0660 + LDA user in this group
     }
     client {
      # The client socket is generally safe to export to everyone. Typical use
      # is to export it to your SMTP server so it can do SMTP AUTH lookups
      # using it.
      path = /var/spool/postfix/private/auth
      #path = /var/run/dovecot/auth-client
      mode = 0660
      user = postfix
      group = postfix
     }
   }

Optionally we can create a system wide sieve rule that will move spam the Spam folder:

# vi /var/vmail/globalsieverc

require ["fileinto"];
# Move spam to spam folder
if anyof(header :contains "X-Spam-Flag" ["YES"], header :contains "X-DSPAM-Result" ["Spam"]) {
  fileinto "Spam";
  stop;
}

And finally, we need to set the appropriate rights on the dovecot conf files:

# chgrp vmail /etc/dovecot/dovecot.conf
# chmod g+r /etc/dovecot/dovecot.conf

6. Restarting the services

Now, everything should be good, and restarting postfix and dovecot get our setting working:

# /etc/init.d/postfix restart
# /etc/init.d/dovecot restart

If issues were to come, well.... /var/log/mail.log is the place to check.

7. References

http://workaround.org/articles/ispmail-etch/