doveadm with 2-level user/domain quotas scheme

[Max Kostikov]

Hi!

I believe now it a right time to return to previous discussion about 
Dovecot's in 2-level user/domain quotas scheme wich was finished here 
https://dovecot.org/pipermail/dovecot/2015-October/102346.html

Here is configuration.

1. Dictionary storage placed in MySQL table "quota2"
root at localhost [(none)]> SHOW COLUMNS FROM quota2 FROM exim;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| username | varchar(100) | NO   | PRI | NULL    |       |
| bytes    | bigint(20)   | NO   |     | 0       |       |
| messages | int(11)      | NO   |     | 0       |       |
+----------+--------------+------+-----+---------+-------+
3 rows in set (0,00 sec)

2. Two types of quota - for domains with index "2" and for users without 
index.
...
plugin {
   quota = dict:user_quota::proxy::sqluserquota
   quota_rule2 = Trash:storage=+10%%
   quota_rule3 = Junk:storage=+10%%
   quota_warning = storage=100%% quota-exceeded 100 %u
   quota_warning2 = storage=95%% quota-warning 95 %u
   quota_warning3 = storage=90%% quota-warning 90 %u
   quota_warning4 = storage=75%% quota-warning 75 %u
   quota2 = dict:domain_quota:%d:proxy::sqldomainquota
}
dict {
   sqluserquota = mysql:/usr/local/etc/dovecot/dovecot-dict-sql-user.conf
   sqldomainquota = 
mysql:/usr/local/etc/dovecot/dovecot-dict-sql-domain.conf
}
service dict {
   unix_listener dict {
   user = mailnull
   mode = 0660
   }
}
...

3. Both stored in the same table and files "dovecot-dict-sql-user.conf" 
and "dovecot-dict-sql-domain.conf" are identical.

connect = host=localhost dbname=exim user=user password=password
map {
   pattern = priv/quota/storage
   table = quota2
   username_field = username
   value_field = bytes
}
map {
   pattern = priv/quota/messages
   table = quota2
   username_field = username
   value_field = messages
}

All quotas for users and domains are calculates correctly until "doveadm 
quota recalc" use.

root at beta:~ # doveadm quota recalc -u foo at my.domain
root at beta:~ # doveadm quota get -u foo at my.domain
Quota name   Type      Value Limit                                       
                             %
user_quota   STORAGE 7850978     -                                       
                             0
user_quota   MESSAGE   32474     -                                       
                             0
domain_quota STORAGE 7850978     -                                       
                             0
domain_quota MESSAGE   32474     -                                ,      
                              0

If we look at MySQL table directly foo at my.domain quota was counted right 
but last users data was copied into domains values.
...
root at localhost [exim]> SELECT * FROM quota2 WHERE username LIKE 
'%my.domain';
+-------------------+-------------+----------+
| username          | bytes       | messages |
+-------------------+-------------+----------+
| foo at my.domain     |  8039401321 |    32474 |
| my.domain         |  8039401321 |    32474 |
| john at my.domain    |  3455382803 |    11142 |
| mary at my.domain    |   544637146 |     1965 |
+-------------------+-------------+----------+
4 rows in set (0.00 sec)
...

Also you may see that "doveadm quota get" above gave wrong values. For 
domain its produces empty output

root at beta:~ # doveadm quota get -u my.domain
doveadm(my.domain): Error: User doesn't exist
Quota name Type Value Limit                                              
                         %

Than if we are trying to calculate quota for domain or for all users 
(-A) it produces an error.

root at beta:~ # doveadm quota recalc -u my.domain
doveadm(my.domain): Error: User doesn't exist
root at beta:~ # doveadm quota recalc -A
Error: User listing returned failure
doveadm: Error: Failed to iterate through some users

So "doveadm quota" almost useless for such quotas scheme except single 
user.
I wrote small shell-script "dovequota.sh" to resolve this issue but I 
believe that doveadm need to be fixed too.
(script may be found here 
https://kostikov.co/problemy-uchyota-domennoj-kvoty-v-dovecot-2).

-- 
With best regards,
Max Kostikov

BBM: 24CA5DF8 | W: https://kostikov.co
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 488 bytes
Desc: OpenPGP digital signature
URL: <https://dovecot.org/pipermail/dovecot/attachments/20171203/aa40517f/attachment.sig>