Important: This post has been updated in a newer article, which adds support for encrypted passwords.

I ran into some issues while setting up a mail server where the users were authenticated through a MySQL database; it wasn’t 100% clear how to do this, and documentation on the ‘net was scarce and mostly not working (outdated most likely).

I’ve managed to set this up on Debian 7.1 (wheezy) – configuration will be similar on other distributions, but of course package names and probably paths will differ.

Note: this setup requires using plaintext passwords in the database. As this password does not lead to any user data, I will be taking the risk of storing them unencrypted; however make sure the passwords used are not the same as the ones leading to user mailboxes, or in case of breach people will be in tears or even worse.

Alternatives:

• You can also use pam_mysql in combination with saslauthd’s PAM checking; which means all MySQL queries are done by the PAM module instead of saslauthd. This requires another package with its own config file; you will find quite some examples of this on the web.
• You can also use saslauthd rimapd functionality to use an IMAP server as the password checking service, which means it will try to auth to your mail server with the specified credentials, and base the ack/nack response on that. This makes your relay server dependent on your IMAP server, which is OK if they are running on the same machine, but I didn’t fully like this idea.
• I later discovered that instead of saslauthd, there is also authdaemond, from the Courier suite; this seems to allow encrypted passwords in the database too. Blog post to set this up here.

Also note that although, in the database, the passwords are not stored encrypted, the authentication to the server will run over a secure, encrypted connection, as we will either use TLS (starting a secure channel from a plaintext session by using the STARTTLS command) or SMTPS (SMTP over SSL) to send mails and authenticate ourselves.

Packages required:

• sasl2-bin
• libsasl2-modules-sql

Configure saslauthd to store its socket inside Postfix’s chroot, by editing /etc/default/saslauthd:

START=yes
OPTIONS="-c -m /var/spool/postfix/var/run/saslauthd"

Add the postfix user into the sasl group:

# adduser postfix sasl

I’ve used the following Postfix parameters in /etc/postfix/main.cf to be able to use SASL authentication:

broken_sasl_auth_clients = yes
smtpd_sasl_auth_enable = yes
smtpd_helo_required = yes
smtpd_tls_CAfile = /etc/ssl/certs/smtpd.pem
smtpd_tls_received_header = yes
smtpd_tls_loglevel = 1

The following directives are used to also use TLS on outgoing mail (being relayed), if available – with fallback to unencrypted:

smtp_use_tls = yes
smtp_tls_security_level = may
smtp_tls_loglevel = 1

Using TLS on outgoing mail is a good idea, as it’ll not deter, but at least slow down our friends at the NSA-and-friends in case they are really interested in your e-mail.

We shouldn’t forget to create the certificate used for TLS; doing this locally with a 10 year validity:

openssl req -new -x509 -nodes -out /etc/ssl/certs/smtpd.pem -keyout /etc/ssl/certs/smtpd.pem -days 3650

You can also use an actual, signed, certificate of course.

To be able to relay using TLS on port 587, and use SMTPS on port 465, add (on Debian, uncomment) the following blocks in /etc/postfix/master.cf:

submission inet n       -       -       -       -       smtpd
  -o syslog_name=postfix/submission
  -o smtpd_tls_security_level=encrypt
  -o smtpd_sasl_auth_enable=yes
  -o smtpd_client_restrictions=permit_sasl_authenticated,reject
  -o milter_macro_daemon_name=ORIGINATING
smtps     inet  n       -       -       -       -       smtpd
  -o syslog_name=postfix/smtps
  -o smtpd_tls_wrappermode=yes
  -o smtpd_sasl_auth_enable=yes
  -o smtpd_client_restrictions=permit_sasl_authenticated,reject
  -o milter_macro_daemon_name=ORIGINATING

If you want to be able to relay using TLS through regular port 25 as well, add the following to main.cf:

smtpd_recipient_restrictions = permit_mynetworks, permit_sasl_authenticated, reject_unauth_destination

I found a lot of examples of the following on the Googles, but as far as I can see none of them contains exactly the one below, so I fiddled until it worked; in /etc/postfix/sasl/smtpd.conf:

pwcheck_method: auxprop
mech_list: plain login
allow_plaintext: true
auxprop_plugin: sql
log_level: 0
sql_engine: mysql
sql_hostnames: 127.0.0.1
sql_user: mail
sql_passwd: topsekr1t
sql_database: mail
sql_select: select password from users where username = '%u@%r'

What seems to be key is the pwcheck_method – most examples set this to saslauthd, which seems to mean the auxprop functions aren’t actually used. One example set it to saslauthd auxprop, which made saslauthd try both PAM and auxprop. This worked, in the end, but produced a PAM failure in the logs for every successful login. Turns out just auxprop is enough to make it query MySQL and MySQL alone.

When using an entire email address as username, it’s split up into %u and %r automatically. If you’re using only login names, the sql_select query will have to be modified to use just %u (or you could perhaps add an OR clause, to be able to mix both systems).

In MySQL, don’t forget to allow 127.0.0.1 as connecting host for this user, this is not the same as “localhost”. Localhost has a special meaning for MySQL, which means “use the unix socket”, and as Postfix is chrooting, it won’t have a localhost unix socket to talk to.

While debugging, you can set log_level in this file to a higher number, and find results in /var/log/auth.log (not syslog or other files where I mistakenly looked first).

The database is setup as follows:

CREATE TABLE users (`username` VARCHAR(80) NOT NULL, `password` VARCHAR(20) NOT NULL, PRIMARY KEY (username)) ENGINE=MyISAM;
INSERT INTO `users` VALUES ('tom@mylittlepony.be', 'friendshipismagic');

This will allow SASL-authenticated TLS-secured mail to be sent with username tom@mylittlepony.be with password friendshipismagic (*).

Final note: make sure you have reverse on your outgoing ipv6 address if you want to send mail to Google’s servers. Set the smtp_bind_address6 Postfix directive to the IP you want to use, in case there are multiple on your machine.

(*) Not a brony. Seriously. Fortunately.