[Dovecot] Unknown user when sending internal email

[SIW]

I use MySQL to store my virtual users, domains and aliases.

My database is setup as follows:

|CREATE| |TABLE| |`dovecot_passwords` (|
|||`username` ||varchar||(100) ||NOT| |NULL||,|
|||`appname` ||varchar||(50) ||NOT| |NULL||,|
|||`||password||` varbinary(256) ||NOT| |NULL||,|
|||PRIMARY| |KEY| |(`username`,`appname`)|
|)

I then add a users:

|
|INSERT| |INTO| |dovecot_passwords (username, appname, ||password||) 
||VALUES||( ||'test at domain.com'||, ||'desktop'||, MD5(||'password'||) );

My /usr/local/etc/dovecot/dovecot-sql.conf.ext has:

|
|driver = mysql |
|connect = host=127.0.0.1 dbname=mailserver user=mailuser password=blahblah|
|default_pass_scheme = PLAIN
password_query = SELECT NULL AS password,'Y' as nopassword, username AS 
user||||FROM dovecot_passwords||||WHERE username = '%u' AND 
password=MD5(REPLACE('%w',' ',''))


Logging in works great and I can use a desktop email client or webmail 
just fine to check email. The problem occurs when I try to email another 
user in MY domain. When I send an email from user1 at domain.com to 
user2 at domain.com I get the following error in the logs:

  to=<user1 at domain.com>, relay=mail.domain.com[private/dovecot-lmtp], 
delay=0.08, delays=0.05/0.01/0/0.02, dsn=5.1.1, status=bounced (host 
mail.domain.com[private/dovecot-lmtp]  said: 550 5.1.1 
<user1 at domain.com> User doesn't exist: user1 at domain.com (in reply to 
RCPT TO command))

|

I thought it may have something to do with this:

http://wiki2.dovecot.org/DomainLost

To quote:


    SQL

password_query gets often misconfigured to drop the domain if username 
and domain are stored separately. For example:

# BROKEN:
password_query = SELECT username AS user, password FROM users WHERE username = '%n' AND domain = '%d'

The "username AS user" changes the username permanently and the domain 
is dropped. You can instead use:

# MySQL:
password_query = SELECT concat(username, '@', domain) AS user, password FROM users WHERE username = '%n' AND domain = '%d'


Despite the above I didn't have any luck. From what I can tell in the 
logs it is using the username AND domain for the user (and not dropping 
off the domain).

Does anyone know how I can get local mail delivery to work again? I have 
changed the way my users are stored in MySQL so that I can make use of 
"Application Specific Passwords". If I revert back to using the previous 
user database internal mail works fine again. I can't seem to figure 
this out so appreciate any help! Any questions please ask.



The details:

Dovecot version: |2.2.12

# OS: FreeBSD 10.0-STABLE amd64  zfs
auth_debug = yes
auth_mechanisms = plain login
auth_verbose = yes
imap_id_log = *
imap_id_send = *
log_path = /var/log/dovecot.log
login_log_format_elements = user=<%u> method=%m rip=%r lip=%l mpid=%e %c %k
mail_location = maildir:/var/mail/vhosts/%d/%n
mail_privileged_group = mail
managesieve_notify_capability = mailto
managesieve_sieve_capability = fileinto reject envelope 
encoded-character vacation subaddress comparator-i;ascii-numeric 
relational regex imap4flags copy include variables body enotify 
environment mailbox date ihave
namespace inbox {
   inbox = yes
   location =
   mailbox Junk {
     auto = subscribe
     special_use = \Junk
   }
   prefix =
}
passdb {
   args = /usr/local/etc/dovecot/dovecot-sql.conf.ext
   driver = sql
}
plugin {
   sieve = /var/mail/dovecotsieve/%d/%n/.dovecot.sieve
   sieve_default = /var/mail/sieve/default.sieve
   sieve_dir = /var/mail/dovecotsieve/%d/%n/sieve
   sieve_global_dir = /var/mail/sieve/
}
protocols = imap lmtp sieve
service auth-worker {
   user = vmail
}
service auth {
   unix_listener /var/spool/postfix/private/auth {
     group = postfix
     mode = 0666
     user = postfix
   }
   unix_listener auth-userdb {
     mode = 0600
     user = vmail

   }
   user = dovecot
}
service imap-login {
   inet_listener imap {
     port = 0
   }
}
service lmtp {
   unix_listener /var/spool/postfix/private/dovecot-lmtp {
     group = postfix
     mode = 0600
     user = postfix
   }
}
service pop3-login {
   inet_listener pop3 {
     port = 0
   }
   inet_listener pop3s {
     port = 0
   }
}
ssl = required
ssl_cert = 
</usr/local/openssl/certs/mail.domain.com.chained.dovecot.sha256.crt
ssl_cipher_list = 
HIGH:EECDH+ECDSA+AESGCM:EECDH+aRSA+AESGCM:EECDH+ECDSA+SHA384:EECDH+ECDSA+SHA256:EECDH+aRSA+SHA384:EECDH+aRSA+SHA256:EECDH:EDH+aRSA:ECDHE-RSA-AES256-SHA:+DHE-RSA-AES256-SHA:!AES256-SHA256:!AES256-GCM-SHA384:!CAMELLIA256-SHA:!AES128:!CAMELLIA128:!aNULL:!eNULL:!LOW:!3DES:!MD5:!EXP:!PSK:!SRP:!DSS:!SSLv2:!RC4:!SEED:+AES256-SHA
ssl_key = </usr/local/openssl/certs/mail.domain.com.sha256.key
ssl_prefer_server_ciphers = yes
userdb {
   args = uid=vmail gid=vmail home=/var/mail/vhosts/%d/%n
   driver = static
}
protocol imap {
   mail_max_userip_connections = 2000
}
protocol lmtp {
   mail_plugins = " sieve"
}
















|