Preparing the database

Now it’s time to prepare the MySQL 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.

Setting up PHPMyAdmin

Install the PHPMyAdmin package:

apt-get install phpmyadmin

When asked which server you intend to use choose “apache2”:

ispmail-jessie-install-packages-phpmyadmin-config-apache

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

ispmail-jessie-install-packages-phpmyadmin-dbconfig

Answer “yes”. To allow the installer to create that database you are required to enter the MySQL “root” user’s password:

ispmail-jessie-install-packages-phpmyadmin-dbconfig-root

The installer will also create another MySQL user so 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

The installer has prepared 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/default-ssl.conf and put this line anywhere between the <VirtualHost> and the </VirtualHost> tags but before any other “Include” lines:

Include /etc/phpmyadmin/apache.conf

Reload the Apache process:

service apache2 reload

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

ispmail-jessie-install-packages-phpmyadmin-loginform

Log in as ‘root’ with the administrative database password you set previously. Then you will find yourself on the main screen:

phpMyAdmin main screen

This will help you manage your databases. You can either use SQL statements directly or click your way through using the phpMyAdmin web interface.

Setting up the database 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. Or you use PHPMyAdmin and click on the SQL tab at the top and enter the query there.

First create the database:

CREATE DATABASE mailserver;

We also need to create a MySQL user called ‘mailuser’ that gets access to that database. Create another random password using “pwgen -s 25” and run this query:

GRANT SELECT,INSERT,UPDATE,DELETE ON mailserver.* TO 'mailuser'@'127.0.0.1' IDENTIFIED BY 'ChangeMe';

Instead of “ChangeMe” use your own password of course.

For our purpose 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 delete 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”.

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

