Skip to content


Understanding and Creating User Quota with Dovecot 1.2+

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:

1
2
3
4
5
6
7
8
9
10
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:

1
2
3
4
5
6
7
8
9
10
11
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:

1
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

1
2
3
4
5
6
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

1
2
3
4
5
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

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

needs to be changed to the following:

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

/etc/dovecot/dovecot-sql.conf

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

This sends the quota_kb value into the user quota rule as follows: *:storage=250000
I say that to explain that you could also add max messasges and anything else you wanted there. Note that I used a % on my trash folder so a quota of 250MB would allow 25MB overage in the trash folder automatically. This does take 1 extra SQL query but I don’t think it really taxes the server at all to do another SQL Query.

Now that we have this done we simply apply the configuration by restarting the dovecot service:

1
/etc/init.d/dovecot reload

Test by logging in to a client that has quota support (IMAP) and seeing if the quota limit is in place. I recommend thunderbird if you don’t have a client that shows IMAP quotas. Note this also fixes the quota in Roundcube (and i’m sure other) Webmail Clients.

Posted in Debian Linux, Linux.

Tagged with , , , , , , .