Re: [Dovecot] 'doveadm quota get' dictionary SQL query ignores specified '@domain' part of username. bad config or bug?
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=1
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=1') AS
quota_rule,
...
Now, at init,
doveadm quota get -A
UsernameQuota name TypeValue
Limit %
myu...@domain1.com User quota STORAGE 0
0 0
myu...@domain1.com User quota MESSAGE 0
1 0
myu...@domain2.com User quota STORAGE 0
0 0
myu...@domain2.com User quota MESSAGE 0
1 0
mysql select * from PARAMS;
++++-+
| ai | MAILBOX_user_domain| quota_bytes | quota_msgs |
+++-++
| 1 | myu...@domain1.com| 0 | 0 |
| 2 | myu...@domain2.com| 0 | 0 |
++++-+
2 rows in set (0.00 sec)
and, after sending a single message to 'myu...@domain1.com', I do see
that Dovecot now recognizes/calculates a quota change, and only for
one domain,
doveadm quota get -A
UsernameQuota name TypeValue
Limit %
myu...@domain1.com User quota STORAGE 3
3 100
myu...@domain1.com User quota MESSAGE 1
1 0
myu...@domain2.com User quota STORAGE 0
0 0
myu...@domain2.com User quota MESSAGE 0
1 0
mysql select * from PARAMS;
++++-+
| ai | MAILBOX_user_domain| quota_bytes | quota_msgs |
+++-++
| 1 | myu...@domain1.com|3269 | 1 |
| 2 | myu...@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=1
...
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
[Dovecot] 'doveadm quota get' dictionary SQL query ignores specified '@domain' part of username. bad config or bug?
Hi,
I'm setting up SQL quota usage in Dovecot2.
I've created a MAILBOX parameter table, 'PARAMS', that contains unique
user@domain pairs, with quota data.
mysql select * from PARAMS;
++++-++
| ai | MAILBOX_user_domain| quota | quota_bytes |
quota_msgs |
++++-++
| 1 | myu...@domain1.com| 1073741824 | 0 |
0 |
| 2 | myu...@domain2.com| 10 | 0 |
0 |
++++-++
2 rows in set (0.00 sec)
I've specified dict usage for quota,
/etc/dovecot/dovecot.conf
!include conf.d/*.conf
protocols = imap lmtp
dict { quota = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext }
created the maps,
/etc/dovecot/dovecot-dict-sql.conf.ext
connect = host=/var/run/mysql/mysql.sock dbname=my_db
user=my_user
password=my_pass
map {
pattern= priv/quota/storage
table = PARAMS
username_field = MAILBOX_user_domain
value_field= quota_bytes
}
map {
pattern= priv/quota/messages
table = PARAMS
username_field = MAILBOX_user_domain
value_field= quota_msgs
}
assigned the global values and backend,
/etc/dovecot/conf.d/90-quota.conf
plugin {
quota = dict:User Quota::proxy::quota
quota_rule = *:storage=1GB:messages=1
quota_rule2 = Trash:storage=+10%%
}
and specified 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`;
Verifying operation with with `doveadm`, I get a result from a !error query,
doveadm quota get -u myu...@domain1.com
Quota name TypeValue Limit %
User quota STORAGE 0 1048576 0
User quota MESSAGE 0 1 0
doveadm quota get -u myu...@domain2.com
Quota name TypeValue Limit %
User quota STORAGE 0 1048576 0
User quota MESSAGE 0 1 0
But notice that there's no specificity by *domain*. Despite being
provided the full user@domain, matched to the map,
username_field = MAILBOX_user_domain
the Dovecot dict query seems to ignore the domain part, and simply
matches on the first of the tow identical user parts.
Specifying -A doesn't help,
doveadm quota get -A
UsernameQuota name Type
Value Limit %
myu...@domain1.com User quota STORAGE 0
1048576 0
myu...@domain1.com User quota MESSAGE 0
1 0
myu...@domain2.com User quota STORAGE 0
1048576 0
myu...@domain2.com User quota MESSAGE 0
1 0
What needs to be done to get domain-specificity in the 'doveadm quota
get' query?
Is my configuration off, or incomplete, or is this a bug?
Thanks,
Rich
