Postgresql – Dovecot cannot authenticate through IMAP

dovecotpostfixpostgresql

I want to setup a mail server with postfix + postgresql and dovecot.
At the moment i can send emails to the mail server but i cannot connect to it with my mail client because it says that the password is wrong… Also there is a similar error on my server logs. I am also using the postfix-admin and dovecot 2.2.10. Here is my server log.

Jan 15 20:06:34 myserver dovecot[18749]: auth: Debug: sql(user@example.com,83.32.150.111,<Dnt42AfwnQBTIJZv>): MD5-CRYPT(mypassword) != 'b6486d12312310sa90sa98312df96acf7
Jan 15 20:06:36 myserver dovecot[18749]: auth: Debug: client passdb out: FAIL        1        user=user@example.com

A few more details about my sql query

-- Logs begin at Tue 2013-07-30 12:31:36 UTC, end at Thu 2014-01-16 11:06:05 UTC. --
Jan 16 11:03:05 myserver dovecot[14711]: auth: Debug: sql(user@example.com,83.32.150.111,<OuSwXhTw9gBTIJZv>): query: SELECT username as user, password, '/home/vmail/example.com/user@example.com' as userdb_home, 'maildir:/home/vmail/example.com/user@example.com' as userdb_mail, 5000 as  userdb_uid, 5000 as userdb_gid FROM mailbox WHERE username = 'user@example.com' AND active = '1'
Jan 16 11:03:05 myserver postgres[14694]: LOG:  statement: SELECT username as user, password, '/home/vmail/example.com/user@example.com' as userdb_home, 'maildir:/home/vmail/example.com/user@example.com' as userdb_mail, 5000 as  userdb_uid, 5000 as userdb_gid FROM mailbox WHERE username = 'user@example.com' AND active = '1'
Jan 16 11:03:05 myserver dovecot[14711]: auth: sql(user@example.com,83.32.150.111,<OuSwXhTw9gBTIJZv>): Password mismatch
Jan 16 11:03:05 myserver dovecot[14711]: auth: Debug: sql(user@example.com,83.32.150.111,<OuSwXhTw9gBTIJZv>): MD5-CRYPT(mypassword) != 'ads09d09sa9das90dsa89das099dsa90ads90asd09980dsa98dsa0909dsa980'
Jan 16 11:03:05 myserver dovecot[14711]: auth: Debug: sql(user@example.com,83.32.150.111,<OuSwXhTw9gBTIJZv>): query: SELECT username as user, password, '/home/vmail/example.com/user@example.com' as userdb_home, 'maildir:/home/vmail/example.com/user@example.com' as userdb_mail, 5000 as  userdb_uid, 5000 as userdb_gid FROM mailbox WHERE username = 'user@example.com' AND active = '1'
Jan 16 11:03:05 myserver postgres[14694]: LOG:  statement: SELECT username as user, password, '/home/vmail/example.com/user@example.com' as userdb_home, 'maildir:/home/vmail/example.com/user@example.com' as userdb_mail, 5000 as  userdb_uid, 5000 as userdb_gid FROM mailbox WHERE username = 'user@example.com' AND active = '1'
Jan 16 11:03:05 myserver dovecot[14711]: auth: sql(user@example.com,83.32.150.111,<OuSwXhTw9gBTIJZv>): Password mismatch
Jan 16 11:03:05 myserver dovecot[14711]: auth: Debug: sql(user@example.com,83.32.150.111,<OuSwXhTw9gBTIJZv>): MD5-CRYPT(mypassword) != 'ads09d09sa9das90dsa89das099dsa90ads90asd09980dsa98dsa0909dsa980'
Jan 16 11:03:07 myserver dovecot[14711]: auth: Debug: client passdb out: FAIL        1        user=user@example.com
Jan 16 11:06:05 myserver dovecot[14711]: imap-login: Disconnected: Inactivity (auth failed, 1 attempts in 180 secs): user=<user@example.com>, method=PLAIN, rip=83.32.150.111, lip=176.58.120.193, TLS, session=<OuSwXhTw9gBTIJZv>

This is my /etc/postfix/main.cf

queue_directory = /var/spool/postfix
command_directory = /usr/bin
daemon_directory = /usr/lib/postfix
data_directory = /var/lib/postfix
mail_owner = postfix
myhostname = mail.example.com
mydomain = example.com
myorigin = $mydomain
inet_interfaces = all
mydestination = localhost
unknown_local_recipient_reject_code = 550
mynetworks_style = host
relay_domains = $mydestination
alias_maps = hash:/etc/postfix/aliases
alias_database = $alias_maps
home_mailbox = Maildir/
debug_peer_level = 2
debugger_command =
     PATH=/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin
     ddd $daemon_directory/$process_name $process_id & sleep 5
