Mysql – Postfix/Dovecot configuration with virtual users

dovecotMySQLpostfix

I followed this tutorial for configuring Postfix + Dovecot with virtual users (in a mysql database) : https://www.digitalocean.com/community/tutorials/how-to-configure-a-mail-server-using-postfix-dovecot-mysql-and-spamassasin

I can create a user (add a row in the database), and then this user can connect with an email client (using IMAP). The problem is that the user can't receive emails (I tried to send emails from a user to another, so local emails).

I've created 2 users, bla and salut, with the domain isp6.lab (I added in the hosts mail.isp6.lab to 127.0.0.1, no issue from this side for local testing).

The /var/log/mail.log looks like this :

Oct  5 13:43:28 debianR postfix/lmtp[6346]: 4284485120: to=<bla@isp6.lab>, relay=mail.isp6.lab[private/dovecot-lmtp], delay=0.12, delays=0.08/0/0/0.04, dsn=5.1.1, status=bounced (host mail.isp6.lab[private/dovecot-lmtp] said: 550 5.1.1 <bla@isp6.lab> User doesn't exist: bla@isp6.lab (in reply to RCPT TO command))

In my /etc/dovecot/conf.d/10-mail.conf, I have this line for the mail_location :

mail_location = maildir:/var/mail/vhosts/%d/%n

And in my /etc/dovecot/conf.d/auth-sql-conf.ext (just a sample, not the full file):

userdb {
  driver = static
  args = uid=vmail gid=vmail home=/var/mail/vhosts/%d/%n
}

When I go to /var/mail/vhosts/isp6.lab, I see the folders of my 2 users like this :

ls /var/mail/vhosts/isp6.lab
    bla
    salut

So my users can log in, they have their email folders on the system, but somehow they can't receive any email because they "don't exist".

Feel free to ask me for other information if needed, I've looked everywhere but can't find the cause of my problem.

EDIT : debug logs are giving more information :

When sending an email from bla to salut :

Oct 05 15:12:22 auth: Debug: auth client connected (pid=0)
Oct 05 15:12:22 auth: Debug: client in: AUTH    1       LOGIN   service=smtp    nologin lip=192.168.1.2 rip=192.168.1.3 secured
Oct 05 15:12:22 auth: Debug: client out: CONT   1       VXNlcm5hbWU6
Oct 05 15:12:22 auth: Debug: client in: CONT    1       YmxhQGlzcDYubGFi
Oct 05 15:12:22 auth: Debug: client out: CONT   1       UGFzc3dvcmQ6
Oct 05 15:12:22 auth: Debug: client in: CONT    1       YmxhYmxh
Oct 05 15:12:22 auth-worker(7448): Debug: sql(bla@isp6.lab,192.168.1.3): query: SELECT NULL AS password, 'Y' as nopassword, email AS user FROM accounts WHERE email = 'bla@isp6.lab' AND pass = password('***********')
Oct 05 15:12:22 auth: Debug: client out: OK     1       user=bla@isp6.lab
Oct 05 15:12:22 lmtp(7467): Debug: none: root=, index=, control=, inbox=, alt=
Oct 05 15:12:22 lmtp(7467): Info: Connect from local
Oct 05 15:12:22 auth: Debug: master in: USER    1       salut@isp6.lab  service=lmtp
Oct 05 15:12:22 auth-worker(7448): Debug: sql(salut@isp6.lab): query: SELECT NULL AS password, 'Y' as nopassword, email AS user FROM accounts WHERE email = 'salut@isp6.lab' AND pass = password('')
Oct 05 15:12:22 auth-worker(7448): Info: sql(salut@isp6.lab): unknown user
Oct 05 15:12:22 auth: Debug: master out: NOTFOUND       1
Oct 05 15:12:22 lmtp(7467): Debug: auth input:
Oct 05 15:12:22 lmtp(7467): Info: Disconnect from local: Client quit (in reset)
Oct 05 15:12:22 lmtp(7467): Debug: none: root=, index=, control=, inbox=, alt=
Oct 05 15:12:22 lmtp(7467): Info: Connect from local
Oct 05 15:12:22 auth: Debug: master in: USER    2       bla@isp6.lab    service=lmtp
Oct 05 15:12:22 auth-worker(7448): Debug: sql(bla@isp6.lab): query: SELECT NULL AS password, 'Y' as nopassword, email AS user FROM accounts WHERE email = 'bla@isp6.lab' AND pass = password('')
Oct 05 15:12:22 auth-worker(7448): Info: sql(bla@isp6.lab): unknown user
Oct 05 15:12:22 auth: Debug: master out: NOTFOUND       2
Oct 05 15:12:22 lmtp(7467): Debug: auth input:
Oct 05 15:12:22 lmtp(7467): Info: Disconnect from local: Client quit (in reset)

It seems like a SQL query is made for finding the user, but without any password (of course, the sender doesn't have the receiver's password), so the user isn't found. No query should be done as I've set up userdb to be static, there shouldn't be any query to find the users. I guess I could solve temporarily by adding a user_query.

PS : my password query comes from this (to be compatible with MySQL PASSWORD) : http://wiki2.dovecot.org/AuthDatabase/SQL#Password_verification_by_SQL_server

PPS : the second sql request is for the bounce back message, that unsuccessfully bounce back to tell that the mail couldn't be delivered.

Best Answer

I finally found the solution. Postfix is configured to look in the database that the user exists to deliver the email, so Dovecot shouldn't have to do it when receiving an email from Postfix to deliver.

That's how it is supposed to work, and the reason why we define a static userdb. It missed one parameter so that Dovecot wouldn't check if the user exists, because Postfix already does it:

/etc/dovecot/conf.d/auth-sql.conf.ext (only useful part):

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

Adding allow_all_users=yes parameter solved the issue.