Mail server with Postfix, Dovecot and MySQL

I will give an example of setting up a simple mail server on Postfix, Dovecot and MySQL.
For the test, I use the Ubuntu 16.04 operating system.

Configure DNS, see my article – How to add MX record

If you named the MX record as mail.example.com, then specify in the /etc/hostname file:

mail

We also specify in the /etc/hosts file (where instead of 192.0.2.2 we specify the external IP address of the server):

127.0.0.1 localhost localhost.localdomain 
192.0.2.2 mail.example.com mail

It is advisable to update the system:

sudo apt update
sudo apt upgrade
sudo reboot

Install certificates, for example, free from Let’s Encrypt as I described in the article:
Installing Certbot in Ubuntu

Install all the necessary components:

sudo apt install postfix postfix-mysql dovecot-core dovecot-imapd dovecot-pop3d dovecot-lmtpd dovecot-mysql mysql-server

During the installation of Postfix, select “Internet Site” and specify the domain, for example example.com (not mail.example.com).

Follow the security recommendations for the database server:

sudo mysql_secure_installation

Connect to the database server:

sudo mysql -u root -p

Let’s create a database, a user and the necessary tables:

CREATE DATABASE mailserver;
GRANT SELECT ON mailserver.* TO 'user'@'127.0.0.1' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
USE mailserver;

