Prepare 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’ 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. 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 database access 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 the mailadmin 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 (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.

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 by the database automatically.
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 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.

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

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. 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 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';
Next page: Making Postfix get its information from the MariaDB database

12 thoughts on “Prepare the database”

  1. Petr

    Hello,
    First, thanks a LOT for this wonderful guide.

    I used it to set up my own personal server, and works like a charm.

    The only thing I’m missing in it, for my needs, is info about setting up a mailing lists using the virtual addresses.
    In my case, I use mailman for a few private small mailing lists for me and my friends (their emails are external from my server), and I fail miserably in setting the proper aliases, which would pass the incoming emails to the mailman processing, and sending the copies to each member of the list.
    I was able to somehow set it up on my old server with mailman2, but now on Bullseye I’d like to use mailman3 , and I’m not able to set it up as working.

    Would you have any experience with this, and would you be able to either help me, or (which would be best) add a section on this topic?

    Thanks a lot for any reaction.

    Reply
  2. Tachi

    I personally find the reasoning behind using MySQL instead of PostgreSQL a bit weak. If a user needs to migrate to a new version of the guide and switch to Postgres from MariaDB you could add a small section in the relevant section about the change (as migrating a database this simple should be straightforward). Apart from the performance benefits, Postgres is also easier to use in some cases (you don’t need to deal with engines, charsets, etc.), and would be really beneficial to new users following the guide for the first time.
    That being said, thanks for your awesome content 🙂

    Reply
  3. Brad

    As one not deeply familiar with SQL commands, I found Adminer to be a useful tool for quick and easy verification that the commands I entered worked correctly, and that I didn’t make typos when naming new items.

    Reply
  4. Xavier

    Thanks for this excellent guide!

    I have two questions related to the data in the MariaDB:

    1. Is the alias table lookup recursive ? For example, if I have two aliases: a@example.org -> b@example.org, and b@example.org -> me@example.org, will any email sent to a@example.org be delivered into me@example.org mailbox ?

    2. Why is the use of the “domain_id” column in the virtual_aliases table ? It seems not to be used in the query… If it is nevertheless useful, which domain_id should I use when I create an alias from a@example.com (domain_id = 1) to a@example.org (domain_id = 2) ?

    Thanks a lot!

    Reply
  5. Hennie van Riel

    Hello Christoph,

    Again, thanks a lot for setting up this thorough manual. It’s been my guide for several project a ran into.

    Could it be that the statement, mentioned just above the small screenshot, mentioning:

    grant select on mailserver.* to ‘mailserver’@’127.0.0.1’ identified by ‘some-password’;

    Should not be:

    grant select on mailserver.* to ‘mailuser’@’127.0.0.1’ identified by ‘some-password’;

    It just triggered me, since I’m running a bullseye Postfix/Dovecot/Roundcube box, since your first bullseye manual, but I’m having some issues regarding ‘virtual_aliases’. So, I’m following your manual again from page 1, and especially take note of any differences compared to previous bullseye manuals.

    Thanks again,

    Reply
  6. Tony

    Hi Christopher,

    First of all; thanks for the excellent guide….

    This has been asked (many years ago) and answered before but I am gonna ask again. Are there any plans to make the SQL queries Postfix Admin compatible?

    Once you said you don’t like Postfix Admin too much since it’s lacking too many functionalities. Considering that it has been 6 years since then, do you still think it’s lacking that many functionalities?

    And finally; what are you using yourself for management; web based or not.

    Thanks….

    Reply
    1. Christoph Haas

      Hi Tony, thanks for the feedback. PostfixAdmin hasn’t really developed. But I totally agree that we need something. I’m managing my few users hardcore-style with SQL queries at the moment. And it hurts. 🙂

      That’s why two weeks ago I finally started developing a web application. It will specifically help with the administration of an ISPmail-style server. Of course it will manage domains, mailboxes and aliases. But it will also manage DKIM signatures, MX records, DMARC and SPF records, check if your server is on common blacklists, monitor your TLS certificate, parse mail.log files per domain, check disk space etc.

      So that’s my side project at the moment. And I’m really looking forward to finally managing users like an adult… and not like a stoned nerd.

      Reply
      1. Sebastian Kalicki

        Hey Christopher,

        thanks for the excellent guide!
        I am also working on an AdminTool. Maybe we can sit together and talk a little bit about it.

        Thanks again,
        Sebastian

        Reply
  7. Steve

    Hi, I’m following this guide and found that when enabling adminer as per your configuration, it resulted in a ‘403 forbidden’ error.

    Checking the README for the debian package, it advises to simply enable the configuration: a2enconf adminer which enables the configuration in /etc/apache2/conf-available/adminer.conf.

    Removing the lines in /etc/apache2/sites-available/webmail.example.org-https.conf then allowed it to load.

    — Steve

    Reply
    1. Bill

      I also did it this way as Steve mentioned: “$ sudo a2enconf adminer” then you will find adminer at https:///adminer.

      As mentioned in the text having a public interface to your database is an issue, I locked it down to my static IP by updating the Directory section of /etc/apache2/conf-available/adminer.conf to look like below:

             Require all granted
             DirectoryIndex conf.php
             Order Deny,Allow
             Deny from all
             Allow from {Your IP Address}

      To test it put in a random IP address and make sure you’ve blocked yourself, the put in your real IP and it should work for you.

      Reply
  8. Naveen

    Hi, Instead of using MariaDB is it possible to use Microsoft Azure Active directory ? so that I do not need to maintain a seperate entry of users in Maria DB and Microsoft AD. If I could ask postfix to query ldap / Microsoft AD (Active directory) then all the users are in one place and the users could use my web application and also e-mail. Thoughts please ?

    Reply
  9. shukuru

    Hi,

    First, thanks for this guide 🙂

    I would like to add a note about granting privileges for rw and ro users. I have to restart MariaDB server to make connection work through Adminer.

    Also I think that enabling logging is a pretty nice action for debugging some database interactions.
    On Debian 11 this can be enabled in `/etc/mysql/mariadb.conf.d/50-server.cnf` (log rotation is on). The log will be findable in `/var/log/mysql` directory.

    Thanks !

    Reply

Leave a Reply

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

Scroll to Top