* 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:
abcwidg...@theirdomain.tld mailuser1
mailus...@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
usern...@domain addresses, and never anything more arbitrary.
What is the format of mailboxes.username in your setup? Will it be
unique?
--
-- Kirill Miazine <k...@krot.org>
