Virtual Domains and Users w/ Postfix / Courier-IMAP / MySQL

Introduction
Disclaimer
MySQL Install
MySQL Setup
Postfix Install
Postfix Setup
Courier-IMAP Install
Courier-IMAP Setup
1. authmysqlrc
Postfix Admin

Introduction

The document is written for Postfix 2.0 (or Postfix Snapshot 20021223) and higher.

This document describes how to setup Virtual Domains (Aliases and Mailboxes)
with Postfix, Courier-IMAP and MySQL. I have found that this is the easiest
combination that allows you to serve Virtual Domains, and Users.
With this it's also very easy to implement webmail systems like Squirrel Mail.

TODO: Expand this document with SASL.

NOTE: One thing that you have to keep in mind is that Courier-IMAP only supports
the Maildir format.

About the used software:

Postfix attempts to be fast, easy to administer, and secure, while at
the same time being sendmail compatible enough to not upset existing users.
Thus, the outside has a sendmail-ish flavor, but the inside is completely
different.

Courier-IMAP is a server that provides IMAP access to Maildirs.
This IMAP server does NOT handle traditional mailbox files (/var/spool/mail,
and derivatives), it was written for the specific purpose of providing
IMAP access to Maildirs.

The MySQL database server is the world's most popular open source database. Its
architecture makes it extremely fast and easy to customize. Extensive reuse of code
within the software and a minimalistic approach to producing functionally-rich
features has resulted in a database management system unmatched in speed,
compactness, stability and ease of deployment. The unique separation of the core
server from the table handler makes it possible to run with strict transaction control
or with ultra-fast transactionless disk access, whichever is most appropriate for the
situation.

Disclaimer

This document assumes that you have some knowledge on Postfix, Courier-IMAP and MySQL.
At least enough to get everything installed. Installing the software is outside the scope of this document

3. MySQL Install

Installation of MySQL is outside the scope of this document.
I'm using an out of the box MySQL install on FreeBSD.

4. MySQL Setup

4.1 Create the database

Create a Postfix user and the Postfix database.
Use "mysql" or "mysql -p" to login to the MySQL Monitor.

USE mysql;
INSERT INTO user (Host, User, Password) VALUES ('localhost','postfix',password('postfix'));
INSERT INTO db (Host, Db, User, Select_priv) VALUES ('localhost','postfix','postfix','Y');
CREATE DATABASE postfix;

4.2 Create the Tables

In order to use MySQL with Postfix we need to create 3 tables.
All these tables have information for Postfix, and some for Courier-IMAP
as noted by each table. There are a couple of columns that are not used by
either Postfix or Courier-IMAP. These columns are:

domain (in some tables)

create_date

change_date

active

These columns are used to make your life easier together with Postfix Admin.
The "active" column is not used at the moment.

4.2.1 Create the Alias Table

#
# Table structure for table alias
#
USE postfix;
CREATE TABLE alias (
  address varchar(255) NOT NULL default '',
  goto text NOT NULL,
  domain varchar(255) NOT NULL default '',
  create_date datetime NOT NULL default '0000-00-00 00:00:00',
  change_date datetime NOT NULL default '0000-00-00 00:00:00',
  active tinyint(4) NOT NULL default '1',
  PRIMARY KEY (address)
) TYPE=MyISAM COMMENT='Virtual Aliases - mysql_virtual_alias_maps';

Postfix: is using the "address" and "goto" column.
Courier: is not using this table.

NOTE: This table can be used for virtual .foward files. This table is nothing more then /etc/aliases that you will find on any *nix OS. Multiple destination email addresses need to be separated by a "," (comma).

4.2.2 Create the Domain Table

#
# Table structure for table domain
#
USE postfix;
CREATE TABLE domain (
  domain varchar(255) NOT NULL default '',
  description varchar(255) NOT NULL default '',
  create_date datetime NOT NULL default '0000-00-00 00:00:00',
  change_date datetime NOT NULL default '0000-00-00 00:00:00',
  active tinyint(4) NOT NULL default '1',
  PRIMARY KEY (domain)
) TYPE=MyISAM COMMENT='Virtual Domains - mysql_virtual_domains_maps';

Postfix: is using the "domain" and "description" column.
Courier: is not using this table.

4.2.3 Create the Mailbox Table

