Preparing the database
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:
| virtual_domains | |
|---|---|
| id | name |
| 1 | example.com |
| 2 | foobar.org |
| virtual_users | |||
|---|---|---|---|
| id | domain_id | password | |
| 1 | 1 | john@example.com | 14cbfb845af1f030e372b1cb9275e6dd |
| 2 | 1 | steve@example.com | a57d8c77e922bf756ed80141fc77a658 |
| 3 | 2 | kerstin@foobar.org | 5d6423c4ccddcbbdf0fcfaf9234a72d0 |
Let us add a simple alias:
| virtual_aliases | |||
|---|---|---|---|
| id | domain_id | source | destination |
| 1 | 1 | steve@example.com | devnull@workaround.org |
| 2 | 2 | kerstin@foobar.org | kerstin42@yahoo.com |
| 3 | 2 | kerstin@foobar.org | kerstin@mycompany.com |
This will make the mail for steve@example.com be redirected to devnull@workaround.org. And the mail for kerstin@foobar.org is redirected to both kerstin42@yahoo.com and kerstin@mycompany.com. Neither Steve nor Kerstin receive a copy of the email.
Comments
a extra field about user description?
Do you think it is a good idea to implement an extra field in virtual_users table, so that administrators could input some information about this user, such a telephone number and working department?
If so, how to implement it?
Just add extra attributes
Just add extra attributes when creating the table or use ALTER TABLE afterwards. I, for example, list first name and last name in the virtual_users table. The CREATE TABLE statement looks like this:
Or, using ALTER TABLE after the virtual_users table has already been created:
Easy as pie :)
Size of local and domain part of an email address
In order to be fully RFC compliant, one should alter the allowed length of the local ('email' field in the virtual_users table) and domain ('name' field in virtual_domains) part of the composed email address. According to the RFC, an emailadres should be maximum 256 characters long, the local part should contain no more then 64 characters and the domain part should count 255 characters at most. For some functionalities however, the maximum size of an emailadres in total may not suppersede the boundary of 254 characters.
So if you want to be safe, set the size of the 'email' field to 64 and the size of the domain name field to 189 (254 - 64 - 1 for the '@' character). If you do not want to put any restrictions on you domain names, you should set a maximum length of 252 characters for the domain name. In this case however, one must check wether the size of the whole email address exceeds the maximum of 254 characters...
Best regards
Kurt
PAsword hashing
For security reasons, you may consider to use the SHA hasing algorithm instead of md5 for your passwords. MySQL offers SHA1 by default. If you do this, you should change the size of the password field to 40 characters.
Best regards
Kurt
Thanks for the comment, Kurt.
Thanks for the comment, Kurt. Actually there are also attack vectors against SHA1. But I believe there is no big problem anyway. After all you would need read access to the virtual_users table. :)
copy of the email
Is it possible to leave original message in Steve and Kerstin mailbox?
peter
Yes
You just need to add an alias for the original recipient to themselves. Like this:
And similar for Kerstin:
Emails sending twice
Hi, when I do that like Kerstin, email to kersting42@yahoo.com was sended twice. How can I fix it, please ?
Virtual domain forwarding
Would you add to this excellent document how to also do domain forwarding?
I would like to receive emails for a domain, scrub them for spam, viruses and so on, and forward the remainder to the clients email server. I know that this is part of virtual_transports but I am nervous about making any changes without the benefit of input from those who know how to do this sort of thing.
Obviously I'll have to get the MX records changed and make changes on the client email server not to accept any unauthenticated email submissions.
Scan + Forward
Not sure if it's really in the focus of this tutorial as it doesn't deal with storing email but just scanning and forwarding.
What you essentially would do is:
A little modification for easier frontend implementation
Hello,
Your tuto is very usefull. I only made some sql modification, for easier domain management. I had a activ fiels in the domain, user and alias tables and insert in the views a where restriction on this fields :
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,
`activ` char(1) NOT NULL default '1',
PRIMARY KEY (`id`),
UNIQUE KEY `UNIQUE_EMAIL` (`domain_id`,`user`),
FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `virtual_domains` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(50) NOT NULL,
`activ` char(1) NOT NULL default '1',
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `virtual_aliases` (
`id` int(11) NOT NULL auto_increment,
`domain_id` int(11) NOT NULL,
`source` varchar(40) NOT NULL,
`destination` varchar(80) NOT NULL,
`activ` char(1) NOT NULL default '1',
PRIMARY KEY (`id`),
FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE VIEW view_aliases AS
SELECT CONCAT(va.source, _utf8 '@',vd.name) AS email,va.destination AS destination
FROM virtual_aliases AS va
Left JOIN virtual_domains AS vd
ON va.domain_id = vd.id
WHERE vd.activ = 1 AND va.activ = 1
ORDER BY name, source;
CREATE VIEW view_users AS
SELECT CONCAT(vu.user, _utf8 '@',vd.name) AS email,vu.password AS password
FROM virtual_users AS vu
Left JOIN virtual_domains AS vd
ON vu.domain_id = vd.id
WHERE vu.activ = 1 AND vd.activ = 1
ORDER BY name, user;
It's now very easy to activate or desactivate, a entire domain, only an mail address or only a redirection by a simple update request, something like :
UPDATE virtual_domains SET activ=ABS(activ - 1) WHERE id=a_domain_id
It's just a detail but it can be usefull
Hello anonymous, i find your
Hello anonymous,
i find your example very interesting, but it don't work. :-(
By Copy&Paste phpmyadmin say:
### Error 1: CREATE TABLE `virtual_users`
#1072 - Key column 'user' doesn't exist in tableSorry for my bad english.Hi, there is a small mistake
Hi,
there is a small mistake in this example. Please use
CREATE TABLE `virtual_users` (
`id` int(11) NOT NULL auto_increment,
`domain_id` int(11) NOT NULL,
`password` varchar(32) NOT NULL,
`user` varchar(100) NOT NULL, <=== Here was the mistake
`activ` char(1) NOT NULL default '1',
PRIMARY KEY (`id`),
UNIQUE KEY `UNIQUE_EMAIL` (`domain_id`,`user`),
FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Best regards,
thomas polnik.
Hello Thomas, thanks for
Hello Thomas,
thanks for help. Now it's work fine!
Best regards,
Christian
patch for GRSsoft interface?
hello thomas,
i use your sql modification on my setup and the GRSsoft interface to manage my mail accounts.
is there an patch for the GRSsoft interface?
thank you and best regards
Activ
Nice idea. "activ" should really be boolean, though.
Running ISPmail on a low memory machine
Hi everyone,
I tried to run this configuration on a 512 MB machine. I often had memory problems and troubleshooted these problems by optimizing the configurations of the applications used in this tutorial. To decrease the memory usage of mysql by about 100 MB you can deaktiviate InnoDB and use MyIsam instead of it. You can't use any foreign keys anymore, but I think in this extremly tiny database setup this is not a problem.
Just change the engine in your create table script to 'MyIsam' instead of 'InnoDB'. To free the memory, you have to activate the skip-innodb line in your my.cnf and restart mysqld. Make sure no other databses/tables use InnoDB, as these database/tables can't be read anymore.
If you have any other ideas to lower the memory usage of this whole configuration, just let me know.
not found INBOX
- hello every body
-I have made to follow what your article , but have some probmem.
+ I can not find INBOX in derectory although in there have SENT
+ I only see some in /var/vmail/example.com/john/Maildir
cur/ .Drafts/ tmp/
dovecot.index new/ .Trash/
dovecot.index.cache .Sent/
dovecot.index.log subscriptions
- anyone can tell me any problem???
- Thanks
namespace private?
I'm not exactly sure what the reason is. But first please make sure your private namespace in the dovecot.conf is defined as this:
namespace private {
separator = .
inbox = yes
}
PHP MyAdmin config comment
I'm new to linux and have been working through your guide. Thanks so much by the way, fantastic guide!
I ran into a stumbling block with PHPMyAdmin where I was denied access to mydomain.com/phpmyadmin. After some hunting I realized that the default apache.conf file (/etc/phpmyadmin/apache.conf) that installed with the phpmyadmin package from Synaptic Package Manager needed an Order directive added to it in the <Directory /usr/share/phpmyadmin> block. I stuck in Order Allow,Deny and Allow from 192.168.1.1 to restrict access to my LAN for now.
You might consider adding a comment that the default config file for phpmyadmin must be edited to this effect before you can access it?
-Mike
Memory usage
Hi,
i tried to get down with the Memory usage for MySQL and skipped the InnoDB for MyIsam so that the the neet foreign keys used are not working anymore.
But in memory usage switching of InnoDB support in my.cnf brought the RAM usage from ~210MB down to 96MB for the whole system (lighttpd, dovecot, postfix, postgrey).
More tweaks to be used i found at http://www.lowendbox.com/blog/reducing-mysql-memory-usage-for-low-end-bo... and also
http://www.lowendbox.com/blog/yes-you-can-run-18-static-sites-on-a-64mb-....
Many thanks for this tutorial!
virtual_domains table error
Hi there,
I followed with good use your tutorial until the point of creating the virtual_domains table;
After typing the code I got this:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that coresponds to your
MySQL server version for the right syntax to use near ''virtual_domains' ( 'id' int(11) NOT NULL
auto_increment, 'name' varchar(50) NOT, at line 1
Thanks
Hmmm
Are you using Debian Lenny with the normal 5.0 MySQL server? Did you paste everything but the "mysql>" line into an SQL shell?
sorry my mistake it's
sorry my mistake
it's working with copy/paste
thanks for your timely response