Your Debian server should now be installed. So 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 can 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: http://YOUR-MAIL-SERVER/phpmyadmin. You should see a web page like:
Create the database
Your first task is to create a new database in MySQL. Let's call it 'mailserver'. In a root shell enter this command:
$> mysqladmin -p create mailserver
You will be asked for the MySQL "root" password that you entered when you installed the MySQL package.
(In phpMyAdmin this can be done by entering "mailserver" in the "Create new database" field and clicking on "Create".)
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.
Connect to your database:
$> mysql -p
When you see the mysql> prompt enter the following SQL statement (your input is shown in bold letters) to grant the appropriate privileges:
mysql> GRANT SELECT ON mailserver.* TO 'mailuser'@'127.0.0.1' IDENTIFIED BY 'mailuser2009'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye
(It can also be done in phpMyAdmin by following these steps: Click on "Privileges". Select "Add a new User" As "User" enter "mailuser". As "Host" choose "local". Enter "mailuser2009" as the password twice. Click on "Go". Next look for "Database-specific privileges". Choose "mailserver" as the database. On the next page tick the "SELECT" checkbox and click on "Go".)
This will create a user called 'mailuser' that has only the privilege to select/read data from the database but not to alter it. If you want to add or alter data in the database either use the 'root' account or create another account for that purpose. The password 'mailuser2009' is just an example. Please replace it by a more decent password. If you lack creativity use "pwgen" or "apg" to create good passwords.
Create the database tables
Inside the newly created database you will have to create tables that store information about domains, forwardings and the users' mailboxes. It's easier here to create the database tables using SQL instead of clicking your way through phpMyAdmin.
Connect to MySQL again and choose the 'mailserver' database:
$> mysql -p mailserver
You will see the mysql> prompt again. First create a table for the list of virtual domains that you want to host:
mysql> 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:
mysql> 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):
mysql> 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;
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.