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 | example.org |
| virtual_users | |||
|---|---|---|---|
| id | domain_id | password | |
| 1 | 1 | john@example.com | 14cbfb845af1f030e372b1cb9275e6dd |
| 2 | 1 | steve@example.com | a57d8c77e922bf756ed80141fc77a658 |
| 3 | 2 | kerstin@example.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@example.org | kerstin42@example.net |
| 3 | 2 | kerstin@example.org | kerstin@example.net |
This will make the mail for steve@example.com be redirected to devnull@workaround.org. And the mail for kerstin@example.org is redirected to both kerstin42@example.net and kerstin@example.net. Neither Steve nor Kerstin receive a copy of the email.
36 Comments
a extra field about user description?
Submitted by Anonymous (not verified) on
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
Submitted by Anonymous (not verified) on
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
Submitted by Anonymous (not verified) on
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
Submitted by Anonymous (not verified) on
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.
Submitted by Christoph Haas on
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
Submitted by Anonymous (not verified) on
Is it possible to leave original message in Steve and Kerstin mailbox?
peter
Yes
Submitted by Christoph Haas on
You just need to add an alias for the original recipient to themselves. Like this:
And similar for Kerstin:
Emails sending twice
Submitted by Anonymous (not verified) on
Hi, when I do that like Kerstin, email to kersting42@yahoo.com was sended twice. How can I fix it, please ?
Virtual domain forwarding
Submitted by Anonymous (not verified) on
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
Submitted by Christoph Haas on
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
Submitted by Anonymous (not verified) on
Hello anonymous, i find your
Submitted by Anonymous (not verified) on
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
Submitted by Anonymous (not verified) on
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
Submitted by Anonymous (not verified) on
Hello Thomas,
thanks for help. Now it's work fine!
Best regards,
Christian
patch for GRSsoft interface?
Submitted by Anonymous (not verified) on
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
Submitted by Anonymous (not verified) on
Running ISPmail on a low memory machine
Submitted by Anonymous (not verified) on
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
Submitted by Anonymous (not verified) on
- 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?
Submitted by Christoph Haas on
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
Submitted by Anonymous (not verified) on
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
Submitted by Anonymous (not verified) on
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-boxes/ and also
http://www.lowendbox.com/blog/yes-you-can-run-18-static-sites-on-a-64mb-link-1-vps/.
Many thanks for this tutorial!
virtual_domains table error
Submitted by Anonymous (not verified) on
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
Submitted by Christoph Haas on
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
Submitted by Anonymous (not verified) on
sorry my mistake
it's working with copy/paste
thanks for your timely response
Forwarding email without attachment
Submitted by Anonymous (not verified) on
Hi,
thank for great tutorial.
Need one extra option. My customer want to his email will be forwarded to other mailbox (and other mail server) without attachment. Is there any way to forward only subject and body of messages only for this one customes ?
Thak you a lot.
problem with foreign key and mysql Ver 14.14 Distrib 5.1.49
Submitted by Anonymous (not verified) on
Then, using ALTER TABLE to add the foreign key:
Are you aware of this problem? and any workaround? I'm new to phpmyadmin, and can't figure out how to add a foreign key there, either.
I've tried adding INDEX (domain_id) prior to the FOREIGN KEY statement per http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html, but to no avail.
data redundancy
Submitted by Anonymous (not verified) on
With the domain_id in both virtual_users and virtual_aliases tables the domain part of the e-mail adresses seem to be redundant?
Au contraire
Submitted by Christoph Haas on
The domain_id refers to the domain in the virtual_domains table. That is called a "foreign key" or "table reference". So if you know the domain ID (or use a "JOIN" in SQL) you get easy access to the forwardings and accounts of a certain domain.
There is a lot of redundancy however in the textual email addresses used in the text fields. Usually you would normalize the database and leave of the domain name in the tables. But that would mean more complex SQL searches which would slow down the whole setup on larger mail servers.
In the end it probably doesn't matter. :)
Unsecure http connection
Submitted by Anonymous (not verified) on
Running phpmyadmin via an unsecured connection. What a bad idea!
Use this three lines to fix this problem.
and use this url afterwards:
Have fun!
wingfire
Postfix subadressing / qmail extension address support
Submitted by Anonymous (not verified) on
(I actually wanted to post this in the "Postfix to Database mappings" section but there is no "Add new comment link there...)
First, thanks a lot for this thorough guide. It has definitely saved me days of work.
I have been trying to get Postfix sub-adresses to work, so e.g. "john+foo@example.com" would be accepted as an alias for "john@example.com". This is Postfix' "subadressing" technique. Qmail has a similar feature, except it uses a '-' as the delimiter, and I am currently migrating from a Qmail setup where I used this quite a bit.
I think it can be done with some SQL tweaking, but that is definitely not my strong side. After reading a bit on the string functions in MySQL I ended up creating an extra SQL query config file mysql-virtual-alias-subaddress-maps.cf
user = mailuser
password = mailuser2009
hosts = 127.0.0.1
dbname = mailserver
query = SELECT destination FROM view_aliases WHERE email=CONCAT(SUBSTRING_INDEX('%s', '-', 1),'@',SUBSTRING_INDEX('%s','@',-1));
I use the '.-' delimiter since this is embedded in a lot of mail-adresses I have from the QMail days.
Then I added a "mysql:/etc/postfix/mysql-virtual-alias-subaddress-maps.cf" to the list of lookups in virtual_alias_maps in main.cf:
virtual_alias_maps = mysql:/etc/postfix/mysql-virtual-alias-maps.cf,mysql:/etc/postfix/mysql-email2email.cf,mysql:/etc/postfix/mysql-virtual-alias-subaddress-maps.cf
And it seems to work.
Regards,
Henrik
The unique key on the email field can cause error
Submitted by Anonymous (not verified) on
if I have for example info@domain1.com and info@domain2.com when you add the second info user you'll get mysql error because of the uniqe option. The domain_id already defines which domain the info@ belongs to I assume .
Where exactly do do you get
Submitted by Christoph Haas on
Where exactly do do you get an error? If you speak of "virtual_users" then you would have two users with the exact same email address - that mustn't happen.
User unknown in local recipient table
Submitted by Anonymous (not verified) on
Hello,
I have a working configuration, but recently I randomly get errors like this:
Apr 24 08:15:25 hostname postfix/smtpd[11062]: connect from smtp7.mailserver.com[xx.xx.xx.xx]
Apr 24 08:15:36 hostname postfix/smtpd[11062]: NOQUEUE: reject: RCPT from smtp7.mailserver.com[xx.xx.xx.xx]: 550 5.1.1 <recipient@hostname.domain.com>: Recipient address rejected: User unknown in local recipient table; from=<return@somesender.com> to=<recipient@hostname.domain.com> proto=SMTP helo=<smtp7.mailserver.com>
Apr 24 08:15:42 hostname postfix/smtpd[11062]: disconnect from smtp7.mailserver.com[xx.xx.xx.xx]
The sender ALWAYS sends his/her mail to recipient@domain.com, but in the log I randomly see messages like "550 5.1.1 <recipient@hostname.domain.com>: Recipient address rejected: User unknown in local recipient table" and it is rejected. Any ideas why this happens? The address recipient@domain.com is valid, but the address recipient@hostname.domain.com is not. Any configuration issues? Can this be load-dependent? I don't see it every time the sender sends a mail to recipient@domain.com, but sometimes it gets rejected.
Any ideas?
Your system is doing fine.
Submitted by Christoph Haas on
Your system is doing fine. After all your virtual mailbox domain is domain.com and not hostname.domain.com. You may want to worry about the piece of software that creates that email. How do you do that? Or is that somebody else's mail server?
Pls help it another subject but please help me
Submitted by Anonymous (not verified) on
I can't create table it shows me an error:
CREATE TABLE `articles` (
`ID` int(11) NOT NULL auto_increment,
`a_title` varchar(255),
`a_subtitle` tinytext,
`a_content` text,
PRIMARY KEY (`ID`)
)
CREATE TABLE `articles_ratings` (
`id` int(11) NOT NULL auto_increment, // Here i have an error #1034 syntax error
`article_id` int(11) NOT NULL,
`rating_value` tinyint(2) NOT NULL,
`rater_ip` varchar(20) NOT NULL,
)
This doesn't have to do with
Submitted by Anonymous (not verified) on
This doesn't have to do with the ISPmail tutorial or mail servers in general. Please don't hijack this section for other topics.