How to map per-user quota with Dovecot quota plugin using MYSQL dict?

Question

Problem: doveadm quota get -u <username> result shows that my MYSQL dict is not correctly mapped to Dovecot quota plugin, as a result IMAP QUOTA capability (with usage and limit) not being reported to email client.

Context: To have per-user quotas reported back to email client from Dovecot. I am using MySQL (postfixadmin schema) to store my quota limits and usage. So far the Dovecot quota plugin is correctly writing quota usage to DB, but I'm not able to map the values from DB to Dovecot quota plugin correctly.

I believe I am using maildir format for my mailboxes:

mail_location = /var/mail/vmail/%d/%u

Dovecot Configuration:

• <username>, uid and gid obfuscated in the examples below:

/etc/dovecot/dovecot.conf

userdb { driver = sql args = /etc/dovecot/dovecot-sql.conf } dict { quota = mysql:/etc/dovecot/dovecot-dict-quota.conf } 

/etc/dovecot/dovecot-sql.conf

user_query = SELECT CONCAT('/var/mail/vmail/', maildir) AS home, #### AS uid, #### as gid, CONCAT('dict:storage=', FLOOR(quota/1000), ' ::proxy::quota') AS quota FROM mailbox WHERE username = '%u' AND active = '1' 

/etc/dovecot/dovecot-dict-quota.conf

• connect values ommitted

connect = host=<> dbname=<> user=<> 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 } 

/etc/dovecot/conf.d/90-quota.conf

plugin { quota = dict:User proxy::quota } 

doveadm user -u <username>

userdb: <username> user : <username> home : /var/mail/vmail/<username> uid : #### gid : #### quota : dict:storage=5120 ::proxy::quota 

doveadm quota get -u <username>

Quota name	Type	Value	Limit	%
storage=5120	STORAGE	9	-	-
storage=5120	MESSAGE	19	-	-

Here I see that the value of key-value pair (kv) storage= is from database and MESSAGE 19 is correct. However the Limit appears in the incorrect column and incorrectly mapped; the Quota name column values should not be kv, instead the name of the quota is expected. Actually the value of the storage= kv should be for STORAGE Limit.

1. How to correctly map the MYSQL dict to the quota plugin so that the Limit column is populated? I believe if this is correctly mapped then the IMAP CAPABILITIES will report correctly.
2. How can I map the MESSAGE Limit? I do not have this limit in db, currently only STORAGE Value & Limit in DB. I would ideally like to have a hard-coded global value here for simplicity, primary concern is STORAGE values.
3. Is value 9 == 8374 rounded? In DB i have 8374 bytes but STORAGE Value shows 9.

Answer 1

dict:storage=

Missing colons (and a name) here.

quota = dict:<quota root name>:<username>[:<option>[...]]:<dictionary URI> syntax has options after the 3rd column. Need to put a name (leaving <username> empty defaults to %u)

Documented here: https://doc.dovecot.org/2.3/configuration_manual/quota/quota_dict/