Hello,
I've been frying my brain with quota and mysql... Here is what I do:
protocol imap {
mail_plugins = quota imap_quota
imap_client_workarounds = outlook-idle delay-newmail
}
plugin {
# 10 MB + 1000 messages quota limit
# quota = maildir:storage=10240:messages=1000
driver = mysql
connect = host=/var/lib/mysql/mysql.sock user=mail_admin
password=XXXXXXXX dbname=mail
user_query = SELECT CONCAT(('/home/vmail/'),
SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/'),
CONCAT('5000'), CONCAT('5000'),
CONCAT('maildir:storage=', quota) FROM
users AS quota WHERE email = '%u';
}
The querry output looks like this:
+----------------------------------------------------------------------------------------------+----------------+----------------+-----------------------------------+
| CONCAT(('/home/vmail/'),
SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/')
|
CONCAT('5000') | CONCAT('5000') |
CONCAT('maildir:storage=', quota) |
+----------------------------------------------------------------------------------------------+----------------+----------------+-----------------------------------+
| /home/vmail/goofy.celuloza.ro/bazy/
| 5000 | 5000 |
maildir:storage=102400000 |
+----------------------------------------------------------------------------------------------+----------------+----------------+-----------------------------------+
I made the query with
http://wiki.dovecot.org/Quota?highlight=%28imap_quota%29 as an example.
The result is:
. OK Logged in.
. capability
* CAPABILITY IMAP4rev1 SASL-IR SORT THREAD=REFERENCES MULTIAPPEND
UNSELECT LITERAL+ IDLE CHILDREN NAMESPACE LOGIN-REFERRALS QUOTA
. OK Capability completed.
. getquotaroot Inbox
. OK No quota.
. getquotaroot inbox
. OK No quota.
. getquotaroot Trash
. OK No quota.
How can I get quota to work?... I'm out of ideas... and I'm an dovecot
NUB... postfix sasl with dovecot with mysql with ENCRYPTED password
works great, pop3 imap and imaps work great also. I just can't get quota
to work... :(
Any advice is welcome.
Thank you!
Bazy wrote on 28-8-2007 23:05:> plugin { > # 10 MB + 1000 messages quota limit > # quota = maildir:storage=10240:messages=1000 > > driver = mysql > connect = host=/var/lib/mysql/mysql.sock user=mail_admin > password=XXXXXXXX dbname=mail > user_query = SELECT CONCAT(('/home/vmail/'), > SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/'), > CONCAT('5000'), CONCAT('5000'), CONCAT('maildir:storage=', quota) FROM > users AS quota WHERE email = '%u'; > } > > The querry output looks like this: > > +----------------------------------------------------------------------------------------------+----------------+----------------+-----------------------------------+ > | CONCAT(('/home/vmail/'), > SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') | > CONCAT('5000') | CONCAT('5000') | CONCAT('maildir:storage=', quota) | > +----------------------------------------------------------------------------------------------+----------------+----------------+-----------------------------------+ > | /home/vmail/goofy.celuloza.ro/bazy/ > | 5000 | 5000 | > maildir:storage=102400000 | > +----------------------------------------------------------------------------------------------+----------------+----------------+-----------------------------------+ >I'm not the expert on the matter, but I think your columns are named wrong. Try changing the latter part of the user_query to 'CONCAT('maildir:storage=', quote) AS quota FROM users WHERE email = '%u'; As an example, I've posted my userdb query: user_query = SELECT concat('/mail/', maildir) as home, concat('maildir:/mail/', maildir) as mail, 108 AS uid, 116 AS gid, concat('maildir:storage=', quota) AS quota FROM mailbox WHERE username '%u' AND active = '1' Hope this helps! Nils. -------------- next part -------------- A non-text attachment was scrubbed... Name: signature.asc Type: application/pgp-signature Size: 187 bytes Desc: OpenPGP digital signature URL: <http://dovecot.org/pipermail/dovecot/attachments/20070829/ec70c3d1/attachment-0002.bin>
Nils Vogels wrote:> Bazy wrote on 28-8-2007 23:05: >> plugin { >> # 10 MB + 1000 messages quota limit >> # quota = maildir:storage=10240:messages=1000 >> >> driver = mysql >> connect = host=/var/lib/mysql/mysql.sock user=mail_admin >> password=XXXXXXXX dbname=mail >> user_query = SELECT CONCAT(('/home/vmail/'), >> SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/'), >> CONCAT('5000'), CONCAT('5000'), CONCAT('maildir:storage=', quota) FROM >> users AS quota WHERE email = '%u'; >> } >> >> The querry output looks like this: >> >> +----------------------------------------------------------------------------------------------+----------------+----------------+-----------------------------------+ >> | CONCAT(('/home/vmail/'), >> SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') | >> CONCAT('5000') | CONCAT('5000') | CONCAT('maildir:storage=', quota) | >> +----------------------------------------------------------------------------------------------+----------------+----------------+-----------------------------------+ >> | /home/vmail/goofy.celuloza.ro/bazy/ >> | 5000 | 5000 | >> maildir:storage=102400000 | >> +----------------------------------------------------------------------------------------------+----------------+----------------+-----------------------------------+ >> > > I'm not the expert on the matter, but I think your columns are named > wrong. Try changing the latter part of the user_query to > 'CONCAT('maildir:storage=', quote) AS quota FROM users WHERE email = '%u'; > > As an example, I've posted my userdb query: > > user_query = SELECT concat('/mail/', maildir) as home, > concat('maildir:/mail/', maildir) as mail, 108 AS uid, 116 AS gid, > concat('maildir:storage=', quota) AS quota FROM mailbox WHERE username > '%u' AND active = '1' > > > Hope this helps! > > Nils. >Thank you Nils, It's working now. I had some configuration options wrong in my mind, I didn't understand them... It looks like this now: auth default { mechanisms = plain login cram-md5 digest-md5 passdb sql { args = /etc/dovecot-sql.conf } # userdb static { # args = uid=5000 gid=5000 home=/home/vmail/%d/%n # } userdb sql { args = /etc/dovecot-userdb-sql.conf } As you can see, i had a userdb static, now I'm querying mysql for it :) and with your help I fixed my query. It looks like this now: SELECT CONCAT(('/home/vmail/'), SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') as home, 5000 as uid, 5000 as gid, CONCAT('maildir:storage=', floor(quota/1024)) as quota FROM users WHERE email = '%u'; Thank you!