[Dovecot] still having difficulties with per-user quotas

[David Mehler]

Hello,

Thanks. I've created a quota table as described in dovecot-dict sql
configuration file and granted the mail user select, insert, update,
and delete rights to that table, while the virtual_users table select
rights only. I configured for the new table. Here's my config, have I
got it? Are the columns now going to stay where I put them?

mysql> show create table virtual_users;
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table         | Create Table






                         |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| virtual_users | CREATE TABLE `virtual_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `domain_id` int(11) NOT NULL,
  `user` varchar(40) NOT NULL,
  `password` varchar(128) NOT NULL,
  `quota` bigint(20) NOT NULL DEFAULT '0',
  `quota_messages` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQUE_EMAIL` (`domain_id`,`user`),
  CONSTRAINT `virtual_users_ibfk_1` FOREIGN KEY (`domain_id`)
REFERENCES `virtual_domains` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table quota;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table


      |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| quota | CREATE TABLE `quota` (
  `username` varchar(100) NOT NULL,
  `bytes` bigint(20) NOT NULL DEFAULT '0',
  `messages` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

dovecot dictionary configuration

map {
  pattern = priv/quota/storage
  table = quota
  username_field = username
  value_field = bytes
}
map {
  pattern = priv/quota/messages
  table = quota
  username_field = username
  value_field = messages
}

Thanks.
Dave.


On 10/21/12, Daniel Parthey <daniel.parthey at informatik.tu-chemnitz.de> wrote:
> David Mehler wrote:
>> Thanks, so if I understand what your saying the reason I'm getting the
>> column update issues is Dovecot is reading from and writing to the
>> quota and quota_messages columns in my virtual_users table?
>>
>> My database user I believe only has select permissions on that table.
>>
>> So, I either need another table and to adjust my dovecot-dict.sql file
>> for that table, that's where Dovecot will write to, or two more
>> columns in the virtual_users table?
>>
>> Which way do you recommend?
>
> I would recommend to create a new table for dovecot_usage
> where dovecot is granted write permission.
>
> Regards
> Daniel
>