dovecot - Jan 2010 - LDA and POP3/IMAP user_query

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

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

Has anyone found a way around this?

Michael

* 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>

On Fri, 2010-01-15 at 12:12 +1300, Michael wrote:
> How do I get IMAP+POP3 vs. LDA to use different user_query statements?
Like Kirill said, you can use %s for it. But sure, it'll make the query
bigger and uglier. Hopefully I'll manage to make v2.0 support something
like:

protocol lda {
  userdb sql {
    args = /etc/dovecot/dovecot-sql-lda.conf
  }
}
protocol imap {
  userdb sql {
    args = /etc/dovecot/dovecot-sql.conf
  }
}
..

Although that also makes it kind of difficult to add it for each
protocol separately.. Maybe I could also add support for

protocol !lda {
  ..
}
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 198 bytes
Desc: This is a digitally signed message part
URL:
<http://dovecot.org/pipermail/dovecot/attachments/20100119/866ff77c/attachment-0002.bin>

On Wed, 20 Jan 2010 01:49:26 you wrote:
> On Fri, 2010-01-15 at 12:12 +1300, Michael wrote:
> > How do I get IMAP+POP3 vs. LDA to use different user_query statements?
>
> Like Kirill said, you can use %s for it. But sure, it'll make the query
> bigger and uglier. Hopefully I'll manage to make v2.0 support something
> like:
>
> protocol lda {
>   userdb sql {
>     args = /etc/dovecot/dovecot-sql-lda.conf
>   }
> }
> protocol imap {
>   userdb sql {
>     args = /etc/dovecot/dovecot-sql.conf
>   }
> }
> ..
>
> Although that also makes it kind of difficult to add it for each
> protocol separately.. Maybe I could also add support for
>
> protocol !lda {
As long as the query can be different for LDA vs. POP3/Imap is good for me 
thanks.

Having said that if there is no reason not to make it a configurable option 
for LDA vs. POP3 vs. Imap may as well go the full way?

Michael