The dovecot wiki for 1.2 was an afterthought in my opinion. It seems they were already starting to focus on 2.0 and just kinda threw 1.2+ (The version that comes with debian squeeze) into the Debian 1.0/1.1 wiki located at http://wiki1.dovecot.org/Quota. I read through this a bunch and decided I’d write a little tutorial about how to setup a user quota using MySQL and Dovecot. From what I’ve seen on the search engines this is something a lot of people are looking for. My mail server setup is based on the configuration from http://workaround.org/ispmail/squeeze which provides a great foundation for setting up a stable PostFix/DoveCot/Amavisd-new solution. So all I needed was a few small things to add to make the quota system work (even with the client reporting the quota to the customer).

First we need to make a small change to our MySQL database that handles users … From the tutorial mentioned above my mailusers are stored in the mailserver database and are stored in the virtual_users table. The table by default looks like this:


CREATE TABLE `virtual_users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`domain_id` int(11) NOT NULL,
`email` varchar(100) NOT NULL,
`password` varchar(32) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`email`),
KEY `domain_id` (`domain_id`),
CONSTRAINT `virtual_users_ibfk_1` FOREIGN KEY (`domain_id`) REFERENCES `virtual_domains` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=119 DEFAULT CHARSET=utf8;


Simply enough we just need to add a quota_kb field with the type INT (11) unsigned so that our database table looks like this:


CREATE TABLE `virtual_users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`domain_id` int(11) NOT NULL,
`email` varchar(100) NOT NULL,
`password` varchar(32) NOT NULL,
`quota_kb` int(11) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`email`),
KEY `domain_id` (`domain_id`),
CONSTRAINT `virtual_users_ibfk_1` FOREIGN KEY (`domain_id`) REFERENCES `virtual_domains` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=119 DEFAULT CHARSET=utf8;


Once this is done you might want to populate a default into the users folder like so:

UPDATE virtual_users SET quota_kb = 250000;


The above code would set a default quota number to 250,000KB or 250MB.

Now that we have the groundwork laid in our MySQL database we simply need to enable a few features in dovecot for the quota to work.
First we need to tell our protocols to support the quota plugin by adding in the mail_plugins into the imap and pop sections of our config like so:

/etc/dovecot/dovecot.conf

protocol imap {
mail_plugins = quota imap_quota
}
protocol pop3 {
mail_plugins = quota
}


Next we need to set a default quota for people who don’t have a quota set for them in quota_kb (or one that is set to null). We do this in the plugins section of the dovecot.conf. This is where the wiki is a bit confusing because it tells you how to do this but somewhat implies (at least to me) that you have to use DICT to connect to the SQL service to check on the quota for individual users.

/etc/dovecot/dovecot.conf

plugin {
quota = maildir:User quota
quota_rule = *:storage=1G
quota_rule2 = Trash:storage=+10%%
}

By default our users will all have a quota of 1GB now. The default quota is checked using maildir size and we add the quota_rule2 to avoid a potential endless loop if the mail quota is met. The quota_rule2 adds 10% to the quota just for the trash folder so that we can actually delete mail when we reach the quota.

Now we just need to tell the dovecot server about our previously created SQL quota_kb so that it knows that we might have some users who have different defaults than the 1GB limit placed in the dovecot.conf. To do this we edit our dovecot-sql.conf that handles our users. Our default user_query

/etc/dovecot/dovecot.conf

userdb static {
args = uid=5000 gid=5000 home=/var/vmail/%d/%n allow_all_users=yes
}


needs to be changed to the following:


userdb sql {
args = /etc/dovecot/dovecot-sql.conf
}

/etc/dovecot/dovecot-sql.conf

user_query = SELECT CONCAT('/var/vmail/',CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1))) AS home, 5000 AS uid, 5000 AS gid, concat('*:storage=', quota_kb) AS quota_rule FROM virtual_users WHERE email='%u'