How to Setup Postfix Mail Server and Dovecot with Database (MariaDB) Securely – Part 1

Setup Postfix Mail Server in CentOS 7
Setup Postfix Mail Server in CentOS 7

In this 3-article series we will discuss how to set up a Postfix mail server with antivirus and spam protection in a CentOS 7 box. Please note these instructions also works on other distributions such as RHEL/Fedora and Debian/Ubuntu.

Part 1: How to Create and Setup Postfix Mail Server Database (MariaDB) Securely

Our plan consists in storing email accounts and aliases in a MariaDB database which is for our convenience, will be managed through phpMyAdmin.

If you choose to not install phpMyAdmin, or are dealing with a CLI-only server, we will also provide the equivalent code to create the database tables that will be used throughout this series.

Since keeping a mail server up and running is one of the essentials tasks that are usually assigned to system administrators and engineers, we will also provide a few tips to efficiently run this critical service in a production environment.

Create A and MX Records for Domain in DNS

Before proceeding further, there are a few prerequisites that must be met:

1. You will need a valid domain registered through a domain registrar. In this series we will use www.linuxnewz.com, which was registered through GoDaddy.

2. Such domain must be pointed to the external IP of your VPS or cloud hosting provider. If you are self-hosting your mail server, you can use the service offered by FreeDNS (requires registration).

In any event, you have to set up A and MX records for your domain as well (you can learn more about MX records in this FAQ from Google).

Once added, you can look them up using an online tool such as MxToolbox or ViewDNS to ensure they are properly set up.

Important: Please note that it may take a while (1-2 days) until the DNS records are propagated and your domain is available. In the meanwhile, you can access your VPS through its IP address to perform the tasks indicated below.

3. Configure the FQDN (Fully Qualified Domain Name) of your VPS:

# hostnamectl set-hostname yourhostname

to set the system hostname, then edit /etc/hosts as follows (replace AAA.BBB.CCC.DDD, yourhostname, and yourdomain with the public IP of your server, your hostname, and your registered domain):

AAA.BBB.CCC.DDD yourhostname.yourdomain.com       yourhostname

where yourhostname is the system hostname that was set previously using hostnamectl command.

Installing Required Software Packages

4. To install required software packages such as Apache, Postfix, Dovecot, MariaDB, PhpMyAdmin, SpamAssassin, ClamAV, etc, you need to enable the EPEL repository:

# yum install epel-release

5. Once you have followed the above steps, install the necessary packages:

In CentOS based Systems:

# yum update && yum install httpd httpd-devel postfix dovecot dovecot-mysql spamassassin clamav clamav-scanner clamav-scanner-systemd clamav-data clamav-update mariadb mariadb-server php phpMyAdmin

In Debian and derivatives:

# aptitude update && aptitude install apache2 postfix dovecot-core dovecot-imapd dovecot-pop3d dovecot-lmtpd dovecot-mysql spamassassin clamav clamav-daemon clamav-base mariadb-client mariadb-server php5 phpMyAdmin

6. Start and enable the web and database servers:

In CentOS based Systems:

# systemctl enable httpd mariadb
# systemctl start httpd mariadb

In Debian and derivatives:

# systemctl enable apache2 mariadb
# systemctl start apache2 mariadb

When the installation is complete and the above service are enabled and running, we will start off by setting up the database and tables to store information about Postfix mail accounts.

Creating Postfix Mail Accounts Database

For simplicity, we will use phpMyAdmin, a tool intended to handle the administration of MySQL / MariaDB databases through a web interface, to create and manage the email database.

However, in order to log on to and use this tool, we need to follow these steps:

