1 Topic by maxie_ro (edited by maxie_ro 2010-10-12 21:25:59)

  • maxie_ro
  • Member
  • Offline
  • From: Constanţa, România
  • Registered: 2009-10-09
  • Posts: 214

Topic: [Enh] MySQL-based used quota

Instead of the default file-based current used quota that Dovecot uses, you can use MySQL-based one. Thus, you can get a real-time usage report much more easily.

Written for the current version of iRedOS, using dovecot 1.1.x. Adapt it to your needs.

1. Backup your conf files to make sure you can quickly restore settings in case something goes wrong.

2. Create table "quota":

CREATE TABLE `quota` (
  `username` varchar(128) NOT NULL,
  `path` varchar(100) NOT NULL,
  `current` bigint(20) default NULL,
  PRIMARY KEY  (`username`,`path`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I suggest you use InnoDB (like I did above), you probably don't want current used quota to get screwed-up too easily. Make sure you use the same charset as in the rest of the tables (I use UTF-8).

3. Create file /etc/dovecot-dict-quota.conf:

connect = host=localhost dbname=vmail user=vmailadmin password=YOUR_PASSWORD_HERE
table = quota
select_field = current
where_field = path
username_field = username

4. Modify your dovecot.conf file, changing the section regarding quota:

plugin {
    quota = dict:user::proxy::quotadict
   [...]

5. Modify the dict section of your dovecot.conf file:

dict {
    quotadict = mysql:/etc/dovecot-dict-quota.conf
   [...]
}

6. Restart dovecot service.

The table entries will get updated only when an user receives or deletes a message. So it might take some time for the table to fill up. You might get some oddities in the mean time (including negative usage quota).

P.S.
Thank Zhang for reminding me to write this. I forgot (twice).

----

Spider Email Archiver: On-Premises, lightweight email archiving software developed by iRedMail team. Supports Amazon S3 compatible storage and custom branding.

2 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,135

Re: [Enh] MySQL-based used quota

Here's tutorial used for Dovecot 1.2:
http://www.iredmail.org/wiki/index.php? … a.In.MySQL

Of course it's based on your tutorial. Thanks very much big_smile

3 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,135

Re: [Enh] MySQL-based used quota

Dovecot-1.1.x requires two records for one user, like this:

mysql> select * from realtime_quota;
+----------+----------------+---------+
| username | path           | current |
+----------+----------------+---------+
| www@a.cn | quota/messages |       2 | 
| www@a.cn | quota/storage  |    1697 | 
+----------+----------------+---------+

But dovecot-1.2 uses one record, like this:

mysql> select * from realtime_quota;
+----------+-------+----------+
| username | bytes | messages |
+----------+-------+----------+
| www@a.cn |  9907 |        2 | 
+----------+-------+----------+