CentOS 7 – Setting up MariaDB for virtual domains and users

How to install Ubuntu Server 19.10

Since we have already installed MariaDB (the drop-in replacement for MySQL) during the postfix installation, we can proceed to the configuration. But if we ever needed to reinstall the package again, we can always use yum:


$ sudo yum install mariadb-server

The first thing to do to start the MariaDB configuration is to start the service. Also, we need to add it to the system startup services:


$ sudo systemctl enable mariadb.service
$ sudo systemctl start mariadb.service

Then we start the configuration by setting up the secure installation mode where we can set up or change the MariaDB root password, remove anonymous user accounts, disable root logins outside of the local host, and so on:


$ sudo mysql_secure_installation

We should make sure to answer yes at the end to finish the configuration.

Now we have the MariaDB service well configured and ready to be used. We will start setting up the new database to use it with Postfix. To add the new database, we need to open the MariaDB shell:


$ sudo mysql -u root -p

Then we create a new database:


> CREATE DATABASE mail;

Next, we switch to that database to start making changes in it:


> USE mail;

Then we create a database user to be the mail administrator by granting them permissions on the mail database:


> GRANT SELECT, INSERT, UPDATE, DELETE ON mail.* TO 'mail_admin'@'localhost' IDENTIFIED BY 'mail_admin_password';
> GRANT SELECT, INSERT, UPDATE, DELETE ON mail.* TO 'mail_admin'@'localhost.localdomain' IDENTIFIED BY 'mail_admin_password';

The administrator password mail_admin_password should be a very strong password to better secure the mail server database.

Then we submit the change:


> FLUSH PRIVILEGES;

Now we start creating the necessary tables inside our database. First, we create the virtual domains table:


> CREATE TABLE domains (domain varchar(50) NOT NULL, PRIMARY KEY (domain) );

Then we create the table that handles mail forwarding:


> CREATE TABLE forwardings (source varchar(80) NOT NULL, destination TEXT NOT NULL, PRIMARY KEY (source) );

Next, we create the table in which we are going to store the mail server users:


> CREATE TABLE users (email varchar(80) NOT NULL, password varchar(20) NOT NULL, PRIMARY KEY (email) );

Finally, we create the transports table:


> CREATE TABLE transport ( domain varchar(128) NOT NULL default '', transport varchar(128) NOT NULL default '', UNIQUE KEY domain (domain) );

We have set up our mail server database table’s initial configuration successfully. We can now leave the MariaDB shell:


> quit

To enable Postfix to communicate with the MariaDB server, we need to set up MariaDB to listen to the localhost at IP address 127.0.0.1. To set this configuration, we need to edit /etc/my.cnf and add the following section [mysql]:

bind-address=127.0.0.1

Then we restart the MariaDB service:


$ sudo systemctl restart mariadb.service

We are not yet finished with our mail server database configuration. Now we go back to the Postfix configuration to set up communication with the database earlier created code within MariaDB. So we need tell Postfix which table of which database it should use to store specific information (users, virtual domains, and so on).

We start by creating configuration files for each table. For the virtual domains configuration, we are creating a file called /etc/postfix/mysql-virtual_domains.cf:


$ sudo nano /etc/postfix/mysql-virtual_domains.cf

Then we put the following code inside it and save it:

user = mail_admin
password = mail_admin_password
dbname = mail
query = SELECT domain AS virtual FROM domains WHERE domain='%s'
hosts = 127.0.0.1

Again, the mail_admin_password should be replaced with the strong one that we created earlier. This goes for all of the following files that we are going to create.

Then we create the configuration file for the virtual forwarding at /etc/postfix/mysql-virtual_forwardings.cf:


$ sudo nano /etc/postfix/mysql-virtual_forwardings.cf

We add the following code inside it:

user = mail_admin
password = mail_admin_password
dbname = mail
query = SELECT destination FROM forwardings WHERE source='%s'
hosts = 127.0.0.1

We create another configuration file for the virtual mailbox called /etc/postfix/mysql-virtual_mailboxes.cf:


$ sudo nano /etc/postfix/mysql-virtual_mailboxes.cf

And we insert the following code:

user = mail_admin
password = mail_admin_password
dbname = mail
query = SELECT CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') FROM users WHERE email='%s'
hosts = 127.0.0.1

And, finally, we do the same for the virtual e-mail mapping by creating the file /etc/postfix/mysql-virtual_email2email.cf:


$ sudo nano /etc/postfix/ mysql-virtual_email2email.cf

Then add the following code inside it and save it:

user = mail_admin
password = mail_admin_password
dbname = mail
query = SELECT email FROM users WHERE email='%s'
hosts = 127.0.0.1

Now we set the files’ permissions and ownership to make Postfix able to handle the new configuration files:


$ sudo chmod o= /etc/postfix/mysql-virtual_*.cf
$ sudo chgrp postfix /etc/postfix/mysql-virtual_*.cf

Then we create a user and group for mail handling. The virtual mailboxes will be all stored under this user home directory. We are choosing the group 5000 to keep our distance from the ones created by the system for the regular users:


$ sudo groupadd -g 5000 vmail
$ sudo useradd -g vmail -u 5000 vmail -d /home/vmail -m

To complete the configuration, we need to make some minor changes to the Postfix configuration. We will not open the configuration file and edit it, we will only add them using the command postconf -e.

We start by locating the new configuration file created to address the database tables:


$ sudo postconf -e 'virtual_alias_domains ='
$ sudo postconf -e 'virtual_alias_maps = proxy:mysql:/etc/postfix/mysql-virtual_forwardings.cf, mysql:/etc/postfix/mysql-virtual_email2email.cf'
$ sudo postconf -e 'virtual_mailbox_domains = proxy:mysql:/etc/postfix/mysql-virtual_domains.cf'
$ sudo postconf -e 'virtual_mailbox_maps = proxy:mysql:/etc/postfix/mysql-virtual_mailboxes.cf'

Then we set the location where the mailbox folder will be created:


$ sudo postconf -e 'virtual_mailbox_base = /home/vmail'

Finally, we see the user UID who will take control of the configuration files and add the mailbox folder:


$ sudo postconf -e 'virtual_uid_maps = static:5000'
$ sudo postconf -e 'virtual_gid_maps = static:5000'

To finish the mail server database configuration, we need to restart the Postfix service to submit the change:


$ sudo systemctl restart postfix.service

We can say that we have finished our mail server database service. Still, if we need to configure Postfix with the virtual domain to use them to send e-mail with a domain name different from the system’s default domain name, we need to make some minor modifications to the Postfix main configuration file. Also, we can always use the command postconf -e to make quick changes:


$ sudo nano /etc/postfix/main.cf

Then we add the following code at the end of the file:

virtual_alias_domains = packtmail2.co.uk
virtual_alias_maps = hash:/etc/postfix/virtual

Then we need to add the new domain to the virtual domain file, /etc/postfix/virtual:


$ sudo nano /etc/postfix/virtual

Then we add the following snippet anywhere:

user1@mail.packtmail2.co.uk user1

Then we apply the change by refreshing the Postfix map and restarting the service:


$ sudo postmap /etc/postfix/virtual
$ sudo systemctl reload postfix

Comments are closed.