Cyrus+SASL+PAM+pam_mysql Migration problem

Wed Jun 28 11:53:13 EDT 2006

>>> sender: "Alexander Dalloz" date: "Tue, Jun 27, 2006 at 08:32:52PM +0200" <<<EOQ
> Am Di, den 27.06.2006 schrieb Alexandru E. Ungur um 18:25:
First of all thank you very much for your help, I really appreciate it.

> Do you use virtdomain support with Cyrus-IMAPd? If not, then appending
> the realm isn't necessary. Else you too have to run saslauthd with
> parameter "-r". Your database and SQL statements do not indicate
> user at realm usage.
Yes, we use virtual domains. However, user and domain are two separate
fields in the table and I don't really understand how pam_mysql is
supposed to work with them like that...
Furthermore, I checked the logs on the old server:
============================================================================
[root at mail root]# grep sasl /var/log/messages*|wc
   2314   27798  320250
[root at mail root]# grep sasl /var/log/messages*|grep AUTHFAIL|wc
   2304   27709  319358

So from 2314 entries related to saslauthd, 2304 are reporting an
error. The rest of 10 are reporting the start/stop of saslauthd...
So I don't get it. The old server uses saslauthd for authentication
but all saslauthd does is fail? Or it only logs the failed events?

The old pam.d/imap is:
============================================================================
[root at mail root]# cat /etc/pam.d/imap
auth    optional        /lib/security/pam_mysql.so user=cyrus
passwd=XXX host=127.0.0.1 db=email table=popusers
usercolumn=alias domaincolumn=domain passwdcolumn=password crypt=0
use_relay_ip=1
password   required     /lib/security/pam_mysql.so user=cyrus
passwd=XXX host=127.0.0.1 db=email table=popusers
usercolumn=alias domaincolumn=domain passwdcolumn=password crypt=0
use_relay_ip=1

But on the latest documentation for pam_mysql, there is no reference to
domaincolumn. I guess, what I fail to understand is how saslauthd
passes the appropriate info to pam_mysql and how pam_mysql processes it
so that it can authenticate against the table based on the USER, DOMAIN
and PASSWORD *different* columns. I saw that if I run saslauthd with -r
it tries to authentidate with user at domain against the alias (usercolumn)
but that won't work with these being separate columns...

Also I cleaned up imapd.conf and the error log is much cleaner now
indeed. Here's the cleaned up imapd.conf:
============================================================================
# cat /etc/imapd.conf
configdirectory: /cyrus/imap
partition-default: /cyrus/spool
defaultacl: lrswipcd
admins: cyrus at domainZ=com cyrus at domainZ.com cyrus
allowanonymouslogin: no
timeout: 400
plaintextloginpause: 0
quotawarn: 90
autocreatequota: 50000
singleinstancestore: yes

drachost: localhost
dracinterval: 600

#sasl_pwcheck_method: pam
sasl_pwcheck_method: saslauthd
loginrealms: all
allowplaintext: yes
sasl_mech_list: PLAIN

sieveusehomedir: false
sievedir: /usr/local/sieve
sieve_maxscriptsize: 32
sieve_maxscripts: 5

partition-0: /cyrus/spool/0
partition-1: /cyrus/spool/1

And the errors when trying to use cyradm:
============================================================================
# cyradm -u cyrus localhost
IMAP Password:
              Login failed: authentication failure at
/usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/Cyrus/IMAP/Admin.pm line 118
cyradm: cannot authenticate to server with  as cyrus

