[Dovecot] Dovecot, mysql, and quota

[Gedalya]

On 02/18/2014 10:29 PM, David Mehler wrote:
>
> Can someone check me on this design so that if I delete a virtual user out
> of the virtual_users table the coresponding row in the quota table is
> also eliminated?
That's a purely MySQL question.

Try something like
CREATE TABLE quota (
   username varchar(100) not null,
   bytes bigint not null default 262144,
   messages integer not null default 0,
   primary key (username)
   CONSTRAINT `username_virtual_users` FOREIGN KEY (`username`) 
REFERENCES `virtual_users` (`email`) ON DELETE CASCADE
);

(Check that, don't just copy and paste..)

>
> My second question has to do with global and per user quotas. If I
> have something like this:
>
> CREATE TABLE quota (
>    username varchar(100) not null,
>    bytes bigint not null default 262144,
>    messages integer not null default 0,
>    primary key (username)
> );
>
> 256MB for all users by default, but then if I give a user a 100MB
> value in the bytes field of their quota entry would that be a per-user
> quota?
dovecot stores the bytes currently used in the bytes column. Do not set 
a default value, or just make it zero!!! This table does not hold the 
allocated quota size, only the used storage space.

Update your passdb / userdb queries to return a quota size, presumably 
from your virtual_users table.


password_query = SELECT password, \
   concat('*:storage=', quota_bytes) as userdb_quota_rule, \
   FROM ........

user_query = SELECT concat('*:storage=', quota_bytes) as quota_rule, \
   FROM ....

Personally I prefer to store the value in megabytes, and use 
concat('*:storage=', quota_mb, 'M') as quota_rule