sql connections during login

[Scott Russell]

Greets.

With Cyrus 2.3.1 (built from tgz) and SASL 2.1.19-5 from RHEL4  when 
using sql plugin I've noticed multiple sql queries and connections 
during a single login. I first noticed from a php script built with 
PEAR::Net::Cyrus and then tested using "imtest -a cyrus -w password -m 
digest-md5". Performance is fine on my small 300 user site but I'm 
curious as to why the extra connection happens (note transaction 4734 
and 4735 in log below.) I'm also a bit curious why the password is 
retrieved twice instead of just once.

I should add that the same sequence of connects and queries happen 
regardless of using digest-md5, cram-md5 or login mechs.

> 060209  8:43:38    4734 Connect     cyrus at localhost on mail
>                    4734 Query       START TRANSACTION
>                    4734 Query       select decode(passwd,'salt') as 
> password from account where acct='cyrus' and allowlogin = '1' and 
> passwd is not NULL
>                    4734 Query       select decode(passwd,'salt') as 
> password from account where acct='cyrus' and allowlogin = '1' and 
> passwd is not NULL
>                    4734 Query       COMMIT
>                    4734 Quit
>                    4735 Connect     cyrus at localhost on mail
>                    4735 Quit

The SASL settings from my /etc/imapd.conf:
> sasl_pwcheck_method: auxprop
> sasl_auxprop_plugin: sql
> sasl_mech_list: PLAIN CRAM-MD5 DIGEST-MD5
> sasl_sql_engine: mysql
> sasl_sql_user: cyrus
> sasl_sql_passwd: password
> sasl_sql_hostnames: localhost
> sasl_sql_database: mail
> sasl_sql_select: select decode(passwd,'salt') as password from account 
> where acct='%u' and allowlogin = '1' and passwd is not NULL
> sasl_sql_usessl: no

 From /var/log/auth.log
> Feb  9 09:09:24 imap-test imap[5508]: DIGEST-MD5 server step 1
> Feb  9 09:09:24 imap-test imtest: DIGEST-MD5 client step 2
> Feb  9 09:09:24 imap-test imtest: DIGEST-MD5 client step 2
> Feb  9 09:09:24 imap-test imap[5508]: DIGEST-MD5 server step 2
> Feb  9 09:09:24 imap-test imap[5508]: sql plugin Parse the username cyrus
> Feb  9 09:09:24 imap-test imap[5508]: sql plugin try and connect to a host
> Feb  9 09:09:24 imap-test imap[5508]: sql plugin trying to open db 
> 'mail' on host 'localhost'
> Feb  9 09:09:24 imap-test imap[5508]: begin transaction
> Feb  9 09:09:24 imap-test imap[5508]: sql plugin create statement from 
> userPassword cyrus imap.linux.ibm.com
> Feb  9 09:09:24 imap-test imap[5508]: sql plugin doing query select 
> decode(passwd,'salt') as password from account where acct='cyrus' and 
> allowlogin = '1' and passwd is not NULL;
> Feb  9 09:09:24 imap-test imap[5508]: sql plugin create statement from 
> cmusaslsecretDIGEST-MD5 cyrus imap.linux.ibm.com
> Feb  9 09:09:24 imap-test imap[5508]: sql plugin doing query select 
> decode(passwd,'salt') as password from account where acct='cyrus' and 
> allowlogin = '1' and passwd is not NULL;
> Feb  9 09:09:24 imap-test imap[5508]: commit transaction
> Feb  9 09:09:24 imap-test imap[5508]: sql plugin Parse the username cyrus
> Feb  9 09:09:24 imap-test imap[5508]: sql plugin try and connect to a host
> Feb  9 09:09:24 imap-test imap[5508]: sql plugin trying to open db 
> 'mail' on host 'localhost'
> Feb  9 09:09:24 imap-test imtest: DIGEST-MD5 client step 3
> Feb  9 09:09:24 imap-test imap[5520]: sql auxprop plugin using mysql 
> engine

-- 
Scott Russell <lnxgeek at us.ibm.com>
IBM Linux Technology Center