CREATE TABLE `virtual_domains` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `virtual_users` (
  `id` int(11) NOT NULL auto_increment,
  `domain_id` int(11) NOT NULL,
  `password` varchar(106) NOT NULL,
  `email` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `virtual_aliases` (
  `id` int(11) NOT NULL auto_increment,
  `domain_id` int(11) NOT NULL,
  `source` varchar(100) NOT NULL,
  `destination` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Fill in the data:

INSERT INTO `mailserver`.`virtual_domains`
  (`id` ,`name`)
VALUES
  ('1', 'example.com'),
  ('2', 'mail.example.com'),
  ('3', 'hostname'),
  ('4', 'localhost.example.com');

INSERT INTO `mailserver`.`virtual_users`
  (`id`, `domain_id`, `password` , `email`)
VALUES
  ('1', '1', ENCRYPT('password', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'email1@example.com'),
  ('2', '1', ENCRYPT('password', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'email2@example.com');

INSERT INTO `mailserver`.`virtual_aliases`
  (`id`, `domain_id`, `source`, `destination`)
VALUES
  ('1', '1', 'alias@example.com', 'email1@example.com');

Check:

SELECT * FROM mailserver.virtual_domains;
SELECT * FROM mailserver.virtual_users;
SELECT * FROM mailserver.virtual_aliases;
exit

Postfix

Make copies of the standard configuration files and open /etc/postfix/main.cf in any text editor:

sudo mv /etc/postfix/main.cf /etc/postfix/main.cf.orig
sudo cp /etc/postfix/master.cf /etc/postfix/master.cf.orig
sudo nano /etc/postfix/main.cf

Add settings to it, for example:

#myorigin = /etc/mailname

smtpd_banner = $myhostname ESMTP $mail_name (Ubuntu)
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

readme_directory = no

# TLS parameters
smtpd_tls_cert_file=/etc/letsencrypt/live/example.com/fullchain.pem
smtpd_tls_key_file=/etc/letsencrypt/live/example.com/privkey.pem
smtpd_use_tls=yes
smtpd_tls_auth_only = yes
smtp_tls_security_level = may
smtpd_tls_security_level = may
smtpd_sasl_security_options = noanonymous, noplaintext
smtpd_sasl_tls_security_options = noanonymous

# Authentication
smtpd_sasl_type = dovecot
smtpd_sasl_path = private/auth
smtpd_sasl_auth_enable = yes

# Restrictions
smtpd_helo_restrictions =
        permit_mynetworks,
        permit_sasl_authenticated,
        reject_invalid_helo_hostname,
        reject_non_fqdn_helo_hostname
smtpd_recipient_restrictions =
        permit_mynetworks,
        permit_sasl_authenticated,
        reject_non_fqdn_recipient,
        reject_unknown_recipient_domain,
        reject_unlisted_recipient,
        reject_unauth_destination
smtpd_sender_restrictions =
        permit_mynetworks,
        permit_sasl_authenticated,
        reject_non_fqdn_sender,
        reject_unknown_sender_domain
smtpd_relay_restrictions =
        permit_mynetworks,
        permit_sasl_authenticated,
        defer_unauth_destination

myhostname = example.com
alias_maps = hash:/etc/aliases
alias_database = hash:/etc/aliases
mydomain = example.com
myorigin = $mydomain
mydestination = localhost
relayhost =
mynetworks = 127.0.0.0/8 [::ffff:127.0.0.0]/104 [::1]/128
mailbox_size_limit = 0
recipient_delimiter = +
inet_interfaces = all
inet_protocols = all

# Handing off local delivery to Dovecot's LMTP, and telling it where to store mail
virtual_transport = lmtp:unix:private/dovecot-lmtp

# Virtual domains, users, and aliases
virtual_mailbox_domains = mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf
virtual_mailbox_maps = mysql:/etc/postfix/mysql-virtual-mailbox-maps.cf
virtual_alias_maps = mysql:/etc/postfix/mysql-virtual-alias-maps.cf,
        mysql:/etc/postfix/mysql-virtual-email2email.cf

# Even more Restrictions and MTA params
disable_vrfy_command = yes
strict_rfc821_envelopes = yes
#smtpd_etrn_restrictions = reject
#smtpd_reject_unlisted_sender = yes
#smtpd_reject_unlisted_recipient = yes
smtpd_delay_reject = yes
smtpd_helo_required = yes
smtp_always_send_ehlo = yes
#smtpd_hard_error_limit = 1
smtpd_timeout = 30s
smtp_helo_timeout = 15s
smtp_rcpt_timeout = 15s
smtpd_recipient_limit = 40
minimal_backoff_time = 180s
maximal_backoff_time = 3h

# Reply Rejection Codes
invalid_hostname_reject_code = 550
non_fqdn_reject_code = 550
unknown_address_reject_code = 550
unknown_client_reject_code = 550
unknown_hostname_reject_code = 550
unverified_recipient_reject_code = 550
unverified_sender_reject_code = 550

In the file /etc/postfix/mysql-virtual-mailbox-domains.cf add:

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

In the file /etc/postfix/mysql-virtual-mailbox-maps.cf add:

user = user
password = password
hosts = 127.0.0.1
dbname = mailserver
query = SELECT 1 FROM virtual_users WHERE email='%s'

In the file /etc/postfix/mysql-virtual-alias-maps.cf add:

user = user
password = password
hosts = 127.0.0.1
dbname = mailserver
query = SELECT destination FROM virtual_aliases WHERE source='%s'

In the file /etc/postfix/mysql-virtual-email2email.cf add:

user = user
password = password
hosts = 127.0.0.1
dbname = mailserver
query = SELECT email FROM virtual_users WHERE email='%s'

In the /etc/postfix/master.cf file, specify:

smtp      inet  n       -       n       -       -       smtpd

submission inet n       -       y      -       -       smtpd
  -o syslog_name=postfix/submission
  -o smtpd_tls_security_level=encrypt
  -o smtpd_sasl_auth_enable=yes
  -o smtpd_sasl_type=dovecot
  -o smtpd_sasl_path=private/auth
  -o smtpd_reject_unlisted_recipient=no
  -o smtpd_client_restrictions=permit_sasl_authenticated,reject
  -o milter_macro_daemon_name=ORIGINATING

smtps     inet  n       -       -       -       -       smtpd
  -o syslog_name=postfix/smtps
  -o smtpd_tls_wrappermode=yes
  -o smtpd_sasl_auth_enable=yes
  -o smtpd_sasl_type=dovecot
  -o smtpd_sasl_path=private/auth
  -o smtpd_client_restrictions=permit_sasl_authenticated,reject
  -o milter_macro_daemon_name=ORIGINATING
  ...

Check the configuration and restart Postfix to apply the changes:

sudo postfix check
sudo systemctl restart postfix

Check:

sudo postmap -q example.com mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf
sudo postmap -q email1@example.com mysql:/etc/postfix/mysql-virtual-mailbox-maps.cf
sudo postmap -q alias@example.com mysql:/etc/postfix/mysql-virtual-alias-maps.cf

Dovecot

We make copies of standard configuration files:

sudo cp /etc/dovecot/dovecot.conf /etc/dovecot/dovecot.conf.orig
sudo cp /etc/dovecot/conf.d/10-mail.conf /etc/dovecot/conf.d/10-mail.conf.orig
sudo cp /etc/dovecot/conf.d/10-auth.conf /etc/dovecot/conf.d/10-auth.conf.orig
sudo cp /etc/dovecot/dovecot-sql.conf.ext /etc/dovecot/dovecot-sql.conf.ext.orig
sudo cp /etc/dovecot/conf.d/10-master.conf /etc/dovecot/conf.d/10-master.conf.orig
sudo cp /etc/dovecot/conf.d/10-ssl.conf /etc/dovecot/conf.d/10-ssl.conf.orig

Add at the end of /etc/dovecot/dovecot.conf:

## Dovecot configuration file
...
# Enable installed protocols
!include_try /usr/share/dovecot/protocols.d/*.protocol
protocols = imap pop3 lmtp
...
postmaster_address=postmaster at example.com

In the file /etc/dovecot/conf.d/10-mail.conf, specify:

...
mail_location = maildir:/var/mail/vhosts/%d/%n/
...
mail_privileged_group = mail
...

Create a system user, group and directory for emails:

sudo mkdir -p /var/mail/vhosts/example.com
sudo groupadd -g 5000 vmail
sudo useradd -g vmail -u 5000 vmail -d /var/mail
sudo chown -R vmail:vmail /var/mail

In the file /etc/dovecot/conf.d/10-auth.conf, specify:

...
disable_plaintext_auth = yes
...
auth_mechanisms = plain login
...
!include auth-system.conf.ext
...
!include auth-sql.conf.ext
...

In the file /etc/dovecot/conf.d/auth-sql.conf.ext, specify:

...
passdb {
  driver = sql
  args = /etc/dovecot/dovecot-sql.conf.ext
}
...
#userdb {
#  driver = sql
#  args = /etc/dovecot/dovecot-sql.conf.ext
#}
...
userdb {
  driver = static
  args = uid=vmail gid=vmail home=/var/mail/vhosts/%d/%n
}
...

In the file /etc/dovecot/dovecot-sql.conf.ext, specify:

...
driver = mysql
...
connect = host=127.0.0.1 dbname=mailserver user=user password=password
...
default_pass_scheme = SHA512-CRYPT
...
password_query = SELECT email as user, password FROM virtual_users WHERE email='%u';
...

Set the rights:

sudo chown -R vmail:dovecot /etc/dovecot
sudo chmod -R o-rwx /etc/dovecot

In the file /etc/dovecot/conf.d/10-master.conf, specify:

...
service imap-login {
  inet_listener imap {
    #port = 143
  }
  inet_listener imaps {
    port = 993
    ssl = yes
  }
  ...
}
...
service pop3-login {
  inet_listener pop3 {
    #port = 110
  }
  inet_listener pop3s {
    port = 995
    ssl = yes
  }
}
...

service lmtp {
  unix_listener /var/spool/postfix/private/dovecot-lmtp {
    #mode = 0666i
    mode = 0600
    user = postfix
    group = postfix
  }
...
}

...
service auth {
  ...
  unix_listener /var/spool/postfix/private/auth {
    mode = 0660
    user = postfix
    group = postfix
  }

  unix_listener auth-userdb {
    mode = 0600
    user = vmail
  }
...
  user = dovecot
}
...

...
service auth-worker {
  ...
  user = vmail
}

In the file /etc/dovecot/conf.d/10-ssl.conf, specify:

...
# SSL/TLS support: yes, no, required. <doc/wiki/SSL.txt>
ssl = required
...
ssl_cert = </etc/letsencrypt/live/example.com/fullchain.pem
ssl_key = </etc/letsencrypt/live/example.com/privkey.pem

Check the configuration and restart dovecot:

doveconf -n
sudo systemctl restart dovecot

Let’s try to send a test letter:

sudo apt-get install mailutils
echo "Email body text" | sudo mail -s "Email subject line" username@gmail.com -aFrom:email1@example.com
sudo mail -f /var/mail/vhosts/example.com/email1

Make sure Postfix and Dovecot are running:

netstat -na | grep LISTEN | grep 25
netstat -na | grep LISTEN | grep 143
netstat -na | grep LISTEN | grep 587
netstat -na | grep LISTEN | grep 993
netstat -na | grep LISTEN | grep 995

The mail server is configured, you can connect the mail client to the ports: IMAP 143 (STARTTLS, Normal password), 587 (STARTTLS, Normal password).

After installation, you can check the security of the mail server with some services:
https://www.immuniweb.com/ssl/
https://mxtoolbox.com/spf.aspx
https://mxtoolbox.com/DMARC.aspx

See also my articles:

• Installing and Configuring Postfix
• Configuring Spamassassin + Postfix
• Configuring ClamAV + Postfix
• How to enable port 587 in Postfix