Virtual Users and Domains with Courier-IMAP and MySQL

From Postfix Wiki

Introduction

This document is written for Postfix 2.0 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 SquirrelMail.

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.

SASL is the Simple Authentication and Security Layer, a method for adding authentication support to connection-based protocols. To use SASL, a protocol includes a command for identifying and authenticating a user to a server and for optionally negotiating protection of subsequent protocol interactions. If its use is negotiated, a security layer is inserted between the protocol and the connection.

Postfix VDA enables quota support for Postfix.

If you are planning to use this howto as a basis for Postfix Admin, please be aware that there is some differences in the tables.

Please read the TABLE_CHANGES.TXT

Disclaimer

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

MySQL Install

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

On slackware 10 I do this way:

% chown mysql.mysql /var/lib/mysql/ -R && mysql_install_db && sh /etc/rc.d/rc.mysqld start \
&& mysql_secure_installation && chown mysql.mysql /var/lib/mysql/ -R && mysqld_safe --user=mysql& \
&& mysqladmin -u root password 'sua_senha, your password'

MySQL Setup

Create the database

% mysqladmin -u root --password='sua_senha, your password' create postfix

These columns are used to make your life easier together with Postfix Admin:

created
modified
active

The "active" column is not used at the moment.

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 '',
  `created` datetime NOT NULL default '0000-00-00 00:00:00',
  `modified` datetime NOT NULL default '0000-00-00 00:00:00',
  `active` tinyint(1) NOT NULL default '1',
  PRIMARY KEY  (address)
) TYPE=MyISAM COMMENT='Postfix Admin - Virtual Aliases';

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

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

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 '',
  `aliases` int(10) NOT NULL default '0',
  `mailboxes` int(10) NOT NULL default '0',
  `maxquota` int(10) NOT NULL default '0',
  `transport` varchar(255) default NULL,
  `backupmx` tinyint(1) NOT NULL default '0',
  `created` datetime NOT NULL default '0000-00-00 00:00:00',
  `modified` datetime NOT NULL default '0000-00-00 00:00:00',
  `active` tinyint(1) NOT NULL default '1',
  PRIMARY KEY  (domain)
) TYPE=MyISAM COMMENT='Postfix Admin - Virtual Domains';

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

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 '',
  `quota` int(10) NOT NULL default '0',
  `domain` varchar(255) NOT NULL default '',
  `created` datetime NOT NULL default '0000-00-00 00:00:00',
  `modified` datetime NOT NULL default '0000-00-00 00:00:00',
  `active` tinyint(1) NOT NULL default '1',
  PRIMARY KEY  (`username`)
) TYPE=MyISAM COMMENT='Postfix Admin - Virtual Mailboxes';

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

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$caea3837$gPafod/Do/8Jj5M9HehhM.','Mailbox User','user@domain.tld/');

The password (MD5 encrypted) is "secret" ($1$caea3837$gPafod/Do/8Jj5M9HehhM.)

This is a "standard" MD5 encrypted password out of /etc/passwd.

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

The second INSERT is a 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 built in MySQL routine to generate MD5 passwords. This is not compatible with Courier-IMAP. If you want you can also use clear text or encrypted passwords.

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

% mysqladmin -u root --password='sua_senha, your password' reload

Postfix Install

Build and install Postfix 2.x, or the latest snapshot. Make sure that you at least build it with MySQL & Postfix VDA (quota). Apply patch like this:

% zcat postfix-x.x.x-vda.patch.gz | patch -p0

I built everything in FreeBSD and the default location is /usr/local/etc/postfix. Your configuration might be different.

RedHat 9 (including SASL):

% make makefiles 'CCARGS=-DHAS_MYSQL -I/usr/include/mysql -DUSE_SASL_AUTH -I/usr/include/sasl' \
'AUXLIBS=-L/usr/lib/mysql -lmysqlclient -lz -lm -L/usr/lib -lsasl'

% make; make install;

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 771 /usr/local/virtual

Postfix Setup

main.cf

The example below 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_limit = 51200000
virtual_mailbox_maps = mysql:/usr/local/etc/postfix/mysql_virtual_mailbox_maps.cf
virtual_minimum_uid = 1001
virtual_transport = virtual
virtual_uid_maps = static:1001
# Additional for quota support
virtual_create_maildirsize = yes
virtual_mailbox_extended = yes
virtual_mailbox_limit_maps = mysql:/usr/local/etc/postfix/mysql_virtual_mailbox_limit_maps.cf
virtual_mailbox_limit_override = yes
virtual_maildir_limit_message = Sorry, the user's maildir has overdrawn his diskspace quota, please try again later.
virtual_overquota_bounce = yes

User comment: virtual_gid_maps and virtual_uid_maps should be set to your 'postfix' gid/uid. You may also have to set virtual_minium_uid, as well.

If you want to use MySQL also to store your Backup MX domains add this as well

relay_domains = mysql:/usr/local/etc/postfix/mysql_relay_domains_maps.cf

Performance and reliability under high load will be much improved if you use the Postfix proxymap service with your MySQL interface. This allows MySQL query connections to be shared among Postfix smtpd processes; without it, you will need much higher-end database hardware as Postfix will need to spawn a number of SQL connections for every smtpd or cleanup process. This problem typically only shows up under high load, just when you least want to see it.

To access MySQL via proxymap, change the MySQL maps lines above to read:

virtual_alias_maps = proxy:mysql:/usr/local/etc/postfix/mysql_virtual_alias_maps.cf
virtual_mailbox_domains = proxy:mysql:/usr/local/etc/postfix/mysql_virtual_domains_maps.cf
virtual_mailbox_maps = proxy:mysql:/usr/local/etc/postfix/mysql_virtual_mailbox_maps.cf

and

relay_domains = proxy:mysql:/usr/local/etc/postfix/mysql_relay_domains_maps.cf

To proxy your virtual_mailbox_limit_maps queries, you must add the map to the proxy_read_maps variable, as that map gets added via the VDA quota patch and is not included in the list of maps which Postfix will automatically allow to be proxied:

virtual_mailbox_limit_maps = proxy:mysql:/usr/local/etc/postfix/mysql_virtual_mailbox_limit_maps.cf
proxy_read_maps = $local_recipient_maps $mydestination $virtual_alias_maps
  $virtual_alias_domains $virtual_mailbox_maps $virtual_mailbox_domains
  $relay_recipient_maps $relay_domains $canonical_maps $sender_canonical_maps
  $recipient_canonical_maps $relocated_maps $transport_maps $mynetworks
  $virtual_mailbox_limit_maps