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)


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

}
service dict {
  unix_listener dict {
  user = mailnull
  mode = 0660
  }
}
...



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
}



root@beta:~ # doveadm quota recalc -u foo@my.domain
root@beta:~ # doveadm quota get -u foo@my.domain



...

+-------------------+-------------+----------+
| username          | bytes       | messages |
+-------------------+-------------+----------+
| foo@my.domain     |  8039401321 |    32474 |
| my.domain         |  8039401321 |    32474 |
| john@my.domain    |  3455382803 |    11142 |
| mary@my.domain    |   544637146 |     1965 |
+-------------------+-------------+----------+
4 rows in set (0.00 sec)
...



root@beta:~ # doveadm quota get -u my.domain
doveadm(my.domain): Error: User doesn't exist




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

2. Two types of quota - for domains with index "2" and for users without index. sqldomainquota = mysql:/usr/local/etc/dovecot/dovecot-dict-sql-domain.conf 3. Both stored in the same table and files "dovecot-dict-sql-user.conf" and "dovecot-dict-sql-domain.conf" are identical. All quotas for users and domains are calculates correctly until "doveadm quota recalc" use. 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@my.domain quota was counted right but last users data was copied into domains values. root@localhost [exim]> SELECT * FROM quota2 WHERE username LIKE '%my.domain'; Also you may see that "doveadm quota get" above gave wrong values. For domain its produces empty output Quota name Type Value Limit % Than if we are trying to calculate quota for domain or for all users (-A) it produces an error. 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
Description: OpenPGP digital signature