Preparing 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 MySQL you may prefer using a web-based tool called PHPMyAdmin to manage your database’s data.

Setting up PHPMyAdmin

Install the PHPMyAdmin package:

apt install phpmyadmin

PHPMyAdmin creates an own small SQL database on your server to store configuration data. So you will get asked:


The installer will also create a database access user to allow PHPMyAdmin to access its own database. That password does not matter much to you. Just press “Enter” to get a randomly created password:


You will also get asked which web server software you want to use. Select Apache:


The installer will create a file /etc/phpmyadmin/apache.conf that needs to be included in your HTTPS virtual host so you can access with your web browser. Edit the file /etc/apache2/sites-available/webmail.example.org-https.conf and put this line anywhere between the <VirtualHost> and the </VirtualHost> tags but before any other “Include” lines:

Include /etc/phpmyadmin/apache.conf

I strongly recommend disabling PhpMyAdmin for any other virtual hosts you are running:

a2disconf phpmyadmin

Otherwise all of your web sites will provide a /phpmyadmin path. That may be confusing and even tempt you to use it on a HTTP (=unencrypted) connection accidentally. If possible you should further restrict access to PHPmyAdmin for security reasons.

Reload the Apache process:

service apache2 reload

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


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

Note: PhpMyAdmin by default connects to the database at “localhost” – which is the same server. We will create a database user “mailadmin” who will be able to connect to “localhost”. However the user “mailserver” which is used by the server processes will connect to “”. Wait, what? Isn’t that the same as “localhost”? Usually it is. But MariaDB and MySQL make a difference. 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 “” then it will create a network connection talking to the TCP socket on port 3306 on your server. So our database user ‘mailserver’@’’ cannot be used in PhpMyAdmin (unless we alter its configuration). But the user ‘mailadmin’@’localhost’ can. Are you still with me? So why do we use ‘’ then anyway? The reason is that Postfix will be restricted to its home directory at /var/spool/postfix. And as the “mysqld.sock” socket lives outside of that directory we need to use a TCP communication to break out of the jail. Phew… crazy. 🙂

Setting up the database schema

Your database will be accessed by two database user accounts:

  • “mailuser” (@ via TCP) will get read access to the database. Postfix and Dovecot will use it to retrieve information about email domains and users.
  • “mailadmin” (@localhost via socket) will get read and write access. You can use it via PhpMyAdmin to manage the database.

Use the “pwgen” command to get two good random passwords for these accounts:

pwgen -s 25 1

Creating the database and schema would be tedious if done with PHPMyAdmin. We will just feed the proper SQL commands to the MySQL server to create the database and tables. A table in database terms is pretty much the same as a spreadsheet. You have rows and columns. And columns are also called fields. SQL statements can be entered in the “mysql” shell that you get when you run the “mysql” command as root on your server:

root@mail:~# mysql

Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 230935
Server version: 10.1.26-MariaDB-0+deb9u1 Debian 9.1

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Now you are connected to the database and can issue SQL commands. First create the database:


We also need to create a MySQL user called ‘mailuser’ that gets access to that database. Instead of “ChangeMe” use the password you created with “pwgen” earlier.

CREATE USER 'mailuser'@'' IDENTIFIED BY 'ChangeMe1';

…and a user to allow you to manage the database:

CREATE USER 'mailadmin'@'localhost' IDENTIFIED BY 'ChangeMe2';

The admin needs read and write access to the database:

GRANT ALL ON mailserver.* TO 'mailadmin'@'localhost';

The other account just needs read permissions:

GRANT SELECt ON mailserver.* TO 'mailuser'@'';

For our purposes we need three tables:


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

Field Purpose
id A unique number identifying each row. It is added automatically by the database.
name The 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,


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

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 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 {SHA256-CRYPT} encrypted. But you may have legacy users from former ISPmail installations that still use {PLAIN-MD5}. Adding the hashing algorithm allows you to have different kinds of hashes.

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,
 PRIMARY KEY (`id`),
 UNIQUE KEY `email` (`email`),
 FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE


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

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

Let’s populate the database with the example.org domain, a john@example.org email account and a forwarding of jack@example.org to john@example.org. 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', '{SHA256-CRYPT}$5$M/GWzmtjsLroRWIf$0qDt7IgXCoCQyvQZO7v.NC46rXzFVBSkZcilijlwgL7', '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 “dovecot pw -s SHA256-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.

Before going live with your mail server in the end you should remove that data again using this simple SQL query:

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

