This tutorial describes how to configure a Mail Server using postfix, courier-imap (POP3 and IMAP) and virtual users, using MySQL.

This tutorial was tested on FreeBSD 7.0. Older versions of FreeBSD, postfix and courier-imap should work too.

Some advantages of using an Email Server with virtual users are:
- better security by not creating system users;
- better management of users and domain names.

Before proceding with installation is better to do a cvsup and then compile world and kernel.
A tutorial regarding this topic can be found here: FreeBSD Production Server


Step 1. Install MySQL server and client
-----------------------------------------------------

cd /usr/ports/databases/mysql50-server
make install

After mysql was compiled and installed add the following line to /etc/rc.conf:
mysql_enable="YES"

Also edit /usr/local/etc/rc.d/mysql-server file and change from mysql_enable="NO" to mysql_enable="YES"

Next, start mysql server:
/usr/local/etc/rc.d/mysql-server start

We have mysql installed and started now we must at least set a password for mysql root user. Also if you do not intend to use
mysql server from other servers is better to start mysql server on localhost (127.0.0.1) only.

mysqladmin passwd mypassword
(replace mypassword with your password)


Step 2 Create a MySQL Database for Virtual Users and Domains
--------------------------------------------------------------------------------------

Create a mysql user that will be used by postfix and Courier-IMAP to interogate the database maildb.
In this case, we will name it mailadmin and the password will be mailpass. 

mysql -u root -p

mysql> grant all privileges on *.* to "mailadmin"@"localhost" identified by "mailpass" with grant option;
mysql> grant reload, process on *.* to "mailadmin"@"localhost";

mysql> create database maildb;

 
Use newly created database in order to create tables:

mysql> use maildb;

Create tables within database:

#---------transport table (used for virtual domains)--------------------
CREATE TABLE transport (
id int(3) NOT NULL auto_increment,
domain varchar(128) NOT NULL default "",
transport varchar(128) NOT NULL default "",
PRIMARY KEY (id)
);

#---------mailusers table (used for virtual users)--------------------
CREATE TABLE mailusers (
id int(5) NOT NULL auto_increment,
address varchar(128) NOT NULL default "",
pass varchar(128) NOT NULL default "",
name varchar(128) NOT NULL default "",
uid smallint(5) unsigned default "125",
gid smallint(5) unsigned default "125",
home varchar(128) default "/var/home/mail",
domain varchar(128) default "",
maildir varchar(255) NOT NULL default "",
quota int(11) default "200000",
active tinyint(1) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY id (id),
UNIQUE KEY address (address)
);

mysql> INSERT INTO mailusers values(""," This email address is being protected from spam bots, you need Javascript enabled to view it ", "password", "any name", "125",\

"125", "/var/home/mail", "mail.freebsdonline.com","mail.freebsdonline.com/user/Maildir", "","1"); \n This email address is being protected from spam bots, you need Javascript enabled to view it

#---------alias table (used for users aliases)-------------------

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));

mysql> insert into mailusers values ("", "root", "12345", "root", 125, 125, "/root", "mail.freebsdonline.com", "Maildir/", 0, 1);

mysql> insert into mailusers values ("", "postmaster", "12345", "root", 125, 125, "/root", "mail.freebsdonline.com", "Maildir/", 0, 1);

mysql> insert into mailusers values ("", "operator", "12345", "root", 125, 125, "/root", "mail.freebsdonline.com", "Maildir/", 0, 1);

mysql> insert into alias values ("postmaster"," This email address is being protected from spam bots, you need Javascript enabled to view it ","","","",1);

mysql> insert into alias values ("root"," This email address is being protected from spam bots, you need Javascript enabled to view it ","","","",1);

mysql> insert into alias values ("operator"," This email address is being protected from spam bots, you need Javascript enabled to view it ","","","",1); \n This email address is being protected from spam bots, you need Javascript enabled to view it \n This email address is being protected from spam bots, you need Javascript enabled to view it \n This email address is being protected from spam bots, you need Javascript enabled to view it

cd /usr/ports/security/cyrus-sasl2
make install WITH_MYSQL_VER=50   # change this to your mysql version 

Edit /usr/local/lib/sasl2/smtpd.conf:

#-----------------------------smtpd.conf--------------------------
pwcheck_method: auxprop
auxprop_plugin: sql
sql_engine: mysql
mech_list: plain login
msql_hostnames: localhost
sql_user: mailadmin
sql_passwd: mailpass
sql_database: maildb
sql_select: SELECT pass FROM mailusers WHERE address = '%u@%r'
sql_verbose: yes


