[2019-11-25]

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

20 thoughts on “Prepare the database

  • 2019-12-29 at 08:49
    Permalink

    Hello Christoph,

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

    Reply
    • 2019-12-29 at 11:42
      Permalink

      I firmly believe it’s there. Search for “create database”. 🙂

      Reply
      • 2019-12-29 at 12:10
        Permalink

        Oh yeah I’ve over read that part. :/

        Reply
  • 2019-12-29 at 14:48
    Permalink

    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
    • 2019-12-29 at 15:15
      Permalink

      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
  • 2020-01-03 at 04:13
    Permalink

    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
  • 2020-01-03 at 04:16
    Permalink

    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
  • 2020-01-12 at 19:08
    Permalink

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

    Reply
    • 2020-01-14 at 12:00
      Permalink

      Indeed. Thanks.

      Reply
  • 2020-01-28 at 10:48
    Permalink

    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
    • 2020-01-28 at 16:00
      Permalink

      That is indeed a good idea. INT goes up to 4 GB only which is not much.

      Reply
      • 2020-03-01 at 17:22
        Permalink

        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
  • 2020-03-27 at 12:54
    Permalink

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

    Reply
  • 2020-04-10 at 21:11
    Permalink

    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
    • 2020-04-12 at 11:48
      Permalink

      Ouch, that totally slipped through. Thanks for spotting it.

      Reply
  • 2020-04-22 at 14:56
    Permalink

    What do you think about running:
    mysql_secure_installation

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

    Reply
  • 2020-05-11 at 22:04
    Permalink

    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
  • 2020-06-16 at 01:31
    Permalink

    Any reason not to use SQLite for a <100 user installation?

    Reply
    • 2020-06-18 at 15:55
      Permalink

      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

Leave a Reply

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