18 thoughts on “Preparing the database

  • 2018-01-26 at 01:39

    Hi Christoph,
    Do you have a recommendation regarding ViMbAdmin. I’ve found it very handy in the past, and it appears to have remained updated (last commit was mid 2017).

    Seems like a nice alternative to have GUI for the above table edits.
    Just curious your opinion

  • 2018-01-31 at 21:38

    Hallo Christoph,
    It is needless to say that you have done a great job with these guides. I’ve followed them multiple times in the past. By far the best source for setting up an email server in the web.

    In the “Setting up PHPMyAdmin” section where you write `However the user “mailserver”` I guess you refer to “mailuser” instead.

    Thank you a lot!!

  • 2018-02-05 at 14:42

    pwgen -s 25 1 –> pwgen -s 25 2
    two passwords are needed

  • 2018-02-13 at 14:46

    here is the lightest weight and easiest bit of feedback you’ll ever get..

    Took a few trys to get out of mysql.. others may appreciate the “exit” command to get out!

  • 2018-03-07 at 20:04

    Hi Christoph,

    I’ve used your great ISPMail “ported” to OpenSuse, and it’s running for more than two years now, perfectly. Small mail server on my private VPS, just a couple of mailboxes.

    So I’m fine with adding users by SQL INSERT statements, and it’s only a little bit a pity that updating pw’s does e.g. not work with roundcubemail, would be cool.

    So my general question is: Why have you “re-invented the wheel” with an own SQL structure and not used postfixadmin (http://postfixadmin.sourceforge.net/)?

    General question:

    • 2018-03-07 at 20:35

      I’d love to have compatibility with Postfixadmin. However I’m pretty sure the project was started when ISPmail tutorials existed already. It would probably be possible to write a converter to their database scheme. But their concept is a bit different and the web interface looks like from last century (sorry).

    • 2018-09-26 at 09:13

      Hi Michael!

      Getting updating passwords in Roundcube to work is very easy, all you have to do in addition to this guide is grant the ‘mailuser’ update privileges on the passwords column, and then set the appropriate password scheme in the roundcube configuration.

      Has been working on my server for years, and is really easy to use!

  • 2018-03-11 at 20:43

    I totally agree that the UI of Postfixadmin needs an urgent refresh. On the other hand I would also love to have an interface for the administration of the mail server. Maybe Christoph can have a look at ViMbAdmin (https://www.vimbadmin.net/) which has a very, very nice interface. I have not checked whether the database structure is very different…

    • 2018-03-11 at 21:24

      Thanks for the reminder. In fact I had vimbadmin on my list of things to check. Unfortunately they use a different database schema. 🙁

    • 2018-12-11 at 05:10

      Nice!!! I was annoying for that…

  • 2018-06-29 at 19:31

    After installing phpmyadmin I was not able to get webmail.example.com/phpmyadmin to load until I added:

    Include /etc/phpmyadmin/apache.conf



  • 2018-09-26 at 06:29

    Can you also provide me an ansible role to install ISPmail Admin please ? Thnak you

  • 2018-09-26 at 08:40

    Hi Christoph!
    For creating the database users, you use “localhost” in one command, and “” in the next command. Maybe you could elaborate a little on why you do this and what the difference is between the two?

    Both are loopback to the local machine, but they loop back on different layers of the protocol stack (“localhost” is looping directly on layer 7, “” goes down to layer 3 and then loops back).
    This has implications on the route the packages may take! For example, localhost is allowed to use UNIX sockets, while is not, this one must explicitly go through the network stack!

    The MariaDB server can see where a packet is coming from, and will differentiate between the two, so it is important to set up the one that the client actually uses.

    • 2018-10-04 at 08:27

      You seem to have done something unsual. There is no PHP7.2 in Debian’s stable distribution. If you start upgrading packages manually you are doomed to manage your server security by yourself. Besides if you follow the web site you mentioned you are even installing files outside of the Debian package system. Pretty sure that’s a bad idea on a public server.

  • 2018-11-11 at 00:35

    Hi Christoph,
    i follow your guides since long time with success.
    Im writing to you because something of extraordinary happened.
    Dovecot auth-worker looks for a users table i never copied and pasted from these pages.
    That’s what logs say after sent an email to gmail account:
    Nov 11 00:45:27 milik postfix/smtp[2426]: Trusted TLS connection established to gmail-smtp-in.l.googl:25: TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)
    Nov 11 00:45:28 milik postfix/smtp[2426]: 0624840342: to=, relay=gmail-smtp-in.l.googl:25, delay=2.9, delays=1.1/0.03/0.54/1.2, dsn=2.0.0, status=sent (250 2.0.0 OK 1541893528 a12-v6si1910797ejk.197 – gsmtp)
    Nov 11 00:45:28 milik postfix/qmgr[2151]: 0624840342: removed
    Nov 11 00:45:36 milik dovecot: auth-worker(2423): Warning: mysql: Query failed, retrying: Table ‘mailserver.users’ doesn’t exist
    Nov 11 00:45:36 milik dovecot: auth-worker(2423): Error: sql(myuser@mydomain,777.777.777.777,): User query failed: Table ‘mailserver.users’ doesn’t exist (using built-in default user_query: SELECT home, uid, gid FROM users WHERE username = ‘%n’ AND domain = ‘%d’)
    Nov 11 00:45:36 milik dovecot: imap-login: Login: user=, method=PLAIN
    Thanks to the built in default users table dovecot mail gets delivered.
    I have dropped mailserver database and went through all the process again paying attention.
    I have red to trick dovecot-sql-auth by creating a table “users” with same parameter as virtual_users here but i don’t like the idea, something extraordinary happened !


Leave a Reply

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