Re: [Dovecot] Dovecot quota with MySQL - username issue

[Axel Luttgens]

Le 26 déc. 2013 à 10:04, michael a écrit :

 Hi all,
 
 I'm using Dovecot 2.2.8 with quota using a MySQL backend. The Quota plugin is 
 listed for POP3, IMAP and LDA.
 
 When it is enabled for LDA, it populates the username field in the MySQL 
 table with e mail addresses. I end up with a mixture of email addresses and 
 usernames, which causes things to not fully work. It *should* be just 
 usernames. the LDA SQL statement is:
 
 [...]
 
 Can this be fixed?
 
 Michael
 

Hello Michael,

I'm a bit late on this one...

I guess your SELECT should return a quota column, probably something like:

CONCAT('dict:user:', mailboxes.username, ':proxy::quotadict') AS quota

because your quota definition in dovecot.conf will be filled with the username 
as known by the service currently making use of it.

HTH,
Axel

[Dovecot] Dovecot quota with MySQL - username issue

[michael]

Hi all,

I'm using Dovecot 2.2.8 with quota using a MySQL backend. The Quota 
plugin is listed for POP3, IMAP and LDA.


When it is enabled for LDA, it populates the username field in the 
MySQL table with e mail addresses. I end up with a mixture of email 
addresses and usernames, which causes things to not fully work. It 
*should* be just usernames. the LDA SQL statement is:


user_query = \
SELECT mailboxes.username, accounts.uid, accounts.uid AS gid, \
concat('/home/',mailboxes.username) AS home, \
(SELECT IF(accounts.new_mail_fmt=1, \
CONCAT('Maildir:',home,'/Maildir'), \
CONCAT('mbox:',home,'/Maildir:INBOX=/var/spool/mail/',mailboxes.username))) 
AS mail_location \

FROM mailboxes JOIN accounts ON accounts.username=mailboxes.username \
WHERE (mailboxes.email='%u' || \
(mailboxes.email=CONCAT('@','%d') AND (SELECT COUNT(email) FROM 
mailboxes \

WHERE email='%u') = 0) AND mailboxes.active IS TRUE)

It clearly knows the username. Usernames on this system are just names, 
not full email addresses.


The configuration of Quota Dict is:

dovecot.conf

dict/quotadict = mysql:/etc/dovecot/dovecot-dict-sql.conf
...
quota = dict:user::proxy::quotadict


dovecot-dict-sql.conf

map {
  pattern = priv/quota/storage
  table = quota
  username_field = username
  value_field = bytes
}
map {
  pattern = priv/quota/messages
  table = quota
  username_field = username
  value_field = messages
}

Can this be fixed?

Michael