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

[Jeff Lacki]

Gedalya <gedalya at gedalya.net> wrote:

> 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

Ok thank you both for your help.  I see why I didnt get anything for %w now.

After reading the docs the past few days Im a little unsure of what setup
I should use then for security purposes (primarily potential sniffing).

Obviously using DIGEST-MD5 (per the docs) is better than CRAM-MD5, but
is that possible to use here if I am trying to match passwords out of my
mysql DB?  I certainly do not want to use plaintext (and quite honestly
Im not sure what 'login' really means either, but thats another issue).

My desire is:
- Only virtual users
- All users info stored in mysql
- login is via squirrelmail ATM, and later imap/iphone or other.

Security is my #1 focus right now.

Can someone explain the best solution?  Or is the best solution to just get
an SSL cert and use plaintext?  (which is actually my future plan).

Thanks!
Jeff

/mf/home/jeep/shell/.signature