dovecot - Oct 2011 - '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

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