Postfix has rapidly become my mail transfer agent (MTA) of choice largely because it was written to allow for the development of modules, but also because it is possessed of an extensive functionality that was easy for me to understand.  Now, I had come from a qmail background, so it took some doin’ to get me away.  However, I think that postfix is a great MTA and here is how to get Postfix working with Courier and MySQL using virtual domains and users.  Why virtual domains?  Well, with all your domains/users/forwards/etc in a database it is much easier to manage than if you had some enormous flat text files.  Also, with a database, you can dynamically add users and domains without having to reload postfix which makes it much more convenient.

As in many of my previous how-tos I use the Debian linux distribution.  Don’t let that discourage you!  These same packages exist for other Linux distributions as well as some BSDs.  Also I am going to be referencing my mail server’s public IP as network.network.network.host but you should use your actual IP address; we will be using the name mail.domain.com as the hostname.  Also, this how-to is completely compatible with my domainkeys how-to over here.

You’re going to need to have a pre-built Debian machine (or Ubuntu) to begin with – let’s assume it’s got it’s own network access – and get root.  Next, we have to install some packages for postfix, mysql, courier, saslauthd, apache, and php (also phpmyadmin):

apt-get install postfix postfix-mysql postfix-doc mysql-client mysql-server courier-authdaemon courier-authlib-mysql courier-pop courier-pop-ssl courier-imap courier-imap-ssl postfix-tls libsasl2 libsasl2-modules libsasl2-modules-sql sasl2-bin libpam-mysql openssl phpmyadmin apache2 libapache2-mod-php5 php5 php5-mysql

Answer the dialog questions with the following answers:

Create directories? No

General Configuration Type? Internet Site

Mail Name? mail.domain.com

SSL required? Ok

On my machine things installed very quickly – in about 5 minutes – but while these packages install you may wish to make a snack, get a drink, or thumb through a comic book.  When this completes, and you’ve been returned to your prompt, you should get your MySQL database setup so Courier can authenticate against it.

You should change the mysql root password first: mysqladmin -u root password mySQLrootPASSWORD

Let’s also create a database for mail (you can use any name you want): mysqladmin -u root -p create maildb

And let’s define the tables in our maildb database by logging into the database as root: mysql -u root -p

Once you’re at the mysql shell you need to create your maildb mysql user which I am going to call postmaster_general with the password mko0*UHB:

GRANT SELECT,INSERT,UPDATE,DELETE privileges on maildb.* TO ‘postmaster_general’@'localhost’ IDENTIFIED BY ‘mko0*UHB’;

GRANT SELECT,INSERT,UPDATE,DELETE ON maildb.* TO ‘postmaster_general’@'localhost.localdomain’ IDENTIFIED BY ‘mko0*UHB’;

It’s important that your MySQL database listen only on localhost and that you limit access to the maildb database from remote IPs.  You want remote access you use phpmyadmin or you don’t get access.  Also be sure you execute the FLUSH PRIVILEGES command before you attempt to access this database as that user.  Now we create the various tables:

USE maildb;

You must first select the database you want to use prior to adding tables or else you get some nasty errors.

CREATE TABLE domains (
domain varchar(50) NOT NULL,
PRIMARY KEY (domain) )
TYPE=MyISAM;

This is the table that domain names are going to go into.  Note that the maximum size domain name you can use is 50 characters.  Really, though, I’ve never seen one with more than half that.

CREATE TABLE forwardings (
source varchar(80) NOT NULL,
destination TEXT NOT NULL,
PRIMARY KEY (source) )
TYPE=MyISAM;

This table will contain information about forwards, sometimes call aliases, and note that the value cannot be NULL or empty.

CREATE TABLE users (
email varchar(80) NOT NULL,
password varchar(20) NOT NULL,
PRIMARY KEY (email)
) TYPE=MyISAM;

This is the table that will hold users and encrypted passwords.

CREATE TABLE transport (
domain varchar(128) NOT NULL default ”,
transport varchar(128) NOT NULL default ”,
UNIQUE KEY domain (domain)
) TYPE=MyISAM;

This table will be responsible for aliased domains.  So if you wanted mail sent to joe@somedomain.net to be delivered to another mail server via smtp this is where that would be stored.  We don’t need anymore tables so enter QUIT; and you’ll be back to the Linux shell.

So at this point you have created your database, tables, and a user.  You have also installed a whole mess of things, for which you are very brave, but it’s time to move on to the next step: configuring postfix to use mysql.

Edit /etc/mysql/my.cf and set the bind-address to 127.0.0.1 – listening only on localhost is an important security guideline for many services.  Do you need mysql listening for some connection out on the internet?  No?  Then bind it to local-host.

Now, to ensure that postix and mysql can work well together, we’re going to have to create some config files to help postfix locate data in the tables we created.  First, to help query domains:

user = postmaster_general
password = mko0*UHB
dbname = maildb
query = SELECT domain AS virtual FROM domains WHERE domain=’%s’
hosts = 127.0.0.1

I called this file vdomains.cf and it is located in /etc/postfix.  Next, the user mailboxes:

