How to Setup Postfix Mail Server on Ubuntu 16.04 (Dovecot - MySQL)

Saheetha Shameer 2016-06-22 Channel: Linux

Abstract: /etc/dovecot/conf.d/auth-sql.conf.ext Make sure your MySQL authentication file looks like this. Modifying the Dovecot + MySQL configuration file/etc/d

Ubuntu Debian . How-to-Setup-Icinga-Web-2-on-Ubuntu-1804-Ubuntu-1604-Debian-9

Linux . How-To-Install-Nginx-MySQL-PHP-(LEMP-Stack)-on-Ubuntu-1604-LTS

Generally, all mailservers consist of three main components: MTA, MDA and MUA. Each components plays a specific role in the process of moving and managing email messages and is important for ensuring proper email delivery. Hence, setting up a Mail server is a difficult process involving the proper configuration of these components. The best way is to install and configure each individual component one by one, ensuring each one works and gradually building up your mail server.

In this article, I'm providing the guidelines on how we can configure a Mail Server on an Ubuntu 16.04 server with Postix (MTA) and Dovecot (MDA) using an external database (MySQL) for managing virtual users. First of all let's start with the pre-requisites for building our Mail server.

Pre-requisites

MySQL installed Server
A Fully qualified hostname
Domain resolving to your server

After full-filling our pre-requisites, we can start building our Mail server one by one.

Send Free Unlimited Emails | SMTP S...

To view this video please enable JavaScript, and consider upgrading to a web browser that supports HTML5 video

Send Free Unlimited Emails | SMTP Server Setup on Ubuntu Virtualmin | Outgoing Mail Server on OCP Installing Packages

First, of all we need to update our APT repository packages and start with installing the required postfix and dovecot packages.

root@ubuntu:~# apt-get install postfix postfix-mysql dovecot-core dovecot-imapd dovecot-lmtpd dovecot-mysql

During the Postfix installation, set-up windows will pop-up for the initial configuration. We need to choose the "internet site" and set a FQDN as our system mail name during the installation phase. This proceeds with the installation of the required packages as below.

Postfix is now set up with a default configuration. If you need to make
changes, edit
/etc/postfix/main.cf (and others) as needed. To view Postfix configuration
values, see postconf(1).

After modifying main.cf, be sure to run '/etc/init.d/postfix reload'.

Running newaliases
Setting up postfix-mysql (3.1.0-3) ...
Processing triggers for libc-bin (2.23-0ubuntu3) ...
Processing triggers for ureadahead (0.100.0-19) ...
Processing triggers for systemd (229-4ubuntu4) ...
Processing triggers for ufw (0.35-0ubuntu2) ...
Processing triggers for dovecot-core (1:2.2.22-1ubuntu2) ..

Create a Database for managing the mail users

Next step is to create a database for managing the email users and domains on our mail server. As I said before, we're managing the email users with this MySQL database. We can install MySQL if it's not installed by running this command apt-get install mysql-server-5.7.

We are going to create a database named "lnmailserver" with three tables as below:

Virtual domains : For managing domains
Virtual users : For managing email users
Virtual Alias : For setting up Aliases

Let's create our databases with all these tables.

Creating a database named lnmailserver.

mysql> CREATE DATABASE lnmailserver;
Query OK, 1 row affected (0.00 sec)

Creating a DB user lnmailuser and granting access to this database with a password.

mysql> GRANT SELECT ON lnmailserver.* TO 'lnmailuser'@'127.0.0.1' IDENTIFIED BY 'lnmail123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

Switching to the database lnmailserver and creating our three tables namely virtual_domains, virtual_users and virtual_aliases with a specification and table format.

