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:

ispmail-jessie-install-packages-phpmyadmin-dbconfig

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:

ispmail-jessie-install-packages-phpmyadmin-dbconfig-adminuser

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

ispmail-jessie-install-packages-phpmyadmin-config-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:

ispmail-jessie-install-packages-phpmyadmin-loginform

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 “127.0.0.1”. 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 “127.0.0.1” then it will create a network connection talking to the TCP socket on port 3306 on your server. So our database user ‘mailserver’@’127.0.0.1’ 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 ‘127.0.0.1’ 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” (@127.0.0.1 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:

CREATE DATABASE mailserver;

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

For our purposes we need three tables:

virtual_domains

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

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
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

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

10 thoughts on “Preparing the database

  • 2018-01-26 at 01:39
    Permalink

    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

    Reply
  • 2018-01-31 at 21:38
    Permalink

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

    Reply
  • 2018-02-05 at 14:42
    Permalink

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

    Reply
  • 2018-02-13 at 14:46
    Permalink

    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!

    Reply
  • 2018-03-07 at 20:04
    Permalink

    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:

    Reply
    • 2018-03-07 at 20:35
      Permalink

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

      Reply
  • 2018-03-11 at 20:43
    Permalink

    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…

    Reply
    • 2018-03-11 at 21:24
      Permalink

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

      Reply
  • 2018-06-29 at 19:31
    Permalink

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

    Include /etc/phpmyadmin/apache.conf

    to

    /etc/apache2/apache2.conf

    Reply

Leave a Reply

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