#
# Table structure for table mailbox
#
USE postfix;
CREATE TABLE mailbox (
  username varchar(255) NOT NULL default '',
  password varchar(255) NOT NULL default '',
  name varchar(255) NOT NULL default '',
  maildir varchar(255) NOT NULL default '',
  domain varchar(255) NOT NULL default '',
  create_date datetime NOT NULL default '0000-00-00 00:00:00',
  change_date datetime NOT NULL default '0000-00-00 00:00:00',
  active tinyint(4) NOT NULL default '1',
  PRIMARY KEY (username)
) TYPE=MyISAM COMMENT='Virtual Mailboxes - mysql_virtual_mailbox_maps';

Postfix: is using the "username" and "maildir" column.
Courier: is using the "username, "password", "name" and "maildir" column.

4.3 Populate the Tables

USE postfix;
INSERT INTO domain (domain,description) VALUES ('domain.tld','Test Domain');
INSERT INTO alias (address,goto) VALUES ('alias@domain.tld', 'user@domain.tld');
INSERT INTO mailbox (username,password,name,maildir) VALUES ('user@domain.tld','$1$DOrHOomo$U6MleaFKGwWLB3iS9P1Yx0','Mailbox User','user@domain.tld/');

The first INSERT is to let Postfix know that this domain is a virtual domain and should
be handeled by Postfix. It's also possible to have everything in one table but I think
this is nicer.

The second INSERT is an virtual alias pointing to the third INSERT.

The third INSERT is an actual Virtual Mailbox, as you can see I'm using MD5 password
for backwards compatibility with local defined mail accounts. If you are using MD5 passwords,
make sure you don't use the builtin MySQL routine to generate MD5 passwords.
This is not compatible with Courier-IMAP. If you want you can also use cleartext or encrypted passwords.

To make sure that the new MySQL users are working, do the following from the command line.

% mysqladmin reload

5. Postfix Install

Build and Install Postfix 2.x, or the latest snapshot. Make sure that you at least build it with MySQL.
I have build everything in FreeBSD and the default location is /usr/local/etc/postfix.
Your configuration might be different.

After that you have to create a directory to have all your virtual users mail dropped in, this directory needs
to be owned by Postfix.

% mkdir /usr/local/virtual
% chown -R postfix:postfix /usr/local/virtual
% chmod -R 751 /usr/local/virtual

6. Postfix Setup

6.1 main.cf

The below example is the part that goes into your main.cf file of Postfix.
The path to the mysql files might be different on your setup. The same might be for
uid_maps, gid_maps and minimum_uid values. These values should be the ones from the postfix user and group.
You can find these in your /etc/passwd file.

virtual_alias_maps = mysql:/usr/local/etc/postfix/mysql_virtual_alias_maps.cf
virtual_gid_maps = static:1001
virtual_mailbox_base = /usr/local/virtual
virtual_mailbox_domains = mysql:/usr/local/etc/postfix/mysql_virtual_domains_maps.cf
virtual_mailbox_maps = mysql:/usr/local/etc/postfix/mysql_virtual_mailbox_maps.cf
virtual_mailbox_limit = 51200000
virtual_minimum_uid = 1001
virtual_transport = virtual
virtual_uid_maps = static:1001

6.2 mysql_virtual_alias_maps.cf

You will need to put this into a text file for postfix to pickup.

user = postfix
password = postfix
hosts = localhost
dbname = postfix
table = alias
select_field = goto
where_field = address

6.3 mysql_virtual_domains_maps.cf

You will need to put this into a text file for postfix to pickup.

user = postfix
password = postfix
hosts = localhost
dbname = postfix
table = domain
select_field = description
where_field = domain

6.4 mysql_virtual_mailbox_maps.cf

You will need to put this into a text file for postfix to pickup.

user = postfix
password = postfix
hosts = localhost
dbname = postfix
table = mailbox
select_field = maildir
where_field = username

7. Courier-IMAP Install

Build and Install Courier-IMAP, make sure that this is build with MySQL.
On FreeBSD: make install WITH_MYSQL=yes

8. Courier-IMAP Setup

8.1 authmysqlrc

NOTE: Make sure that there are no (trailing) spaces in this file, only tabs!!

