Now it’s time to prepare the MySQL database that will store information that controls your mail server. In the process you will have to enter SQL queries. You may enter them on the ‘mysql’ command line. But if you are less experienced with MySQL I suggest you start easy with “phpMyAdmin” by pointing your web browser at this URL: https://YOUR-MAIL-SERVER/phpmyadmin. You should see a web page like:
Log in as ‘root’ with the administrative database password you set previously. Then you will find yourself on the 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. I will explain both ways. If you choose to use SQL statement you need to enter “mysql -u root -p” and enter the database management password first.
Create the database
Your first task is to create a new database in MySQL where you will store the control information for your mail server.
Click on “Database” and “Create new database”. Enter ‘mailserver’ as the name of the new database and click “Create”:
CREATE DATABASE 'mailserver';
Add a less privileged MySQL user
For security reasons you should create another MySQL user account with fewer privileges. Postfix just needs to read from the database so it does not need write access. So the ‘root’ user would be a poor choice.
Click on the “mailserver” database in the left column. Then click the “Privileges” tab. Now click on “Create a new user”. Fill out the dialog:
Set the “User name” to “mailuser”. And as “Host” select “Local” so that the text field becomes “localhost”. Click on the “Generate” button to create a random password. Do not forget to write that password down.
Scroll down the page and click on the final “Go” button.
For security reasons you should take away all access privileges except for the “SELECT” privilege. So within the “Database-specific privileges” section first click on “Uncheck All” and then just enable the ckeckbox next to “SELECT”. Now click on “Go” again.
GRANT SELECT ON mailserver.* TO 'mailuser'@'127.0.0.1' IDENTIFIED BY 'fLxsWdf5ABLqwhZr';
(You better create your own password using “apg” or “pwgen” instead of using this one.)
Create the database tables
In the newly created database you will have to create tables that store information about domains, forwardings and the users’ mailboxes. First create a table for the list of virtual domains that you want to host:
|SQL statement||SQL statement|
Click on “Create table” on the left. Call the new table “virtual_domains”. It will hold the virtual domains – as the name says. Create an “id” column as integer as a unique index and using auto-increment. Create a “name” column as VARCHAR of size 50. The form should look like:
Click on “Save”.
CREATE TABLE `virtual_domains` ( `id` int(11) NOT NULL auto_increment, `name` varchar(50) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
The next table contains information on the actual user accounts. Every user has a username and password. It is used for accessing the mailbox by POP3 or IMAP, logging into the webmail service or to send mail (“relay”) if they are not in your local network. As users tend to easily forget things the user’s email address is also used as the login username. Let’s create the users table:
|Once again click on “Create table”. Call the new table “virtual_users”. Create columns like this:
CREATE TABLE `virtual_users` ( `id` int(11) NOT NULL auto_increment, `domain_id` int(11) NOT NULL, `password` varchar(32) NOT NULL, `email` varchar(100) 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;
The email field will contain the email address/username. And the password field will contain an MD5 hash of the user’s password. The unique key on the email field makes sure that there are no two users in a domain accidentally.
And finally a table is needed for aliases (email forwardings from one account to another):
|And again click on “Create table”. Call the new table “virtual_aliases”. Create columns like this:
CREATE TABLE `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;
Here the source column contains the email address of the user who wants to forward their mail. In case of catchall addresses the source looks like “@domain”. The destination column contains the target email address. As described in the section on virtual domains there can be several rows for a source address designating multiple destinations who will get copies of an email.
You wonder about the foreign keys? They express that entries in the virtual_aliases and virtual_users tables are connected to entries in the virtual_domains table. This will keep the data in your database consistent because you cannot create virtual aliases or virtual users that are not connected to a virtual domain. The suffix ‘ON DELETE CASCADE’ means that if you delete a row from the referenced table that the deletion will also be done on the current table automatically. So you do not leave orphaned entries accidentally. Imagine that you do not host a certain domain any longer. You can remove the domain entry from the virtual_domains table and all dependent/referenced entries in the other tables will also be removed. (Note however that this would not remove the physical mail directories from the hard disk automatically.)
An example of the data in the tables:
Let us add a simple alias:
This will make the mail for firstname.lastname@example.org be redirected to email@example.com. And the mail for firstname.lastname@example.org is redirected to both email@example.com and firstname.lastname@example.org. Neither Steve nor Kerstin receive a copy of the email.
Let’s populate the database with the example.org domain, a email@example.com email account and a forwarding of firstname.lastname@example.org to email@example.com. Open a MySQL shell (or click on the “SQL” tab in phpMyAdmin) and issue the following SQL queries:
INSERT INTO `mailserver`.`virtual_domains` ( `id` , `name` ) VALUES ( '1', 'example.org' ); INSERT INTO `mailserver`.`virtual_users` ( `id` , `domain_id` , `password` , `email` ) VALUES ( '1', '1', MD5( 'summersun' ) , 'firstname.lastname@example.org' ); INSERT INTO `mailserver`.`virtual_aliases` ( `id`, `domain_id`, `source`, `destination` ) VALUES ( '1', '1', 'email@example.com', 'firstname.lastname@example.org' );