Rich
2011-Oct-19 02:17 UTC
[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 | myuser at domain1.com | 1073741824 | 0 | 0 | | 2 | myuser at domain2.com | 1000000000 | 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=10000 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 myuser at domain1.com Quota name Type Value Limit % User quota STORAGE 0 1048576 0 User quota MESSAGE 0 10000 0 doveadm quota get -u myuser at domain2.com Quota name Type Value Limit % User quota STORAGE 0 1048576 0 User quota MESSAGE 0 10000 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 Username Quota name Type Value Limit % myuser at domain1.com User quota STORAGE 0 1048576 0 myuser at domain1.com User quota MESSAGE 0 10000 0 myuser at domain2.com User quota STORAGE 0 1048576 0 myuser at domain2.com User quota MESSAGE 0 10000 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
Rich
2011-Oct-19 14:41 UTC
[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=10000 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=10000') AS quota_rule, ... Now, at init, doveadm quota get -A Username Quota name Type Value Limit % myuser at domain1.com User quota STORAGE 0 0 0 myuser at domain1.com User quota MESSAGE 0 10000 0 myuser at domain2.com User quota STORAGE 0 0 0 myuser at domain2.com User quota MESSAGE 0 10000 0 mysql> select * from PARAMS; +----+------------------------+------------+-------------+ | ai | MAILBOX_user_domain | quota_bytes | quota_msgs | +----+------------------------+-------------+------------+ | 1 | myuser at domain1.com | 0 | 0 | | 2 | myuser at domain2.com | 0 | 0 | +----+------------------------+------------+-------------+ 2 rows in set (0.00 sec) and, after sending a single message to 'myuser at domain1.com', I do see that Dovecot now recognizes/calculates a quota change, and only for one domain, doveadm quota get -A Username Quota name Type Value Limit % myuser at domain1.com User quota STORAGE 3 3 100 myuser at domain1.com User quota MESSAGE 1 10000 0 myuser at domain2.com User quota STORAGE 0 0 0 myuser at domain2.com User quota MESSAGE 0 10000 0 mysql> select * from PARAMS; +----+------------------------+------------+-------------+ | ai | MAILBOX_user_domain | quota_bytes | quota_msgs | +----+------------------------+-------------+------------+ | 1 | myuser at domain1.com | 3269 | 1 | | 2 | myuser at 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=10000 ... 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
Maybe Matching Threads
- [bug] Changing dovecot 'quota' Value incorrectly overwrites Limit with same value
- Are you successfully using using SQL queries to manage Dovecot2 Quota Values & Limits?
- Exporting keytab for SPN failure
- Exporting keytab for SPN failure
- ISC's dhcp server, radvd and bind9 now adding samba as an AD DC