[Dovecot] help with AES_DECRYPT and password lookup - mysql password_query

[Gedalya]

On 04/28/2012 06:28 PM, Jeff Lacki wrote:
>>> 1. Is it even possible to do this via 'password_query'?
>> Please provide your dovecot version and output of the following command:
>> doveconf -n
>> and the complete external sql query files without passwords.
>>
>> You might alsolet the SQL server compare the encrypted
>> password in the database with the encrypted string:
>>
>> password_query = SELECT NULL AS password, \
>>    'Y' as nopassword, userid AS user \
>>    FROM users WHERE userid='%u' AND AES_ENCRYPT('%w','mykey')=password
>>
>> Regards,
>> Daniel
> Thank you Daniel.  I downloaded and compiled 2.1.5 yesterday.
> The problem seems to be that '%w' evaulates to an empty string:
>
> Debug: sql(jeff,127.0.0.1): query: SELECT NULL AS password, 'Y' as nopassword, userid AS user FROM users WHERE userid='jeff' AND AES_DECRYPT('', 'key')=password
>
> I also just noticed that version 2.0.15 in my output below is coming from
> somewhere?  I did try setting things up under 2.0.15 initially last week,
> but wanted to be up to date so downloaded the latest yesterday.  I never did
> get it all working under 2.0.15 either btw.
>
> dovecot -n -c /opt/dovecot/etc/dovecot/dovecot.conf
> # 2.0.15: /opt/dovecot/etc/dovecot/dovecot.conf
> # OS: Linux 2.6.35.14-106.fc14.x86_64 x86_64 Fedora release 14 (Laughlin) ext4
> auth_debug = yes
> auth_debug_passwords = yes
> auth_mechanisms = cram-md5
> auth_verbose = yes
> auth_verbose_passwords = plain
> default_client_limit = 225
> first_valid_uid = 1000
> listen = *
> lock_method = flock
> mail_location = mbox:/var/mail/%d/%1n/%n:INDEX=/var/indexes/%d/%1n/%n
> mail_privileged_group = mail
> mbox_lock_timeout = 1 mins
> mbox_write_locks = fcntl
> namespace {
>    inbox = yes
>    location =
>    prefix =
>    separator = .
>    type = private
> }
> passdb {
>    args = /opt/dovecot/etc/dovecot/conf.d/dovecot-sql.conf.ext
>    driver = sql
> }
> protocols = imap
> service auth {
>    inet_listener {
>      port = 12345
>    }
>    unix_listener /var/spool/postfix/private/auth {
>      group = postfix
>      mode = 0666
>      user = postfix
>    }
>    user = $default_internal_user
> }
> service imap-login {
>    inet_listener imap {
>      port = 143
>    }
>    service_count = 1
> }
> ssl_cert =</etc/pki/dovecot/certs/dovecot.pem
> ssl_key =</etc/pki/dovecot/private/dovecot.pem
> userdb {
>    args = /opt/dovecot/etc/dovecot/conf.d/dovecot-sql.conf.ext
>    driver = sql
> }
> userdb {
>    args = /opt/dovecot/etc/dovecot/conf.d/dovecot-sql.conf.ext
>    driver = sql
> }
> userdb {
>    args = /opt/dovecot/etc/dovecot/conf.d/dovecot-sql.conf.ext
>    driver = sql
> }
> protocol imap {
>    imap_idle_notify_interval = 1 mins
>    imap_max_line_length = 64 k
>    mail_max_userip_connections = 5
> }
>
>
> /mf/home/jeep/shell/.signature

Yeap, you seem to only allow cram-md5. In this case, you client isn't 
transmitting the actual password that the user is typing, so dovecot 
simply doesn't have the password you want it to put in %w. It rather has 
a digest of it.
The only way to use a non-plaintext auth mechanism is to provide dovecot 
the correct password from the database in plaintext.

http://wiki2.dovecot.org/Authentication/Mechanisms