Postfix MySQL

- Tech and Technology

Postfix + MySQL Virtual Mail How To

Creating the Mysql database

You use the following sql (copy and paste to a file but remember to edit the username, password fields).

Create Database `email`;

grant all privileges on `email`.* to 'username'@'localhost' identified by 'password' with grant option;

CREATE TABLE `alias` (
`email` varchar(255) NOT NULL default '',
`goto` text NOT NULL,
`domain` varchar(255) NOT NULL default '',
PRIMARY KEY (`email`),
KEY `domain` (`domain`)
) TYPE=MyISAM;

INSERT INTO `alias` VALUES ('@vacation.localhost', '@vacation.localhost', 'vacation.localhost');
INSERT INTO `alias` VALUES ('@responder.localhost', '@responder.localhost', 'responder.localhost');

CREATE TABLE `domain` (
`domain` varchar(255) NOT NULL default '',
`transport` varchar(50) NOT NULL default 'virtual',
PRIMARY KEY (`domain`)
) TYPE=MyISAM;

INSERT INTO `domain` VALUES ('vacation.localhost', 'vacation');
INSERT INTO `domain` VALUES ('responder.localhost', 'responder');

CREATE TABLE `mailbox` (
`username` varchar(255) NOT NULL default '',
`password` varchar(255) NOT NULL default '',
`maildir` varchar(255) NOT NULL default '',
`quota` int(10) NOT NULL default '0',
`domain` varchar(255) NOT NULL default '',
`lock` enum('y','n') NOT NULL default 'n'
) TYPE=MyISAM;

CREATE TABLE `vacation` (
`email` varchar(255) NOT NULL default '',
`subject` varchar(255) default NULL,
`message` text,
`cache` text NOT NULL,
`domain` varchar(255) NOT NULL default '',
PRIMARY KEY (`email`),
KEY `domain` (`domain`)
) TYPE=MyISAM;

Building Postfix

You must build Postfix with support for Mysql for this to work. If your using an rpm based distro you can grab the src.rpm file, install it, edit the postfix.spec file in /usr/src/redhat/SPECS and change the Mysql line from 0 to 1 and build the postfix rpm with Mysql support. To build the rpm after changing the spec file simply run rpmbuild -bb postfix.spec.

master.cf file

Edit the master.cf file for Postfix and add the following to the end of the file.

vacation unix - n n - - pipe flags=DRhu user=vacation argv=/home/vacation/hhvacation responder unix - n n - - pipe flags=DRhu user=responder argv=/home/responder/responder.cgi

main.cf file

Edit the main.cf for Postfix and add the following to the bottom of the file. The virtual_minimum_uid and virtual_minimum_gid should be the uid and gid of the postfix user on your system. By default that is user/group 89/89 on CentOS systems.

transport_maps = mysql:/etc/postfix/mysql_virtual_transport.cf
virtual_alias_maps = mysql:/etc/postfix/mysql_virtual_alias_maps.cf
virtual_gid_maps = static:89
virtual_mailbox_base = /usr/local/virtual
virtual_mailbox_domains = mysql:/etc/postfix/mysql_virtual_domains_maps.cf
virtual_mailbox_limit = 51200000
virtual_mailbox_maps = mysql:/etc/postfix/mysql_virtual_mailbox_maps.cf
virtual_minimum_uid = 89
virtual_transport = virtual
virtual_uid_maps = static:89
virtual_create_maildirsize = yes
virtual_mailbox_extended = yes
virtual_mailbox_limit_maps = mysql:/etc/postfix/mysql_virtual_mailbox_limit_maps.cf
virtual_mailbox_limit_override = yes
virtual_maildir_limit_message = Sorry, the user's maildir has no space available in their inbox.
virtual_overquota_bounce = yes

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

virtual_alias_maps = proxy:mysql:/etc/postfix/mysql_virtual_alias_maps.cf
virtual_mailbox_domains = proxy:mysql:/etc/postfix/mysql_virtual_domains_maps.cf

virtual_mailbox_limit_maps = proxy:mysql:/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

mysql_virtual_transport.cf

Create a file named mysql_virtual_transport.cf and add the following to it. Remember to adjust the username and password of the database accordingly.

host = localhost
dbname = email
user = username
password = password
table = domain
select_field = transport
where_field = domain

mysql_virtual_mailbox_maps.cf

