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

Michael Fleming -

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.