dovecot - Feb 2014 - Dovecot, mysql, and quota

Hello,

I'm running dovecot 2.2 and wanting to get quotas going. I've got the
plugins loaded appropriately and am wanting to configure a dictionary
backend to talk to my mysql database.

I've got a single database called mail which has all the mail-related
items in it. One table is for virtual users which postfix uses. It
looks like this:

CREATE TABLE `virtual_users` (
  `id` int(11) NOT NULL auto_increment,
  `domain_id` int(11) NOT NULL,
  `password` varchar(128) NOT NULL,
  `email` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
and a user looks like:
INSERT INTO `mail`.`virtual_users` (
  `id` ,
  `domain_id` ,
  `password` ,
  `email`
)
VALUES (
  '1', '1', SHA2( 'PasswordGoesHere', 512) ,
'username at domain.com'
);

plugin {
  # SQL backend:
  quota = dict:User quota::proxy::sqlquota
}
dict {
  sqlquota = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext
}

Now when creating the quota table i'd like to have the username field
linked to my virtual_users table username field, so I don't have to
set up two tables both with the same username information in it. 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?

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?

Thanks.
Dave.

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