21 thoughts on “Preparing the database

  • 2015-11-23 at 03:32
    Permalink

    The SQL statement
    “GRANT SELECT,INSERT,UPDATE,DELETE ON mailserver.* TO ‘mailuser’@’127.0.0.1’ IDENTIFIED BY ‘mypassword’;
    does not result in a new user ‘mailuser’ for me.

    I’ve had a look at myphpadmin, and it confirms that no user was created.

    IS there an error in the statement, or am I missing something here. (my SQL IQ = 0)

    Reply
    • 2016-06-23 at 04:12
      Permalink

      You’re right. Create the user first, eg. “CREATE USER mailuser@localhost IDENTIFIED BY ‘mypassword’;”

      Reply
  • 2015-11-23 at 03:36
    Permalink

    Just realized this is an SQL user, not a mail user…so won’t appear in the tables.

    What I am getting to postmap -q example.org mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf
    is:-
    postmap: warning: connect to mysql server 127.0.0.1: Access denied for user ‘mailuser’@’localhost’ (using password: YES)
    postmap: fatal: table mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf: query error: Success

    Reply
    • 2015-11-23 at 09:46
      Permalink

      @Kostas: Do you get any errors when you run that SQL query? Did you use the right password? Why do you use “localhost” instead of “127.0.0.1”? (Yes, that’s a difference even it sounds stupid.)

      Reply
      • 2015-11-24 at 06:43
        Permalink

        Hi Christoph, thanks for replying.

        My postfix/mysql-virtual-mailbox-domains.cf is exactly like the example you gave on the next page, i.e. uses 127.0.0.1, but, when I run the query

        “postmap -q example.org mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf”
        I get
        “warning: connect to mysql server 127.0.0.1: Access denied for user ‘mailuser’@’localhost'”.

        I’m not sure where it is getting mailuser@localhost. The db definitely has the user mailuser @ 127.0.0.1 with privileges SELECT, INSERT, UPDATE, DELETE – I checked with phpMyAdmin.

        I think this is a clue:-
        I can log into mysql with #mysql –user=mailuser –password=password –database=mailserver and that works, but trying to log in as mailuser@127.0.0.1 or mailuser@localhost does not succeed.

        I’m puzzled as to where postmap gets the user mailuser@localhost.

        Then I realised where I made a big mistake – in the example for /etc/postfix/mysql-virtual-mailbox-domains.cf
        user = mailuser
        password = fLxsWdf5ABLqwhZr​ <- use your own database password here
        Well, I only changed the password and left "<- user your…." in the line – not part of the password at all! Now i removed that reran postconf and then tried with postmap example as you suggest and all is well!

        Still not sure where postmap gets mailuser@localhost from….although it now doesn't matter.

        Reply
        • 2016-03-01 at 03:07
          Permalink

          When you specify a host when you grant privileges to a user in mysql the host you grant them from counts as unique thing, never mind that on a networking level localhost should always resolve to 127.0.0.1. Also, some systems using localhost makes mysql prefer using a socket on the filesystem instead of the network access.

          Of course, phpmyadmin uses localhost so if you want to be able to log into phpmyadmin as the mailuser user account you need to double up the grant statements

          GRANT SELECT,INSERT,UPDATE,DELETE ON mailserver.* TO ‘mailuser’@’127.0.0.1’ IDENTIFIED BY ‘mailuserpass’;
          GRANT SELECT,INSERT,UPDATE,DELETE ON mailserver.* TO ‘mailuser’@’localhost’ IDENTIFIED BY ‘mailuserpass’;

          Reply
  • 2016-01-13 at 22:43
    Permalink

    I think you want “doveadm pw -s SHA256-CRYPT” to generate a password for the mail user. You have “dovecot pw”.

    Reply
    • 2016-04-06 at 14:58
      Permalink

      this was my solving to get the problem with the “bad password” error thanks!!

      Reply
  • 2016-01-23 at 05:01
    Permalink

    I am migrating from a previous version (Lenny!) and ran into the new more secure password hashes:
    Is there a way to migrate the existing users to the new system without having all the users change their passwords somehow (or change it for them)?

    Reply
  • 2016-02-03 at 13:11
    Permalink

    On the LAMP box I rent the phpmyadmin was already installed.
    Thus,
    # apt-get install phpmyadmin
    had no effect

    I tried
    #dpkg-reconfigure phpmyadmin
    And it prompted to recreate phpmyadmin database. Since I was not sure if it is the right choice I selected No, And there was no screen with dbconfig-common
    I also tried
    #dpkg-reconfigure dbconfig-common
    and this neither helps.

    Reply
    • 2016-02-03 at 13:29
      Permalink

      I’ve found how to init DB manually:
      mysql -u roundcube -p”**************” roundcube < /usr/share/roundcube/SQL/mysql.initial.sql

      Reply
  • 2016-02-09 at 07:42
    Permalink

    Hi,

    Looking into the DB structure, I can notice that there is no quota per mailbox. I have noticed that in the previous setup you have that. Can you please tell me how to implement different mailbox quota for the mailboxes?

    Thanks in advance.

    Reply
  • 2016-02-22 at 15:37
    Permalink

    Cristoph,

    Your work is very much appreciated. Thank you for giving such clear instructions.

    For others who have used https://letsencrypt.org and used the option “letsencrypt-auto –apache” to install the SSL automatically then it’s worth noting that the file to edit to include phpmyadmin (and roundcube later on) instead of default-ssl.conf is:

    /etc/apache2/sites-available/000-default-le-ssl.conf
    (This is the file that’s created by letsencrypt)

    As it’s got a 000 at the beginning of the filename it takes precedence (I’m guessing) over the default-ssl.conf file that’s why any changes to default-ssl.conf would get ignored.

    (This got me very confused yesterday when I worked all the way to the roundcube setup page of here and managed to mess it all up!)

    Thanks again. A brilliant piece of work.

    Reply
  • 2016-05-23 at 19:08
    Permalink

    Hello,
    Thank you again for this great tutorial.
    Is there any reason why you chose SHA256 and not SHA512? Have you identified any problem, for example with 32-bit computers or with postfix, dovecot or roundcube?
    Thank you for your answer.

    Reply
    • 2016-05-23 at 19:34
      Permalink

      To be honest I was just following the Dovecot documentation and at that time SHA512 was not documented. So I somehow stayed with it. But it’s probably safe to use SHA512. I haven’t tested created hashed/salted passwords using SQL queries (like: ENCRYPT (‘new password’, CONCAT(‘$5$’, SUBSTRING(SHA(RAND()), -16)))). But if that wouldn’t work we could still use “dovecotadm pw”.

      Reply
  • 2016-06-16 at 18:25
    Permalink

    Hello,

    Thank you very much for this tutorial, well done.

    I am new into mysql and I got an error:
    ERROR 1005 (HY000) can´t create table mailserver.virtual_aliases (errno: 150)

    Maybe someone have had the same issue ?

    Reply
  • 2016-08-01 at 00:57
    Permalink

    Hello, great tutorial. I’m dutch sorry for my english.
    If there is anyone who is not able to succeed the tutorial to send emails to outside with roundcube or login at webmail.
    open /etc/roundcube/config.inc.php
    you have to set smtp_server as smtp.yourinternetprovider.nl the port with 587. (ssl)
    And its important that the des_key have exactly 24 characters as you can read in the comments above it, insteed of 25 of this tutorial. Its the same as your mailuser so if that is not 24 characters you have to update your mailuser password to 24 characters.

    After this i can send emails with roundcube outside my domain en i can login at roundcube webmail.
    Without above settings i am not able to send email outside my domain.
    Without the des_key of 24 characters i am not able to login at roundcube webmail.
    I found this information outside this tutorial it is better to have this information explained in this tutorial.

    I suggest to use letsencrypt instead of the method via starttls.
    you don’t have to create an account you can download letsencrypt and open its installscript to automatically install the ssl keys in the right place. After the scripts ends your ready and are secured within a couple of minutes.

    How to download, install letsencrypt you can find in the documentation on the website of letsencrypt.

    Reply
  • 2016-09-10 at 16:09
    Permalink

    It seems the ‘Include /etc/phpmyadmin/apache.conf’ isn’t in the ansible playbook template for ‘ansible-ispmail-jessie/roles/ispmail-webmail-apache/templates/webmail.conf-443.j2’

    Reply
  • 2016-09-14 at 08:20
    Permalink

    Hi,

    great tutorial. I used it a couple of times. One short question: Why are you not using postfixadmin for creating databases?

    Reply
  • 2016-09-14 at 08:26
    Permalink

    Thanks. Postfixadmin came up a while after I published the first version of the ISPmail guide. It is using a different database schema and I didn’t actually want to make my readers change the schema for no reason. I consider the setup I describe more a basic installation and leave the details of managing the server up to the reader. But I understand that some kind of web management tool would actually be needed. To be honest I’m no big fan of Postfixadmin. It lacks too much functionality that I would expect. Maybe during my next vacation I’m picking up that task from my todo list and come up with something. 🙂

    Reply

Leave a Reply

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

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close