Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
If you think this is database related, enable log_statement = 'all', re-rest, and edit your question to include the actual SQL and result. Then comment here when done. –  Craig Ringer Jan 16 at 5:12
    
Your DB queries looks a bit weird for me. You have used postfixadmin and its DB schema as backend, isn't it? –  Kondybas Jan 16 at 7:23
    
@CraigRinger Hello, i have upate my question and i have add a more detailed log about my query –  kokeroulis Jan 16 at 11:40
    
@Kondybas Yes, my DB schema is based on postfix-admin. My queries is based on this tutorial. wiki.archlinux.org/index.php/… –  kokeroulis Jan 16 at 11:43

1 Answer 1

You have to change your DB-queries for dovecot in a such way:

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

May be you have to use local_part field instead of username but that is dependant on postfix and authorization scheme you have used.

share|improve this answer
    
Hello, Sorry for my late reply. The above queries didn't work. It still returns the same error. My username field on the postgresql is "user@example.com", while the local_part is "user". So i think that i must use the username field. Right? –  kokeroulis Jan 21 at 10:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.