Preparing the database
Now 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:

Login as "root" with your MySQL administrative password. Then you will find yourself on the main screen:

This will help you manage your databases. But in the course of this tutorial I will just document how to work with MySQL using the console. SQL statements will be marked in blue.
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.
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 mailserver
When you see the "mysql>" prompt enter the following SQL statement to grant the appropriate privileges but instead of the dummy password 'mailuser2011' please choose a more secure password. The "pwgen" or "apg" tools will generate random passwords for you if you don't feel creative. I will keep using 'mailuser2011' in this tutorial though.
GRANT SELECT ON mailserver.*
TO 'mailuser'@'127.0.0.1'
IDENTIFIED BY 'mailuser2011';
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.
Create the database tables
In the newly created database you will have to create tables that store information about domains, forwardings and the users' mailboxes. First create a table for the list of virtual domains that you want to host:
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:
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):
CREATE TABLE `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.org |
| 2 | example.net |
virtual_users
| id | domain_id | password | |
|---|---|---|---|
| 1 | 1 | john@example.org | 14cbfb845af1f030e372b1cb9275e6dd |
| 2 | 1 | steve@example.org | a57d8c77e922bf756ed80141fc77a658 |
| 3 | 2 | kerstin@example.net | 5d6423c4ccddcbbdf0fcfaf9234a72d0 |
Let us add a simple alias:
virtual_aliases
| id | domain_id | source | destination |
|---|---|---|---|
| 1 | 1 | steve@example.org | devnull@workaround.org |
| 2 | 2 | kerstin@example.net | kerstin42@yahoo.com |
| 3 | 2 | kerstin@example.net | kerstin@mycompany.com |
This will make the mail for steve@example.org be redirected to devnull@workaround.org. And the mail for kerstin@example.net is redirected to both kerstin42@yahoo.com and kerstin@mycompany.com. Neither Steve nor Kerstin receive a copy of the email.
Test data
Let's populate the database with the example.org domain, a john@example.org email account and a forwarding of jack@example.org to john@example.org. Open a MySQL shell and issue the following SQL queries:
INSERT INTO `mailserver`.`virtual_domains` (
`id` ,
`name`
)
VALUES (
'1', 'example.org'
);
INSERT INTO `mailserver`.`virtual_users` (
`id` ,
`domain_id` ,
`password` ,
`email`
)
VALUES (
'1', '1', MD5( 'summersun' ) , 'john@example.org'
);
INSERT INTO `mailserver`.`virtual_aliases` (
`id`,
`domain_id`,
`source`,
`destination`
)
VALUES (
'1', '1', 'jack@example.org', 'john@example.org'
);
49 Comments
Table with virtual_users has
Submitted by Anonymous (not verified) on
Table with virtual_users has bad width and data in it are unreadable.
How do you mean exactly?
Submitted by Christoph Haas on
How do you mean exactly?
I got the same issue. See
Submitted by Anonymous (not verified) on
I got the same issue. See this screenshot: http://i.imgur.com/xncre.png
Using Opera 11.11 on Debian 6.
Thank you - that helped. I
Submitted by Christoph Haas on
Thank you - that helped. I accidentally had the table width set to 20 pixels. And neither Chrome nor Firefox stumbled upon that. Should look better now, right?
Mh, no, still looks weird.
Submitted by Anonymous (not verified) on
Mh, no, still looks weird. But now all tables have the same width - see http://i.imgur.com/ORjdA.png.
Thanks. I have finally
Submitted by Christoph Haas on
Thanks. I have finally downloaded Opera and saw the issue. Somehow Drupal insists on either using % values for the table width or by default fix a table to 200 pixels wide. I don't get why it does that. Now the table may look pretty wide but at least it's readable. :)
You forgot create database
Submitted by Anonymous (not verified) on
You forgot create database mailserver; and USE mailserver;
You are partly right : this
Submitted by Anonymous (not verified) on
You are partly right :
this command creates database with name mailserver
mysqladmin -p create mailserver;
but yeah command use mailserver was forgot :)
You are both right. Thanks
Submitted by Christoph Haas on
You are both right. Thanks for the hint. I have fixed that and now it's:
OMG I completely missed that
Submitted by Anonymous (not verified) on
OMG I completely missed that command :)
No, you didn't miss it. I
Submitted by Christoph Haas on
No, you didn't miss it. I changed that today. :)
By the way, do you have an
Submitted by Anonymous (not verified) on
By the way, do you have an eta for the last part of the tutorial?
My ETAs have often been wrong
Submitted by Christoph Haas on
My ETAs have often been wrong so I hesitate to name it. :) But half of the documents is already written and I just need to test and review it. I would feel bad if I just put it online like this and have others test it. Looking at my spare time situation it's probably going to be on the weekend (May 22nd).
Awesome! Did you plan to add
Submitted by Anonymous (not verified) on
Awesome! Did you plan to add a section about SPF/DKIM/greylisting/smtpd restrictions/PTR/...?
I remember you had one in the queue two years ago, it would be a _great_ value added!
Yes, the pages on SPF
Submitted by Christoph Haas on
Yes, the pages on SPF (tumgreyspf), DNS management and smtpd_*_restrictions are done and just need to be proofread. They will be released on saturday probably. DKIM is still left to do but I have already done the research and put together the notes. Stay tuned.
Password tip.
Submitted by Anonymous (not verified) on
Not knowing the first thing about configuring a mail server, in my ignorance I used a password that included a # (in order to try and make it secure).
This proved to be a stumbling block, presumably because the # is an illegal character. Dovecot didn't like it at all!
I then had to go back and change my password throughout. So the morel of the story, is be careful about which characters you use for a secure password.
password hash
Submitted by Anonymous (not verified) on
From where and how i can get the pw hash?
The password hash is stored
Submitted by Christoph Haas on
The password hash is stored in the "password" field/column of the "virtual_users" table.
Security
Submitted by Anonymous (not verified) on
I'm not happy with unsalted
Submitted by Christoph Haas on
I'm not happy with unsalted hashes either. Let me try to research if Postfix and Dovecot nowadays support salts.
Dovecot does for sure. I'm
Submitted by Anonymous (not verified) on
Dovecot does for sure. I'm using salted MD5-crypt for that.
Not entirely sure about Postfix. I couldn't find any direct MD5-crypt support, but without investigating this right to the bottom: Postfix does support PAM, and I believe you can hook PAM up to the table where dovecot gets it's credentials. I may be wrong though, I'm new to this ;-)
Do I need uncrypted pass in database to use CRAM-MD5 (dovecot 2)
Submitted by Anonymous (not verified) on
I'm trying to setup mailserver with Dovecot 2.0.
This guide is great, I adopted some settings to make Dovecot 2.0 config but have problem with auth mechanisms other than PLAIN and LOGIN.
I want to use only one (default) password scheme in my Mysql database.
I tried to use DIGEST-MD5 and CRAM-MD5.
I know now that DIGEST-MD5 requires to store passwords in plain text but http://wiki2.dovecot.org/Authentication/PasswordSchemes says that if "going to use CRAM-MD5 authentication, the password needs to be stored in either PLAIN or CRAM-MD5 scheme".
I tried combination of:
auth_mechanisms = plain login cram-md5
default_password_scheme = PLAIN-MD5
but it doesn't authenticate my roundcube webmail account. When I remove "cram-md5" everything works fine. Any ideas ;-) ?
-bobie
Postfix doesn't need to
Submitted by duckdalbe (not verified) on
As postfix is authenticating against dovecot it doesn't need to know anything about the hashing-scheme or the salt of the password, no?
SSHA256
Submitted by Anonymous (not verified) on
just tested this. Setting the line
"default_pass_scheme = SSHA256"
in dovecot-sql.conf, and generating a suiteable hash to use in the database using
"dovecotpw -s SSHA256"
Thanks for an awesome tutorial
sep
Using phpMyAdmin
Submitted by Anonymous (not verified) on
Here's how I converted your steps to those in phpMyAdmin (including the FOREIGN KEY steps:
* Create a less privileged user for use by the mailserver database.
phpMyAdmin -> Databases -> mailserver -> Privileges -> Add a new user -> User name: mailuser -> Host: Local
-> Password / Re-type: mailusersecretpw -> Data: Select (ticked) -> Administration: Grant (ticked) -> Go
* Create a table for the list of virtual domains:
phpMyAdmin -> Databases -> mailserver -> Create a new table on database mailserver: Name: virtual_domains -> Number of fields: 2 -> Go
-> Field: id -> Type: INT -> Length/Value: 11 -> Collation: utf8_unicode_ci
-> Index: PRIMARY -> AUTO_INCREMENT (ticked)
-> Field: name -> Type: VARCHAR -> Length/Value: 50 -> Collation: utf8_unicode_ci
-> Storage Engine: InnoDB -> Collation: utf8_unicode_ci -> Save
* Create a table for the user accounts:
phpMyAdmin -> Databases -> mailserver -> Create a new table on database mailserver: Name: virtual_users -> Number of fields: 4 -> Go
-> Field: id -> Type: INT -> Length/Value: 11 -> Collation: utf8_unicode_ci
-> Index: PRIMARY -> AUTO_INCREMENT (ticked)
-> Field: domain_id -> Type: INT -> Length/Value: 11 -> Collation: utf8_unicode_ci
-> Field: password -> Type: VARCHAR -> Length/Value: 32 -> Collation: utf8_unicode_ci
-> Field: email -> Type: VARCHAR -> Length/Value: 100 -> Collation: utf8_unicode_ci -> Index: UNIQUE
-> Storage Engine: InnoDB -> Collation: utf8_unicode_ci -> Save
-> domain_id: (ticked) -> Action: Index (Icon) -> Relation view -> domain_id: FOREIGN KEY (INNODB): mailserver.virtual_domains.id -> ON DELETE: CASCADE
* Create a table for the aliases 9for forwarding emails from one account to the other):
phpMyAdmin -> Databases -> mailserver -> Create a new table on database mailserver: Name: virtual_aliases -> Number of fields: 4 -> Go
-> Field: id -> Type: INT -> Length/Value: 11 -> Collation: utf8_unicode_ci
-> Index: PRIMARY -> AUTO_INCREMENT (ticked)
-> Field: domain_id -> Type: INT -> Length/Value: 11 -> Collation: utf8_unicode_ci
-> Field: source -> Type: VARCHAR -> Length/Value: 100 -> Collation: utf8_unicode_ci
-> Field: destination -> Type: VARCHAR -> Length/Value: 100 -> Collation: utf8_unicode_ci
-> Storage Engine: InnoDB -> Collation: utf8_unicode_ci -> Save
-> domain_id: (ticked) -> Action: Index (Icon) -> Relation view -> domain_id: FOREIGN KEY (INNODB): mailserver.virtual_domains.id -> ON DELETE: CASCADE
Using phpMyAdmin
Submitted by Anonymous (not verified) on
Unnecessary. You can click on SQL in phpMyAdmin, copy and paste all SQL commands and let phpMyAdmin perform them. Faster, easier and less error prone.
Why does virtual_aliases need domain_id?
Submitted by Anonymous (not verified) on
The email adresses are fully qualified. Isn't the domain_id column useless?
domain_id
Submitted by Anonymous (not verified) on
I got it now. It is usefull
Submitted by Anonymous (not verified) on
I got it now. It is usefull for deleting the aliases after a domain was deleted from virtual_domains. This is ensured through FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE at the creation of the virtual_aliases table.
Note on "Add a less privileged MySQL user"
Submitted by Anonymous (not verified) on
After creating the user, you have to "flush privileges" in the mysql client console as shown below.
Otherwise the new user wont be able to query the database, and you will later get an error in the next section Postfix/Database configuration similar to the one below
Not quite right
Submitted by Christoph Haas on
The "GRANT" automatically flushes the privileges. You just need to "FLUSH PRIVILEGES" if you are adding users manually into the mysql.user table.
Helped me though
Submitted by Michael (not verified) on
I don't know which one of you is right or wrong on this topic, or how I managed to balls up this whole thing, but somewhere in the process I wasn't able to access my dabatase anymore and this command helped me.
mysql vs. postmap (Berkeley DB files)
Submitted by Anonymous (not verified) on
keine weiterleitung von A auf A mehr nötig?
Submitted by Anonymous (not verified) on
hallo christoph,
in de früheren anleitungen war es immer nötig, den email account explizit nochmal auf sich selber zu leiten, sprich in virtual alias als bsp.
john@example.org -> john@example.org
nun bin ich etwas verwirrt. ist das ab sofort nicht mehr nötig?
natürlich ein danke auch von mir für dieses aktuelle tut.
gruß
henning
Doch
Submitted by Christoph Haas on
Doch. Siehe email2email in http://workaround.org/ispmail/squeeze/postfix-database-configuration
Error 1064
Submitted by Anonymous (not verified) on
I am trying to use MySQL 5.1 for this tutorial and I am having problems.
I couldn't get the commands to create the databases to work; instead I got "ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near <insert code>."
To work around it I followed Anonymous' guide on creating the tables with phpMyAdmin, which worked fine.
However, once I tried the sample data I got the same error, even if I pasted them into phpMyAdmin.
A bit of research points keyword changes in MySQL 5.1, but beyond that I'm stuck because I am new to relational databases.
MySQL Engine = InnoDB ???
Submitted by Anonymous (not verified) on
Hi all,
I would like to use my existing MySQL server so I am stuck at the point after I created the new database with name "mailserver". I wanted to create the tables as showed on this tutorial, but I realized that you use the command for using the InnoDB engine. My existing MySQL Server and all the databases use the MyISAM engine and /etc/mysql/my.cnf is configured with "skip-innodb"
So how should I proceed ? any help appreciated.
"skip-innodb" shouldn't be
Submitted by Christoph Haas on
"skip-innodb" shouldn't be mentioned in your global my.cnf by default. MyISAM is a painful evil toy kind of a legacy crap database engine (to put it nicely). Nowadays InnoDB is the proper engine type for MySQL databases unless you really really have a reason to use MyISAM and know what you are doing. So I suggest you remove "skip-innodb" from your my.cnf (I'd be surprised if it had been there by default) and create the tables as InnoDB.
Although to be honest MyISAM would work for most installations either. :) I've just wasted days and weeks of my life fighting MyISAM-based production databases. Thus the grief.
I can tell you that having
Submitted by Anonymous (not verified) on
I can tell you that having experience exactly the opposite. That's why I work with the default engine Mode MyISAM and avoid InnoDB if not excplicitly choosed by the devs or customers. I can tell you storied with corrupt tables and so on ... :) however, I just changed the text to MyISAM when creating the tables. I will of course stay at MyISAM as all my databases use this engine mode. I used skip-innodb cause all my tables won't use it, so the engine support is NOT NEEDED. Also "mysqltuner" reported that to me and suggested to use this tag.
How to delete a mailbox?
Submitted by Anonymous (not verified) on
Hi,
coming from the viewpoint of a web developer I like the database approach for creating users in the database and suddenly they get there own mailbox with the first email.
But what about when I have to remove a mailbox, cause the person isn't working anymore for the company? I know how to delete the entries from the database, but what happens to the mailbox and mailspace on dovecot? Is the "real mailbox" deleted automatically or what are the necessary steps to remove the mailbox from dovecot (and of course free the used disk space)?
Thanks for a hint in the right direction.
Kind regards
Ulf
You need to delete the
Submitted by Anonymous (not verified) on
custom user names?
Submitted by Jost (not verified) on
Your setup uses the email address as login name. While I see the beauty in it for the average user or SOHO server, this brings some limitations in complex setups with shared addresses and site-wide user names.
Is it possible to modify this setup to allow generic user names (I suppose these would be the mailbox names also), and then map addresses for incoming mail to these mailboxes?
For example:
- Mail for info@example.org and comments@example.org should be accepted and delivered to mailbox 'helpdeskinbound' (which in turn might have sieve scripts set up to sort mail)
- the user 'helpdeskinbound' cannot send mail. The mailbox is only used as shared name space.
- the different user 'helpdeskoutbound' can send mail with both from:info@example.org and from:comments@example.org, but cannot receive mail.
How would one go about doing this? Would this be the "email2email"-mapping (which maps to itself in your tutorial, because email=username?)?
I know I can already associate (as in restrict) sender addresses for outgoing mail to usernames (and therefore also prevent users from sending mail by providing an empty mapping), but having generic mailbox names would be a cleaner solution.
You can name the accounts
Submitted by Christoph Haas on
You can name the accounts anything you like. Email addresses are just easier to remember and I haven't seen any brute force attacks against such format of user names. It's just a matter of changing the mappings or inventing an additional column in the virtual_users table.
For outgoing email you can use any valid account on your server. So you are not limited there.
I personally prefer the original schema and rather use forwardings to send email to a single or multiple mailboxes.
The email2email mapping is not related to that. You may want to re-read that section.
user email mappings
Submitted by Jost (not verified) on
so getting more technical this means for "local" addresses all I would need to do is to tell postfix what destination parameter to call /usr/lib/dovecot/deliver with for storing the mail addressed to info@example.org into the appropriate mailbox. And in the above example that would bei "-d inbound", because that's what the dovecot mailbox is called. Right?
Before that (and for outgoing mail), postfix only deals with email addresses (without caring what user the address belongs to), and after that dovecot only deals with usernames, not caring what email address may or may not be associated with a certain mailbox.
Is that about right?
I need a "login" name different than "email"
Submitted by dacer (not verified) on
Hi thanks for this How to, really fantastic.
I'm porting my email from external service to our servers, and now, we have a login name different than email name. For example, I have a "test@mydomain.com", but when login (outlook/thunderbird), I have a different name, for example 'zxc123'. When send, email arrive as test@mydomain.com. And when login in webmail (squirremail and roundcube) I can use as login 'zxc123' or email address 'test@mydomain.com'. I know, it'is confusing, but I have a lot of programs sending emails with login and I can change all now.
How can I do it?.
Thanks
MySQL connection error
Submitted by Zach Cheung (not verified) on
I have no idea why error happens when I use 127.0.0.1 in GRANT sentence, while localhost instead is ok.
mysql> GRANT SELECT ON mailserver.* TO 'mailuser'@'127.0.0.1' IDENTIFIED BY 'mailuser2011';
#mysql -u mailuser -p
Enter password:
ERROR 1045 (28000): Access denied for user 'mailuser'@'localhost' (using password: YES)
here is my hostname and hosts file:
#cat /etc/hostname
manhuabudang
#cat /etc/hosts
127.0.0.1 localhost.localdomain localhost
199.180.254.143 manhuabudang.com manhuabudang
In the sense of MySQL 127.0.0
Submitted by Christoph Haas on
In the sense of MySQL 127.0.0.1 is not the same as localhost. You granted rights for 127.0.0.1 but tried to connect to "localhost" (which uses the UNIX socket). I recommend using 127.0.0.1 everywhere.
Password field length
Submitted by Christian Schnell (not verified) on
If you plan to use MD5-CRYPT style passwords, note that they have a length > 32 characters (34 in my case), but table virtual_users limits passwords to 32 characters. I've changed the metadata to:
CREATE TABLE `virtual_users` (
`id` int(11) NOT NULL auto_increment,
`domain_id` int(11) NOT NULL,
`password` varchar(64) 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;
and it works as expected. Note that MySQL drops overlapping characters without warning.
Virtual Aliases Question
Submitted by Michael Sloan (not verified) on
I'm in the process of replacing an archaid Solaris system running sendmail/WU-IMAP with SUSE using Postfix/Dovecot and since I may end up hosting mail for multiple domains, I chose to go with the virtual domains/users/aliases as outlined in your tutorial. I am still in the testing stages, preparing to migrate mail from one system to the other, and was creating mailboxes for the users here (there are about 100), and started looking at the mailing lists on this server.
One of these is for announcements to staff and the /etc/aliases file has the line:
staff: :include:/etc/staff.txt
and /etc/staff.txt has entries like:
joe@example.com
john@example.com
When I try to do this using virtual_aliases, I end up with a response to an attempted test message (echo test | staff@example.com) that says:
<":include:/etc/staff.txt"@host.domain> (expanded from
<staff@host.domain>): unknown user: ":include:/etc/staff.txt"
So obviously something isn't quite right. How do you reproduce this sort of functionality? Do you use some dummy address which is then expanded in the local alias file (/etc/aliases)? It seems like adding every staff member to virtual_aliases database would be more cumbersome, even with a web front-end.
I've removed the actual host/domain name here, but the system does deliver mail correctly - the problem is with the approach to the alias.