Howto - Configuring Postfix + SASL + MySQL for SMTP Auth

Version 1.0 / 22-Sep-2007
Bill Pitz

Managed IT and Network Engineering: North Bay Logistics, LLC

DISCLAIMER: What follows is the result of my personal findings. What worked for me may not work for you. This document describes setting up MySQL authentication using cleartext passwords. Do not use unless you have a secure channel between the Postfix/SASL server and the MySQL server. I am sharing this information in the hope that it will help others, but in no way will or do I accept any responsibility for whatever may occur as a result of your using this document. You have been warned.

Objectives

A Postfix SMTP server which will perform SMTP AUTH (via AUTH LOGIN commands) against a MySQL database.

The Cyrus SASL package should use its own connection to the database rather than relying on PAM.

I thought this would be a simple undertaking, but it turned out not to be. The web is polluted with dozens of HOWTO guides that all go about accomplishing this goal in different ways. Unfortunately, none of them matched what I wanted to do -- perform SMTP AUTH against a MySQL database in the simplest fashion possible. I quickly discovered that Cyrus-SASL is sorely lacking documentation and is extremely difficult to understand and troubleshoot.

After a great deal of searching, trying, and failing, I finally discovered that it is not necessary to run saslauthd in order to perform the SASL authentication against MySQL. What follows is the bare minimum that I needed to perform in order to get Postfix+SASL+MySQL SMTP AUTH working. Comments welcome, but as always, YMMV. Good luck.

I. What you need:

SASL packages compiled with MySQL support (most RPM distributions will NOT suffice).
Postfix compiled with SASL support (most RPM distributions suffice).
MySQL server configured for access from the Postfix server. Passwords must be plaintext and users must be user@domain.com in a single column, or user in one column and domain in another.

II. Compile Cyrus-SASL

If you can get away with removing the RPM distribution of SASL included with your Linux distribution, do it.

If you need to leave the existing SASL installation in place, make note of where the SASL files are located. On Red Hat and SuSE based systems, this is typically /usr/lib/sasl2.

Retrieve the latest Cyrus-SASL libraries (ftp://ftp.andrew.cmu.edu/pub/cyrus-mail).

Unpack the Cyrus-SASL archive.

Run ./configure with the following options. Tailor to your specific needs, but the set below will work for most situations where you just want to run Postfix->SASL authentication without using saslauthd, etc. At the very least, you NEED to include --enable-sql, --with-mysql=/usr (/usr will work for most Red Hat and SuSE distributions, but your path may be different), and --with-plugindir=/usr/lib/sasl2 (again, /usr/lib/sasl2 will work for most Red Hat and SuSE distributions, but your path may be different -- and, if you're running x86_64, you may need to deal with /usr/lib64 as well).

./configure \ --enable-sql \ --with-mysql=/usr \ --enable-anon \ --enable-plain \ --enable-login \ --disable-krb4 \ --disable-otp \ --disable-cram \ --disable-digest \ --without-pam \ --without-saslauthd \ --without-pwcheck \ --with-dblib=berkeley \ --with-bdb-libdir=/usr/local/bdb/lib \ --with-bdb-incdir=/usr/local/bdb/include \ --with-openssl=/usr/lib/openssl \ --with-plugindir=/usr/lib/sasl2

Run make followed by make install to install. (If you already have Cyrus-SASL2 installed and just want to add the SQL modules, you can just copy plugins/*sql* from the source tree to /usr/lib/sasl2 or equivalent.)

III. Configure Cyrus-SASL for Postfix

1. In your /usr/lib/sasl2 (or equivalent) directory, create a file called smtpd.conf (Note: Use /etc/sasl2/smtpd.conf on SuSE Linux 10.x). In this file, you will need to place the following information:
sasl_pwcheck_method: auxprop sasl_auxprop_plugin: mysql login plain crammd6 digestmd5 sasl_mech_list: PLAIN LOGIN sql_engine: mysql sql_user: SQL USERNAME sql_passwd: SQL PASSWORD sql_hostnames: SQL HOSTNAME sql_database: SQL DATABASE NAME sql_select: SELECT PASSWORD_COLUMN FROM TABLE_NAME WHERE EMAIL_FIELD = '%u@%r'
Replace as follows:

SQL USERNAME = Username for accessing MySQL database
SQL PASSWORD = Password for accessing MySQL database
SQL HOSTNAME = Hostname for accessing MySQL database
SQL DATABASE NAME = Name of database on MySQL server

PASSWORD_COLUMN = The name of the column in the MySQL table containing the plaintext password.
TABLE_NAME = The name of the table in the MySQL database
EMAIL_FIELD = The field containing the e-mail address of the user

sql_select contains the SELECT statement needed to retrieve the user's plaintext password from the table. This is standard MySQL SELECT statement syntax. %u will be replaced with the provided username and %r will be replaced with anything that was after the "@" sign in the string provided as the username. You will have to tailor this line depending upon your MySQL configuration.

IV. Configure Postfix for Cyrus-SASL

Configuring postfix is relatively straightforward. Open /etc/postfix/main.cf in your favorite text editor.

Add the following lines to main.cf:
# Enable SASL Authentication smtpd_sasl_auth_enable = yes # Report Authenticated Username In Headers smtpd_sasl_authenticated_header = yes # Set Path for SASL Auth (this references the smtpd.conf file created earlier) smtpd_sasl_path = smtpd # Support Broken Microsoft Clients broken_sasl_auth_clients = yes

If you don't currently have an smtpd_recipient_restrictions entry, add the following:
smtpd_recipient_restrictions = permit_sasl_authenticated, reject_unauth_destination

If you do have an smtpd_recipient_restrictions entry, add permit_sasl_authenticated to the appropriate position (most likely the beginning).

Restart Postfix.

Attempt to send mail using SMTP-AUTH.

V. Debugging
Unfortunately, the debugging for this option is not particularly great. Your maillog (/var/log/maillog or /var/log/mail) is the best place to look for information. Postfix will drop some of the SASL authentication output here, particularly upon failure. Some information is also logged to /var/log/messages. This worked for me, after hours of searching and trying various configurations. Like with all HOWTOs, YMMV.