dovecot - Oct 2011 - Are you successfully using using SQL queries to manage Dovecot2 Quota Values & Limits?

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