How do I get IMAP+POP3 vs. LDA to use different user_query statements?
The problem is that the input fields are different.
IMAP/POP3 is an email address
LDA is a username
I have written a long statement to try and cater for both however it still only works 99% of the time, and the remaining 1% fails. The 1% that fails sees the mail being delivered to the wrong mbox. In this particular case the same client owned both mboxes so it was ok, but if this had not been the case things could have gotten real embarrassing.
Think of the following:
abcwidgets@theirdomain.tld mailuser1 mailuser1@adomain.tld mailuser2
Now when using the Postfix LDA, email for each address is properly delivered to the specified mbox, however in this case because their is a matching name for the mbox and the email address lefthand content, the compound SQL query returns both results and the LDA will use the first one, which in this case was the wrong one.
This is the query used:
user_query =
SELECT mailboxes.username AS user, mailboxes.uid, mailboxes.gid,
concat('*:storage=',mailquota,'M') AS quota_rule
FROM mailboxes JOIN accounts ON accounts.username = mailboxes.username
WHERE ((mailboxes.username = '%n' AND accounts.active='1') ||
(mailboxes.email = '%u' AND mailboxes.active='1' AND accounts.active='1') ||
(mailboxes.email = CONCAT('@','%d') AND mailboxes.active='1'
AND (SELECT COUNT(email) FROM mailboxes WHERE email='%u') = '0'))
which additionally caters for the few catch alls our users have.
I think this is a massive oversight in the design of Dovecot to use one query to for 2 separate and distinct processes, that both use different parameters.
From what I can tell it seems to assume that a mail system will always use username@domain addresses, and never anything more arbitrary.
Has anyone found a way around this?
Michael