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@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.