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