Re: [Dovecot] MySQL dict issues

2012-07-18 Thread l...@airstreamcomm.net 

On 7/17/12 7:59 AM, Timo Sirainen wrote:

On 11.7.2012, at 21.10, l...@airstreamcomm.net wrote:


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, d...@test.tld):  Error: net_connect_unix(/var/run/dovecot/dict) 
failed: Permission denied (euid=5000() egid=5000() 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 srwrwx 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.

http://wiki2.dovecot.org/Dict explains this.

It does indeed, thanks.



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.

Identical? Or perhaps one is the number of bytes and the other is the number of 
messages?
There were two rows being created for each username.  My problem was 
that I forgot to create a PK on the username field, so there must be two 
operations on the database that create/update user information by 
dovecot that require that PK to keep it to one row.



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'

This is done when recalculating quota.


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

Updated.

Thanks!

Re: [Dovecot] MySQL dict issues

2012-07-17 Thread Timo Sirainen 
On 11.7.2012, at 21.10, l...@airstreamcomm.net wrote:

> 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, d...@test.tld):  Error: 
> net_connect_unix(/var/run/dovecot/dict) failed: Permission denied 
> (euid=5000() egid=5000() 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 srwrwx 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.

http://wiki2.dovecot.org/Dict explains this.

> 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.

Identical? Or perhaps one is the number of bytes and the other is the number of 
messages?

> 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'

This is done when recalculating quota.

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

Updated.

[Dovecot] MySQL dict issues

2012-07-11 Thread l...@airstreamcomm.net 
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, d...@test.tld):  Error: 
net_connect_unix(/var/run/dovecot/dict) failed: Permission denied 
(euid=5000() egid=5000() 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 srwrwx 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:


90-quota.conf:

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
}

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 
privileges.


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