sql connections during login

[Ken Murchison]

Scott Russell wrote:
> 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.


If you look at auth.log, you'll notice that its looking for both 
userPassword (the current plaintext secret used for all mechs) and 
cmusaslsecretDIGEST-MD5 (the legacy DIGEST-MD5 plaintext equivalent). 
SASLv1 used to have mechanism specific entries in sasldb, so we look for 
them for backwards compatibility.


> 
> 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
> 
> 


-- 
Kenneth Murchison
Project Cyrus Developer/Maintainer
Carnegie Mellon University