||<tablestyle="float:right; font-size: 0.9em; width:40%; background:#F1F1ED; margin: 0 0 1em 1em;" style="padding:0.5em;"><<TableOfContents(2)>>||

= Introduction =

This page shows you how to create a simple database structure for use as virtual user and domain information store for email systems such as Postfix and/or Dovecot.

= MySQL Installation and Setup =

You should already have MySQL setup and configured. If not you can follow the [[MySQL5FromSource]] or [[ApacheMySQLPHP]] guides or install a full LAMP using the command
{{{
sudo apt-get install lamp-server^
}}}
(remember the ^ at the end).

The rest of this document will assume some familiarity with the MySQL command line and will just contain example commands, not step by step instructions.

= Database Structure Overview =

The database will consist of three tables: domains; mailboxes; and aliases; all linked together via a common value - a domain name. For the examples in this documentation, I am using a database called "postfix" and the user will be "postfixuser".

{{attachment:dboverview.png}}

= Creating the Database and Tables =

Create a new database, user and switch to using it with the following MySQL commands. '''(You should use much better passwords!)'''
{{{
CREATE DATABASE postfix;
GRANT SELECT ON postfix.* to postfixuser@localhost IDENTIFIED BY 'reallybadpassword';
FLUSH PRIVILEGES;
USE postfix;
}}}

The first table to create is "domains". This will hold a simple list of domain names and descriptions. This is an ideal table to use for Postfix to read a list of domain names that it is collecting or relaying emails for.
{{{
CREATE TABLE domains (
    domain VARCHAR(255) NOT NULL PRIMARY KEY,
    description VARCHAR(255) NOT NULL,
    active TINYINT(1) NOT NULL DEFAULT 1
);
}}}

The second table provides most of the user account information. This table has a dual primary key of username and domain, so that you can have the same username for different domains if you wish. Unlike other examples I have chosen '''not''' to have a path to a maildor or mailbox - instead, these are generated by the config files using the other information in the table (see examples below). This should make the databse as flexible as possible.
{{{
CREATE TABLE mailbox (
    username VARCHAR(255) NOT NULL,
    password VARCHAR(100) NOT NULL,
    name VARCHAR(100) NOT NULL,
    domain VARCHAR(255) NOT NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (username, domain)
);
}}}

And the final table is anothe simple one. Although not completely required, this table does provide a way of creating aliases and distrobution lists very easily.
{{{
CREATE TABLE aliases (
    address VARCHAR(255) NOT NULL PRIMARY KEY,
    goto TEXT NOT NULL,
    domain VARCHAR(255)
);
}}}

That's just about the minimum that is needed from the database side. See the examples below for ideas on howto configure Postfix or Dovecot to use these tables for user information.

= Example Postfix configuration files =
For this example I am going to create the files required to setup virtual user maps. For a more complete understanding of how postfix's virtual delivery systems work, you should visit the [[http://www.postfix.org|postfix homepage]] or read the [[Postfix]]  and [[PostfixCompleteVirtualMailSystemHowto]] pages from this wiki. (Note: this last howto is not complete, but does provide some very useful information.)

First we need to create a config file that tells postfix how to read information from our database. These files should normally be created in /etc/postfix. The file in this example (/etc/postfix/mysql_virtual_mailbox_maps.cf) will be used for looking up virtual mailbox locations. Postfix will provide an email address in the form username@domain, and expects a path to a mailbox location.

Note: For "hosts", I am using the loopback IP address not "localhost", otherwise postfix will try to connect to the socket file in /var/run/mysql which it would find very difficult since postfix runs in a chroot jail in /var/spool/postfix.
{{{
user = postfixuser
password = password
hosts = 127.0.0.1
dbname = postfix
table = mailbox
select_field = CONCAT(domain, '/', username, '/')
where_field = CONCAT(username, '@', domain)
#additional_conditions = and active = 1
}}}

Now that this file has been created, you will need to tell postfix to use it for looking up mailboxed by editing /etc/postfix/main.cf and adding/updating the following lines:-
{{{
virtual_mailbox_maps = mysql:/etc/postfix/mysql_virtual_mailbox_maps.cf
virtual_mailbox_base = /home/vmail
}}}
= Example Dovecot configuration =
Dovecot supports using SQL databases without the need for any other modules, and by reading the example configuration file (/etc/dovecot/dovecot-sql.conf) it should be easy to edit to suit your needs. The code below is my copy of this file with the comments removed.
{{{
driver = mysql
connect = dbname=postfix user=postfixuser password=password host=localhost
default_pass_scheme = PLAIN
password_query = SELECT CONCAT(username, '@', domain) as user, password FROM mailbox WHERE username='%n' AND domain='%d'
}}}
For more information see the [[Dovecot]] wiki page or the [[http://www.dovecot.org/|Dovecot Homepage]].
----
Categories: CategoryEmail