The below is a part of the authmysqlrc file that is relevant to our setup.
The things that you might need to change are the default_domain, mysql_password,
mysql_uid and mysql_gid.

#DEFAULT_DOMAIN		domain.tld
MYSQL_CRYPT_PWFIELD	password
MYSQL_DATABASE		postfix
MYSQL_GID_FIELD		'1001'
MYSQL_HOME_FIELD	'/usr/local/virtual'
MYSQL_LOGIN_FIELD	username
MYSQL_MAILDIR_FIELD	maildir
MYSQL_NAME_FIELD	name
MYSQL_OPT		0
MYSQL_PASSWORD		postfix
#MYSQL_PORT		0
#MYSQL_QUOTA_FIELD	quota
MYSQL_SERVER		localhost
# Default FreeBSD Socket
#MYSQL_SOCKET		/var/mysql/mysql.sock
# Default RedHat Socket
#MYSQL_SOCKET		/var/lib/mysql/mysql.sock
MYSQL_UID_FIELD		'1001'
MYSQL_USERNAME		postfix
MYSQL_USER_TABLE	mailbox
#MYSQL_WHERE_CLAUSE	server='mailhost.example.com'

Read the hints when you have trouble logging in:

Make sure that there are NO spaces in the authmysqlrc file, only tabs.

Make sure that there are only single quotes ' around static values like: '/usr/local/virtual', 'UID', 'GID'

NO single quotes around localhost!

Make sure that localhost exists in your /etc/hosts file.

Including IPv6 during the compilation could cause a problem.

The MYSQL_GID_FIELD and MYSQL_UID_FIELD are for the UID and GID of the postfix user and group, NOT for the MySQL user and group.

9. Postfix Admin

Here you can find the Postfix Admin tool that I have written in PHP. It's in
production at 2 sites that I maintain. If you want to install this make sure
that you have a WebServer running that handles PHP correctly
and is able to handle .htaccess fiels. I'm not going to answer any questions
on your WebServer / PHP install.

Postfix Admin is a way to give Virtual Domain owners total controll over thier domain.

9.1 Postfix Admin Download

You can download Postfix Admin here.

9.2 Postfix Admin Install

Unpack Postfix Admin in the directory where you want it. For example: /usr/local/www/<site>/postfixadmin
There is also an Admin Admin part, change directory to the "admin" directory and change the path to the
.htpasswd file in the .htaccess file.

Some other information that you might want to look at is in the site_lib.php file.

In order to be able to read & write from the database I have created a seperate user in MySQL.
I do this because Postfix Admin needs to have more rights on the Postfix database.
If you are worried about the password for the database. I have Postfix Admin running as the
WebServer owner:group, that way your postfix username and password are somewhat protected against local users.

USE mysql;
INSERT INTO user (Host, User, Password) VALUES ('localhost','postfixadmin',password('postfixadmin'));
INSERT INTO db (Host, Db, User, Select_priv, Insert_priv, Update_priv, Delete_priv) VALUES ('localhost', 'postfix', 'postfixadmin', 'Y', 'Y', 'Y', 'Y');

Make sure you reload MySQL.

% mysqladmin reload

9.3 Postfix Admin Setup

In order to use Postfix Admin you have to add another table to the Postfix database.

#
# Table structure for table admin
#
USE postfix;
CREATE TABLE admin (
  username varchar(255) NOT NULL default '',
  password varchar(255) NOT NULL default '',
  domain varchar(255) NOT NULL default '',
  create_date datetime NOT NULL default '0000-00-00 00:00:00',
  change_date datetime NOT NULL default '0000-00-00 00:00:00',
  active tinyint(4) NOT NULL default '1',
  PRIMARY KEY (username)
) TYPE=MyISAM COMMENT='Virtual Admins - Store Virtual Domain Admins';

9.4 Postfix Admin Usage

Once you have done all this you can take your browser and browse to the location
where you have Postfix Admin installed. Goto the admin part first in order to put
some real domains that you host in the database. http://your.domain.tld/postfixadmin/admin
The default login is admin / admin.

9.5 Who is using Postfix Admin

BSD WebSolutions

EuroSoftwareParks

ILYA

workAV

YazzY.org

shikoku.gods.de

Misamis University - Ozamiz City, Philippines

This document was written by Mischa Peters. You can reach me at mischa at high5 dot net.
Let me know if you like it.