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 @ 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 @, matched to the map, username_field = MAILBOX_user_domain the Dovecot dict query seems to ignore the part, and simply matches on the first of the tow identical 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