[Dovecot] Integrating with Drupal SQL db

[list at airstreamcomm.net]

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.