Dovecot not running quota sql query

Question

I'm trying to configure dovecot to handle quota limits.

I have followed a guide and configure the right steps. I even compared them with our current mailserver.

The problem is, Dovecot doesn't write to the quota2 table in our database. I have checked both the mysql logs and the maillog file (dovecot log location), but I don't see any errors about the quota. All other queries are working perfect. The quota in the users table is also fetched.

Does anyone have a clue? What am I missing?

Dovecot -n output:

# 2.0.9: /etc/dovecot/dovecot.conf # OS: Linux 3.14.35-28.38.amzn1.x86_64 x86_64 ext4 auth_mechanisms = plain login dict { quotadict = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext } mail_location = maildir:/var/spool/mail/virtual/%d/%n mail_plugins = quota mail_privileged_group = mail mbox_write_locks = fcntl passdb { args = /etc/dovecot/conf.d/dovecot-sql.conf.ext driver = sql } plugin { quota = dict:user::proxy::quotadict } service auth-worker { user = vmail } service auth { unix_listener /var/spool/postfix/private/auth { group = postfix mode = 0666 user = postfix } unix_listener auth-userdb { mode = 0600 user = vmail } user = dovecot } service imap-login { inet_listener imap { port = 143 } } service lmtp { unix_listener /var/spool/postfix/private/dovecot-lmtp { group = postfix mode = 0600 user = postfix } } ssl_ca = </root/ssl/root-cert.pem ssl_cert = </root/ssl/private-cert.pem ssl_key = </root/ssl/private-key.pem userdb { args = /etc/dovecot/conf.d/dovecot-sql.conf.ext driver = sql } protocol lda { mail_plugins = quota } protocol imap { mail_plugins = quota imap_quota imap_quota } protocol pop3 { mail_plugins = quota pop3_client_workarounds = outlook-no-nuls oe-ns-eoh } 

dovecot-sql.conf.ext:

password_query = SELECT email as user, password FROM users WHERE email='%u'; user_query = \ SELECT CONCAT('/var/spool/mail/virtual/',CONCAT(SUBSTRING_INDEX(email,'@',-1),'/', \ SUBSTRING_INDEX(email,'@',1))) AS home, \ 5001 AS uid, \ 5001 AS gid, \ CONCAT('maildir:storage=',quota) AS quota \ FROM users WHERE email='%u' \ AND enabled = '1'; 

dovecot/dovecot-dict-sql.conf.ext:

map { pattern = priv/quota/storage table = quota2 username_field = username value_field = bytes } map { pattern = priv/quota/messages table = quota2 username_field = username value_field = messages } 

UPDATE:

Log files (without amavis)

