[Dovecot] Integrating with Drupal SQL db

Tue Mar 12 16:57:31 EET 2013

On Tue, 12 Mar 2013 09:41:42 -0500, "list at airstreamcomm.net"
<list at airstreamcomm.net> wrote:
> On 3/11/13 10:54 PM, info at stos.se wrote:
>> Hi again,
>>
>> this is what I've found regarding how Drupal 7 hashes.
>>
>> $hash = md5($salt . $password, TRUE);
>> do {
>>      $hash = md5($hash . $password, TRUE);
>> } while (--$count);
>>
>>
>> The whole final hash value is encoded into 16 base64 characters and
>> prepended by an identifying string, the standard phpass MD5 mode uses
$P$
>> (Drupal’s modified version uses $S$ to indicate SHA-512) and a single
>> base64 character to indicate the number of MD5 iterations used. Examples
>> of
>> a hashed password are:
>>
>> # Drupal 7 hash
>> $S$CgwilRJS4VIF1.2y0R7B4qkXJ8F8SJPcuvXRKGlMWESVXMST.5n4
>>
>> WordPress 3.0.4 uses the phpass default of 8193 iterations ($count being
>> 8192) and Drupal 7 uses 16385 — notice that the Drupal password has C
>> after the identifier whereas WordPress has B, converted from crypt style
>> base64 (character set [./0-9A-Za-z]) these are 14 and 13 respectively,
>> then
>> take 214 + 1 = 16385. A John the Ripper benchmark, after patching and
>> enabling the usage of phpass portable passwords (WordPress style, 8193
>> iterations), quotes approximately 700 passwords checked per second.
>>
>> Can I use this inforamtion to make Dovecot understand how to interpret
>> the
>> hash?
>>
>> Thanks!
>>
>> Regards
>> Tobias
>>
>> On Mon, 11 Mar 2013 14:00:22 -0500, "list at airstreamcomm.net"
>> <list at airstreamcomm.net> wrote:
>>> On 3/11/13 11:57 AM, info at stos.se wrote:
>>>> Hi
>>>>
>>>> I'm trying to get Dovecot to use Drupal users password for
>> authenticating
>>>> IMAP users. But I just cant figure out how to make Dovecot understand
>> the
>>>> password hash type that Drupal 7 is using.
>>>>
>>>> My example user with password Teacher1 looks like this in Drupal
>>>> database:
>>>> $S$DZwJa.U8HXT2PvTmwCK13rGEYEvnx5DB6/hlqnfCBum4s4U7MVWU
>>>>
>>>> Dovecot retrieves this hash but complains that its not a recognized
>>>> hash
>>>> type, or that the hash is wrong, depending on if I change the default
>>>> hash
>>>> type in Dovecot config.
>>>>
>>>> Any help appreciated.
>>>>
>>>>
>>>> root at SSiS:/etc/postfix# dovecot --version
>>>> 1.2.15
>>>> root at SSiS:/etc/postfix# dovecot -n
>>>> # 1.2.15: /etc/dovecot/dovecot.conf
>>>> # OS: Linux 2.6.32-12-pve i686 Debian 6.0.7 simfs
>>>> log_timestamp: %Y-%m-%d %H:%M:%S
>>>> login_dir: /var/run/dovecot/login
>>>> login_executable: /usr/lib/dovecot/imap-login
>>>> mail_privileged_group: mail
>>>> mail_location: maildir:/home/vmail/
>>>> mbox_write_locks: fcntl dotlock
>>>> auth default:
>>>>     verbose: yes
>>>>     debug: yes
>>>>     debug_passwords: yes
>>>>     passdb:
>>>>       driver: pam
>>>>     passdb:
>>>>       driver: sql
>>>>       args: /etc/dovecot/dovecot-sql.conf
>>>>     userdb:
>>>>       driver: passwd
>>>> root at SSiS:/etc/postfix#
>>>> root at SSiS:/etc/postfix# grep -v '^ *\(#.*\)\?$'
>>>> /etc/dovecot/dovecot-sql.conf
>>>> driver = mysql
>>>> connect = host=127.0.0.1 dbname=Drupal user=Dru_Adm password=localu
>>>> default_pass_scheme = CRYPT
>>>> password_query = SELECT name AS user, pass AS password FROM users
WHERE
>>>> name='%n'
>>>> user_query = SELECT
>>>>
CONCAT(SUBSTRING_INDEX(mail,'@',-1),'/',SUBSTRING_INDEX(mail,'@',1),'/')
>>>> AS
>>>> mail FROM users WHERE name='%n'
>>>> root at SSiS:/etc/postfix# tail /var/log/mail.log
>>>> Mar 11 16:17:42 SSiS dovecot: auth(default): new auth connection:
>>>> pid=8593
>>>> Mar 11 16:17:51 SSiS dovecot: auth(default): client in:
>>>>
>>
AUTH#0111#011PLAIN#011service=imap#011secured#011lip=127.0.0.1#011rip=127.0.0.1#011lport=143#011rport=52316#011resp=AFRlYWNoZXIxAFRlYWNoZXIx
>>>> Mar 11 16:17:51 SSiS dovecot: auth-worker(default):
>>>> pam(Teacher1,127.0.0.1): lookup service=dovecot
>>>> Mar 11 16:17:51 SSiS dovecot: auth-worker(default):
>>>> pam(Teacher1,127.0.0.1): #1/1 style=1 msg=Password:
>>>> Mar 11 16:17:54 SSiS dovecot: auth-worker(default):
>>>> pam(Teacher1,127.0.0.1): pam_authenticate() failed: Authentication
>>>> failure
>>>> (password mismatch?) (given password: Teacher1)
>>>> Mar 11 16:17:54 SSiS dovecot: auth-worker(default):
>>>> sql(Teacher1,127.0.0.1): query: SELECT name AS user, pass AS password
>>>> FROM
>>>> users WHERE name='Teacher1'
>>>> Mar 11 16:17:54 SSiS dovecot: auth-worker(default):
>>>> sql(Teacher1,127.0.0.1): Password mismatch
>>>> Mar 11 16:17:54 SSiS dovecot: auth-worker(default):
>> md5_verify(Teacher1):
>>>> Not a valid MD5-CRYPT or PLAIN-MD5 password
>>>> Mar 11 16:17:54 SSiS dovecot: auth-worker(default): Invalid OTP data
in
>>>> passdb
>>>> Mar 11 16:17:54 SSiS dovecot: auth-worker(default): Invalid OTP data
in
>>>> passdb
>>>> Mar 11 16:17:54 SSiS dovecot: auth-worker(default):
>>>> sql(Teacher1,127.0.0.1): CRYPT(Teacher1) !=
>>>> '$S$DZwJa.U8HXT2PvTmwCK13rGEYEvnx5DB6/hlqnfCBum4s4U7MVWU'
>>>> Mar 11 16:17:56 SSiS dovecot: auth(default): client out:
>>>> FAIL#0111#011user=Teacher1
>>>> Mar 11 16:18:01 SSiS dovecot: imap-login: Disconnected: Too many
>>>> invalid
>>>> commands (auth failed, 1 attempts): user=<Teacher1>, method=PLAIN,
>>>> rip=127.0.0.1, lip=127.0.0.1, secured
>>>> Mar 11 16:32:36 SSiS dovecot: auth(default): new auth connection:
>>>> pid=9075
>>>> Mar 11 16:32:41 SSiS dovecot: imap-login: Disconnected: Too many
>>>> invalid
>>>> commands (no auth attempts): rip=127.0.0.1, lip=127.0.0.1, secured
>>>> root at SSiS:/etc/postfix#
>>>>
>>>>
>>> As far as I understand Drupal uses salted passwords, so you would need
>>> to return the password + salt in the sql query.  I am not sure what
>>> position the salt is offset for a password with Drupal, but that should
>>> be simple to determine looking at the source.
> This is not going to work via SQL query unfortunately.  Another option 
> would be to modify Drupal to also save a copy of the password in another 
> table which could be used for Dovecot.  This module might be what you're 
> looking for http://drupal.org/project/cryptpw.  It creates a table of 
> user information that has a CRYPT password, which dovecot could use for 
> authentication.

That is great! I had the idea of a similar solution but it never crossed my
mind that there might be an actual module for it. I can see that the module
is for Drupal 6, not 7. But it might be possible to find another one.

/T