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 example.org
virtual_users
id domain_id email 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?

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. :)

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.

 

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
 

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

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?

Forwarding email without attachment

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

 
Copying/pasting the "CREATE TABLE `virtual_users` ..." text above gets the following error:
ERROR 1005 (HY000): Can't create table 'mailserver.virtual_users' (errno: 150)
 
Leaving out the FOREIGN KEY line lets the table creation work.

Then, using ALTER TABLE to add the foreign key:

 

mysql> alter table `virtual_users` add  FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE;
ERROR 1005 (HY000): Can't create table 'mailserver.#sql-ca0_6f' (errno: 150)

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

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

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

Running phpmyadmin via an unsecured connection. What a bad idea!

Use this three lines to fix this problem.

a2enmod ssl
a2ensite default-ssl
/etc/init.d/apache2 restart

and use this url afterwards:

https://example.org/phpmyadmin

Have fun!
wingfire

Postfix subadressing / qmail extension address support

(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

User unknown in local recipient table

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.

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

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,
)