[root at mailtx1 ~]# tail -n40 /var/log/debug
Jun 28 03:35:44 mailtx1 master[13434]: about to exec /usr/lib/cyrus-imapd/imapd
Jun 28 03:35:44 mailtx1 imap[13434]: executed 
Jun 28 03:35:44 mailtx1 imap[13434]: sql_select option missing
Jun 28 03:35:44 mailtx1 imap[13434]: auxpropfunc error no mechanism available
Jun 28 03:35:44 mailtx1 imap[13434]: _sasl_plugin_load failed on sasl_auxprop_plug_init for plugin: sql
Jun 28 03:35:44 mailtx1 imap[13434]: accepted connection
Jun 28 03:35:44 mailtx1 perl: No worthy mechs found
Jun 28 03:35:50 mailtx1 saslauthd[13439]: pam_mysql - option verbose is set to "yes"
Jun 28 03:35:50 mailtx1 saslauthd[13439]: pam_mysql - pam_mysql_close_db() called.
Jun 28 03:35:50 mailtx1 saslauthd[13439]: pam_mysql - pam_sm_authenticate() called.
Jun 28 03:35:50 mailtx1 saslauthd[13439]: pam_mysql - pam_mysql_open_db() called.
Jun 28 03:35:50 mailtx1 saslauthd[13439]: pam_mysql - pam_mysql_open_db() returning 0.
Jun 28 03:35:50 mailtx1 saslauthd[13439]: pam_mysql - pam_mysql_check_passwd() called.
Jun 28 03:35:50 mailtx1 saslauthd[13439]: pam_mysql - pam_mysql_format_string() called
Jun 28 03:35:50 mailtx1 saslauthd[13439]: pam_mysql - pam_mysql_quick_escape() called.
Jun 28 03:35:50 mailtx1 saslauthd[13439]: pam_mysql - SELECT password FROM popusers WHERE alias = 'cyrus'
Jun 28 03:35:51 mailtx1 saslauthd[13439]: pam_mysql - pam_mysql_check_passwd() returning 6.
Jun 28 03:35:51 mailtx1 saslauthd[13439]: pam_mysql - pam_mysql_sql_log() called.
Jun 28 03:35:51 mailtx1 saslauthd[13439]: pam_mysql - pam_mysql_sql_log() returning 0.
Jun 28 03:35:51 mailtx1 saslauthd[13439]: pam_mysql - pam_mysql_converse() called.
Jun 28 03:35:51 mailtx1 saslauthd[13439]: pam_mysql - pam_mysql_open_db() called.
Jun 28 03:35:51 mailtx1 saslauthd[13439]: pam_mysql - pam_mysql_check_passwd() called.
Jun 28 03:35:51 mailtx1 saslauthd[13439]: pam_mysql - pam_mysql_format_string() called
Jun 28 03:35:51 mailtx1 saslauthd[13439]: pam_mysql - pam_mysql_quick_escape() called.
Jun 28 03:35:51 mailtx1 saslauthd[13439]: pam_mysql - SELECT password FROM popusers WHERE alias = 'cyrus'
Jun 28 03:35:51 mailtx1 saslauthd[13439]: pam_mysql - pam_mysql_check_passwd() returning 0.
Jun 28 03:35:51 mailtx1 saslauthd[13439]: pam_mysql - pam_mysql_sql_log() called.
Jun 28 03:35:51 mailtx1 saslauthd[13439]: pam_mysql - pam_mysql_sql_log() returning 0.
Jun 28 03:35:51 mailtx1 saslauthd[13439]: pam_mysql - pam_sm_authenticate() returning 0.
Jun 28 03:35:51 mailtx1 saslauthd[13439]: DEBUG: auth_pam: pam_acct_mgmt failed: User account has expired
Jun 28 03:35:51 mailtx1 saslauthd[13439]: pam_mysql - pam_mysql_release_ctx() called.
Jun 28 03:35:51 mailtx1 saslauthd[13439]: pam_mysql - pam_mysql_destroy_ctx() called.
Jun 28 03:35:51 mailtx1 saslauthd[13439]: pam_mysql - pam_mysql_close_db() called.
Jun 28 03:35:51 mailtx1 saslauthd[13439]: do_auth         : auth failure: [user=cyrus] [service=imap] [realm=] [mech=pam] [reason=PAM acct error]
Jun 28 03:35:51 mailtx1 imap[13434]: badlogin: localhost [127.0.0.1] plaintext cyrus SASL(-13): authentication fail

If there's anything else I can do to debug this,
I'd appreciate any tips/rtfms (with links :D)/etc.

Also if there's any other simpler/more straight way of using cyrus+
virtual domains+mysql, where the mysql structure already exists and
has to be used as it is, that'd be great.
The table structure is this:
mysql> describe popusers;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| clientid | int(10) unsigned |      |     | 0       |                |
| emailid  | int(11)          |      | MUL | NULL    | auto_increment |
| alias    | char(32)         |      |     |         |                |
| domain   | char(255)        | YES  |     | NULL    |                |
| password | char(32)         | YES  |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
Where alias is the username, the rest (domain, password) are self
explaining.

Thank you very much,
Alex