thr3ads.net - dovecot - Quota in MySql Dict not recalculate automatic [Nov 2018]

If this information is useful, please help other people find it:
Share via:

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>

Aki Tuomi

2018-Nov-15 19:08 UTC

head link

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=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

head link

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>

Maybe Matching Threads

Search for more maybe matching threads

dovecot - Nov 2018 - Quota in MySql Dict not recalculate automatic

Quota in MySql Dict not recalculate automatic

Quota in MySql Dict not recalculate automatic

Antwort: Re: Quota in MySql Dict not recalculate automatic

Maybe Matching Threads