[Workaround.org] Dynamic quota limits with mysql

Sebastian Büttner bueddl1 at googlemail.com
Sun Mar 11 14:11:41 CET 2012


Hey dear list-members,

I tried today to extend the dovecot quota setup at my server to allow
dynamic quota rules. Everything seemed to work fine.

I simply edited the user_query in the sql backend (dovecot 2.0!) to look
like this:

/************************
user_query = SELECT '/var/vmail/%d/%n' AS home, 'maildir:~/Maildir' AS
mail, 5000 AS uid, 5000 AS gid, GROUP_CONCAT( CONCAT( q.`selector`, ':',
q.`type`, '=', IF(q.`extends`=1, '+', ''), q.`limit`, q.`limit_unit` )
ORDER BY q.`id` ASC SEPARATOR ';') AS `quota_rule` FROM virtual_users u
LEFT JOIN `virtual_quota` q ON (u.id = q.user_id) WHERE u.`email` = '%u';
/************************

And created a table in the database:


/************************
CREATE TABLE IF NOT EXISTS `virtual_quota` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `selector` varchar(80) NOT NULL,
  `extends` tinyint(1) NOT NULL DEFAULT '0',
  `type` enum('storage','messages','bytes') NOT NULL DEFAULT 'storage',
  `limit` int(11) NOT NULL,
  `limit_unit` enum('','b','k','M','G','T','%%') NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
/************************

`id` is just a primary index for this table,
`user_id` represents the id field in the `virtual_users` table,
`selector` is the area the rule is set for,
`extends` is a boolean , if set then the `limit` value is meant to be
aditional to the main space
`type` can be 'storage', ' messages' or 'bytes'
`limit` defines the actual limit and
`limit_unit` defines the `limit`s unit - b, k, M, G, T, % or nothing

No problems so far. - Lets see some example data:

`id` | `user_id` | `selector` | `extends` | `type`          | `limit` |
`limit_unit`
------------------------------------------------------------------------------------------------------
1    |       1      |        '*'     |     0         | 'storage'      |
3     | 'G'
2    |       1      |   'Trash'   |     1         | 'storage'       |
100   | 'M'
3    |       1      |        '*'     |     0         | 'messages'  |
1000|

The query above returns this result for the `quota_rule` field for user 1:

/************************
<http://176.9.78.55/phpmyadmin/tbl_get_field.php?db=hs103&table=virtual_users&where_clause=.%60%2Fvar%2Fvmail%2F%25d%2F%25n%60+%3D+%27%2Fvar%2Fvmail%2F%25d%2F%25n%27+AND+.%60maildir%3A%7E%2FMaildir%60+%3D+%27maildir%3A%7E%2FMaildir%27+AND+.%60uid%60+%3D+5000+AND+.%60gid%60+%3D+5000+AND+.%60quota_rule%60+%3D+CAST%280x2a3a73746f726167653d33473b54726173683a73746f726167653d2b313025253b2a3a6d657373616765733d31303030+AS+BINARY%29&transform_key=quota_rule&sql_query=SELECT+%27%2Fvar%2Fvmail%2F%25d%2F%25n%27+AS+home+%2C+%27maildir%3A%7E%2FMaildir%27+AS+mail+%2C+5000+AS+uid+%2C+5000+AS+gid+%2C+GROUP_CONCAT%28+CONCAT%28+q.+%60selector%60+%2C+%27%3A%27+%2C+q.+%60type%60+%2C+%27%3D%27+%2C+IF%28+q.+%60extends%60+%3D+1+%2C+%27%2B%27+%2C+%27%27+%29+%2C+q.+%60limit%60+%2C+IF%28+q.+%60limit_unit%60+%3D+%27%25%27+%2C+%27%25%25%27+%2C+q.+%60limit_unit%60+%29+%29+ORDER+BY+q.+%60id%60+ASC+SEPARATOR+%27%3B%27+%29+AS+%60quota_rule%60++FROM+virtual_users+u+LEFT+JOIN+%60virtual_quota%60+q+ON+%28+u.id+%3D+q.user_id+%29+&token=096e44a2c303e19ac2a41a088a4f6b0f>*:storage=3G;Trash:storage=+100M;*:messages=1000
/************************

This looks to me as a good result, BUT, the logfile says (when I connect
via IMAP):

/************************
Mar 11 13:39:04 xxx dovecot: imap(xxxxxx at xxxxxx): Error: user xxxxxx at xxxxxx:
Initialization failed: Failed to initialize quota: Invalid quota root
quota: Invalid rule *:storage=3G;Trash:storage=+100M;*:messages=1000: Rule
limit cannot have '+'
/************************

So, my question simply is: Why are '+' not allowed in here? In the file
based config '90-quota.conf', there is the rule
'quota_rule2 = Trash:storage=+100M' , also with a Plus signed and working!

What did I wrong?

Thank you very much in advance.

Regards,
Sebastian Büttner
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://workaround.org/pipermail/workaround-chitchat/attachments/20120311/291e860f/attachment.html>


More information about the Workaround-chitchat mailing list