Preparing the database

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 ‘mysql’ command. But if you are less experienced with SQL you may prefer using a web interface. That’s what you installed Adminer for.

Why not PostgreSQL?

Several comments show that some system administrators prefer the PostgreSQL database engine over MariaDB. Count me in. So why is this guide still using MariaDB/MySQL? Mainly for historical reasons. I want to spare you any unneccessary changes when migrating to a new version. If you are familiar with PostgreSQL however you can easily adapt this guide to PostgreSQL. There are dovecot-pgsql and postfix-pgsql packages. And Adminer works with PostgreSQL as well.

Setting up Adminer

Basically Adminer is just a couple of PHP files served from your Apache web server. The setup is simple. Edit your /etc/apache2/sites-available/webmail.example.org-https.conf file and put this line anywhere between the <VirtualHost> and the </VirtualHost> tags:

Alias /adminer /usr/share/adminer/adminer

Reload the Apache process:

systemctl reload apache2

Open your web browser and point it to “https://webmail.example.org/adminer”. It will show the login form. Good.

Security

Having an SQL admin interface publicly available on your web site is an invitation for internet scoundrels to do bad things. Consider protecting the /adminer location by an additional password. The Apache documentation shows you how to do that. Or use a less obvious path than “/adminer” in the Alias.

You will not be able to login yet. The only available database user is ‘root’, but it is only usable from the shell – not over a network.

Generate two random passwords

In this section you will create the basic database “mailserver” and two users. One user (“mailadmin”) will be able to change the data in the database and is meant for you. The other user (“mailserver”) can only read from the database and is meant for the server processes.

Use the pwgen tool to create two random passwords for these users:

pwgen -s1 30 2

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

Create the ‘mailserver’ database

This step is simple. Connect to the database using the ‘mysql’ command:

mysql

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

MariaDB [(none)]>

Now you are expected to speak SQL. To create a database enter:

CREATE DATABASE mailserver;

You will be told that your query was OK and that one new row was added.

Create the database users

Now you have an empty database. Let us give the “mailadmin” database user the required privileges to manage it.

You are still connected to the database, right? To create a user with full permissions enter this SQL command. Please use the first password you just generated instead of mine:

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

Also create the read-only user that will grant Postfix and Dovecot database access later (use your second random password here).

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

Wait a minute. Why is there “127.0.0.1” instead of “localhost” in the second SQL command? Is that a typo? No, it’s not. Well, in network terminology those two are identical. But 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.

When you use Adminer you will have to use ‘localhost’ as a database server when using the ‘mailadmin’ user but ‘127.0.0.1’ when using the ‘mailserver’ user.

Now you can use Adminer to log in using the mailadmin account and the first password:

You should get logged in and see the “mailserver” database:

Creating the database tables

Do you remember that I introduced three Postfix mappings earlier? One for virtual domains, one for virtual aliases and another for virtual users? Each of the mappings needs a database table that you will create now. Feel free to use Adminer. I will however also show the SQL statement to create the tables that you can enter on the ‘mysql’ command-line tool. Below you can click on either [Adminer] or [SQL] to choose.

The first table to create is…

virtual_domains

This table just holds the list of domains that you will use as virtual_mailbox_domains in Postfix.

ColumnPurpose
idA unique number identifying each row. It is added by the database automatically.
nameThe name of the domain you want to receive email for.
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;

virtual_users

The next table contains information about your users. Each mail account takes up one row.

ColumnPurpose
idA unique number identifying each row. It is added by the database automatically.
domain_idContains the number of the domain’s id in the virtual_domains table. This is called a foreign key. A “delete cascade” makes sure that if a domain is deleted that all user accounts in that domain are also deleted to avoid orphaned rows.
emailThe email address of the mail account.
passwordThe 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. Previous 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.
quotaThe 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 means that there is no limit.
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;

virtual_aliases

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

FieldPurpose
idA unique number identifying each row. It is added by the database automatically.
domain_idContains the number of the domain’s id in the virtual_domains table again.
sourceThe email address that the email was actually sent to. In case of catch-all addresses (that accept any address in a domain) the source looks like “@example.org”.
destinationThe email address that the email should instead be sent to.
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;

As described in the section about domain types there can be multiple targets for one source email address. You just would need to insert several rows with the same source address and different destination addresses that will get copies of an email. Postfix will consider all matching rows.

Example data to play with

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 mailserver.virtual_domains (id,name) VALUES ('1','example.org');

REPLACE INTO mailserver.virtual_users (id,domain_id,password,email)
 VALUES ('1', '1', '{BLF-CRYPT}$2y$05$.WedBCNZiwxY1CG3aleIleu6lYjup2CIg0BP4M4YCZsO204Czz07W', 'john@example.org');

REPLACE INTO mailserver.virtual_aliases (id,domain_id,source,destination)
 VALUES ('1', '1', 'jack@example.org', 'john@example.org');

Do you wonder how I got the long cryptic password? I ran “doveadm pw -s BLF-CRYPT” 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 to increase their security.

Remember to remove that sample data before you go live with your mail server. Thanks to the delete cascade you just need to remove the virtual_domain. The alias and the mailbox will be deleted automatically. This would be the SQL query you should run before taking your mail server into production:

DELETE FROM mailserver.virtual_domains WHERE name='example.org';

5 thoughts on “Preparing the database”

  1. For anyone who uses postgresql instead:

    CREATE TABLE IF NOT EXISTS virtual_domains ( id integer primary key generated by default as identity, name varchar(50) not null);
    CREATE TABLE IF NOT EXISTS virtual_users ( id integer primary key generated by default as identity, domain_id integer NOT NULL, email varchar(254) NOT NULL, password varchar(150) NOT NULL, quota bigint NOT NULL DEFAULT 0, UNIQUE (email), FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE );
    CREATE TABLE IF NOT EXISTS virtual_aliases ( id integer primary key generated by default as identity, domain_id integer NOT NULL, source varchar(254) NOT NULL, destination varchar(254) NOT NULL, FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE );

  2. Robbert Harms

    For the virtual_aliases, does the domain_id refer to the source or to the destination address? Suppose I forward mails from domain A to domain B, which domain_id would I put in the virtual_alias?

    Thanks again for this wonderful guide. I have been using it for 10 years now.

    1. I think I know the answer, these virtual_aliases are only within one domain, you can not use them to forward to another domain.

      1. Christoph Haas

        You can forward to any domain you like. The domain_id refers to the source domain.

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll to Top