Dovecot unable to perform mysql query

Question

I have been following the ISPMail tutorials on workaround.org (the 2.9 Wheezy version) and thus far everything has been working fine. When I reached the step to "Testing email delivery" step I noticed a error about the query in the output log from /var/log/mail.log.

May 14 06:48:59 mail postfix/pickup[17704]: EA4AD240A98: uid=0 from=<root>
May 14 06:48:59 mail postfix/cleanup[17776]: EA4AD240A98: message-id=<[email protected]>
May 14 06:48:59 mail postfix/qmgr[17706]: EA4AD240A98: from=<[email protected]>, size=429, nrcpt=1 (queue active)
May 14 06:49:00 mail dovecot: auth-worker(17782): mysql(127.0.0.1): Connected to database mailserver
May 14 06:49:00 mail dovecot: auth-worker(17782): Warning: mysql: Query failed, retrying: Table 'mailserver.users' doesn't exist
May 14 06:49:00 mail dovecot: auth-worker(17782): Error: sql([email protected]): User query failed: Table 'mailserver.users' doesn't exist (using built-in default user_query: SELECT home, uid, gid FROM users WHERE username = '%n' AND domain = '%d')
May 14 06:49:00 mail dovecot: lda([email protected]): msgid=<[email protected]>: saved mail to INBOX
May 14 06:49:00 mail postfix/pipe[17780]: EA4AD240A98: to=<[email protected]>, relay=dovecot, delay=0.09, delays=0.03/0.01/0/0.06, dsn=2.0.0, status=sent (delivered via dovecot service)
May 14 06:49:00 mail postfix/qmgr[17706]: EA4AD240A98: removed

I found this rather interesting that it isn't finding the DB so I went back through and checked EVERY file that I touched that involved the DB (including the postfix cf files) and everything is correct so I am baffled at this point, but oddly enough it would seem the email still made it to the correct destination in /var/vmail/domain.com/.

Should I be worried about this or am I missing something here? Since it is a message from dovecot it would be the query from dovecot-sql.conf.ext which I am including here

driver = mysql
connect = host=127.0.0.1 dbname=mailserver user=blocked password=***REMOVED***
default_pass_scheme = PLAIN-MD5
password_query = SELECT email as user, password FROM virtual_users WHERE email='%u';

Answer 1

As it says in your mail.log, the value for user_query is not defined. That's why Dovecot is falling back to the default query.

Open up your configuration file /etc/dovecot/dovecot-sql.conf.ext and the value for user_query will most probably be commented out or empty. Fill it with an appropriate query.

Like me, if you are using the same UID and GID for all the mail accounts (say 5000), you can do something as simple as

user_query = SELECT ('5000') as 'uid',('5000') as 'gid'

Just another thing I noticed in your configuration file. Better avoid using PLAIN-MD5 as your default_pass_scheme. Better switch to something stronger like SHA512.

Hope you find my answer helpful. Cheers!

Answer 2

use nano or vi (nano save = ctrl+o, then exit = ctrl+x) vi save = insert button and esc button then :wq! enter It will save and quit.

/etc/dovecot/conf.d/auth-sql.conf.ext

...
passdb {
  driver = sql
  args = /etc/dovecot/dovecot-sql.conf.ext
}
...
userdb {
  driver = static
  args = uid=vmail gid=vmail home=/home/vmail/%d/%n/Maildir
}

Comment out the userdb section with the sql driver:

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

systemctl restart dovecot and it should work.

The problem is that the userdb driver is using sql and it is looking for the table users in the database, but this table does not exist.

This is a problem with a popular tutorial, of which I found a lot of variations circulating on the internet (which is original? I do not know). This tutorial does not explain that the userdb lines should be commented out, it only contains instructions about adding or modifying lines in config files. The tutorial uses MariaDB or MySQL for virtual users table. In this case we need only driver = sql for the passdb, which leads to dovecot-sql.conf.ext, which contains proper query to the database for the password and a static way to get homedir.

I tested this on my server with the settings as I think they should be, after having read the tutorial I found (I think you used the same tutorial) and I had a similar issue. I just triple checked or even quadruple my postfix and dovecot settings on my own server and this solution solved the issue. I configured the dovecot and postfix together with clamav, spamassassin and postgrey. All is working pretty brilliant, but I spent over 40 hours at all to check step by step every single line in dovecot and postfix config files.

Edited March 22nd 2019

I have read a book where there is another explanation for the pam driver.

In /etc/dovecot/conf.d/10-auth.conf file at the end there is a one line:

!include auth-system.conf.ext

In file /etc/dovecot/conf.d/auth-system.conf.ext there is a default commented out section which looks like this one.

#passdb {
    #driver = pam
    # [session=yes] [setcred=yes] [failure_show_msg=yes] [max_requests=<n>]
    # [cache_key=<key>] [<service name>]
    #args = dovecot
#}
#userdb {
# <doc/wiki/AuthDatabase.Passwd.txt>
#driver = passwd
# [blocking=no]
#args =
#override_fields = home=/home/virtual/%u
#}

If you want to use a static local accounts instead of those created in database manually (I did not found any automate way to put mass e-mail account into MariaDB) your code in /etc/dovecot/conf.d/auth-system.conf.ext should be changed to:

passdb {
    driver = pam
    # [session=yes] [setcred=yes] [failure_show_msg=yes] [max_requests=<n>]
    # [cache_key=<key>] [<service name>]
    #args = dovecot
}
userdb {
# <doc/wiki/AuthDatabase.Passwd.txt>
driver = passwd
# [blocking=no]
#args =
#override_fields = home=/home/virtual/%u
}

This is really short explanation. Dovecot can uses SQL database where you create users, or it can use PAM to authenticate local user's accounts in system. It depends only from administrator, which way to choose. In my opinion SQL is much more safe than PAM and /etc/shadow, because if you want to create a user in a MySQL/MariaDB you can choose SHA like in the example below:

INSERT INTO Users_tbl (DomainId, password, Email) VALUES (1, ENCRYPT('yourdifficultpasswordphrase', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), '[email protected]');

And then:

nano /etc/dovecot/dovecot-sql.conf.ext

driver = mysql
connect = host=localhost dbname=yourdbname user=dbuser 
password=yourdifficultpassword
default_pass_scheme = SHA512-CRYPT
password_query = SELECT Email as User, password FROM Users_tbl WHERE 
Email='%u';

SHA-512 do the job. Of course this is really short explanation, but hope it will explain the difference and why SQL is better choice.

Answer 3

Dovecot is looking for a table called users while you seem to have data in virtual_users table:

Query failed, retrying: Table 'mailserver.users' doesn't exist

Either rename the table or configure dovecot to use virtual_users table.

Answer 4

This is not an exact answer but may be relevant to your problem so that new visitors can troubleshoot. see the link below

https://www.dovecot.org/list/dovecot/2013-March/089342.html