Step 4. Install and configure Postfix
------------------------------------------------

Compile and install Postfix with MySQL and SASL2.

cd /usr/ports/mail/postfix
make install

(If you are on 7.0 you will notice current version of postfix is marked broken. If that is your case install an older version of postfix: cd /usr/ports/mail/postfix24; make install).

Modify Postfix files (located in /usr/local/etc/postfix):

#------------------------------main.cf---------------------------------
soft_bounce = no
queue_directory = /var/spool/postfix
command_directory = /usr/local/sbin
daemon_directory = /usr/local/libexec/postfix
mail_owner = postfix
default_privs = nobody

myhostname = mail.freebsdonline.com
inet_interfaces = all
unknown_local_recipient_reject_code = 550
mynetworks = 127.0.0.0/8
mail_spool_directory = /var/home/mail
debug_peer_level = 2
debugger_command =
PATH=/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin
xxgdb $daemon_directory/$process_name $process_id & sleep 5
sendmail_path = /usr/local/sbin/sendmail
html_directory = no
manpage_directory = /usr/local/man
sample_directory = /usr/local/etc/postfix
setgid_group = maildrop
readme_directory = no
alias_database = mysql:/usr/local/etc/postfix/mysql/virtual_alias_maps.cf
alias_maps = mysql:/usr/local/etc/postfix/mysql/virtual_alias_maps.cf
virtual_mailbox_limit = 0
mailbox_size_limit=0
message_size_limit = 102400000

#sasl
smtpd_sasl_local_domain = $myhostname
broken_sasl_auth_clients = yes
smtpd_sasl_auth_enable = yes
smtpd_sasl_application_name=smtpd
smtpd_sasl_security_options = noanonymous
smtpd_helo_required = yes
smtpd_recipient_restrictions = permit_mynetworks,
permit_sasl_authenticated,
reject_invalid_hostname,
reject_non_fqdn_hostname,
reject_non_fqdn_sender,
reject_non_fqdn_recipient,
reject_unknown_sender_domain,
reject_unknown_recipient_domain,
reject_unauth_destination,
permit

#virtual
virtual_uid_maps = static:125
virtual_gid_maps = static:125
virtual_transport = virtual
virtual_alias_maps = mysql:/usr/local/etc/postfix/mysql/virtual_alias_maps.cf
transport_maps=mysql:/usr/local/etc/postfix/mysql/transport.cf
virtual_mailbox_base=/var/home/mail
virtual_mailbox_maps=mysql:/usr/local/etc/postfix/mysql/users.cf
mydestination = $mydomain, $myhostname, $transport_maps

#set local recipient maps
local_recipient_maps = $virtual_mailbox_maps
smtpd_sender_login_maps=mysql:/usr/local/etc/postfix/mysql/sender_check.cf
smtpd_sender_restrictions=reject_sender_login_mismatch, permit_sasl_authenticated

Leave master.cf file default.
Create the directory /var/spool/postfix and chown it to root:
mkdir /var/spool/postfix
chown root /var/spool/postfix
# ---------------------------------- end main.cf -------------------------------


Create a directory named mysql owned by root in /usr/local/etc/postfix and put the following files and content in it:

#-----------------------------sender-check.cf---------------------------------
user=mailadmin
password=mailpass
dbname=maildb
hosts=localhost
query = SELECT address FROM mailusers WHERE address='%s' and active=1

#-----------------------------transport.cf---------------------------------
user=mailadmin
password=mailpass
dbname=maildb
hosts=localhost
query = SELECT transport FROM transport WHERE domain='%s'

#-----------------------------users.cf---------------------------------
user=mailadmin
password=mailpass
dbname=maildb
hosts=localhost
query = SELECT maildir FROM mailusers WHERE address='%s'

#-----------------------------virtual_alias_maps.cf---------------------------------
user=mailadmin
password=mailpass
dbname=maildb
hosts=localhost
query = SELECT goto FROM alias WHERE address='%s' and active=1

#-----------------------------virtual_domains_maps.cf---------------------------------
user=mailadmin
password=mailpass
dbname=maildb
hosts=localhost
query = SELECT domain FROM mailusers WHERE domain='%s'

Change mod to 640 for all files in mysql directory

#cd /usr/local/etc/postfix/mysql
#chmod 640 *


