Prepare the database

Now it’s time to prepare the MariaDB database that stores information controlling your mail server. In the process you will have to enter SQL queries – the language of relational database servers. You may enter them on the ‘mysql’ command line. 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 restart apache2

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

You will not be able to login yet. The only available database user is ‘root’ and MariaDB prevents you from using it with a password.

Create the ‘mailserver’ database

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

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

MariaDB [(none)]>

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

CREATE DATABASE mailserver;

Create the database users

Now that you have an empty database you can create a database user that has administrative privileges on it. Let’s call it ‘mailadmin’. And also let’s have another user called ‘mailserver’ with only read privileges.

Each user needs a good random password. Use the pwgen tool to create two random passwords:

pwgen -s1 30 2

Take a note of the passwords. Connect to the database again using the ‘mysql’ command.

mysql

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

MariaDB [(none)]>

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';

Now you can use Adminer to log in using that account:

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 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.

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.

Why am I doing this to you? The reason is that Postfix runs in a restricted environment for security reasons. Its home directory is /var/spool/postfix and it cannot access anything outside of that directory on your server. So the MariaDB socket at /var/run/mysqld/mysqld.sock is inaccessible for Postfix. That’s the reason why we use TCP communication to 127.0.0.1 instead of socket communication. Phew… crazy. 🙂

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.

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.

Column

Purpose

id

A unique number identifying each row. It is added automatically by the database.

virtual_users

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

Column

Purpose

id

A unique number identifying each row. It is added automatically by the database.

domain_id

Contains 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.

email

The email address of the mail account.

password

The hashed password of the mail account. It is prepended by the hashing algorithm. 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.

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 means that there is no limit.

virtual_aliases

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

Field

Purpose

id

A unique number identifying each row. It is added automatically by the database.

domain_id

Contains the number of the domain’s id in the virtual_domains table. A “delete cascade” makes sure that if a domain is delete that all user accounts in that domain are also deleted to avoid orphaned rows.

source

The 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”.

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.

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.

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”. You can try that yourself and 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:

particularly it was preferred to use postgres, but I think it is logical what you say, I do not want to get tangled up doing something that is not in the guide, but it was great to add a data of how to mount a server with postgres engine even if it is 0

I particularly prefer to use postgres, but I think it is logical what you say, I do not want to get tangled up doing something that is not in the guide, but something good would be to add a data of how to mount a server with postgres engine even if it is 0

If any of the content on workaround.org has made your daily life less miserable you are invited to donate via Paypal to email@christoph-haas.de. I also have a wish list of Amazon things for my projects if you would like to surprise me. However please don't feel obliged.