[Dovecot] SQL field format for digest-md5?

[Darren Pilgrim]

Timo Sirainen wrote:
> On Dec 23, 2008, at 11:51 PM, Darren Pilgrim wrote:
> 
>> Timo Sirainen wrote:
>>> On Dec 23, 2008, at 8:57 PM, Darren Pilgrim wrote:
>>>> I'm enabling digest-md5 authentication with "user at example.com"   
>>>> username and plain-text passwords stored in a MySQL database.   
>>>> What  should the password field contain in order to work with  
>>>> digest-md5?   Would the following:
>>>>
>>>> SELECT CONCAT('{digest-md5}', MD5(CONCAT(username, '::',  
>>>> password)))  AS password ...
>>>>
>>>> be correct?
>>> Don't try to do anything special. Just:
>>> SELECT username as user, password FROM ..
>> That's what I already have.  It works for plain, login and cram-md5;  
>> however, but digest-md5 fails.  Reading the wiki page[1] for digest- 
>> md5 says the user at example.com username format breaks because I'm not  
>> using realms.  My options are either set auth_realms or store  
>> passwords using the DIGEST-MD5 scheme.  I'm trying to do the later  
>> since I can't realistically set or maintain auth_realms.
> 
> So you're using Dovecot v1.0? I think issues related to this are fixed  
> in v1.1 already.

I'm running v1.1.7.

> Anyway that SELECT looks correct. Have you tested that it produces the  
> exact same result as when running dovecotpw -s digest-md5?

I get a different hash from dovecotpw -s digest-md5 than I do from 
MySQL's MD5(CONCAT(username,'::',password)) and the md5 program:

$ dovecotpw -s digest-md5 -u brt.a at srv.twinthornes.com
<password prompts>
{DIGEST-MD5}24b21a60612e1cac3317e44e4354c219

mysql> SELECT MD5(CONCAT(username,'::',password)) AS hash FROM mailbox 
WHERE username='brt.a at srv.twinthornes.com';
+----------------------------------+
| hash                             |
+----------------------------------+
| e422c685cfe2c9be72e2be3172003fca |
+----------------------------------+

$ echo -n "brt.a at srv.twinthornes.com::[password redacted]" | md5
e422c685cfe2c9be72e2be3172003fca


If I store the dovecotpw hash in the password column instead of the 
plaintext password:

mysql> update mailbox set 
password='{DIGEST-MD5}24b21a60612e1cac3317e44e4354c219' where 
username='brt.a at srv.twinthornes.com';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

I still get a password mismatch:

Dec 23 23:50:23 srv dovecot: auth(default): client in: AUTH     2 
DIGEST-MD5      service=smtp    nologin
Dec 23 23:50:23 srv dovecot: auth(default): client out: CONT    2 
cmVhbG09IiIsbm9uY2U9ImVpaEZyTFZlTUtBTEoybFphbHR0QVE9PSIscW9wPSJhdXRoIixjaGFyc2V0PSJ1dGYtOCIsYWxnb3JpdGhtPSJtZDUtc2VzcyI=
Dec 23 23:50:23 srv dovecot: auth(default): client in: CONT<hidden>
Dec 23 23:50:23 srv dovecot: auth-worker(default): 
sql(brt.a at srv.twinthornes.com): query: SELECT password FROM mailbox 
WHERE username = 'brt.a at srv.twinthornes.com' AND active=1
Dec 23 23:50:23 srv dovecot: auth(default): 
digest-md5(brt.a at srv.twinthornes.com): password mismatch
Dec 23 23:50:25 srv dovecot: auth(default): client out: FAIL    2 
user=brt.a at srv.twinthornes.com