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.

ColumnPurpose
idA unique number identifying each row. It is added automatically by the database.
nameThe name of the domain you want to receive email for.

This SQL statement creates a table like that:

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. Every mail account takes up one row.

ColumnPurpose
idA unique number identifying each row. It is added automatically by the database.
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 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.
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.

This is the appropriate SQL query to create that table:

USE mailserver;
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 automatically by the database.
domain_idContains 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.
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.

This is the required SQL query you need to run:

USE mailserver;
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.

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

DELETE FROM mailserver.virtual_domains WHERE name='example.org';
Next page: Miscellaneous Articles

24 thoughts on “Prepare the database”

  1. Benedikt

    Hello Christoph,

    the information about creating the mailserver database before start using it, is missing on this page.

    Reply
  2. LadyLinux

    Hiya,

    Fantastic guide

    On Debian “Buster” I get this when I try to create salted passwords

    “Fatal: Unknown scheme: BLV-CRYPT”

    I think its suppose to be “BLF-CRYPT” right ??

    Other than that I am having a grand time with this guide. Learning things also and for a near 30 year experienced Unix/Linux admin that is saying something.

    Bravo!!

    LadyLinux

    Reply
    1. Christoph Haas

      Completely right. Thanks. Typo fixed. Great to hear you even enjoy the guide. I expected it’s mostly work for most and requires more patience than the average sysadmin has. 🙂

      Reply
  3. Azazel Mendoza

    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

    Reply
  4. Azazel Mendoza

    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

    Reply
  5. Ulf

    I guess that “service apache2 reload” should read “systemctl reload apache2” here as well?

    Reply
  6. ensarman

    I recommend to put a major limit in the quota field, int(11) is not enough because the MySQL int type

    its better to use bigint to set a quota with more than 1 gigabyte

    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;

    Reply
      1. Tomas

        Hi,
        I think
        `quota` BIGINT UNSIGNED NOT NULL DEFAULT 0,
        should be used here. Is there a reason we would need a negative value (in Bytes)?
        Thank you for a great guide!

        Reply
  8. Thierry Dumont

    Very impressive guide.
    Question: I will serve very few accounts: so is there any problem using sqlite in place of mariadb?
    thanks again!

    Reply
  9. Marjorie

    Shouldn’t the password hashing function “dovecot pw -s BLF-CRYPT” be “doveadm pw -s BLF-CRYPT”?

    At least that’s what works for me.

    Reply
  10. Tom

    What do you think about running:
    mysql_secure_installation

    It looks like it contains a few sane recommendations for securing the database.

    Reply
  11. Martin

    Hallo Christoph,
    gibt es ein PHP Script bzw. Code um ein Passwort zu generieren?

    Auf der Konsole allein bringt es nicht und ich habe über die Tante nicht gefunden.

    Danke

    Reply
    1. harshness

      I suppose that the practical limitation on SQLite is the fact that the tables are locked on writes. This probably isn’t a concern unless you’re adding/modifying users like a crazy person.

      Reply
  13. Thomas

    Hey Christoph,
    “ON DELETE CASCADE” should be used with caution. If someone deregister or transfer a domain, the whole account would be deleted. I had exactly such a problem with a mail hoster. I was on the way transfering my domain to a new hoster and was waiting for the “KK-Antrag” to be finished.

    My plan was to wait with the final backup till the domain was transfered and the DNS entry showing on the new server. So no new mails would be delivered. You can imagin what happend after the “KK-Antrag” was finished. My account was deleted. The system could not handle a transfer. My mail package term of contract should run for about two weeks but was killed due to such a system.

    Fortunately I have done some Backups before and had lose only few mails but with such a DELETE ON CASCADE the user has no chance to migrate to another hoster.

    This technique is good to remove things like invoice positions when the invoice was deleted. Or when a user was deleted to remove additional informations like address data. This here looks like removing a user will remove all linked invoices from the system.

    Reply
  14. Richard Sarain

    Hi,
    Excellent tutorials, I have used them for nearly 10 years and like fine wine, they get better and better!

    I had a small problem with too many redirects on Opera and Firefox when trying to access Adminer.

    I fixed it by adding this to the xxx-https.conf file:

    RewriteCond %{HTTPS} off

    Is this correct?

    Kind regards
    Richard

    Reply
  15. superkuh

    I finished the mailserver setup using this guide and it’s working great. I’d like to add another virtual_user now to the virtual domain I set up earlier. Can I just open mysql and add the two lines below (where there ID is 5 because back during setup I had 4 other IDs)?

    REPLACE INTO mailserver.virtual_users (id,domain_id,password,email)
    VALUES (‘5’, ‘1’, ‘{BLF-CRYPT}$2etcetcetcetcetcetcetcetceetc’, ‘newvirtualuser@mydomain.com’);

    Is that it? Will the vmail dirs be made automatically? Do I have to do anything else with postconf to get postfix to “process” the new database virtual_user at the existing virtual_domain?

    I assume since all the mappings are set up already for postfix adding the database entries are all I need. But before I do so I’d like to be sure.

    Reply
  16. Vince

    How does one get MariaDB to listen to both localhost (socket) and 127.0.0.1 (tcp)? Is there a way to get it to listen to both without using 0.0.0.0 to open to whole world?

    Reply

Leave a Reply

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

Scroll to Top