user = postmaster_general
password = mko0*UHB
dbname = maildb
query = SELECT CONCAT(SUBSTRING_INDEX(email,’@',-1),’/',SUBSTRING_INDEX(email,’@',1),’/') FROM users WHERE email=’%s’
hosts = 127.0.0.1

I call this file vmailboxes.cf and it is also in /etc/postfix/.  Next we do forwards:

user = postmaster_general
password = mko0*UHB
dbname = maildb
query = SELECT query = SELECT destination FROM forwardings WHERE source=’%s’
hosts = 127.0.0.1

I call this file /etc/postfix/vforwards.cf.  Last, let’s do the file I call vtransport.cf, used for smtp relays:

user = postmaster_general
password = mko0*UHB
dbname = maildb
query = SELECT transport FROM transport WHERE domain=’%s’
hosts = 127.0.0.1

Lastly the email forwards called /etc/postfix/vemail.cf:

user = postmaster_general
password = mko0*UHB
dbname = maildb
query = SELECT email FROM users WHERE email=’%s’
hosts = 127.0.0.1

You’ll need to set the permissions on these files with a little chmod o- ./v*.cf and chgrp postfix ./v*.cf as well.

Now that we’ve defined a means for postfix and mysql to interact, we need to create a user who can access the physical mailboxes of these users:

groupadd -g 3001 mailboxes

useradd -g mailboxes -u 3001 mailboxes -d /home/mailboxes -m

Let’s create the SSL key and csr because I am assuming you want to support SSL:

openssl genrsa -des3 -out /ssl/mail.domain.com.key 1024

openssl req -new -key /ssl/mail.domain.com.key -out /ssl/mail.domain.com.csr

Then create your own self-signed certificate (you can also submit your csr to a certificate authority for approval):

openssl x509 -req -days 365 -in /ssl/mail.domain.com.csr -signkey /ssl/mail.domain.com.key -out /ssl/mail.domain.com.crt

Be sure to chmod o- /ssl/mail.domain.com.* before moving on – you want postfix to be able to read these.

Time to configure postfix (adjust your commands based on what things are called):

postconf -e ‘myhostname = mail.domain.com’
postconf -e ‘mydestination = mail.domain.com, localhost, localhost.localdomain’
postconf -e ‘mynetworks = 127.0.0.0/8′
postconf -e ‘virtual_alias_domains =’
postconf -e ‘virtual_alias_maps = proxy:mysql:/etc/postfix/vforwards.cf, mysql:/etc/postfix/vemail.cf’
postconf -e ‘virtual_mailbox_domains = proxy:mysql:/etc/postfix/vdomains.cf’
postconf -e ‘virtual_mailbox_maps = proxy:mysql:/etc/postfix/vmailboxes.cf’
postconf -e ‘virtual_mailbox_base = /home/mailboxes’
postconf -e ‘virtual_uid_maps = static:3001′
postconf -e ‘virtual_gid_maps = static:3001′
postconf -e ’smtpd_sasl_auth_enable = yes’
postconf -e ‘broken_sasl_auth_clients = yes’
postconf -e ’smtpd_recipient_restrictions = permit_mynetworks, permit_sasl_authenticated, reject_unauth_destination’
postconf -e ’smtpd_use_tls = yes’
postconf -e ’smtpd_tls_cert_file = /ssl/mail.domain.com.cert’
postconf -e ’smtpd_tls_key_file = /ssl/mail.domain.com.key’
postconf -e ‘transport_maps = proxy:mysql:/etc/postfix/vtransport.cf’
postconf -e ‘virtual_create_maildirsize = yes’
postconf -e ‘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’

Did you see the following config parameters?

postconf -e ’smtpd_tls_cert_file = /ssl/mail.domain.com.cert’
postconf -e ’smtpd_tls_key_file = /ssl/mail.domain.com.key’

Well, now you know why we created us some SSLs before.  Note that my quick post about verifying SSLs using the openssl command and modulus applies here.  See this for more info.

Now we work to get sasl functioning, which is part of the process of getting postfix and mysql to play nice.  You must execute a command to create the run directory for saslauthd:

mkdir -p /var/spool/postfix/var/run/saslauthd

Now we edit /etc/default/saslauthd and set START=yes and OPTIONS=”-c -m /var/spool/postfix/var/run/saslauthd -r”

Next cd /etc/pam.d and create a file called smpt and put the following in:

auth    required   pam_mysql.so user=postmaster_general passwd=mko0*UHB host=127.0.0.1 db=maildb table=users usercolumn=email passwdcolumn=password crypt=1
account sufficient pam_mysql.so user=postmaster_general passwd=mko0*UHB host=127.0.0.1 db=maildv table=users usercolumn=email passwdcolumn=password crypt=1

You’re also going to need an /etc/posfix/sasl/smtpd.conf containing:

pwcheck_method: saslauthd
mech_list: plain login
allow_plaintext: true
auxprop_plugin: mysql
sql_hostnames: 127.0.0.1
sql_user: postmaster_general
sql_passwd: mko0*UHB
sql_database: maildb
sql_select: select password from users where email = ‘%u’

Then you restart postfix, saslauthd, and mysql since we skipped that earlier.

Now we have to configure courier so it uses those nifty MySQL databases we setup earlier.  First we edit /etc/courier/authdaemonrc and set authmodulelist=”authmysql” then we need to enter the following as the contents of authmysqlrc in /etc/courier/:

MYSQL_SERVER localhost
MYSQL_USERNAME postmaster_general
MYSQL_PASSWORD mko0*UHB
MYSQL_PORT 0
MYSQL_DATABASE maildb
MYSQL_USER_TABLE users
MYSQL_CRYPT_PWFIELD password
#MYSQL_CLEAR_PWFIELD password
MYSQL_UID_FIELD 3001
MYSQL_GID_FIELD 3001
MYSQL_LOGIN_FIELD email
MYSQL_HOME_FIELD “/home/mailboxes”
MYSQL_MAILDIR_FIELD CONCAT(SUBSTRING_INDEX(email,’@',-1),’/',SUBSTRING_INDEX(email,’@',1),’/')
#MYSQL_NAME_FIELD

restart /etc/init.d/courier* to ensure these changes take.  If you telnet to localhost port 25 you should be able to connect.  Execute ehlo localhost and you should see:

250-STARTTLS

250-AUTH LOGIN PLAIN

These status messages indicate you’re all good.  Add some domains, users, forwards, etc using phpmyadmin and you’re good to go.