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

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 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 Dovecot Homepage.


Categories: CategoryEmail

MySQLVirtualEmail (last edited 2011-06-07 19:16:14 by 157-157-186-230)