SASL MySQL Backend Considered Beneficial

[Ian G Batten]

On 11 Aug 08, at 1648, Martin Schweizer wrote:

> Hello
>
> I have two mail server (FreeBSD 7.0, sendmail/cyrus v2.3.12p2), incl.
> replication with sync_client/-master. Until now they works perfect.
> Now I changed on the sync_server from sasldb (Berkeley db1.85) to
> saslauthd (with saslauthd -a getpwent, the Unix password file). All
> works but not the replication (but the login to cyrus imapd works). So
> I tracked down the problem to the sasldb file. I seems that the sync
> mechanism needs the sync_authname in the sasldb (it not check the
> password file). Is this correct?

In passing, and for what it's worth, one of the best moves I ever made  
on my private Cyrus server, which I'm working myself up to do for my  
day job Cyrus server, was to switch over to using the mysql backend to  
SASL and divorcing it both from the password file and from the /etc/ 
sasldb mechanism.

I did it because it means I can operate mail accounts disjoint from  
real user accounts: I can log in, but my wife, kids, parents etc only  
have the ability to send and receive email.  But most importantly it  
means I have an authentication database which I can secure on a per- 
subsystem basis while sharing records.  Trying to use /etc/sasldb with  
the same authenticators shared between cyrus (running as uid cyrus)  
and sendmail (running as uid smmta) is a living hell, whereas with  
MySQL I just use imapd.conf settings:

sasl_pwcheck_method: auxprop
sasl_auxprop_plugin: sql
sasl_auto_transition: yes
sasl_sql_engine: mysql
sasl_sql_hostnames: localhost
sasl_sql_user: cyrus
sasl_sql_passwd: xxxx
sasl_sql_database: cyrussasl
sasl_sql_select: select %p from users where username = '%u'
sasl_sql_insert: insert into users (username, realm, %p) values ('%u',  
'%r', '%v')
sasl_sql_update: update users set %p='%v' where username='%u'

and in Sendmail.conf (in my case in /opt/sasl2/lib/sasl2, but your  
mileage will vary):


pwcheck_method: auxprop
auxprop_plugin: sql
sql_engine: mysql
sql_hostnames: localhost
sql_user: sendmail
sql_passwd: xxxx
sql_database: cyrussasl
sql_select: SELECT userPassword FROM users WHERE username = '%u'
mech_list: digest-md5 cram-md5
sql_verbose: yes

My database has accreted columns, so when I commission users I have to  
put their secret into several of them, which I should fix one day:

CREATE TABLE `users` (
   `username` varchar(64) NOT NULL default '',
   `realm` varchar(64) default NULL,
   `userPassword` varchar(64) default NULL,
   `cmusaslsecretPLAIN` varchar(64) default NULL,
   `cmusaslsecretDIGEST` varchar(64) default NULL,
   `MD5` varchar(64) default NULL,
   `cmusaslsecretCRAM` varchar(64) default NULL,
   PRIMARY KEY  (`username`)
) TYPE=InnoDB

Not the question you asked, I know, but I'm been meaning to mention  
just how flexible this setup is.

ian