Configure Database — MySQL | Vithurshan SELVARAJAH

In this section we will create a database mailserver and two users. The first user mailadmin will be able to modify the database data and is intended for mail server administrator. The other user mailserver can only read the database data and is intended for server processes.

First we need to create two random passwords for these users using the pwgen tool:

  pwgen -s1 30 2

Take a note of the passwords or store them somewhere safe.

Connect to the database:

  sudo mysql

Create a database:

  CREATE DATABASE mailserver;

Now we have an empty database. Let's create the user mailadmin and give the necessary privileges to manage the database:

grant all on mailserver.* to 'mailadmin'@'localhost' identified by 'gefk6lA2brMOeb8eR5WYaMEdKDQfnF';

CAREFUL: Please use the first password you just generated instead of mine.

Also create the read-only user that will be used by Postfix and Dovecot to access the database:

grant select on mailserver.* to 'mailserver'@'127.0.0.1' identified by 'x893dNj4stkHy1MKQq0USWBaX4ZZdq';

CAREFUL: Use your second random password here instead of mine.

NOTE: /Here we are using 127.0.0.1 instead of localhost because MariaDB (and Oracle’s MySQL) distinguishes between the two. If you initiate a database connection to localhost then you talk to the socket file which lives at /var/run/mysqld/mysqld.sock on your server. But if you connect to 127.0.0.1 it will create a network connection talking to the TCP socket on port 3306 on your server. The difference is that any process on your server can talk to 127.0.0.1. But the socket file has certain user/group/other permissions just like any other file on your file system. Postfix will be restricted to its /var/spool/postfix directory and cannot by default access that socket file. So by using 127.0.0.1 we circumvent that limitation.

Let's create tables to store virtual domains, virtual aliases and virtual users. This table just holds the list of domains that you will use as virtual_mailbox_domains in Postfix:

USE mailserver;

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

The next table contains information about your users:

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

The last table contains forwardings from an email address to other email addresses:

CREATE TABLE IF NOT EXISTS `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;

Example Data

Let’s populate the database with an example1.com domain, a user1@example1.com email account and a forwarding of postmaster@example1.com to user1@example1.com.

To add that sample data just run these SQL queries:

REPLACE INTO mailserver.virtual_domains (id,name) VALUES ('1','example1.com');

REPLACE INTO mailserver.virtual_users (id,domain_id,password,email)
 VALUES ('1', '1', '{BLF-CRYPT}$2y$05$4OM6TTQ45gXcMcgtfiwdd.FwaeY8lOu3xaYxChjmg/vpyOPTnEpTK', 'user1@example1.com');


REPLACE INTO mailserver.virtual_aliases (id,domain_id,source,destination)
 VALUES ('1', '1', 'postmaster@example1.com', 'user1@example1.com');

The string used in the password column is a secure hash of the simple password SecurePass and is generated using the following command:

  doveadm pw -s BLF-CRYPT

Once you have installed Dovecot you can try that yourself but you will get a different output. The reason is that the passwords are salted to increase their security.

Don't forget to delete this sample data before putting your mail server into production. With the delete cascade, you only need to delete the virtual domain. The alias and mailbox will be automatically deleted. Here is the SQL query you need to run before putting your mail server into production:

DELETE FROM mailserver.virtual_domains WHERE name='example1.com';