[Dovecot] Are you successfully using using SQL queries to manage Dovecot2 Quota Values & Limits?
Rich
pgnet.dev+rich-dovecot at gmail.com
Fri Oct 28 23:01:17 EEST 2011
On Mon, Oct 24, 2011 at 11:14 AM, Rich <pgnet.dev+rich-dovecot at gmail.com> wrote:
> I'm able to consistently reproduce this problem/behavior, but not
> workaround or fix it, yet.
>
> Rich
>
> On Wed, Oct 19, 2011 at 7:41 AM, Rich <pgnet.dev+rich-dovecot at gmail.com> wrote:
>> I've made some progress, but quota 'Limit' is still not fully
>> functioning for me.
>>
>> A couple of changes have helped:
>>
>> Specifying a "%u% as username format,
>>
>> /etc/dovecot/conf.d/90-quota.conf
>> plugin {
>> - quota = dict:User Quota::proxy::quota
>> + quota = dict:User Quota:%u:proxy::quota
>> quota_rule = *:bytes=1073741824:messages=10000
>> quota_rule2 = Trash:storage=+10%%
>> }
>>
>> using 'username' rather than 'user' in the user iteration query
>>
>> /etc/dovecot/sql/virtmail-userdb-sql.cf
>> driver = mysql
>> connect = host=/var/run/mysql/mysql.sock dbname=my_db user=my_user
>> password=my_pass
>> user_query = CALL UserDBQuery('%n','%d');
>> - iterate_query = SELECT `MAILBOX_user_domain` AS user FROM `PARAMS`;
>> + iterate_query = SELECT `MAILBOX_user_domain` AS username FROM `PARAMS`;
>>
>> and adding to my user_query,
>>
>> CREATE PROCEDURE `UserDBQuery`(
>> ...
>> SELECT ...
>> concat('*:bytes=', quota_bytes, ':messages=10000') AS quota_rule,
>> ...
>>
>> Now, at init,
>>
>> doveadm quota get -A
>> Username Quota name Type Value Limit %
>> myuser at domain1.com User quota STORAGE 0 0 0
>> myuser at domain1.com User quota MESSAGE 0 10000 0
>> myuser at domain2.com User quota STORAGE 0 0 0
>> myuser at domain2.com User quota MESSAGE 0 10000 0
>>
>> mysql> select * from PARAMS;
>> +----+------------------------+------------+-------------+
>> | ai | MAILBOX_user_domain | quota_bytes | quota_msgs |
>> +----+------------------------+-------------+------------+
>> | 1 | myuser at domain1.com | 0 | 0 |
>> | 2 | myuser at domain2.com | 0 | 0 |
>> +----+------------------------+------------+-------------+
>> 2 rows in set (0.00 sec)
>>
>> and, after sending a single message to 'myuser at domain1.com', I do see
>> that Dovecot now recognizes/calculates a quota change, and only for
>> one domain,
>>
>> doveadm quota get -A
>> Username Quota name Type Value Limit %
>> myuser at domain1.com User quota STORAGE 3 3 100
>> myuser at domain1.com User quota MESSAGE 1 10000 0
>> myuser at domain2.com User quota STORAGE 0 0 0
>> myuser at domain2.com User quota MESSAGE 0 10000 0
>>
>> mysql> select * from PARAMS;
>> +----+------------------------+------------+-------------+
>> | ai | MAILBOX_user_domain | quota_bytes | quota_msgs |
>> +----+------------------------+-------------+------------+
>> | 1 | myuser at domain1.com | 3269 | 1 |
>> | 2 | myuser at domain2.com | 0 | 0 |
>> +----+------------------------+------------+-------------+
>> 2 rows in set (0.00 sec)
>>
>>
>> But, the Limit's wrong. It's not picking up the global Limit from
>>
>> /etc/dovecot/conf.d/90-quota.conf
>> ...
>> --> quota_rule = *:bytes=1073741824:messages=10000
>> ...
>>
>> and once a message quota Value is calculated, the Limit is set ==
>> Value, resulting in an incorrrect quota %-age of 100%.
>>
>> Is Limit supposed to be specified per-user?
>>
>> Rich
>>
>
More information about the dovecot
mailing list