doveadm with 2-level user/domain quotas scheme

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

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@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@beta:~ # doveadm quota recalc -u [hidden email]
root@beta:~ # doveadm quota get -u [hidden email]
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 [hidden email] quota was counted right
but last users data was copied into domains values.
...
root@localhost [exim]> SELECT * FROM quota2 WHERE username LIKE
'%my.domain';
+-------------------+-------------+----------+
| username          | bytes       | messages |
+-------------------+-------------+----------+
| [hidden email]     |  8039401321 |    32474 |
| my.domain         |  8039401321 |    32474 |
| [hidden email]    |  3455382803 |    11142 |
| [hidden email]    |   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@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@beta:~ # doveadm quota recalc -u my.domain
doveadm(my.domain): Error: User doesn't exist
root@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

signature.asc (499 bytes) Download Attachment