Skip to content

Database setup

Now it’s time to prepare the MariaDB database that stores the information that controls your mail server. In the process you will have to enter SQL queries – the language of relational database servers. You may enter them in a terminal window using the ‘mariadb’ command.

This step is simple. Connect to the database using the mariadb command in your shell:

Run this on your server
mariadb

You should see the MariaDB prompt that allows you to enter further SQL commands:

MariaDB [(none)]>

To create the new database, send this SQL command:

CREATE DATABASE mailserver;

In this section you will create the basic database called mailserver and two users:

UserPermissionsPurpose
mailserverreadUsed by Postfix/Dovecot
mailadminread/writeUsed by you
Used by Roundcube (for password changes)

Create the database users:

grant all privileges on mailserver.* to 'mailadmin'@'localhost' identified by 'MAILADMIN-PASSWORD-HERE';
grant select on mailserver.* to 'mailserver'@'127.0.0.1' identified by 'MAILSERVER-PASSWORD-HERE';

By now you have an empty database and two user accounts to access it. Now you need three tables:

  • virtual_domains
  • virtual_aliases
  • virtual_users

Let’s create the entire database schema in one go:

USE mailserver;
CREATE TABLE IF NOT EXISTS `virtual_domains` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY (`name`)
);
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
);
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
);

The first table keeps the list of domains that your mail server will be responsible for. Each row in this table represents a domain.

  • id: A unique number identifying each row. It is set automatically.
  • name: Domain name.

The second table contains information about your users – or rather: your mailboxes. Each mail account requires one row in this table.

  • id: A unique number identifying each row. It is set automatically.
  • domain_id: A reference to the domain in the virtual_domains table. So each domain can have many mailboxes.
  • email: The email address of the mailbox.
  • password: The hashed password of the mail account. It is prepended by the password scheme. By default it is {BLF-CRYPT} also known as bcrypt which is considered very secure. Older ISPmail guides used {SHA256-CRYPT} or even older crypt schemes. Prepending the password field the hashing algorithm in curly brackets allows you to have different kinds of hashes. So you can easily migrate your old passwords without locking out users. Users with older schemes should get a new password if possible to increase security.
  • quota: The number of bytes that this mailbox can store. You can use this value to limit how much space a mailbox can take up. The default value is 0 which applies the default quota. This is an optional feature that is discussed later.

The third table contain optional forwardings from one email address to another – or several other – addresses.

  • id: A unique number identifying each row. It is set automatically.
  • domain_id: References the domain in the virtual_domains table.
  • source: The alias email address.
  • destination: The email address where this alias gets forwarded to.

Too much theory so far? I can imagine. Let’s populate the database with an example.org domain, a john@example.org email account and a forwarding of jack@example.org to john@example.org. We will use that information in the next chapter to play with.

To add that sample data just run these SQL queries:

REPLACE INTO virtual_domains
(id, name)
VALUES (10,'example.org');
REPLACE INTO virtual_users
(id, domain_id, password, email)
VALUES (
1,
10,
'{BLF-CRYPT}$2y$05$.WedBCNZiwxY1CG3aleIleu6lYjup2CIg0BP4M4YCZsO204Czz07W',
'john@example.org');
REPLACE INTO virtual_aliases
(id, domain_id, source, destination)
VALUES (
1,
10,
'jack@example.org',
'john@example.org');

Do you wonder how I got the long cryptic password? I ran…

doveadm pw

…to create a secure hash of the simple password “summersun”. 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. Every time you will get a different hash. That prevents reverse-engineering the original password.