Dovecot and Individual Quota

May 24, 2015 811 views
Email MySQL Ubuntu

My Postfix + MySQL + Dovecot works perfectly.
I managed to correctly configure the global quota.
Now I need to configure individual quotas using mysql.

My auth-sql.conf.ext in conf.d dir:

passdb {
  driver = sql
  args = /etc/dovecot/dovecot-sql.conf.ext
}
userdb {
  driver = static
  args = uid=vmail gid=vmail home=/var/mail/vhosts/%d/%n
}

My 90-quota.conf in conf.d dir:

plugin {
  quota_rule = *:storage=1G 
}

dovecot-sql.conf.ext in Dovecot root dir:

driver = mysql
connect = host=127.0.0.1 dbname=********* user=********* password=*************
default_pass_scheme = SHA512-CRYPT
password_query = SELECT email as user, password FROM virtual_users WHERE email='%u';

My MySQL virutal_users table:

CREATE TABLE `virtual_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `domain_id` int(11) NOT NULL,
  `password` varchar(106) NOT NULL,
  `email` varchar(100) 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=51 DEFAULT CHARSET=utf8;

I need to adjust the template below that is on the Dovecot 2.x documentation for my profile.

user_query = SELECT uid, gid, home, \
  concat('*:bytes=', quota_limit_bytes) AS quota_rule \
  FROM users WHERE userid = '%u'

# MySQL with userdb prefetch: Remember to prefix quota_rule with userdb_
# (just like all other userdb extra fields):
password_query = SELECT userid AS user, password, \
  uid AS userdb_uid, gid AS userdb_gid, \
  concat('*:bytes=', quota_limit_bytes) AS userdb_quota_rule \
  FROM users WHERE userid = '%u'

I can not do. My table has all of these fields.

Can you help me?

Be the first one to answer this question.