Mike’s Mailer Cookbook: SMTP auth, SASL and MySQL

I’m putting this up here as both a reminder to myself and just in case it’s useful to others.

I serve my IMAP user credentials (Courier in my case) from a MySQL backend and know from experience that my users find it very convenient to use the same set of credentials when sending mail with SMTP AUTH.

Now I can extract details and add them to /etc/saslauthdb2 et. al but a) it’s a little too fiddly especially when you’ve got all the security bells and whistles turned on and b) it seems like needless replication, and I’m big fan of KISS theory in systems admin.

So why not use the Cyrus-SASL SQL plugin:

  • Install the SQL plugin – “yum install cyrus-sasl-sql”
  • (For postfix) Create an “smtpd.conf” file in /usr/lib(64)/sasl2″ containing something similar to the following:

pwcheck_method: auxprop
auxprop_plugin: sql
mech_list: cram-md5 digest-md5
sql_engine: mysql
sql_hostnames: <my-db-server>
sql_user: <my-user-with-select-grant>
sql_passwd: <my-sql-password>
sql_database: <my-db-with-imap-creds?
sql_select: SELECT clear FROM passwd WHERE id = ‘%u@%r’ AND active = 1

  • Enable SMTP AUTH in  your config (Read The Fine Material for that)
  • Give it a test run with your MUA of choice.

I personally only offer CRAM/DIGEST (not plaintext LOGIN/PLAIN) because I’m paranoid – you mileage my vary; I also use the stock standard SQL schema that courier-authlib prefers and use user@domain (“%u” – user, “%r” SASL realm, commonly your domain) for virtual login names – adjust the sql_select statement above to suit your environment, and switch “clear” for “crypt” if you want to offer PLAIN / LOGIN instead.

I’ve been using this for what feels like forever but it’s cheap and has served me well; I’m always open to improvements though.

2 Replies to “Mike’s Mailer Cookbook: SMTP auth, SASL and MySQL”

  1. Have you considered using saslauthd and the rimap authentication mechanism? With “rimap” postfix/saslauthd will authenticate the user with the imap daemon. That way you don’t have to put databases specific information in two places (imap config and postfix config) and can use only the imap daemon as single authentication endpoint.

  2. I had considered rimap, however it’s an extra call to the IMAP server, which in turn is another trip to the database – while I’m doing that I might as well call the database directly, as it’s a little quicker (and would scale better for many users). Plus, with my method you can still authenticate and send mail if for whatever reason the IMAP service itself is offline.

Leave a Reply

Your email address will not be published. Required fields are marked *