mysql> USE lnmailserver;
Database changed
mysql> CREATE TABLE `virtual_domains` (
-> `id` INT NOT NULL AUTO_INCREMENT,
-> `name` VARCHAR(50) NOT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE `virtual_users` (
-> `id` INT NOT NULL AUTO_INCREMENT,
-> `domain_id` INT NOT NULL,
-> `password` VARCHAR(106) NOT NULL,
-> `email` VARCHAR(120) 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;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE `virtual_aliases` (
-> `id` INT NOT NULL AUTO_INCREMENT,
-> `domain_id` INT 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;
Query OK, 0 rows affected (0.02 sec)

Adding the domains, users and aliases to each of these tables according to our requirements.

mysql> INSERT INTO `lnmailserver`.`virtual_domains`
-> (`id` ,`name`)
-> VALUES
-> ('1', 'linoxidemail.com'),
-> ('2', 'ubuntu.linoxidemail.com');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> INSERT INTO `lnmailserver`.`virtual_users`
-> (`id`, `domain_id`, `password` , `email`)
-> VALUES
-> ('1', '1', ENCRYPT('blogger123', CONCAT('$6

mysql> INSERT INTO `lnmailserver`.`virtual_aliases`
-> (`id`, `domain_id`, `source`, `destination`)
-> VALUES
-> ('1', '1', '[email protected]', '[email protected]');
Query OK, 1 row affected (0.00 sec)

Verifying each table contents

mysql> select * from virtual_domains;
+----+-------------------------+
| id | name |
+----+-------------------------+
| 1 | linoxidemail.com |
| 2 | ubuntu.linoxidemail.com |
+----+-------------------------+
2 rows in set (0.00 sec)

mysql> select * from virtual_users;
+----+-----------+------------------------------------------------------------------------------------------------------------+---------------------------+
| id | domain_id | password | email |
+----+-----------+------------------------------------------------------------------------------------------------------------+---------------------------+
| 1 | 1 | $6$da4aa6fc680940d4$jt1plE8Lvo4hcjdP3N0pNxSC/o1ZsN4mpJ4WCcwk2mSqyY7/2l4ayyI7GcipeTf0uwzk5HnWbjddvv/jGomh41 | [email protected] |
| 2 | 1 | $6$36d2dc2e68ab56f6$L2b/D44yuT7qXsw22kTFPfxTbEbUuRDhr0RDoBnRc/q/LGcRF3NsLQCyapXdYKyA2zkSE9MJIXL7nHAbbCmlO. | [email protected] |
+----+-----------+------------------------------------------------------------------------------------------------------------+---------------------------+
2 rows in set (0.00 sec)

mysql> select * from virtual_aliases;
+----+-----------+-----------------------+---------------------------+
| id | domain_id | source | destination |
+----+-----------+-----------------------+---------------------------+
| 1 | 1 | [email protected] | [email protected] |
+----+-----------+-----------------------+---------------------------+
1 row in set (0.00 sec)

mysql > exit

Configuring Postfix

Our next step is to modify the Postfix configuration according to our configuration plan of how we need to accept SMTP connections. Before making any changes to the configuration, it is always advised to take a backup for the file.

root@ubuntu:~# cp -rp /etc/postfix/main.cf /etc/postfix/main.cf-bkp

Now we can open up the file and make the following changes.

Modify the following entries to enable TLS support for the users to connect, specify the SSL certificate which is used to secure the connection.

This section is modified from:

#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:${data_directory}/smtpd_scache
#smtp_tls_session_cache_database = btree:${data_directory}/smtp_scache

To :

smtpd_tls_cert_file=/etc/ssl/certs/dovecot.pem
smtpd_tls_key_file=/etc/ssl/private/dovecot.key
smtpd_use_tls = yes
smtpd_tls_auth_only = yes

I'm using free Dovecot SSL certificates which is specified here. We can generate dovecot self signed SSL certificates with the below command. If you've a valid SSL certificate for your hostname, you can specify those instead.

openssl req -new -x509 -days 1000 -nodes -out "/etc/ssl/certs/dovecot.pem" -keyout "/etc/ssl/private/dovecot.key"

We need to add these TLS parameters to the Postfix configuration which makes Postfix to use Dovecot for authentication and to initialize connections.

smtpd_sasl_type = dovecot
smtpd_sasl_path = private/auth
smtpd_sasl_auth_enable = yes
smtpd_recipient_restrictions = permit_sasl_authenticated permit_mynetworks reject_unauth_destination

We need to comment the "mydestination" default entries and update it to use "localhost" alone.

mydestination = localhost

Confirm the myhostname part, whether it's set properly as our FQDN hostname.

root@ubuntu:~# grep myhostname /etc/postfix/main.cf
smtpd_banner = $myhostname ESMTP $mail_name (Ubuntu)
myhostname = ubuntu.linoxidemail.com

Modifying this parameter enables Postfix to use Dovecot's LMTP instead of its own LDA to save emails to the local mailboxes, thereby enabling local mail delivery for all the domains listed in the MySQL database.
```
virtual_transport = lmtp:unix:private/dovecot-lmtp
```
Last, but not least we need to tell Postfix that we're using external database to manage the domains, users and aliases. We need to add the configuration path to fetch these details from the database tables.

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

Now we need to create these files mentioned above one by one. Please see my file details below:

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

root@ubuntu:~# cat /etc/postfix/mysql-virtual-mailbox-domains.cf
user = lnmailuser
password = lnmail123
hosts = 127.0.0.1
dbname = lnmailserver
query = SELECT 1 FROM virtual_domains WHERE name='%s'
root@ubuntu:~#

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

root@ubuntu:~# cat /etc/postfix/mysql-virtual-mailbox-maps.cf
user = lnmailuser
password = lnmail123
hosts = 127.0.0.1
dbname = lnmailserver
query = SELECT 1 FROM virtual_users WHERE email='%s'
root@ubuntu:~#

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

root@ubuntu:~# cat /etc/postfix/mysql-virtual-alias-maps.cf
user = lnmailuser
password = lnmail123
hosts = 127.0.0.1
dbname = lnmailserver
query = SELECT destination FROM virtual_aliases WHERE source='%s'

These files describes how Postfix connects with the external database. We need to restart Postfix after making these changes.

root@ubuntu:~# service postfix restart

We need to run these following commands to confirm the connectivity and check whether Postfix is able to fetch the required information from the database.

To check whether Postfix finds your domain from the database, we can run this. This should return '1' if the attempt is successful.

root@ubuntu:/etc/ssl/certs# postmap -q linoxidemail.com mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf
1

To check whether Postfix finds your required email address from the database, we can run this. This also should return '1' if it goes successful.

root@ubuntu:/etc/ssl/certs# postmap -q [email protected] mysql:/etc/postfix/mysql-virtual-mailbox-maps.cf
1

To check whether Posfix finds your email forwarder from the database, we can run this. This should return your email forwarder set if the attempt is successful.

root@ubuntu:/etc/ssl/certs# postmap -q [email protected] mysql:/etc/postfix/mysql-virtual-alias-maps.cf
[email protected]

Please Note : You can connect securely with your email clients using Postfix on port 587, you can open the port by uncommenting the following part in the Postfix master confguration : /etc/postfix/master.cf.

You need to restart Postfix after making any changes to the configuration. By using telnet command, you can confirm whether the port is open.

Configuring Dovecot

Our next step is to configure our MDA to allow POP3 or IMAP protocols and other configuration settings to connect to external database and Postfix. We are mainly modifying the following files.

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

It's always advised to take backup for these files before making any configuration changes. We can modify each file one by one.

Modifying the dovecot main configuration file : /etc/dovecot/dovecot.conf

The following setting is uncommented by default. But we need to ensure that it is uncommented.

!include conf.d/*.conf

We can enable all required protocols in this directive. If you need to enable POP3, we can append pop3 to this line and also make sure to install the required dovecot packages "dovecot-pop3d" to enable that.

!include_try /usr/share/dovecot/protocols.d/*.protocol
protocols = imap lmtp

Modifying the Dovecot Mail configuration file : /etc/dovecot/conf.d/10-mail.conf

We need to find the following parameter "mail_location" in the configuration and update with our mail storage path. I've my mail folders located inside "/var/mail/vhosts/" folder. Hence, I modified the file path as below:

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

We need to set the "mail_privileged_group" parameter to "mail".

mail_privileged_group = mail

Once this is done, we need to make we've set proper ownership and permissions for our mail folders. Create the mail folders for each domains which we've registered in the MySQL table inside this folder "/var/mail/vhosts" and set proper ownerships/permissions.

root@ubuntu:~# ls -ld /var/mail
drwxrwsr-x 2 root mail 4096 Apr 21 16:56 /var/mail
root@ubuntu:~# mkdir -p /var/mail/vhosts/linoxidemail.com

Created a separate user/group named "vmail" with an id 5000 and changed the mail folders ownerships to that.
root@ubuntu:~# groupadd -g 5000 vmail
root@ubuntu:~# useradd -g vmail -u 5000 vmail -d /var/mail
root@ubuntu:~# chown -R vmail:vmail /var/mail

Modifying the Dovecot authentication file : /etc/dovecot/conf.d/10-auth.conf

Disable plain text authentication to ensure security by modifying the below parameter to "yes".

disable_plaintext_auth = yes

Modify the "auth_mechanisms" parameter as below:

auth_mechanisms = plain login

We need to comment the mentioned line and enable the MySQL authentication by uncommenting the auth-sql.conf.ext line as below:

#!include auth-system.conf.ext
!include auth-sql.conf.ext

Modifying the authentication SQL file : /etc/dovecot/conf.d/auth-sql.conf.ext

Make sure your MySQL authentication file looks like this.

Modifying the Dovecot + MySQL configuration file : /etc/dovecot/dovecot-sql.conf.ext

We need to uncomment the "driver" parameter and set to MySQL as below:

driver = mysql

Modify and set the connection parameters as per our database name and user.

connect = host=127.0.0.1 dbname=lnmailserver user=lnmailuser password=lnmail123

Modify the default_pass_scheme to SHA-512 and password_query line as below:

default_pass_scheme = SHA512-CRYPT
password_query = SELECT email as user, password FROM virtual_users WHERE email='%u';

Please note : Set permissions on the /etc/dovecot directory so the vmail user can use it.

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

Modifying Dovecot Master configuration file : /etc/dovecot/conf.d/10-master.conf

We are modifying four sections in this configuration file. IMAP section, local mail transfer section, authentication section and last authenticating worker process section. Please see the screenshots of each section below to view the modifications:

Modifying the SSL configuration : /etc/dovecot/conf.d/10-ssl.conf

We're modifying this section to enable SSL for the incoming/outgoing connections. This configuration settings are optional. But I'd recommend these for more security.

Change the SSL parameter to required

ssl = required

Specify the SSL cert and key file location for our configuration. You can view the screenshot for more details.

You need to restart Dovecot after all these modification.

That's all :) We've completed with our Mail server setup. Hurray! You can access your email account using your username and password on any of your preferred email client. I could successfully access my email account using these settings below:

I hope you enjoyed reading this article. I would recommend your valuable suggestions and comments on this.
Have a Nice day!

, SUBSTRING(SHA(RAND()), -16))), '[email protected]'), -> ('2', '1', ENCRYPT('blogger321', CONCAT('$6

Verifying each table contents

Configuring Postfix

Now we can open up the file and make the following changes.

Modify the following entries to enable TLS support for the users to connect, specify the SSL certificate which is used to secure the connection.

We need to add these TLS parameters to the Postfix configuration which makes Postfix to use Dovecot for authentication and to initialize connections.

We need to comment the "mydestination" default entries and update it to use "localhost" alone.

Confirm the myhostname part, whether it's set properly as our FQDN hostname.

Modifying this parameter enables Postfix to use Dovecot's LMTP instead of its own LDA to save emails to the local mailboxes, thereby enabling local mail delivery for all the domains listed in the MySQL database.
```
 
```
Last, but not least we need to tell Postfix that we're using external database to manage the domains, users and aliases. We need to add the configuration path to fetch these details from the database tables.

Now we need to create these files mentioned above one by one. Please see my file details below:

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

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

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

These files describes how Postfix connects with the external database. We need to restart Postfix after making these changes.

We need to run these following commands to confirm the connectivity and check whether Postfix is able to fetch the required information from the database.

To check whether Postfix finds your domain from the database, we can run this. This should return '1' if the attempt is successful.

To check whether Postfix finds your required email address from the database, we can run this. This also should return '1' if it goes successful.

To check whether Posfix finds your email forwarder from the database, we can run this. This should return your email forwarder set if the attempt is successful.

You need to restart Postfix after making any changes to the configuration. By using telnet command, you can confirm whether the port is open.

Configuring Dovecot

Our next step is to configure our MDA to allow POP3 or IMAP protocols and other configuration settings to connect to external database and Postfix. We are mainly modifying the following files.

It's always advised to take backup for these files before making any configuration changes. We can modify each file one by one.

Modifying the dovecot main configuration file : /etc/dovecot/dovecot.conf

The following setting is uncommented by default. But we need to ensure that it is uncommented.

We can enable all required protocols in this directive. If you need to enable POP3, we can append pop3 to this line and also make sure to install the required dovecot packages "dovecot-pop3d" to enable that.

Modifying the Dovecot Mail configuration file : /etc/dovecot/conf.d/10-mail.conf

We need to find the following parameter "mail_location" in the configuration and update with our mail storage path. I've my mail folders located inside "/var/mail/vhosts/" folder. Hence, I modified the file path as below:

We need to set the "mail_privileged_group" parameter to "mail".

Modifying the Dovecot authentication file : /etc/dovecot/conf.d/10-auth.conf

Disable plain text authentication to ensure security by modifying the below parameter to "yes".

Modify the "auth_mechanisms" parameter as below:

We need to comment the mentioned line and enable the MySQL authentication by uncommenting the auth-sql.conf.ext line as below:

Modifying the authentication SQL file : /etc/dovecot/conf.d/auth-sql.conf.ext

Make sure your MySQL authentication file looks like this.

Modifying the Dovecot + MySQL configuration file : /etc/dovecot/dovecot-sql.conf.ext

We need to uncomment the "driver" parameter and set to MySQL as below:

Modify and set the connection parameters as per our database name and user.

Modify the default_pass_scheme to SHA-512 and password_query line as below:

Please note : Set permissions on the /etc/dovecot directory so the vmail user can use it.

Modifying Dovecot Master configuration file : /etc/dovecot/conf.d/10-master.conf

Modifying the SSL configuration : /etc/dovecot/conf.d/10-ssl.conf

We're modifying this section to enable SSL for the incoming/outgoing connections. This configuration settings are optional. But I'd recommend these for more security.

Change the SSL parameter to required

Specify the SSL cert and key file location for our configuration. You can view the screenshot for more details.

You need to restart Dovecot after all these modification.

I hope you enjoyed reading this article. I would recommend your valuable suggestions and comments on this.
Have a Nice day!

, SUBSTRING(SHA(RAND()), -16))), '[email protected]'); Query OK, 2 rows affected, 2 warnings (0.01 sec) Records: 2 Duplicates: 0 Warnings: 2

Verifying each table contents

Configuring Postfix

Now we can open up the file and make the following changes.

Modify the following entries to enable TLS support for the users to connect, specify the SSL certificate which is used to secure the connection.

We need to add these TLS parameters to the Postfix configuration which makes Postfix to use Dovecot for authentication and to initialize connections.

We need to comment the "mydestination" default entries and update it to use "localhost" alone.

Confirm the myhostname part, whether it's set properly as our FQDN hostname.

Modifying this parameter enables Postfix to use Dovecot's LMTP instead of its own LDA to save emails to the local mailboxes, thereby enabling local mail delivery for all the domains listed in the MySQL database.
```
 
```
Last, but not least we need to tell Postfix that we're using external database to manage the domains, users and aliases. We need to add the configuration path to fetch these details from the database tables.

Now we need to create these files mentioned above one by one. Please see my file details below:

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

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

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

These files describes how Postfix connects with the external database. We need to restart Postfix after making these changes.

We need to run these following commands to confirm the connectivity and check whether Postfix is able to fetch the required information from the database.

To check whether Postfix finds your domain from the database, we can run this. This should return '1' if the attempt is successful.

To check whether Postfix finds your required email address from the database, we can run this. This also should return '1' if it goes successful.

To check whether Posfix finds your email forwarder from the database, we can run this. This should return your email forwarder set if the attempt is successful.

You need to restart Postfix after making any changes to the configuration. By using telnet command, you can confirm whether the port is open.

Configuring Dovecot

Our next step is to configure our MDA to allow POP3 or IMAP protocols and other configuration settings to connect to external database and Postfix. We are mainly modifying the following files.

It's always advised to take backup for these files before making any configuration changes. We can modify each file one by one.

Modifying the dovecot main configuration file : /etc/dovecot/dovecot.conf

The following setting is uncommented by default. But we need to ensure that it is uncommented.

We can enable all required protocols in this directive. If you need to enable POP3, we can append pop3 to this line and also make sure to install the required dovecot packages "dovecot-pop3d" to enable that.

Modifying the Dovecot Mail configuration file : /etc/dovecot/conf.d/10-mail.conf

We need to find the following parameter "mail_location" in the configuration and update with our mail storage path. I've my mail folders located inside "/var/mail/vhosts/" folder. Hence, I modified the file path as below:

We need to set the "mail_privileged_group" parameter to "mail".

Modifying the Dovecot authentication file : /etc/dovecot/conf.d/10-auth.conf

Disable plain text authentication to ensure security by modifying the below parameter to "yes".

Modify the "auth_mechanisms" parameter as below:

We need to comment the mentioned line and enable the MySQL authentication by uncommenting the auth-sql.conf.ext line as below:

Modifying the authentication SQL file : /etc/dovecot/conf.d/auth-sql.conf.ext

Make sure your MySQL authentication file looks like this.

Modifying the Dovecot + MySQL configuration file : /etc/dovecot/dovecot-sql.conf.ext

We need to uncomment the "driver" parameter and set to MySQL as below:

Modify and set the connection parameters as per our database name and user.

Modify the default_pass_scheme to SHA-512 and password_query line as below:

Please note : Set permissions on the /etc/dovecot directory so the vmail user can use it.

Modifying Dovecot Master configuration file : /etc/dovecot/conf.d/10-master.conf

Modifying the SSL configuration : /etc/dovecot/conf.d/10-ssl.conf

We're modifying this section to enable SSL for the incoming/outgoing connections. This configuration settings are optional. But I'd recommend these for more security.