Preparing the database

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

Awesome! Did you plan to add

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!

Password tip.

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.

Security

Thank you for this awesome howto. However I'd like to criticize that you propose to use unsalted md5 password hashes. This is not really safe, at least you should use a salt with that and probably a better hashing algorithm.

Dovecot does for sure. I'm

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)

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

SSHA256

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

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

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.

I got it now. It is usefull

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"

After creating the user, you have to "flush privileges" in the mysql client console as shown below.



mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

 

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



administrator@debian:/etc$ sudo postmap -q example.org mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf

postmap: warning: connect to mysql server 127.0.0.1: Access denied for user 'mailuser'@'localhost' (using password: YES)

Helped me though

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.

keine weiterleitung von A auf A mehr nötig?

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

Error 1064

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

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.

I can tell you that having

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?

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

custom user names?

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.

user email mappings

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"

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

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

Password field length

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

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.