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

30 thoughts on “Preparing the database”

  1. Adon

    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
  2. Panos Angel

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

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

    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
    1. Christoph Haas

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

      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!

      Reply
  6. rucksman

    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
    1. Christoph Haas

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

      Reply
  8. aj

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

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

    Reply
  10. Jost

    Hi Christoph!
    For creating the database users, you use “localhost” in one command, and “127.0.0.1” 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, “127.0.0.1” 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 127.0.0.1 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.

    Reply
    1. Christoph Haas

      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.

      Reply
  13. Gab

    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:
    [code]
    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
    [/code]
    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 !
    Regards

    Reply
  14. Florian

    Hallo Christoph,

    seit diesem Setup (oder ich mache was falsch) kann man aber keine Weiterleitungen mehr auf z.B. Google machen. Eine Weiterleitung info@domain.de an test@google.com returned immer:

    550-5.7.1 This message does not have authentication information or
    fails to pass 550-5.7.1 authentication checks. To best protect our users
    from spam, the 550-5.7.1 message has been blocked. Please visit 550-5.7.1
    https://support.google.com/mail/answer/81126#authentication for more 550
    5.7.1 information. x20si3877492wmh.163 – gsmtp (in reply to end of DATA
    command)

    Viele Grüße

    Florian

    Reply
    1. Christoph Haas

      Moin Florian. Über Google könnte ich mich den ganzen Tag aufregen. Oft drosseln sie mich. Dann nehmen sie mal wieder einen halben Tag lang gar keine Mails mehr von mir an. Ich habe schon mehrere Anfragen an deren Postmaster-Team gestellt. Glaub mal nicht, dass ich auch nur einmal eine Reaktion von denen bekommen hätte. Google ist die Pest.
      Okay, genug aufgeregt. 🙂 Sie sagen dir nicht, was die Ursache ist. Du hast mehrere Möglichkeiten:
      – schaff dir einen SPF-TXT-Eintrag an
      – nutze DKIM
      – registriere dich bei Google mit deiner Domain
      – stell sicher, dass du TLS als Transportverschlüsselung an hast
      Gruß,
      Christoph

      Reply
      1. Florian

        Ich hab schon einiges probiert aber sobald eine Weiterleitung im Spiel ist blocken die komplett. Total schade das ganze und keine Ahnung wie ich das lösen soll, da das für mich wichtig ist. Will grad nen neuen Server aufbauen da der alte auch schon etwas in die Jahre gekommen ist, aber der Schritt blockt mich gerade. DKIM nutze ich. SPF Eintrag nutze ich. Wie man sich bei Google mit ner Domain registriert: Keine Ahnung. TLS ist auf alle Fälle an. Aber die Politik von Google ist echt richtig nervig…

        Gruß Florian

        Reply
        1. Christoph Haas

          Achte drauf, dass die Weiterleitung nicht bedingungslos alle Mails weiter schickt. Sonst schickst du Spam an Google und machst sie bockig. Am besten die virtual_aliases gar nicht benutzen und das ganze nur per Sieve-Regeln weiterleiten. Dann werden Spam-Mails vorher rausgefiltert.

          Bzgl. Google: https://postmaster.google.com

          Reply
  17. Mike Goodman

    If the user’s password is encrypted/salted prior to entry into the table how do users change their passwords? The process looks illogical.

    Reply
  18. Mike Goodman

    “dovecot pw -s SHA256-CRYPT” to create a secure hash of the simple password “summersun” needs the -p flag before the pasword.

    Reply
  19. Melik

    Hi Christoph,

    I believe the word “mailserver” is supposed to be “mailuser” in the following sentence:

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

    Reply
  20. Andi

    Let’s say that I put an external email address in the destination field in virtual_aliases, would it then forward the email to another mail server or will this only work internally on the same mail server?

    Reply
    1. Andi

      I just decided to test it and it seems like it is able to relay it to another server! 🙂

      Reply
  21. peek

    I’m being plagued by:

    Package phpmyadmin is not available, but is referred to by another package.
    This may mean that the package is missing, has been obsoleted, or
    is only available from another source

    E: Package ‘phpmyadmin’ has no installation candidate

    when doing a “apt-get install phpmyadmin” even after a “apt-get update” & “apt-get upgrade”.

    /etc/apt/sources.list contains:

    deb http://ftp.au.debian.org/debian/ stable main
    deb-src http://ftp.au.debian.org/debian/ stable main

    deb http://security.debian.org/debian-security stable/updates main
    deb-src http://security.debian.org/debian-security stable/updates main

    # stable-updates, previously known as ‘volatile’
    deb http://ftp.au.debian.org/debian/ stable-updates main
    deb-src http://ftp.au.debian.org/debian/ stable-updates main

    It seems I missed some memo on phpmyadmin with buster …

    Reply

Leave a Reply

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

Scroll to Top