sendmail_path = /usr/bin/sendmail
newaliases_path = /usr/bin/newaliases
mailq_path = /usr/bin/mailq
setgid_group = postdrop
html_directory = no
manpage_directory = /usr/share/man
sample_directory = /etc/postfix/sample
readme_directory = /usr/share/doc/postfix
inet_protocols = ipv4
relay_domains = *
virtual_alias_maps = pgsql:/etc/postfix/virtual_alias_maps.cf
virtual_mailbox_domains = pgsql:/etc/postfix/virtual_domains_maps.cf
virtual_mailbox_maps = pgsql:/etc/postfix/virtual_mailbox_maps.cf
virtual_mailbox_base = /home/vmail
virtual_mailbox_limit = 512000000
virtual_minimum_uid = 5000
virtual_transport = virtual
virtual_uid_maps = static:5000
virtual_gid_maps = static:5000
smtpd_sasl_auth_enable = yes
smtpd_sasl_type = dovecot
smtpd_sasl_path = /var/run/dovecot/auth-client
smtpd_recipient_restrictions = permit_mynetworks, permit_sasl_authenticated, reject_unauth_destination
smtpd_relay_restrictions = permit_mynetworks, permit_sasl_authenticated, reject_unauth_destination
smtpd_sasl_security_options = noanonymous
smtpd_sasl_tls_security_options = $smtpd_sasl_security_options
smtpd_tls_auth_only = yes
smtpd_tls_cert_file = /etc/ssl/private/server.crt
smtpd_tls_key_file = /etc/ssl/private/server.key
smtpd_sasl_local_domain = $mydomain
broken_sasl_auth_clients = yes
smtpd_tls_loglevel = 1

this is my /etc/dovecot/dovecot.conf

disable_plaintext_auth=no
protocols = imap sieve

ssl = yes
ssl_cert = </etc/ssl/certs/server.crt
ssl_key = </etc/ssl/private/server.key
first_valid_uid = 5000
first_valid_gid = 5000
auth_username_chars = abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890.-_@

namespace {
    location = maildir:/home/vmail/%d/%n/Maildir
    type = private
    prefix = INBOX.
    inbox = yes
    hidden = yes
}

service auth {

    unix_listener auth-userdb {
        mode = 0600
        user = vmail # User running dovecot-lda
        #group = vmail # Or alternatively mode 0660 + dovecot-lda user in this group
      }
}

passdb  {
    driver=sql
    args = /etc/dovecot/dovecot-sql.conf
}

userdb  {
    driver=static
    args = uid=5000 gid=5000 home=/home/vmail/%d/%n allow_all_users=yes
}

protocol imap {
  imap_client_workarounds = delay-newmail tb-extra-mailbox-sep
}

protocol lda {
    postmaster_address = user@example.com
    hostname = local.exaple.com
    sendmail_path = /usr/sbin/sendmail
    mail_plugins = $mail_plugins sieve
    log_path = /var/log/dovecot-lda-errors.log
    info_log_path = /var/log/dovecot-lda.log
}

protocol sieve {
# Defaults are OK, so nothing in this section.
}

plugin {
  sieve = ~/.dovecot.sieve
  sieve_global_path = /home/vmail/globalsieverc
  sieve_dir = ~/
}

passdb {
    driver = sql
    args = /etc/dovecot/dovecot-sql.conf
}

userdb {
    driver = sql
    args = /etc/dovecot/dovecot-sql.conf
}

mail_debug=yes
auth_debug_passwords=yes

And this is the dovecot-sql.conf

driver = pgsql
connect = host=localhost dbname=postfix_db user=user_for_db password=password_for_db
default_pass_scheme = MD5-CRYPT

# Get the mailbox
user_query = SELECT '/home/vmail/%d/%u' as home, 'maildir:/home/vmail/%d/%u' as mail, 5000 AS uid, 5000 AS gid, concat('dirsize:storage=',  quota) AS quota FROM mailbox WHERE username = '%u' AND active = '1'
# Get the password
password_query = SELECT username as user, password, '/home/vmail/%d/%u' as userdb_home, 'maildir:/home/vmail/%d/%u' as userdb_mail, 5000 as  userdb_uid, 5000 as userdb_gid FROM mailbox WHERE username = '%u' AND active = '1'

Thank you in advance.

Best Answer

I don't know to what extent you've redacted the output above, but in the important lines of the SQL query (Password mismatch, and the line that follows) it's comparing an MD5-hash to a very long string.

MD5 hashes are 32 bytes long, so no string at all will ever MD5-hash to the sort of string you give, which looks to be 63 bytes long, and in addition is composed of non-hex characters. If this is not all the result of poor (and pointless) redaction, a mismatch in hashing algorithms may well be your problem (SHA256 produces 64-byte checksums, so that might be what you're supposed to be using).