SUnsin at gv-bayern.de
2018-Nov-15 18:52 UTC
Quota in MySql Dict not recalculate automatic
Hi,
I have a working Installation with:
Ubuntu 16.04 LTS
Dovecot 2.2.22
MySql 5.7.24
Postfixadmin 3.2
Apache 2.4.18
Rainloop 1.12.1
I manage the E-Mail Accounts with postfixadmin in a MySql-DB.
Also I use quotas with Quota Backend postfixadmin-DB (dict).
Everything works fine.
Now I installed a new Server with the following Versions and
migrate the Configs to the new System.
Ubuntu 18.04 LTS
Doveot 2.2.33
MySQL 5.7.24
Postfixadmin 3.2
Apache 2.4.18
Rainloop 1.12.1
The Problem is now: When I receive a E-Mail, the quota will not
Update (recalculate) automatical in the MySql-DB from Dovecot.
In the old System there execute the SQL Statement after receiving a E-Mail,
for example:
UPDATE quota2 SET bytes=bytes+3076,messages=messages+1 WHERE username <E-Mail
Address>
On the new System with the newer Dovecot Version, the Statement never comes
after
receive an E-Mail.
With Dovecot Version 2.3 on the old System with Ubuntu 16.04, everything
works fine
with the same config.
Is a BUG in the Dovecot Version 2.2.33 possible?
Can everbody help me?
Kind regards
Stephan
dovecot -n
# 2.2.33.2 (d6601f4ec): /etc/dovecot/dovecot.conf
# Pigeonhole version 0.4.21 (92477967)
# OS: Linux 4.15.0-39-generic x86_64 Ubuntu 18.04.1 LTS
auth_debug = yes
auth_debug_passwords = yes
auth_master_user_separator = *
auth_mechanisms = plain login
auth_verbose_passwords = yes
dict {
sqldomainquota = mysql:/etc/dovecot/dovecot-dict-sql-domain.conf.ext
sqluserquota = mysql:/etc/dovecot/dovecot-dict-sql-user.conf.ext
}
disable_plaintext_auth = no
login_greeting = Dovecot ready.
mail_debug = yes
mail_location = maildir:~/Maildir
mail_plugins = " quota"
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 index ihave
duplicate mime foreverypart extracttext
namespace inbox {
inbox = yes
location mailbox Drafts {
special_use = \Drafts
}
mailbox Junk {
special_use = \Junk
}
mailbox Sent {
special_use = \Sent
}
mailbox "Sent Messages" {
special_use = \Sent
}
mailbox Trash {
special_use = \Trash
}
prefix separator = /
}
passdb {
args = /etc/dovecot/master-users
driver = passwd-file
master = yes
pass = yes
}
passdb {
args = /etc/dovecot/dovecot-sql.conf.ext
driver = sql
}
plugin {
quota = dict:User Quota::noenforcing:proxy::sqluserquota
quota_grace = 10%%
quota_status_nouser = DUNNO
quota_status_overquota = 552 5.2.2 Mailbox is over quota / Mailbox ist
voll
quota_status_success = DUNNO
quota_warning = storage=95%% quota-warning 95 %u
quota_warning2 = storage=80%% quota-warning 80 %u
}
postmaster_address = postmaster at xxx
protocols = " imap lmtp sieve pop3 sieve"
service auth {
unix_listener /var/spool/postfix/private/auth {
group = postfix
mode = 0666
user = postfix
}
}
service dict {
unix_listener dict {
group = vmail
mode = 0660
user = vmail
}
}
service lmtp {
inet_listener lmtp {
address = localhost
port = 24
}
}
service managesieve-login {
inet_listener sieve {
port = 4190
}
}
service quota-status {
client_limit = 1
executable = quota-status -p postfix
inet_listener {
port = 12340
}
}
service quota-warning {
executable = script /usr/local/bin/quota-warning.sh
unix_listener quota-warning {
group = vmail
mode = 0660
user = vmail
}
user = vmail
}
ssl_cert = xxx
ssl_key = # hidden, use -P to show it
userdb {
driver = prefetch
}
userdb {
args = /etc/dovecot/dovecot-sql.conf.ext
driver = sql
}
protocol lmtp {
mail_plugins = " quota"
}
protocol imap {
mail_max_userip_connections = 10
mail_plugins = " quota imap_quota"
}
grep -v '^ *\(#.*\)\?$' dovecot-sql.conf :
driver = mysql
connect = host=localhost dbname=postfixadmin user=pfadmin
password=UCLGXEb9rEEOTAO
default_pass_scheme = SHA512-CRYPT
password_query = \
SELECT username as user, password, '/var/vmail/%Ld/%Ln' as
userdb_home, \
'maildir:/var/vmail/%Ld/%Ln' as userdb_mail, 5000 as userdb_uid, 5000
as
userdb_gid \
FROM mailbox WHERE username = '%Lu' AND active = '1'
user_query = \
SELECT '/var/vmail/%Ld/%Ln' as home, \
'maildir:/var/vmail/%Ld/%Ln' as mail, \
5000 AS uid, \
5000 AS gid, \
concat('*:bytes=', \
if(mailbox.quota = 0, domain.maxquota*1024000, mailbox.quota)) \
as quota_rule \
FROM mailbox, domain \
WHERE username = '%Lu' AND mailbox.active = '1' AND \
domain.domain = '%d' AND domain.active = '1'
iterate_query = SELECT username as user FROM mailbox WHERE active = '1'
Ref. Systeme / Bereich Digitalisierung und Informationstechnik (IT)
Telefon: 089 2868-3280
Telefax: 089 2868-3275
Internet-E-Mail: SUnsin at gv-bayern.de
Notes-E-Mail: Stephan Unsin at GVB
http://www.gv-bayern.de
Genossenschaftsverband Bayern e.V.
T?rkenstrasse 22-24, 80333 M?nchen
Es gelten die jeweils aktuellen Allgemeinen Auftragsbedingungen des
Genossenschaftsverbands Bayern e.V.
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
<https://dovecot.org/pipermail/dovecot/attachments/20181115/d9a18697/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 4584 bytes
Desc: S/MIME Cryptographic Signature
URL:
<https://dovecot.org/pipermail/dovecot/attachments/20181115/d9a18697/attachment-0001.p7s>
dict quota is known to have this problem that it is not always updated on time. You should use quota count with quota_clone plugin instead (might require upgrade). Please remember to change password for mysql user pfadmin, as you accidentically sent it to the list. Aki> On 15 November 2018 at 20:52 SUnsin at gv-bayern.de wrote: > > > > > Hi, > > I have a working Installation with: > Ubuntu 16.04 LTS > Dovecot 2.2.22 > MySql 5.7.24 > Postfixadmin 3.2 > Apache 2.4.18 > Rainloop 1.12.1 > > I manage the E-Mail Accounts with postfixadmin in a MySql-DB. > Also I use quotas with Quota Backend postfixadmin-DB (dict). > Everything works fine. > > Now I installed a new Server with the following Versions and > migrate the Configs to the new System. > Ubuntu 18.04 LTS > Doveot 2.2.33 > MySQL 5.7.24 > Postfixadmin 3.2 > Apache 2.4.18 > Rainloop 1.12.1 > > The Problem is now: When I receive a E-Mail, the quota will not > Update (recalculate) automatical in the MySql-DB from Dovecot. > > In the old System there execute the SQL Statement after receiving a E-Mail, > for example: > UPDATE quota2 SET bytes=bytes+3076,messages=messages+1 WHERE username > <E-Mail Address> > > On the new System with the newer Dovecot Version, the Statement never comes > after > receive an E-Mail. > > With Dovecot Version 2.3 on the old System with Ubuntu 16.04, everything > works fine > with the same config. > > Is a BUG in the Dovecot Version 2.2.33 possible? > Can everbody help me? > > Kind regards > Stephan > > > dovecot -n > # 2.2.33.2 (d6601f4ec): /etc/dovecot/dovecot.conf > # Pigeonhole version 0.4.21 (92477967) > # OS: Linux 4.15.0-39-generic x86_64 Ubuntu 18.04.1 LTS > auth_debug = yes > auth_debug_passwords = yes > auth_master_user_separator = * > auth_mechanisms = plain login > auth_verbose_passwords = yes > dict { > sqldomainquota = mysql:/etc/dovecot/dovecot-dict-sql-domain.conf.ext > sqluserquota = mysql:/etc/dovecot/dovecot-dict-sql-user.conf.ext > } > disable_plaintext_auth = no > login_greeting = Dovecot ready. > mail_debug = yes > mail_location = maildir:~/Maildir > mail_plugins = " quota" > 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 index ihave > duplicate mime foreverypart extracttext > namespace inbox { > inbox = yes > location > mailbox Drafts { > special_use = \Drafts > } > mailbox Junk { > special_use = \Junk > } > mailbox Sent { > special_use = \Sent > } > mailbox "Sent Messages" { > special_use = \Sent > } > mailbox Trash { > special_use = \Trash > } > prefix > separator = / > } > passdb { > args = /etc/dovecot/master-users > driver = passwd-file > master = yes > pass = yes > } > passdb { > args = /etc/dovecot/dovecot-sql.conf.ext > driver = sql > } > plugin { > quota = dict:User Quota::noenforcing:proxy::sqluserquota > quota_grace = 10%% > quota_status_nouser = DUNNO > quota_status_overquota = 552 5.2.2 Mailbox is over quota / Mailbox ist > voll > quota_status_success = DUNNO > quota_warning = storage=95%% quota-warning 95 %u > quota_warning2 = storage=80%% quota-warning 80 %u > } > postmaster_address = postmaster at xxx > protocols = " imap lmtp sieve pop3 sieve" > service auth { > unix_listener /var/spool/postfix/private/auth { > group = postfix > mode = 0666 > user = postfix > } > } > service dict { > unix_listener dict { > group = vmail > mode = 0660 > user = vmail > } > } > service lmtp { > inet_listener lmtp { > address = localhost > port = 24 > } > } > service managesieve-login { > inet_listener sieve { > port = 4190 > } > } > service quota-status { > client_limit = 1 > executable = quota-status -p postfix > inet_listener { > port = 12340 > } > } > service quota-warning { > executable = script /usr/local/bin/quota-warning.sh > unix_listener quota-warning { > group = vmail > mode = 0660 > user = vmail > } > user = vmail > } > ssl_cert = xxx > ssl_key = # hidden, use -P to show it > userdb { > driver = prefetch > } > userdb { > args = /etc/dovecot/dovecot-sql.conf.ext > driver = sql > } > protocol lmtp { > mail_plugins = " quota" > } > protocol imap { > mail_max_userip_connections = 10 > mail_plugins = " quota imap_quota" > } > > > > > grep -v '^ *\(#.*\)\?$' dovecot-sql.conf : > > driver = mysql > connect = host=localhost dbname=postfixadmin user=pfadmin > password=UCLGXEb9rEEOTAO > default_pass_scheme = SHA512-CRYPT > password_query = \ > SELECT username as user, password, '/var/vmail/%Ld/%Ln' as userdb_home, \ > 'maildir:/var/vmail/%Ld/%Ln' as userdb_mail, 5000 as userdb_uid, 5000 as > userdb_gid \ > FROM mailbox WHERE username = '%Lu' AND active = '1' > user_query = \ > SELECT '/var/vmail/%Ld/%Ln' as home, \ > 'maildir:/var/vmail/%Ld/%Ln' as mail, \ > 5000 AS uid, \ > 5000 AS gid, \ > concat('*:bytes=', \ > if(mailbox.quota = 0, domain.maxquota*1024000, mailbox.quota)) \ > as quota_rule \ > FROM mailbox, domain \ > WHERE username = '%Lu' AND mailbox.active = '1' AND \ > domain.domain = '%d' AND domain.active = '1' > iterate_query = SELECT username as user FROM mailbox WHERE active = '1' > > Ref. Systeme / Bereich Digitalisierung und Informationstechnik (IT) > Telefon: 089 2868-3280 > Telefax: 089 2868-3275 > Internet-E-Mail: SUnsin at gv-bayern.de > Notes-E-Mail: Stephan Unsin at GVB > > http://www.gv-bayern.de > > Genossenschaftsverband Bayern e.V. > T?rkenstrasse 22-24, 80333 M?nchen > > Es gelten die jeweils aktuellen Allgemeinen Auftragsbedingungen des > Genossenschaftsverbands Bayern e.V.
SUnsin at gv-bayern.de
2018-Nov-16 09:47 UTC
Antwort: Re: Quota in MySql Dict not recalculate automatic
With quota count and quota_clone it works, but can I use noenforcing with
quota count?
quota = count:User quota:noenforcing
When I test this config, I receive no quota-warning E-Mail:
service quota-warning {
executable = script /usr/local/bin/quota-warning.sh
# user = dovecot
user = vmail
unix_listener quota-warning {
user = vmail
group = vmail
mode = 0660
}
}
plugin {
quota_warning = storage=95%% quota-warning 95 %u
quota_warning2 = storage=80%% quota-warning 80 %u
}
I wouldn?t like reject an E-Mail although des quota ist over the limit.
Stephan
Von: "Aki Tuomi" <aki.tuomi at open-xchange.com>
An: "Dovecot Mailing List" <dovecot at dovecot.org>,
SUnsin at gv-bayern.de
Datum: 15.11.2018 20:15
Betreff: Re: Quota in MySql Dict not recalculate automatic
dict quota is known to have this problem that it is not always updated on
time. You should use quota count with quota_clone plugin instead (might
require upgrade).
Please remember to change password for mysql user pfadmin, as you
accidentically sent it to the list.
Aki
> On 15 November 2018 at 20:52 SUnsin at gv-bayern.de wrote:
>
>
>
>
> Hi,
>
> I have a working Installation with:
> Ubuntu 16.04 LTS
> Dovecot 2.2.22
> MySql 5.7.24
> Postfixadmin 3.2
> Apache 2.4.18
> Rainloop 1.12.1
>
> I manage the E-Mail Accounts with postfixadmin in a MySql-DB.
> Also I use quotas with Quota Backend postfixadmin-DB (dict).
> Everything works fine.
>
> Now I installed a new Server with the following Versions and
> migrate the Configs to the new System.
> Ubuntu 18.04 LTS
> Doveot 2.2.33
> MySQL 5.7.24
> Postfixadmin 3.2
> Apache 2.4.18
> Rainloop 1.12.1
>
> The Problem is now: When I receive a E-Mail, the quota will not
> Update (recalculate) automatical in the MySql-DB from Dovecot.
>
> In the old System there execute the SQL Statement after receiving a
E-Mail,> for example:
> UPDATE quota2 SET bytes=bytes+3076,messages=messages+1 WHERE username >
<E-Mail Address>
>
> On the new System with the newer Dovecot Version, the Statement never
comes> after
> receive an E-Mail.
>
> With Dovecot Version 2.3 on the old System with Ubuntu 16.04, everything
> works fine
> with the same config.
>
> Is a BUG in the Dovecot Version 2.2.33 possible?
> Can everbody help me?
>
> Kind regards
> Stephan
>
>
> dovecot -n
> # 2.2.33.2 (d6601f4ec): /etc/dovecot/dovecot.conf
> # Pigeonhole version 0.4.21 (92477967)
> # OS: Linux 4.15.0-39-generic x86_64 Ubuntu 18.04.1 LTS
> auth_debug = yes
> auth_debug_passwords = yes
> auth_master_user_separator = *
> auth_mechanisms = plain login
> auth_verbose_passwords = yes
> dict {
> sqldomainquota = mysql:/etc/dovecot/dovecot-dict-sql-domain.conf.ext
> sqluserquota = mysql:/etc/dovecot/dovecot-dict-sql-user.conf.ext
> }
> disable_plaintext_auth = no
> login_greeting = Dovecot ready.
> mail_debug = yes
> mail_location = maildir:~/Maildir
> mail_plugins = " quota"
> 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 index ihave
> duplicate mime foreverypart extracttext
> namespace inbox {
> inbox = yes
> location > mailbox Drafts {
> special_use = \Drafts
> }
> mailbox Junk {
> special_use = \Junk
> }
> mailbox Sent {
> special_use = \Sent
> }
> mailbox "Sent Messages" {
> special_use = \Sent
> }
> mailbox Trash {
> special_use = \Trash
> }
> prefix > separator = /
> }
> passdb {
> args = /etc/dovecot/master-users
> driver = passwd-file
> master = yes
> pass = yes
> }
> passdb {
> args = /etc/dovecot/dovecot-sql.conf.ext
> driver = sql
> }
> plugin {
> quota = dict:User Quota::noenforcing:proxy::sqluserquota
> quota_grace = 10%%
> quota_status_nouser = DUNNO
> quota_status_overquota = 552 5.2.2 Mailbox is over quota / Mailbox ist
> voll
> quota_status_success = DUNNO
> quota_warning = storage=95%% quota-warning 95 %u
> quota_warning2 = storage=80%% quota-warning 80 %u
> }
> postmaster_address = postmaster at xxx
> protocols = " imap lmtp sieve pop3 sieve"
> service auth {
> unix_listener /var/spool/postfix/private/auth {
> group = postfix
> mode = 0666
> user = postfix
> }
> }
> service dict {
> unix_listener dict {
> group = vmail
> mode = 0660
> user = vmail
> }
> }
> service lmtp {
> inet_listener lmtp {
> address = localhost
> port = 24
> }
> }
> service managesieve-login {
> inet_listener sieve {
> port = 4190
> }
> }
> service quota-status {
> client_limit = 1
> executable = quota-status -p postfix
> inet_listener {
> port = 12340
> }
> }
> service quota-warning {
> executable = script /usr/local/bin/quota-warning.sh
> unix_listener quota-warning {
> group = vmail
> mode = 0660
> user = vmail
> }
> user = vmail
> }
> ssl_cert = xxx
> ssl_key = # hidden, use -P to show it
> userdb {
> driver = prefetch
> }
> userdb {
> args = /etc/dovecot/dovecot-sql.conf.ext
> driver = sql
> }
> protocol lmtp {
> mail_plugins = " quota"
> }
> protocol imap {
> mail_max_userip_connections = 10
> mail_plugins = " quota imap_quota"
> }
>
>
>
>
> grep -v '^ *\(#.*\)\?$' dovecot-sql.conf :
>
> driver = mysql
> connect = host=localhost dbname=postfixadmin user=pfadmin
> password=xxx
> default_pass_scheme = SHA512-CRYPT
> password_query = \
> SELECT username as user, password, '/var/vmail/%Ld/%Ln' as
userdb_home,
\> 'maildir:/var/vmail/%Ld/%Ln' as userdb_mail, 5000 as userdb_uid,
5000
as> userdb_gid \
> FROM mailbox WHERE username = '%Lu' AND active = '1'
> user_query = \
> SELECT '/var/vmail/%Ld/%Ln' as home, \
> 'maildir:/var/vmail/%Ld/%Ln' as mail, \
> 5000 AS uid, \
> 5000 AS gid, \
> concat('*:bytes=', \
> if(mailbox.quota = 0, domain.maxquota*1024000, mailbox.quota))
\> as quota_rule \
> FROM mailbox, domain \
> WHERE username = '%Lu' AND mailbox.active = '1' AND \
> domain.domain = '%d' AND domain.active = '1'
> iterate_query = SELECT username as user FROM mailbox WHERE active =
'1'
>
> Ref. Systeme / Bereich Digitalisierung und Informationstechnik (IT)
> Telefon: 089 2868-3280
> Telefax: 089 2868-3275
> Internet-E-Mail: SUnsin at gv-bayern.de
> Notes-E-Mail: Stephan Unsin at GVB
>
> http://www.gv-bayern.de
>
> Genossenschaftsverband Bayern e.V.
> T?rkenstrasse 22-24, 80333 M?nchen
>
> Es gelten die jeweils aktuellen Allgemeinen Auftragsbedingungen des
> Genossenschaftsverbands Bayern e.V.
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
<https://dovecot.org/pipermail/dovecot/attachments/20181116/5fafee26/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: graycol.gif
Type: image/gif
Size: 105 bytes
Desc: not available
URL:
<https://dovecot.org/pipermail/dovecot/attachments/20181116/5fafee26/attachment-0001.gif>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 4584 bytes
Desc: S/MIME Cryptographic Signature
URL:
<https://dovecot.org/pipermail/dovecot/attachments/20181116/5fafee26/attachment-0001.p7s>