• Michael [2010-01-15 12:12]:

How do I get IMAP+POP3 vs. LDA to use different user_query statements?

The problem is that the input fields are different.

I don't understand what you mean.

IMAP/POP3 is an email address

LDA is a username

Again, sorry, but I don't understand what you mean.

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'))

Eeek. This wasn't pretty. I've reformatted the query to make it more readable.

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'));

A suggestion is that you replace the first line after WHERE with

 ((mailboxes.username = '%u' AND accounts.active='1') ||

That is, you replace %n with %u.

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.

"I think this is a massive oversight in the design of" your query.

In any case, you can use CASE ... WHEN ... and check whether '%s' (service) is deliver (for LDA) or not...

From what I can tell it seems to assume that a mail system will always use username@domain addresses, and never anything more arbitrary.

What is the format of mailboxes.username in your setup? Will it be unique?

-- -- Kirill Miazine <km@krot.org>