Rick van den Hof
2014-10-14 13:38:42 UTC
Hi,
In our setup, we use two quota roots. One for user quota and one for
domain quota. If a user has no quota, then the domain quota is applied.
For a user with user quota this is how it looks:
# doveadm quota get -u test at shellz.nl
Quota name Type Value Limit %
Domain quota STORAGE 1439155 2560000 56
Domain quota MESSAGE 21257 - 0
User quota STORAGE 0 102400 0
User quota MESSAGE 0 - 0
In this case, the whole domain contains 21257 messages but the account
itself contains 0.
For a user with only domain quota, this is how it looks:
# doveadm quota get -u rick at shellz.nl
Quota name Type Value Limit %
Domain quota STORAGE 1439155 2560000 56
Domain quota MESSAGE 21257 - 0
User quota STORAGE 693299 - 0
User quota MESSAGE 12876 - 0
So far so good. My account contains 12876 messages.
Now I've sent a message containing a 1mb.bin as attachment to
test at shellz.nl:
# doveadm quota get -u test at shellz.nl
Quota name Type Value Limit %
Domain quota STORAGE 1440540 2560000 56
Domain quota MESSAGE 21258 - 0
User quota STORAGE 1384 102400 1
User quota MESSAGE 1 - 0
Seems to be fine, the message on disk is indeed 1.4MB and this has been
added to both domain and user quota.
Now see what happens when I run the following command:
# doveadm quota recalc -u test at shellz.nl
# doveadm quota get -u test at shellz.nl
Quota name Type Value Limit %
Domain quota STORAGE 1384 2560000 0
Domain quota MESSAGE 1 - 0
User quota STORAGE 1384 102400 1
User quota MESSAGE 1 - 0
The recalc action has updated the domain usage to reflect the specific
user's usage (my 12876 messages are no longer counted).
We log domain usage (usage_domain) in a seperate table from mailbox usage (usage_mailbox).
These are the queries that set the usage:
256 Query BEGIN
256 Query DELETE FROM usage_domain WHERE domain = 'shellz.nl'
256 Query DELETE FROM usage_domain WHERE domain = 'shellz.nl'
256 Query INSERT INTO usage_domain (bytes,domain) VALUES ('1418118','shellz.nl') ON DUPLICATE KEY UPDATE bytes='1418118'
256 Query INSERT INTO usage_domain (messages,domain) VALUES ('1','shellz.nl') ON DUPLICATE KEY UPDATE messages='1'
256 Query COMMIT
257 Query BEGIN
257 Query DELETE FROM usage_mailbox WHERE userdomain = 'test at shellz.nl'
257 Query DELETE FROM usage_mailbox WHERE userdomain = 'test at shellz.nl'
257 Query INSERT INTO usage_mailbox (bytes,userdomain) VALUES ('1418118','test at shellz.nl') ON DUPLICATE KEY UPDATE bytes='1418118'
257 Query INSERT INTO usage_mailbox (messages,userdomain) VALUES ('1','test at shellz.nl') ON DUPLICATE KEY UPDATE messages='1'
257 Query COMMIT
How do I prevent this from happening? It should only update the usage_mailbox table when I run the recalc command.
Could this be because we use seperate tables for domain and user usage?
dovecot -n:
# 2.2.9: /etc/dovecot/dovecot.conf
# OS: Linux 3.13.0-37-generic x86_64 Ubuntu 14.04.1 LTS
auth_mechanisms = plain login
auth_username_chars = abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890+=_.-@&
dict {
expire = mysql:/etc/dovecot/dovecot-sql-expire.conf
quotadomaindict = mysql:/etc/dovecot/dovecot-sql-quota-domain.conf
quotauserdict = mysql:/etc/dovecot/dovecot-sql-quota-user.conf
}
disable_plaintext_auth = no
listen = *,[::]
mail_fsync = never
mail_location = maildir:~/Maildir
mail_plugins = quota expire
managesieve_notify_capability = mailto
managesieve_sieve_capability = fileinto reject envelope encoded-character vacation subaddress comparator-i;ascii-numeric relational regex imap4flags copy include variables body enotify environment mailbox date ihave
passdb {
args = /etc/dovecot/dovecot-sql-user.conf
driver = sql
}
plugin {
expire = Trash
expire2 = Spam
expire_dict = proxy::expire
mail_log_events = delete copy save expunge mailbox_delete mailbox_rename
mail_log_fields = uid box msgid size
quota = dict:Domain quota:%d:proxy::quotadomaindict
quota_rule2 = Trash:ignore
quota_warning = storage=99%% doquotawarning 99 %u
quota_warning2 = storage=95%% doquotawarning 95 %u
quota_warning3 = storage=75%% doquotawarning 75 %u
quota2 = dict:User quota::proxy::quotauserdict
quota2_rule2 = Trash:ignore
quota2_warning = storage=99%% doquotawarning 99 %u
quota2_warning2 = storage=95%% doquotawarning 95 %u
quota2_warning3 = storage=75%% doquotawarning 75 %u
}
protocols = imap pop3
service auth-worker {
unix_listener auth-worker {
mode = 0600
user = vmail
}
}
service auth {
unix_listener /var/spool/postfix/private/auth {
group = postfix
mode = 0660
user = postfix
}
unix_listener auth-master {
mode = 0600
user = vmail
}
unix_listener auth-userdb {
mode = 0600
user = vmail
}
user = vmail
}
service dict {
unix_listener dict {
mode = 0600
user = vmail
}
}
service doquotawarning {
executable = script /usr/bin/doquotawarning.py
unix_listener doquotawarning {
user = vmail
}
user = vmail
}
service imap-login {
chroot =
process_limit = 200
process_min_avail = 2
}
service imap {
executable = imap postlogin
}
service managesieve-login {
chroot =
}
service pop3-login {
chroot =
process_limit = 50
process_min_avail = 2
}
service pop3 {
executable = pop3 postlogin
}
service postlogin {
executable = script-login /usr/local/postfixint/dolastlogin.py
user = $default_internal_user
}
ssl_cert = </etc/ssl/certs/wild.totaal.net/wild.totaal.net.pem
ssl_cipher_list = ALL:!LOW:!SSLv2:ALL:!aNULL:!ADH:!eNULL:!EXP:RC4+RSA:+HIGH:+MEDIUM
ssl_key = </etc/ssl/certs/wild.totaal.net/wild.totaal.net.key
userdb {
driver = prefetch
}
userdb {
args = /etc/dovecot/dovecot-sql-user.conf
driver = sql
}
protocol imap {
imap_client_workarounds = delay-newmail tb-extra-mailbox-sep
imap_logout_format = bytes=%i/%o
mail_max_userip_connections = 50
mail_plugins = quota imap_quota mail_log notify expire
}
protocol pop3 {
mail_max_userip_connections = 50
mail_plugins = quota expire
pop3_client_workarounds = outlook-no-nuls oe-ns-eoh
pop3_logout_format = top=%t/%p, retr=%r/%b, del=%d/%m, size=%s, bytes=%i/%o
pop3_uidl_format = %f
}
protocol lda {
deliver_log_format = msgid=%m: %$
mail_fsync = optimized
mail_plugins = quota sieve expire
postmaster_address = hostmaster at vps04.totaal.net
rejection_reason = Your message to <%t>' was automatically rejected:%n%r
}
dovecot-sql-quota-domain.conf:
connect = xxx
map {
pattern = priv/quota/storage
table = usage_domain
username_field = domain
value_field = bytes
}
map {
pattern = priv/quota/messages
table = usage_domain
username_field = domain
value_field = messages
}
dovecot-sql-quota-user.conf:
connect = xxx
map {
pattern = priv/quota/storage
table = usage_mailbox
username_field = userdomain
value_field = bytes
}
map {
pattern = priv/quota/messages
table = usage_mailbox
username_field = userdomain
value_field = messages
}
Thanks in advance to anyone who might be able to shed some light in this situation :).
Kind regards,
Rick van den Hof
--
Manager Engineering
Totaalnet Internet Works B.V.
IJsselburcht 4e
6825 BP Arnhem
+31(0)26-3844944 | r.vandenhof at tiw.eu (PGP Key: 0x5A66E935)
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 473 bytes
Desc: Digital signature
URL: <http://dovecot.org/pipermail/dovecot/attachments/20141014/fb606c18/attachment-0001.sig>
In our setup, we use two quota roots. One for user quota and one for
domain quota. If a user has no quota, then the domain quota is applied.
For a user with user quota this is how it looks:
# doveadm quota get -u test at shellz.nl
Quota name Type Value Limit %
Domain quota STORAGE 1439155 2560000 56
Domain quota MESSAGE 21257 - 0
User quota STORAGE 0 102400 0
User quota MESSAGE 0 - 0
In this case, the whole domain contains 21257 messages but the account
itself contains 0.
For a user with only domain quota, this is how it looks:
# doveadm quota get -u rick at shellz.nl
Quota name Type Value Limit %
Domain quota STORAGE 1439155 2560000 56
Domain quota MESSAGE 21257 - 0
User quota STORAGE 693299 - 0
User quota MESSAGE 12876 - 0
So far so good. My account contains 12876 messages.
Now I've sent a message containing a 1mb.bin as attachment to
test at shellz.nl:
# doveadm quota get -u test at shellz.nl
Quota name Type Value Limit %
Domain quota STORAGE 1440540 2560000 56
Domain quota MESSAGE 21258 - 0
User quota STORAGE 1384 102400 1
User quota MESSAGE 1 - 0
Seems to be fine, the message on disk is indeed 1.4MB and this has been
added to both domain and user quota.
Now see what happens when I run the following command:
# doveadm quota recalc -u test at shellz.nl
# doveadm quota get -u test at shellz.nl
Quota name Type Value Limit %
Domain quota STORAGE 1384 2560000 0
Domain quota MESSAGE 1 - 0
User quota STORAGE 1384 102400 1
User quota MESSAGE 1 - 0
The recalc action has updated the domain usage to reflect the specific
user's usage (my 12876 messages are no longer counted).
We log domain usage (usage_domain) in a seperate table from mailbox usage (usage_mailbox).
These are the queries that set the usage:
256 Query BEGIN
256 Query DELETE FROM usage_domain WHERE domain = 'shellz.nl'
256 Query DELETE FROM usage_domain WHERE domain = 'shellz.nl'
256 Query INSERT INTO usage_domain (bytes,domain) VALUES ('1418118','shellz.nl') ON DUPLICATE KEY UPDATE bytes='1418118'
256 Query INSERT INTO usage_domain (messages,domain) VALUES ('1','shellz.nl') ON DUPLICATE KEY UPDATE messages='1'
256 Query COMMIT
257 Query BEGIN
257 Query DELETE FROM usage_mailbox WHERE userdomain = 'test at shellz.nl'
257 Query DELETE FROM usage_mailbox WHERE userdomain = 'test at shellz.nl'
257 Query INSERT INTO usage_mailbox (bytes,userdomain) VALUES ('1418118','test at shellz.nl') ON DUPLICATE KEY UPDATE bytes='1418118'
257 Query INSERT INTO usage_mailbox (messages,userdomain) VALUES ('1','test at shellz.nl') ON DUPLICATE KEY UPDATE messages='1'
257 Query COMMIT
How do I prevent this from happening? It should only update the usage_mailbox table when I run the recalc command.
Could this be because we use seperate tables for domain and user usage?
dovecot -n:
# 2.2.9: /etc/dovecot/dovecot.conf
# OS: Linux 3.13.0-37-generic x86_64 Ubuntu 14.04.1 LTS
auth_mechanisms = plain login
auth_username_chars = abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890+=_.-@&
dict {
expire = mysql:/etc/dovecot/dovecot-sql-expire.conf
quotadomaindict = mysql:/etc/dovecot/dovecot-sql-quota-domain.conf
quotauserdict = mysql:/etc/dovecot/dovecot-sql-quota-user.conf
}
disable_plaintext_auth = no
listen = *,[::]
mail_fsync = never
mail_location = maildir:~/Maildir
mail_plugins = quota expire
managesieve_notify_capability = mailto
managesieve_sieve_capability = fileinto reject envelope encoded-character vacation subaddress comparator-i;ascii-numeric relational regex imap4flags copy include variables body enotify environment mailbox date ihave
passdb {
args = /etc/dovecot/dovecot-sql-user.conf
driver = sql
}
plugin {
expire = Trash
expire2 = Spam
expire_dict = proxy::expire
mail_log_events = delete copy save expunge mailbox_delete mailbox_rename
mail_log_fields = uid box msgid size
quota = dict:Domain quota:%d:proxy::quotadomaindict
quota_rule2 = Trash:ignore
quota_warning = storage=99%% doquotawarning 99 %u
quota_warning2 = storage=95%% doquotawarning 95 %u
quota_warning3 = storage=75%% doquotawarning 75 %u
quota2 = dict:User quota::proxy::quotauserdict
quota2_rule2 = Trash:ignore
quota2_warning = storage=99%% doquotawarning 99 %u
quota2_warning2 = storage=95%% doquotawarning 95 %u
quota2_warning3 = storage=75%% doquotawarning 75 %u
}
protocols = imap pop3
service auth-worker {
unix_listener auth-worker {
mode = 0600
user = vmail
}
}
service auth {
unix_listener /var/spool/postfix/private/auth {
group = postfix
mode = 0660
user = postfix
}
unix_listener auth-master {
mode = 0600
user = vmail
}
unix_listener auth-userdb {
mode = 0600
user = vmail
}
user = vmail
}
service dict {
unix_listener dict {
mode = 0600
user = vmail
}
}
service doquotawarning {
executable = script /usr/bin/doquotawarning.py
unix_listener doquotawarning {
user = vmail
}
user = vmail
}
service imap-login {
chroot =
process_limit = 200
process_min_avail = 2
}
service imap {
executable = imap postlogin
}
service managesieve-login {
chroot =
}
service pop3-login {
chroot =
process_limit = 50
process_min_avail = 2
}
service pop3 {
executable = pop3 postlogin
}
service postlogin {
executable = script-login /usr/local/postfixint/dolastlogin.py
user = $default_internal_user
}
ssl_cert = </etc/ssl/certs/wild.totaal.net/wild.totaal.net.pem
ssl_cipher_list = ALL:!LOW:!SSLv2:ALL:!aNULL:!ADH:!eNULL:!EXP:RC4+RSA:+HIGH:+MEDIUM
ssl_key = </etc/ssl/certs/wild.totaal.net/wild.totaal.net.key
userdb {
driver = prefetch
}
userdb {
args = /etc/dovecot/dovecot-sql-user.conf
driver = sql
}
protocol imap {
imap_client_workarounds = delay-newmail tb-extra-mailbox-sep
imap_logout_format = bytes=%i/%o
mail_max_userip_connections = 50
mail_plugins = quota imap_quota mail_log notify expire
}
protocol pop3 {
mail_max_userip_connections = 50
mail_plugins = quota expire
pop3_client_workarounds = outlook-no-nuls oe-ns-eoh
pop3_logout_format = top=%t/%p, retr=%r/%b, del=%d/%m, size=%s, bytes=%i/%o
pop3_uidl_format = %f
}
protocol lda {
deliver_log_format = msgid=%m: %$
mail_fsync = optimized
mail_plugins = quota sieve expire
postmaster_address = hostmaster at vps04.totaal.net
rejection_reason = Your message to <%t>' was automatically rejected:%n%r
}
dovecot-sql-quota-domain.conf:
connect = xxx
map {
pattern = priv/quota/storage
table = usage_domain
username_field = domain
value_field = bytes
}
map {
pattern = priv/quota/messages
table = usage_domain
username_field = domain
value_field = messages
}
dovecot-sql-quota-user.conf:
connect = xxx
map {
pattern = priv/quota/storage
table = usage_mailbox
username_field = userdomain
value_field = bytes
}
map {
pattern = priv/quota/messages
table = usage_mailbox
username_field = userdomain
value_field = messages
}
Thanks in advance to anyone who might be able to shed some light in this situation :).
Kind regards,
Rick van den Hof
--
Manager Engineering
Totaalnet Internet Works B.V.
IJsselburcht 4e
6825 BP Arnhem
+31(0)26-3844944 | r.vandenhof at tiw.eu (PGP Key: 0x5A66E935)
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 473 bytes
Desc: Digital signature
URL: <http://dovecot.org/pipermail/dovecot/attachments/20141014/fb606c18/attachment-0001.sig>