Redundant database query?

[Ken Murchison]

Patrick Gibson wrote:
> On 28-Oct-04, at 4:59 PM, Ken Murchison wrote:
> 
>>> sasl_sql_statement: select PASSWORD from USER_ACCOUNTS where 
>>> USERNAME='%u'
>>
>>
>> You won't get rid of the second query for any of the SASL mechanisms. 
>> The only thing you can do is keep it from selecting the *same* column 
>> twice.
> 
> 
> Hi Ken,
> 
> I'm not sure I get what you mean by selecting the same column twice -- 
> unless I'm mistaken, the query selects the PASSWORD column just once.

 From the log that you posted, it looks like its selecting PASSWORD 
twice for user 'patrick'.  Am I incorrect?

> As for not being able to get rid of the second query -- is this a SASL 
> bug?

Depends on your point of view.  I'd say no, but others might disagree.


 > I can't understand why on earth it would be doing this.

SASLv1 used to save a separate secret for each SASL mechanism, e.g. 
cmusaslsecretDIGEST-MD5, cmusaslsecretPLAIN.

SASLv2 now saves only the userPassword secret which is shared amongst 
the mechanisms (except for special cases like OTP).

When each SASL mechanism goes to fetch the secret from the auxprop 
plugin, it doesn't know if the old secret has been upgraded to the new 
secret (which only happens with a plaintext login), so it asks for both, 
and will use whatever it receives.

> I guess I'll try the cyrus-sasl list.

You'll get the same answer from me over there.  ;)


>>
>>> On 28-Oct-04, at 1:53 PM, Ken Murchison wrote:
>>>
>>>> Patrick Gibson wrote:
>>>>
>>>>> I've got cyrus-imapd 2.2.8 setup to authenticate via a MySQL 
>>>>> database. I've enabled logging on the database while I'm setting 
>>>>> things up, and I've noticed that when I login, Cyrus runs the same 
>>>>> query twice:
>>>>>    47 Connect     cyrus at localhost on RETRIX_TRANSITION
>>>>>    47 Query       START TRANSACTION
>>>>>    47 Query       select PASSWORD from USER_ACCOUNTS where 
>>>>> USERNAME='patrick'
>>>>>    47 Query       select PASSWORD from USER_ACCOUNTS where 
>>>>> USERNAME='patrick'
>>>>>    47 Query       COMMIT
>>>>> Any idea as to why this would be happening?
>>>>
>>>>
>>>>
>>>> What does your sql_select option look like?  What SASL mechanism 
>>>> generated this query.
>>>>
>>>> My guess is that SASL is trying to fetch both userPassword and a 
>>>> legacy mech-specific secret (e.g. cmusaslsecretDIGEST-MD5), but 
>>>> since you apparently aren't using the %p variable in your sql_select 
>>>> option (you've hardcoded the property as "PASSWORD", the same column 
>>>> gets selected twice.
>>>>
>>>> -- 
>>>> Kenneth Murchison     Oceana Matrix Ltd.
>>>> Software Engineer     21 Princeton Place
>>>> 716-662-8973 x26      Orchard Park, NY 14127
>>>> --PGP Public Key--    http://www.oceana.com/~ken/ksm.pgp
>>>>
>>> ---
>>> Cyrus Home Page: http://asg.web.cmu.edu/cyrus
>>> Cyrus Wiki/FAQ: http://cyruswiki.andrew.cmu.edu
>>> List Archives/Info: http://asg.web.cmu.edu/cyrus/mailing-list.html
>>
>>
>>
>> -- 
>> Kenneth Murchison     Oceana Matrix Ltd.
>> Software Engineer     21 Princeton Place
>> 716-662-8973 x26      Orchard Park, NY 14127
>> --PGP Public Key--    http://www.oceana.com/~ken/ksm.pgp
>>
> 
> ---
> Cyrus Home Page: http://asg.web.cmu.edu/cyrus
> Cyrus Wiki/FAQ: http://cyruswiki.andrew.cmu.edu
> List Archives/Info: http://asg.web.cmu.edu/cyrus/mailing-list.html
> 


-- 
Kenneth Murchison     Oceana Matrix Ltd.
Software Engineer     21 Princeton Place
716-662-8973 x26      Orchard Park, NY 14127
--PGP Public Key--    http://www.oceana.com/~ken/ksm.pgp
---
Cyrus Home Page: http://asg.web.cmu.edu/cyrus
Cyrus Wiki/FAQ: http://cyruswiki.andrew.cmu.edu
List Archives/Info: http://asg.web.cmu.edu/cyrus/mailing-list.html