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

[Jeff Lacki]

> > 
> > 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