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 email 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:

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,
  `firstname` varchar(100) DEFAULT NULL,
  `lastname` varchar(100) DEFAULT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `email` (`email`),
  FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Or, using ALTER TABLE after the virtual_users table has already been created:

ALTER TABLE virtual_users ADD (
  firstname varchar(100) default NULL,
  lastname varchar(100) default NULL
);

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:

  • point the MX record of the respective zone towards your mail server
  • list the domain in your "relay_domains"
  • add a transport mapping (transport_maps) so Postfix know what to do with the email
  • configure any content filter (AMaViS or a milter) as usual

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 table

 

Sorry 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