[Dovecot] MySQL dict issues

list at airstreamcomm.net list at airstreamcomm.net
Wed Jul 11 21:10:40 EEST 2012

Testing using the mysql dict for quota storage 
(dovecot-2.0.12-2_127.el5), and ran into a couple issues.  First is a 
permissions issue:

dovecot: lmtp(26786, dude at test.tld):  Error: 
net_connect_unix(/var/run/dovecot/dict) failed: Permission denied 
(euid=5000(<unknown>) egid=5000(<unknown>) missing +r perm: 
/var/run/dovecot/dict, euid is not dir owner)

The file /var/run/dovecot/dict is listed with srw------- perms.  I 
changes the perms to srw----rwx and it works, but I would like to figure 
out how to configure properly to run with the uid and gid 5000 as this 
is the user for virtual mail.

The second issue is that two identical rows are created in the mysql 
database for each user quota.  The next message delivered updates both 
rows.  Here is my quota config:


plugin {
   quota_rule = *:storage=10G
   quota_rule2 = Trash:storage=+10%
plugin {
   quota = dict:user::proxy::userquota
dict {
   userquota = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext


connect = host=a.b.c.d dbname=mail user=user password=derp
map {
   pattern = priv/quota/storage
   table = domain_user_quotas
   username_field = username
   value_field = bytes
map {
   pattern = priv/quota/messages
   table = domain_user_quotas
   username_field = username
   value_field = message_count

The last issue might be a documentation omission.  Dovecot warned me 
that the mysql user did not have DELETE privileges on the quota table:

dovecot: dict: Error: sql dict: commit failed: DELETE command denied to 
user 'user'@'a.b.c.d' for table 'domain_user_quotas'

The quota dict documentation (at http://wiki2.dovecot.org/Quota/Dict) 
does not mention DELETE being needed:

MySQL uses the following queries to update the quota. You need suitable 

INSERT INTO table (bytes,username) VALUES ('112497180','foo at spam.dom') ON DUPLICATE KEY UPDATE bytes='112497180';
INSERT INTO table (messages,username) VALUES ('1743','foo at spam.dom') ON DUPLICATE KEY UPDATE messages='1743';
UPDATE table SET bytes=bytes-14433,messages=messages-2 WHERE username = 'foo at spam.dom';

More information about the dovecot mailing list