Servidor de Email – Parte 2 – MariaDB

This is the second part of the process of creating the mail server on a CentOS 8. If you came here by accident, You should read first the previous publication on the Instalação dos Pacotes

Why use MariaDB/MySQL?

First of all I must clarify that I prefer to use PostgreSQL instead of MariaDB/MySQL for many reasons, However, There is one that weighs against the use of PostgreSQL in a unified server with WordPress and E-mail services is the simple fact that WP does not have native support for PostgreSQL. You can have more information on this topic on the official WordPress documentation This link. How am I basing on personal experience to write this document and also for small services, I'm using the most common and most accessible. If the use of PostgreSQL is of your interest, You can consult this document later where I'll show you how to migrate from MariaDB for PostgreSQL.

Configuring MariaDB/MySQL

Assuming that you are following the steps of this manual, You'll be with an installation of MariaDB still not configured. If you already have a functional installation, You can skip to the next step.

Initially I block any and all access to the external source database. Only from connections created within the server itself is that they may be made for safety. For that we open the file /etc/my.cnf. d/server.cnf and we add the lines below right after the entry [mysqld] existing.

[mysqld]
# Diretorio para o arquivo errmsg.sys no idioma que você desejar usar 
language=/usr/share/mysql/portuguese
# Ativa o logging por default para ajudar a encontrar problemas
general-log
# Indica que o log será enviado para uma tabela ao invés de ser escrito em um arquivo. Útil para quem não quer ficar conectando no servidor para analisar os logs. Você poderá utilizar o PHPMyAdmin para fazer as consultas
log_output=TABLE
# Habilita o relatório de consultas lentas
slow_query_log
# Tempo mínimo para considerar uma consulta lenta (em segundos)
long_query_time=5.0
# Indica para desativar o mecanismo de rede e somente aceitar conexões através do arquivo de socket local
skip-networking
bind-address = 127.0.0.1

If the intention is to allow remote connections to MariaDB, You must run the following commands to enable remote connections.

$ firewall-cmd --add-port=3306/tcp 
$ firewall-cmd --permanent --add-port=3306/tcp

Restart the service for the changes to take effect

$ sudo systemctl stop mysqld && sudo systemctl start mysqld

Creating user for Postfix and the required tables

So we can use the Postfix with the MariaDB we have to create a user for access to and from it the required tables to manage virtual domains and accounts.

First let's create the database.

NOTE: If it was not made any change in MariaDB installation you can connect without the use of password for the user ‘;root’;@’;localhost’;, otherwise, use the option -p to be requested enter the password.

$ mysql -u root

Then we'll create the user and tables. I'm going to use mypostfixdb as the name for the database and mypostfixdbuser for the user name. Change the information according to what you want, mainly mypostfixdbuser_password. This information will be used in the configuration of Postfix files that use tables created.

MariaDB> CREATE DATABASE mypostfixdb;
MariaDB> GRANT SELECT, INSERT, UPDATE, DELETE ON mypostfixdb.* TO 'mypostfixdbuser'@'localhost' IDENTIFIED BY 'mypostfixdbuser_password';
MariaDB> GRANT SELECT, INSERT, UPDATE, DELETE ON mypostfixdb.* TO 'mypostfixdbuser'@'localhost.localdomain' IDENTIFIED BY 'mypostfixdbuser_password';
MariaDB> FLUSH PRIVILEGES;
MariaDB> USE mypostfixdb;
MariaDB> CREATE TABLE virtual_domains (id int(11) NOT NULL AUTO_INCREMENT, name VARCHAR(100) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
MariaDB> CREATE TABLE virtual_users(id int(11) NOT NULL AUTO_INCREMENT, vdomain_id int(11) NOT NULL, password VARCHAR(100) NOT NULL, email VARCHAR(100) NOT NULL, PRIMARY KEY (id), UNIQUE KEY email(email), FOREIGN KEY (vdomain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8;
MariaDB> CREATE TABLE virtual_aliases (id int(11) NOT NULL AUTO_INCREMENT, vdomain_id int(11) NOT NULL, source VARCHAR(100) NOT NULL, PRIMARY KEY (id),FOREIGN KEY (vdomain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8;

These tables will store the accounts and virtual domains and will be accessed by Dovecot (According to the proposal of this tutorial).
And that's all.

Now we're ready to configure the Dovecot.