7. Enable the MariaDB account (you can do this by running the mysql_secure_installation utility from the command line, assigning a password for user root, and setting the default settings proposed by the tool EXCEPT “Disallow root login remotely?“:

Disable MySQL root Login
Disable MySQL root Login

or otherwise create a new database user:

MariaDB [(none)]> CREATE USER 'dba'@'localhost' IDENTIFIED BY 'YourPasswordHere';
MariaDB [(none)]> GRANT ALL PRIVILEGES ON * . * TO 'dba'@'localhost';
MariaDB [(none)]> FLUSH PRIVILEGES;
Create New Database User
Create New Database User

Secure Apache with a Certificate

8. Since we will be using a web application to manage the email server database, we need to take the necessary precautions to protect connections to the server. Otherwise, our phpMyAdmin credentials will travel in plain text over the wire.

To set up Transport Layer Security (TLS) in your server, follow the steps outlined in Part 8 of the RHCE series: Implementing HTTPS through TLS using Network Security Service (NSS) for Apache before proceeding further.

Note: if you do not have access to the server’s console you will need to find another way to generate the necessary entropy during the key creation. In that case, you may want to consider installing rng-tools and running rngd -r /dev/urandom.

Configure and Secure PhpMyAdmin

9. In /etc/httpd/conf.d/phpMyAdmin.conf (CentOS) or /etc/phpmyadmin/apache.conf (Debian and derivatives), locate all the occurrences of the following lines and make sure they point to the public IP of your server:

Require ip AAA.BBB.CCC.DDD
Allow from AAA.BBB.CCC.DDD

Additionally, disable the default aliases and create a new one to access your phpMyAdmin login page. This will help to secure the site against bots and external attackers who target www.yourdomain.com/phpmyadmin or www.yourdomain.com/phpMyAdmin.

#Alias /phpMyAdmin /usr/share/phpMyAdmin
#Alias /phpmyadmin /usr/share/phpMyAdmin
Alias /managedb /usr/share/phpMyAdmin

Also, add following line inside <IfModule mod_authz_core.c>:

Require all granted
Secure PhpMyAdmin
Secure PhpMyAdmin

Create Apache VirtualHost for Domain

10. Make sure your domain is added to the enabled sites. Create /etc/httpd/sites-available/linuxnewz.com.conf (CentOS) or /etc/apache2/sites-available/linuxnewz.com (Debian) with the following contents (make sure the DocumentRoot, sites-available, and sites-enabled directories exist):

<VirtualHost *:80>
    ServerName www.linuxnewz.com
    ServerAlias linuxnewz.com
    DocumentRoot /var/www/linuxnewz.com/public_html
    ErrorLog /var/www/linuxnewz.com/error.log
    CustomLog /var/www/linuxnewz.com/requests.log combined
    Options Indexes FollowSymLinks
</VirtualHost>

and the symbolic link:

On CentOS:
# ln -s /etc/httpd/sites-available/linuxnewz.com.conf /etc/httpd/sites-enabled/linuxnewz.com.conf
On Debian:
# a2ensite linuxnewz.com

and you’re done.

Setup Postfix Email Database

11. Now you can open your phpMyAdmin interface at https://www.yourdomain.com/managedb (note that managedb is the alias that we set up earlier for the phpMyAdmin data directory).

If that does not work (which can be caused by a delay in the propagation or lack of configuration of DNS records) for the time being you can try using your server’s public IP address instead of www.yourdomain.com:

PhpMyAdmin Login
PhpMyAdmin Login

In any event, after you log on to phpMyAdmin you will see the following interface. Click New in the left section:

Create New Database in PhpMyAdmin
Create New Database in PhpMyAdmin

Enter a name for the database (EmailServer_db in this case, no need to select a Collation) and click Create:

Enter Database Name
Enter Database Name

12. On the next screen, choose a name for the first table (where we will store the domains this mail server will manage.

Please note that even when in this series we will only manage one domain, you can add more later) and the number of fields you want in it, then click Go. You will be prompted to name and configure those two fields, where you may safely proceed as indicated in the following images:

Create Database Table
Create Database Table

When you choose PRIMARY under Index for DomainId, accept the default values and click Go:

Add Database Index
Add Database Index

Alternatively, you can click Preview SQL to see the code under the hood:

CREATE TABLE `EmailServer_db`.`Domains_tbl` ( `DomainId` INT NOT NULL AUTO_INCREMENT , `DomainName` VARCHAR(50) NOT NULL , PRIMARY KEY (`DomainId`)) ENGINE = InnoDB;
Database Table Index Code
Database Table Index Code

When you’re ready, click Save to confirm changes. You will then be able to click New under EmailServer_db to continue creating tables:

Create Tables under Database
Create Tables under Database

13. Now follow these steps to create the rest of the tables. Click on the SQL tab and enter the indicated code for each database object.

Note that in this case we chose to create the table using a SQL query because of the relationships that must be established between different tables:

Users_tbl

CREATE TABLE `Users_tbl` ( 
    `UserId` INT NOT NULL AUTO_INCREMENT,  
    `DomainId` INT NOT NULL,  
    `password` VARCHAR(100) NOT NULL,  
    `Email` VARCHAR(100) NOT NULL,  
    PRIMARY KEY (`UserId`),  
    UNIQUE KEY `Email` (`Email`),  
    FOREIGN KEY (DomainId) REFERENCES Domains_tbl(DomainId) ON DELETE CASCADE 
) ENGINE = InnoDB; 
Create Postfix User Table
Create Postfix User Table

You should get a confirmation message (if not, phpMyAdmin will prompt for syntax errors):

MySQL Confirmation
MySQL Confirmation

Alias_tbl

CREATE TABLE `Alias_tbl` (
    `AliasId` INT NOT NULL AUTO_INCREMENT, 
    `DomainId` INT NOT NULL, 
    `Source` varchar(100) NOT NULL, 
    `Destination` varchar(100) NOT NULL, 
    PRIMARY KEY (`AliasId`), 
    FOREIGN KEY (DomainId) REFERENCES Domains_tbl(DomainId) ON DELETE CASCADE
) ENGINE = InnoDB;

(Click Go at the bottom to proceed with the creation of the table).

Up to this point, you should have the following database structure:

Database Structure
Database Structure

Which means you’re ready to start adding some records in the next section.

Creating a Postfix Domain, Users and Aliases

14. We will now insert the following records into the three tables. The passwords for [email protected] and [email protected] will be encrypted and the INSERT INTO Users_tbl statement.

Also, please note that the emails sent to [email protected] will be redirected to [email protected]:

INSERT INTO Domains_tbl (DomainName) VALUES ('linuxnewz.com');  
INSERT INTO Users_tbl (DomainId, password, Email) VALUES (1, ENCRYPT('PasswordForFirstEmailAccount', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), '[email protected]');  
INSERT INTO Users_tbl (DomainId, password, Email) VALUES (1, ENCRYPT('PasswordForSecondEmailAccount', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), '[email protected]');  
INSERT INTO Alias_tbl (DomainId, Source, Destination) VALUES (1, '[email protected]', '[email protected]');

Having added our domain, two user accounts, and an email alias we are ready to continue setting up our email server in the next article of this series, where we will configure Dovecot and Postfix.

Summary

In this article we have listed the packages required to install an Postfix email server in a CentOS 7 VPS, and explained how to manage the underlying database using phpMyAdmin.

In the next two articles we will review the configuration of the two programs that will take care of the email distribution for our domain (Part 2) and show you how to add protection against spam and viruses (Part 3) for your server.

Until then, feel free to contact us using the form below if you have any questions or comments.

If you read this far, tweet to the author to show them you care. Tweet a thanks
Gabriel Cánepa
Gabriel Cánepa is a GNU/Linux sysadmin and web developer from Villa Mercedes, San Luis, Argentina. He works for a worldwide leading consumer product company and takes great pleasure in using FOSS tools to increase productivity in all areas of his daily work.

Each tutorial at TecMint is created by a team of experienced Linux system administrators so that it meets our high-quality standards.

Join the TecMint Weekly Newsletter (More Than 156,129 Linux Enthusiasts Have Subscribed)
Was this article helpful? Please add a comment or buy me a coffee to show your appreciation.

52 thoughts on “How to Setup Postfix Mail Server and Dovecot with Database (MariaDB) Securely – Part 1”

  1. I’d just like to point out that the function ENCRYPT is not available in MySQL 8 and that one has to use other stronger mean of encryption. I know here we are supposed to use MariaDB but I must use mysql. The function that worked for me up to this point is SHA2 and the syntax works like this:

    INSERT INTO Users_tbl (DomainId, password, Email) VALUES (1, SHA2('mypassword', 256), '[email protected]');

    Albeit there are stronger algorithms, at least implementing this one was easy and fast. I found that Bcrypt is recommended by experts but I thing it might be more difficult to implement.

    Hopefully someone else finds this useful.

    Reply
      • Hi Ravi, still hoping for guide update for Postfix 3.4.2 (current stable release)

        The Part 2, Configuring Postfix Mail Server is the part of this series that doesn’t work with current Postfix configuration files, for instance, these shown variables:

        append_dot_mydomain = no
        biff = no
        config_directory = /etc/postfix
        dovecot_destination_recipient_limit = 1
        message_size_limit = 4194304
        

        (and others)

        do not exists in the current main.cf file.

        Reply
  2. Can you list step-by-step of configuration Mariadb store user mail account I young of knowledge of Mariadb I really need help

    Thanks!

    Reply
  3. I have a Centos 7 mail and web server combo using the mariadb for email accounts. I would like to add wordpress db for multiple websites instead of the traditional web server.

    Any insights on how to accomplish this? I tried to add the wordpress db and had issues and would like to have multiple instances of wordpress for multiple sites.

    I appreciate any help you can give me.

    Reply
  4. Great article series. Quite detailed & extensive but I could not find any way to restrict the user mail accounts in size. I mean how can one restrict the mailboxes maximum size to some arbitrary amount to save the system from getting completely filled up by a runaway user.
    Or maybe I overlooked some configuration detail.
    Thanks…

    Reply
    • Hi,
      Replying to my own comment. Well, Isn’t there any configuration for mail quotas in the this setup ? I think that is the missing piece & will complete the scenario. Thanks.

      Reply
      • Hello Everybody,

        Is the comment section closed ? I was expecting some sort of a reply to my query on Quota Enforcement. Many Thanks…

        Reply
        • If you have read through the setup you must have noticed the Directory /etc/dovecot/conf.d/ where all dovecot configuration files are stored.

          Take a look at 90-quota.conf and adjust to your needs.

          Reply
  5. I was struggling for days to find out, why I always got password mismatches. After debugging dovecot auth-worker and creating the password
    using:

    doveadm pw -p myfirstpassword -s sha512
    {SHA512}NiPagKRP+1ROZ29XI1unx+dqQ9PozCOZpk3AiAKE5vx/ZKCYbND++Jq84QbOM3LTxYIjDLgcn6NBF84rpg6pvg==

    I found that $6$………………………. is no longer valid with dovecot version 2.2.13

    When I took the complete string:

    {SHA512}NiPagKRP+1ROZ29XI1unx+dqQ9PozCOZpk3AiAKE5vx/ZKCYbND++Jq84QbOM3LTxYIjDLgcn6NBF84rpg6pvg==

    and put it into the password field of the SQL Database I could login with user and password.

    I am using maria-db (mysql Ver 15.1 Distrib 10.0.30-MariaDB, for debian-linux-gnu (x86_64)) which is a opensource fork of mysql. I cannot imagine that this caused the problem. So please update Your great tutorial accordingly.

    Reply
    • Thanks for your comments this solved my Authentication Failure with the clients.

      Dist: Ubuntu 16.04
      mysql Ver 15.1 Distrib 10.0.31-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

      dovecot –version
      2.2.22 (fe789d2)

      Reply
  6. Thanks for the wonderful tutorial, that’s a lot of work!

    One problem, though in your “Users_tbl”, the password field needs to be longer, maybe “varchar(110)” rather than “varchar(100)”. The SHA512 hash is several characters too long, so gets automatically truncated. I spent quite a few hours on this before I could successfully log in!

    Reply
    • I have confirmed the above comment, using varchar 100 caused password mismatch as the return hash value is 106, needs to be extended. Other than that a great guide.

      Reply
  7. Hello,
    Thank you very much for these 5 articles, but I would like How to Setup Postfix Mail Server and Dovecot With the directory ldap of samba4 ?

    Thank you for your responses.

    Reply

Got something to say? Join the discussion.

Thank you for taking the time to share your thoughts with us. We appreciate your decision to leave a comment and value your contribution to the discussion. It's important to note that we moderate all comments in accordance with our comment policy to ensure a respectful and constructive conversation.

Rest assured that your email address will remain private and will not be published or shared with anyone. We prioritize the privacy and security of our users.