Step 5. Installing Courier-IMAP and Courier-authlib

Compile courier-imap with support for openssl and courier-authlib with support for mysql.

cd /usr/ports/mail/courier-imap
make install

cd /usr/ports/security/courier-authlib
make install

Configure courier-authlib (the authentication daemon for courier-imap):
Go to /usr/local/etc/authlib and modify authdaemonrc.dist into authdaemonrc
Make sure that in authmodulelist authmysql is listed.
Modify authmysqlrc.dist into authmysqlrc and place the following content:

#--------------------authmysqlrc-----------------------------
MYSQL_SERVER localhost
MYSQL_USERNAME mailadmin
MYSQL_PASSWORD mailpass
MYSQL_PORT 3306
MYSQL_OPT 0
MYSQL_DATABASE maildb
MYSQL_USER_TABLE mailusers
#MYSQL_CRYPT_PWFIELD crypt #use this instead of the following in case you prefer to use crypted passwords
MYSQL_CLEAR_PWFIELD clear
MYSQL_UID_FIELD '125'
MYSQL_GID_FIELD '125'
MYSQL_LOGIN_FIELD address
MYSQL_HOME_FIELD '/var/home'
MYSQL_NAME_FIELD name
MYSQL_MAILDIR_FIELD CONCAT("/var/home/mail/",maildir) # in this particular case, you need this to get the full path to the maildir

Note: Make sure not to add spaces in authmysqlrc, only tabs are allowed, and use only single quotes!

Configure courier-imap
Rename the files: imapd-dist, imapd-ssl-dist, pop3d-dist, pop3d-ssl-dist into imapd, imapd-ssl, pop3, pop3-ssl
(choose which daemons you want to run). The ones that you want to start, modify the line that contains the "START" word
from NO to YES ( for example in imapd modify IMAPDSTART=NO to IMAPDSTART=YES).

Step 6 Debug installation - check if all services was started
---------------------------------------------------------------------------------

Check if postfix is started:

ps awux | grep postfix

You should get the following processes:
root     8485  0.0  0.1 15784  3684  ??  Is    4Mar08   0:02.91 /usr/local/libexec/postfix/master
postfix  8487  0.0  0.1 15920  3804  ??  I     4Mar08   0:00.93 qmgr -l -t fifo -u
postfix 70707  0.0  0.1 15868  3684  ??  I     3:20PM   0:00.01 pickup -l -t fifo -u
root    72283  0.0  0.0  5900  1152  p2  RL+   4:06PM   0:00.00 grep postfix

Check if you have courier-imap, courier-pop3 and courier-authlib started:
ps awux | grep courier

root     7101  0.0  0.0  3568   996  p5- I     4Mar08   0:00.31 /usr/local/sbin/courierlogger -pid=/var/run/pop3d.pid -start /usr/l
root     7102  0.0  0.0  3604  1260  p5- I     4Mar08   0:00.57 /usr/local/libexec/courier-imap/couriertcpd -address=0 -maxprocs=40
root     7124  0.0  0.0  3568   996  p5- I     4Mar08   0:00.00 /usr/local/sbin/courierlogger -facility=mail -pid=/var/run/authdaem
root     7125  0.0  0.0 12584  1976  p5- I     4Mar08   0:00.05 /usr/local/libexec/courier-authlib/authdaemond
root     7126  0.0  0.0 12584  2200  p5- I     4Mar08   0:00.16 /usr/local/libexec/courier-authlib/authdaemond
root     7127  0.0  0.0 12584  2200  p5- I     4Mar08   0:00.17 /usr/local/libexec/courier-authlib/authdaemond
root     7128  0.0  0.0 12584  2200  p5- I     4Mar08   0:00.17 /usr/local/libexec/courier-authlib/authdaemond
root     7129  0.0  0.0 12584  2200  p5- I     4Mar08   0:00.17 /usr/local/libexec/courier-authlib/authdaemond
root     7130  0.0  0.0 12584  2200  p5- I     4Mar08   0:00.17 /usr/local/libexec/courier-authlib/authdaemond

courierlogger is for courier-pop3, you also must have a process started for courier-imap and one for authdaemond.

Debugging installation
--------------------------
How to activate logs:
MySQL
Edit /usr/local/etc/rc.d/mysq-server and add to command_args line: --log=/var/log/mysql
Restart MySQL. Log will be written in /var/log/mysql file.

postfix
Errors are written to /var/log/maillog, by default.