[Dovecot] LDA and POP3/IMAP user_query

[Kirill Miazine]

* 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 at theirdomain.tld		mailuser1
>mailuser1 at 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 at domain addresses, and never anything more arbitrary.

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

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