May 8 10:03:47 host dovecot: master: Warning: Killed with signal 15 (by pid=18369 uid=0 code=kill) May 8 10:03:47 host dovecot: imap: Server shutting down. bytes=9721/22462 May 8 10:03:47 host dovecot: imap: Server shutting down. bytes=4471/11038 May 8 10:03:47 host dovecot: imap: Server shutting down. bytes=4663/11356 May 8 10:03:47 host dovecot: imap: Server shutting down. bytes=8663/23549 May 8 10:03:48 host dovecot: master: Dovecot v2.0.9 starting up (core dumps disabled) May 8 10:03:48 host dovecot: ssl-params: Generating SSL parameters May 8 10:03:50 host dovecot: ssl-params: SSL parameters regeneration completed May 8 10:03:55 host dovecot: auth: mysql: Connected to 127.0.0.1 (mail) May 8 10:04:47 host dovecot: imap-login: Login: user=<[email protected]>, method=PLAIN, rip=80.113.202.232, lip=172.31.18.109, mpid=18399, TLS May 8 10:04:47 host dovecot: imap: Debug: Loading modules from directory: /usr/lib64/dovecot May 8 10:04:47 host dovecot: imap: Debug: Module loaded: /usr/lib64/dovecot/lib10_quota_plugin.so May 8 10:04:47 host dovecot: imap: Debug: Module loaded: /usr/lib64/dovecot/lib11_imap_quota_plugin.so May 8 10:04:47 host dovecot: imap: Debug: Added userdb setting: plugin/quota=maildir:storage=10 May 8 10:04:47 host dovecot: imap([email protected]): Debug: Effective uid=5001, gid=5001, home=/var/spool/mail/virtual/jouwweb.email/test May 8 10:04:47 host dovecot: imap([email protected]): Debug: Quota root: name=storage=10 backend=maildir args= May 8 10:04:47 host dovecot: imap([email protected]): Debug: maildir++: root=/var/spool/mail/virtual/jouwweb.email/test, index=, control=, inbox=/var/spool/mail/virtual/jouwweb.email/test May 8 10:04:49 host dovecot: imap-login: Login: user=<[email protected]>, method=PLAIN, rip=80.113.202.232, lip=172.31.18.109, mpid=18402, TLS May 8 10:04:49 host dovecot: imap: Debug: Loading modules from directory: /usr/lib64/dovecot May 8 10:04:49 host dovecot: imap: Debug: Module loaded: /usr/lib64/dovecot/lib10_quota_plugin.so May 8 10:04:49 host dovecot: imap: Debug: Module loaded: /usr/lib64/dovecot/lib11_imap_quota_plugin.so May 8 10:04:49 host dovecot: imap: Debug: Added userdb setting: plugin/quota=maildir:storage=10 May 8 10:04:49 host dovecot: imap([email protected]): Debug: Effective uid=5001, gid=5001, home=/var/spool/mail/virtual/jouwweb.email/test May 8 10:04:49 host dovecot: imap([email protected]): Debug: Quota root: name=storage=10 backend=maildir args= May 8 10:04:49 host dovecot: imap([email protected]): Debug: maildir++: root=/var/spool/mail/virtual/jouwweb.email/test, index=, control=, inbox=/var/spool/mail/virtual/jouwweb.email/test May 8 10:04:51 host dovecot: imap-login: Login: user=<[email protected]>, method=PLAIN, rip=80.113.202.232, lip=172.31.18.109, mpid=18404, TLS May 8 10:04:51 host dovecot: imap: Debug: Loading modules from directory: /usr/lib64/dovecot May 8 10:04:51 host dovecot: imap: Debug: Module loaded: /usr/lib64/dovecot/lib10_quota_plugin.so May 8 10:04:51 host dovecot: imap: Debug: Module loaded: /usr/lib64/dovecot/lib11_imap_quota_plugin.so May 8 10:04:51 host dovecot: imap: Debug: Added userdb setting: plugin/quota=maildir:storage=10 May 8 10:04:51 host dovecot: imap([email protected]): Debug: Effective uid=5001, gid=5001, May 8 10:03:47 host dovecot: master: Warning: Killed with signal 15 (by pid=18369 uid=0 code=kill) May 8 10:03:47 host dovecot: imap: Server shutting down. bytes=9721/22462 May 8 10:03:47 host dovecot: imap: Server shutting down. bytes=4471/11038 May 8 10:03:47 host dovecot: imap: Server shutting down. bytes=4663/11356 May 8 10:03:47 host dovecot: imap: Server shutting down. bytes=8663/23549 May 8 10:03:48 host dovecot: master: Dovecot v2.0.9 starting up (core dumps disabled) May 8 10:03:48 host dovecot: ssl-params: Generating SSL parameters May 8 10:03:50 host dovecot: ssl-params: SSL parameters regeneration completed May 8 10:03:55 host dovecot: auth: mysql: Connected to 127.0.0.1 (mail) May 8 10:04:47 host dovecot: imap-login: Login: user=<[email protected]>, method=PLAIN, rip=80.113.202.232, lip=172.31.18.109, mpid=18399, TLS May 8 10:04:47 host dovecot: imap: Debug: Loading modules from directory: /usr/lib64/dovecot May 8 10:04:47 host dovecot: imap: Debug: Module loaded: /usr/lib64/dovecot/lib10_quota_plugin.so May 8 10:04:47 host dovecot: imap: Debug: Module loaded: /usr/lib64/dovecot/lib11_imap_quota_plugin.so May 8 10:04:47 host dovecot: imap: Debug: Added userdb setting: plugin/quota=maildir:storage=10 May 8 10:04:47 host dovecot: imap([email protected]): Debug: Effective uid=5001, gid=5001, home=/var/spool/mail/virtual/jouwweb.email/test May 8 10:04:47 host dovecot: imap([email protected]): Debug: Quota root: name=storage=10 backend=maildir args= May 8 10:04:47 host dovecot: imap([email protected]): Debug: maildir++: root=/var/spool/mail/virtual/jouwweb.email/test, index=, control=, inbox=/var/spool/mail/virtual/jouwweb.email/test May 8 10:04:49 host dovecot: imap-login: Login: user=<[email protected]>, method=PLAIN, rip=80.113.202.232, lip=172.31.18.109, mpid=18402, TLS May 8 10:04:49 host dovecot: imap: Debug: Loading modules from directory: /usr/lib64/dovecot May 8 10:04:49 host dovecot: imap: Debug: Module loaded: /usr/lib64/dovecot/lib10_quota_plugin.so May 8 10:04:49 host dovecot: imap: Debug: Module loaded: /usr/lib64/dovecot/lib11_imap_quota_plugin.so May 8 10:04:49 host dovecot: imap: Debug: Added userdb setting: plugin/quota=maildir:storage=10 May 8 10:04:49 host dovecot: imap([email protected]): Debug: Effective uid=5001, gid=5001, home=/var/spool/mail/virtual/jouwweb.email/test May 8 10:04:49 host dovecot: imap([email protected]): Debug: Quota root: name=storage=10 backend=maildir args= May 8 10:04:49 host dovecot: imap([email protected]): Debug: maildir++: root=/var/spool/mail/virtual/jouwweb.email/test, index=, control=, inbox=/var/spool/mail/virtual/jouwweb.email/test May 8 10:04:51 host dovecot: imap-login: Login: user=<[email protected]>, method=PLAIN, rip=80.113.202.232, lip=172.31.18.109, mpid=18404, TLS May 8 10:04:51 host dovecot: imap: Debug: Loading modules from directory: /usr/lib64/dovecot May 8 10:04:51 host dovecot: imap: Debug: Module loaded: /usr/lib64/dovecot/lib10_quota_plugin.so May 8 10:04:51 host dovecot: imap: Debug: Module loaded: /usr/lib64/dovecot/lib11_imap_quota_plugin.so May 8 10:04:51 host dovecot: imap: Debug: Added userdb setting: plugin/quota=maildir:storage=10 May 8 10:04:51 host dovecot: imap([email protected]): Debug: Effective uid=5001, gid=5001, home=/var/spool/mail/virtual/jouwweb.email/test May 8 10:04:51 host dovecot: imap([email protected]): Debug: Quota root: name=storage=10 backend=maildir args= May 8 10:04:51 host dovecot: imap([email protected]): Debug: maildir++: root=/var/spool/mail/virtual/jouwweb.email/test, index=, control=, inbox=/var/spool/mail/virtual/jouwweb.email/test May 8 10:04:53 host dovecot: imap-login: Login: user=<[email protected]>, method=PLAIN, rip=80.113.202.232, lip=172.31.18.109, mpid=18406, TLS May 8 10:04:53 host dovecot: imap: Debug: Loading modules from directory: /usr/lib64/dovecot May 8 10:04:53 host dovecot: imap: Debug: Module loaded: /usr/lib64/dovecot/lib10_quota_plugin.so May 8 10:04:53 host dovecot: imap: Debug: Module loaded: /usr/lib64/dovecot/lib11_imap_quota_plugin.so May 8 10:04:53 host dovecot: imap: Debug: Added userdb setting: plugin/quota=maildir:storage=10 May 8 10:04:53 host dovecot: imap([email protected]): Debug: Effective uid=5001, gid=5001, home=/var/spool/mail/virtual/jouwweb.email/test May 8 10:04:53 host dovecot: imap([email protected]): Debug: Quota root: name=storage=10 backend=maildir args= May 8 10:04:53 host dovecot: imap([email protected]): Debug: maildir++: root=/var/spool/mail/virtual/jouwweb.email/test, index=, control=, inbox=/var/spool/mail/virtual/jouwweb.email/test May 8 10:06:06 host dovecot: lmtp(18427): Debug: none: root=, index=, control=, inbox= May 8 10:06:06 host dovecot: lmtp(18427): Connect from local May 8 10:06:06 host dovecot: lmtp(18427): Debug: Loading modules from directory: /usr/lib64/dovecot May 8 10:06:06 host dovecot: lmtp(18427): Debug: Module loaded: /usr/lib64/dovecot/lib10_quota_plugin.so May 8 10:06:06 host dovecot: lmtp(18427): Debug: auth input: [email protected] home=/var/spool/mail/virtual/jouwweb.email/test uid=5001 gid=5001 quota=maildir:storage=10 May 8 10:06:06 host dovecot: lmtp(18427): Debug: Added userdb setting: plugin/quota=maildir:storage=10 May 8 10:06:06 host dovecot: lmtp(18427, [email protected]): Debug: Effective uid=5001, gid=5001, home=/var/spool/mail/virtual/jouwweb.email/test May 8 10:06:06 host dovecot: lmtp(18427, [email protected]): Debug: Quota root: name=storage=10 backend=maildir args= May 8 10:06:06 host dovecot: lmtp(18427, [email protected]): Debug: maildir++: root=/var/spool/mail/virtual/jouwweb.email/test, index=, control=, inbox=/var/spool/mail/virtual/jouwweb.email/test May 8 10:06:06 host dovecot: lmtp(18427, [email protected]): 9nRZJ46KTFX7RwAAx8Xrpw: msgid=<[email protected]>: saved mail to INBOX May 8 10:06:06 host dovecot: lmtp(18427): Disconnect from local: Client quit May 8 10:04:51 host dovecot: imap([email protected]): Debug: Quota root: name=storage=10 backend=maildir args= May 8 10:04:51 host dovecot: imap([email protected]): Debug: maildir++: root=/var/spool/mail/virtual/jouwweb.email/test, index=, control=, inbox=/var/spool/mail/virtual/jouwweb.email/test May 8 10:04:53 host dovecot: imap-login: Login: user=<[email protected]>, method=PLAIN, rip=80.113.202.232, lip=172.31.18.109, mpid=18406, TLS May 8 10:04:53 host dovecot: imap: Debug: Loading modules from directory: /usr/lib64/dovecot May 8 10:04:53 host dovecot: imap: Debug: Module loaded: /usr/lib64/dovecot/lib10_quota_plugin.so May 8 10:04:53 host dovecot: imap: Debug: Module loaded: /usr/lib64/dovecot/lib11_imap_quota_plugin.so May 8 10:04:53 host dovecot: imap: Debug: Added userdb setting: plugin/quota=maildir:storage=10 May 8 10:04:53 host dovecot: imap([email protected]): Debug: Effective uid=5001, gid=5001, home=/var/spool/mail/virtual/jouwweb.email/test May 8 10:04:53 host dovecot: imap([email protected]): Debug: Quota root: name=storage=10 backend=maildir args= May 8 10:04:53 host dovecot: imap([email protected]): Debug: maildir++: root=/var/spool/mail/virtual/jouwweb.email/test, index=, control=, inbox=/var/spool/mail/virtual/jouwweb.email/test May 8 10:06:06 host dovecot: lmtp(18427): Debug: none: root=, index=, control=, inbox= May 8 10:06:06 host dovecot: lmtp(18427): Connect from local May 8 10:06:06 host dovecot: lmtp(18427): Debug: Loading modules from directory: /usr/lib64/dovecot May 8 10:06:06 host dovecot: lmtp(18427): Debug: Module loaded: /usr/lib64/dovecot/lib10_quota_plugin.so May 8 10:06:06 host dovecot: lmtp(18427): Debug: auth input: [email protected] home=/var/spool/mail/virtual/jouwweb.email/test uid=5001 gid=5001 quota=maildir:storage=10 May 8 10:06:06 host dovecot: lmtp(18427): Debug: Added userdb setting: plugin/quota=maildir:storage=10 May 8 10:06:06 host dovecot: lmtp(18427, [email protected]): Debug: Effective uid=5001, gid=5001, home=/var/spool/mail/virtual/jouwweb.email/test May 8 10:06:06 host dovecot: lmtp(18427, [email protected]): Debug: Quota root: name=storage=10 backend=maildir args= May 8 10:06:06 host dovecot: lmtp(18427, [email protected]): Debug: maildir++: root=/var/spool/mail/virtual/jouwweb.email/test, index=, control=, inbox=/var/spool/mail/virtual/jouwweb.email/test May 8 10:06:06 host dovecot: lmtp(18427, [email protected]): 9nRZJ46KTFX7RwAAx8Xrpw: msgid=<[email protected]>: saved mail to INBOX May 8 10:06:06 host dovecot: lmtp(18427): Disconnect from local: Client quit 

If you are interested in the log with amavis includes, check the gist file below: https://gist.github.com/jordi12100/f14842f724d62a47c570

UPDATE 2 (thanks to Fox): The quota is updating. However, the mail doesn't seem to block when I am over my limit. Current limit is 10kb, which I exceeded after I sent a couple of messages. Missing any setting?

Answer 1

A little guess based on comparing your configuration to my.

CONCAT('maildir:storage=',quota) AS quota should rather be CONCAT('maildir:storage=',quota) AS quota_rule

I think you are overriding the quota= setting from configuration, by per-user entry. (But I am no expert, so really just a guess)

Do not remember why, but my quota query is concat('*:storage=', quota) as quota_rule, should that help somehow ...

Edit: I think it gets delivered, because you are using lmtp, but do not have quota enabled for lmtp. You need something like I have:

protocol lmtp { mail_plugins = quota fts fts_solr sieve }