Incoming emails rejected or POP3 logon rejected

I had ISPConfig 3.0.4 installed and everything working except that all incoming email was rejected with:

Code:

Status: 5.1.1
Diagnostic-Code: x-unix; user unknown

I eventually tracked this down to authentication in Dovecot delivery. The message was being accepted by Postfix, passed through Amavis, back into Postfix and thence to Dovecot.

I then discovered that there had been a problem with the "user_query = " command in /etc/dovecot/dovecot-sql.conf, so I changed it in accordance with

http://bugtracker.ispconfig.org/index.php?do=details&task_id=1634&opened=599&status[0]=

And incoming mails worked! But now email client logging in (which was working) doesn't

It appears that the same SQL statement is used to retrieve the email address during delivery and the account login username at POP3/IMAP login time. If I change the WHERE clause from:

Code:

WHERE login = '%u' AND disable%Ls = 'n'

to

Code:

WHERE email = '%u' AND disable%Ls = 'n'

then the delivery works but login doesn't, and vice-versa.

I should add that ISPConfig is set to allow custom login names.

I *think* a solution is to user the password query to get both username and password (by prepending "userdb_" to the appropriate fields, as per Dovecot docs) and then the user query is only used for delivery. Or, I can change the dovecot config file to use two separate sql config files.

Comments, please?

 

I solved the problem like this:

In dovecot.conf, change

Code:

passdb {
  args = /etc/dovecot/dovecot-sql.conf
  driver = sql
}

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

to

Code:

passdb {
  args = /etc/dovecot/dovecot-sql.conf
  driver = sql
}
userdb {
  driver = prefetch
}
#Following userdb only used for delivery, so lookup on email
userdb {
  args = /etc/dovecot/dovecot-sql.conf
  driver = sql
}

And in dovecot-sql.conf, I used these two queries:

Code:

password_query = SELECT password, /
maildir AS userdb_home, /
uid AS userdb_uid, /
gid as userdb_gid /
FROM mail_user /
WHERE login = '%u' AND disable%Ls = 'n'

user_query = SELECT email as user, /
maildir as home, /
CONCAT('maildir:', maildir, '/Maildir') as mail, /
uid, gid, /
CONCAT('*:storage=', quota, 'B') AS quota_rule /
FROM mail_user /
WHERE email = '%u' AND disable%Ls = 'n'

This is more efficient than the original as only one query is executed to do user/PW lookup for mail client logon. As I've got ISPConfig set to use fixed UID & GID, it may well be possible to optimise this so that only the maildir & quota needs to be fetched from the DB, the rest of the fields being static.

As before, I'd appreciate comments. Although this now works, I still have the niggling feeling it should have been fixed some other way :}