Create the file mysql_virtual_mailbox_maps.cf and add the following to it. Remember to adjust the username and password of the database acordingly.

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

mysql_virtual_mailbox_limit_maps.cf

Create the file mysql_virtual_mailbox_limit_maps.cf and add the following to it. Remember to adjust the username and password of the database accordingly.

hosts = localhost
dbname = email
user = username
password = password
table = mailbox
select_field = quota
where_field = username

mysql_virtual_domains_maps.cf

Create the file mysql_virtual_domains_maps.cf and add the following to it. Remember to adjust the username and password of the database accordingly.

hosts = localhost
dbname = email
user = username
password = password
table = domain
select_field = domain
where_field = domain

mysql_virtual_alias_maps.cf

Create the file mysql_virtual_alias_maps.cf and add the following to it. Remember to adjust the username and password of the database accordingly.

hosts = localhost
dbname = email
user = username
password = password
table = alias
select_field = goto
where_field = email

mysql_relay_domains_maps.cf

Create the file mysql_relay_domains_maps.cf and add the following to it. Remember to adjust the username and password of the database accordingly.

hosts = localhost
dbname = email
user = username
password = password
table = domain
select_field = domain
where_field = domain

Courier-imap configuration

I suggest Courier-imap (mainly cause I can tell you how to configure it). To configure Courier-imap for the Mysql based virtual mail you need to edit the etc/imapd file and create/edit the authmysqlrc file for Courier-imap.

First edit the etc/imapd file and add the following line:

IMAP_CAPABILITY="IMAP4rev1 UIDPLUS CHILDREN NAMESPACE THREAD=ORDEREDSUBJECT THREAD=REFERENCES SORT QUOTA IDLE"

Remember to keep it all on one line even if your browser shows it on multiple lines.

Next edit or create the authmysqlrc file (usually found in /etc/authlib after installing courier). Add the following information to the file and remember to adjust the username and password accordingly.

MYSQL_DATABASE email
MYSQL_USERNAME username
MYSQL_LOGIN_FIELD username
MYSQL_CRYPT_PWFIELD password
MYSQL_GID_FIELD '89'
MYSQL_HOME_FIELD '/usr/local/virtual'
MYSQL_MAILDIR_FIELD maildir
MYSQL_OPT 0
MYSQL_PASSWORD password
MYSQL_QUOTA_FIELD quota
MYSQL_SERVER localhost
MYSQL_SOCKET /var/lib/mysql/mysql.sock
MYSQL_UID_FIELD '89'
MYSQL_USER_TABLE mailbox
MYSQL_WHERE_CLAUSE `lock`='n'

the uid and gid should be the uid and gid of the postfix user and group.

smtpd.conf file

The smtpd.conf file is for AUTH SMTP support. You will need to have it contain the following instead of the default. The file can usually be found in /usr/lib/sasl2.

pwcheck_method: saslauthd
mech_list: login plain

saslauthd configuration changes

You will need to tell saslauthd to use mechanism rimap instead of shadow to validate using the imap server.

On rpm based distributions you can create/edit the file /etc/sysconfig/saslauthd and put the following lines in it:

MECH="rimap"
FLAGS="-O localhost"

If your not using an rpm based distribution you will need to make sure saslauthd is started with saslauthd -a rimap -O localhost.

Finishing Up

Now restart courier-authlib, courier-imap, saslauthd, and postfix for the virtual mail system.

Difference from a regular install (according to the INSTALL file)

The hhvacation program should not be installed into /usr/bin on a Postfix/Mysql virtual mail system.

First create a user for vacation with

useradd -d /home/vacation -s /bin/nologin -c "Vacation Responder" vacation

Install the hhvacation file into /home/vacation and make sure it is owned by user vacation.

You will need to edit the hhvacation file and define the following variables.

$virtual="0"; #set this to 1 for virtual mail system
$pfhost=""; #set this to the postfix virtual mail db hostname
$pfname="; #set this to the postfix virtual mail db name
$pfuser=""; #set this to the postfix virtual mail db username
$pfpass=""; #set this to the postfix virutal mail db password

Save the changes and the vacation system is ready to go.
The rest of the gnuhh-hhelper package installs just like any other system.

Virtual Mail is not currently supported by the dedicated mail server daemon. Virtual Mail can only be used when mail is hosted on the same server as the web hosting.
-