Hello,
Un dernier problème sur lequel je planche depuis quelques jours maintenant sur ma conf : la gestion du quota avec mysql.
Pour ma conf, j’ai donc du postfix, interfacé avec dovecot et je créé mes comptes/domaines avec postfixadmin. De ce côté là, tout fonctionne bien. Par contre, quand je set des quotas pour les BAL, les quotas seté dans postfixadmin (qui correspondent donc aux quotas que l’on retrouve dans la clef quota de la table mailbox de la base postfix) ne sont pas pris en compte par Dovecot.
En effet, si je set 10M dans postfixadmin, en base de donnée c’est bien mise à jour, en revanche si je fais un
doveadm quota get -u tata@mondomaine.com
j’obtiens ce qui correspond au quota seté en dure dans le fichier 90-quota.conf, variable quota_rule :
Quota name Type Value Limit %
User Quota STORAGE 60 102400 0
User Quota MESSAGE 7 - 0
J’ai essayé même après avoir forcé un recalcule du quota par Dovecot, idem.
Le problème est donc que Dovecot ne va pas chercher le quota dans MySQL, mais dans le fichier de conf en dure et je ne comprends pas pourquoi…
Voici ma conf Dovecot :
# 2.3.4.1 (f79e8e7e4): /etc/dovecot/dovecot.conf
# Pigeonhole version 0.5.4 ()
# OS: Linux 4.19.0-5-amd64 x86_64 Debian 10.0 ext4
# Hostname: mail.mondomaine.org
auth_mechanisms = plain login
dict {
sqlquota = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext
}
first_valid_uid = 5000
imap_idle_notify_interval = 30 mins
last_valid_uid = 5000
listen = *
mail_debug = yes
mail_gid = 5000
mail_location = maildir:/var/mail/vhosts/%d/%n/mail
mail_plugins = quota
mail_privileged_group = vmail
mail_uid = 5000
maildir_stat_dirs = yes
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 {
auto = subscribe
special_use = \Drafts
}
mailbox Junk {
auto = subscribe
special_use = \Junk
}
mailbox Sent {
auto = subscribe
special_use = \Sent
}
mailbox "Sent Messages" {
special_use = \Sent
}
mailbox Trash {
auto = subscribe
special_use = \Trash
}
prefix =
}
passdb {
args = /etc/dovecot/dovecot-sql.conf.ext
driver = sql
}
plugin {
quota = dict:User Quota::proxy::sqlquota
quota_exceeded_message = Quota exceeded, please contact your system administrator.
quota_grace = 10%%
quota_rule = *:storage=100M
quota_rule2 = Trash:storage=+100M
quota_warning = storage=100%% quota-warning 100 %u
quota_warning2 = storage=95%% quota-warning 95 %u
quota_warning3 = storage=90%% quota-warning 90 %u
quota_warning4 = storage=85%% quota-warning 85 %u
sieve = file:~/sieve;active=~/.dovecot.sieve
}
protocols = imap lmtp sieve
service auth-worker {
user = vmail
}
service auth {
unix_listener /var/spool/postfix/private/auth {
group = postfix
mode = 0666
user = postfix
}
unix_listener auth-userdb {
group = vmail
mode = 0600
user = vmail
}
user = dovecot
}
service dict {
unix_listener dict {
group = vmail
mode = 0660
user = vmail
}
}
service imap-login {
inet_listener imap {
port = 143
}
inet_listener imaps {
port = 993
ssl = yes
}
service_count = 0
}
service lmtp {
unix_listener /var/spool/postfix/private/dovecot-lmtp {
group = postfix
mode = 0600
user = postfix
}
}
service managesieve-login {
inet_listener sieve {
port = 4190
}
service_count = 1
}
service quota-warning {
executable = script /etc/dovecot/quota.sh
unix_listener quota-warning {
group = vmail
mode = 0660
user = vmail
}
user = vmail
}
ssl = required
ssl_cert = </etc/letsencrypt/live/cert_global_mondomaine/fullchain.pem
ssl_cipher_list = ALL:!LOW:!SSLv2:!EXP:!aNULL
ssl_dh = # hidden, use -P to show it
ssl_key = # hidden, use -P to show it
ssl_min_protocol = TLSv1.1
ssl_prefer_server_ciphers = yes
userdb {
args = uid=vmail gid=vmail home=/var/mail/vhosts/%d/%n
driver = static
}
protocol lmtp {
mail_plugins = quota sieve
postmaster_address = postmaster@mondomaine.org
}
protocol lda {
info_log_path =
log_path =
mail_plugins = sieve quota
postmaster_address = postmaster@mondomaine.org
quota_full_tempfail = yes
}
protocol imap {
mail_max_userip_connections = 50
mail_plugins = quota imap_sieve quota imap_quota
postmaster_address = postmaster@mondomaine.org
}
dovecot-dict-sql.conf.ext :
# This file is commonly accessed via dict {} section in dovecot.conf
connect = host=localhost dbname=postfix user=postfix password=ddsqdsqdqsd
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
}
map {
pattern = shared/expire/$user/$mailbox
table = expires
value_field = expire_stamp
fields {
username = $user
mailbox = $mailbox
}
}
dovecot-sql.conf.ext :
driver = mysql
connect = host=127.0.0.1 dbname=postfix user=postfix password=sdfdsfdsffdffds
default_pass_scheme = MD5-CRYPT
password_query = SELECT username AS user,password FROM mailbox \
WHERE username = '%u' AND active='1'
user_query = SELECT '/var/mail/vhosts/%d/%n' as home, 'maildir:/var/mail/vhosts/%d/%n' as mail, 5000 AS uid, 12 AS gid, concat('*:bytes=', quota) AS quota_rule FROM mailbox WHERE username = '%u' AND active = '1'
Je me demande si le problème ne vient pas de ma requête SQL contenue dans
dovecot-sql.conf.ext, j’ai fait pas mal d’essais en la concevant différemment, le problème reste entier.
Concernant la log, pour une exectution de doveadm quota get :
Jul 24 14:43:45 auth: Debug: master in: USER 1 tata@mydomain.com service=doveadm
Jul 24 14:43:45 auth-worker(26532): Debug: sql(tata@mydomain.com): query: SELECT username AS user,password FROM mailbox WHERE username = 'tata@mydomain.com' AND active='1'
Jul 24 14:43:45 auth: Debug: sql(tata@mydomain.com): Credentials: 2431243732306337623462244846523273766c4d39704f7a4636466873364f625031
Jul 24 14:43:45 auth: Debug: userdb out: USER 1 tata@mydomain.com uid=5000 gid=5000 home=/var/mail/vhosts/mydomain.com/tata
Jul 24 14:43:49 auth: Debug: client in: AUTH 3 PLAIN service=imap secured=tls session=UZIwqWyOJtBYefsL lip=x.x.x.x rip=y.y.y.y lport=993 rport=53286 local_name=imap.mydomain.com ssl_cipher=ECDHE-RSA-AES256-GCM-SHA384 ssl_cipher_bits=256 ssl_pfs=KxECDHE ssl_protocol=TLSv1.2
Jul 24 14:43:49 auth: Debug: client passdb out: CONT 3
Jul 24 14:43:49 auth: Debug: client in: AUTH 4 PLAIN service=imap secured=tls session=49YwqWyOJ9BYefsL lip=51.254.71.219 rip=y.y.y.y lport=993 rport=53287 local_name=mail.mydomain.org ssl_cipher=ECDHE-RSA-AES256-GCM-SHA384 ssl_cipher_bits=256 ssl_pfs=KxECDHE ssl_protocol=TLSv1.2
Jul 24 14:43:49 auth: Debug: client passdb out: CONT 4
Jul 24 14:43:49 auth: Debug: client in: CONT 3 AHRhdGFAZ2wtYW1mLmNvbQAzMmYzcGJ3 (previous base64 data may contain sensitive data)
Jul 24 14:43:49 auth-worker(26532): Debug: sql(tata@mydomain.com,y.y.y.y,<UZIwqWyOJtBYefsL>): query: SELECT username AS user,password FROM mailbox WHERE username = 'tata@mydomain.com' AND active='1'
Jul 24 14:43:49 auth: Debug: client passdb out: OK 3 user=tata@mydomain.com
Jul 24 14:43:49 auth: Debug: master in: REQUEST 565313537 26534 3 2df404d6ed568a04d1f147d0e6f753fa session_pid=26539 request_auth_token
Jul 24 14:43:49 auth: Debug: master userdb out: USER 565313537 tata@mydomain.com uid=5000 gid=5000 home=/var/mail/vhosts/mydomain.com/tata auth_token=a7e8eb11386c0e78cc7f7248ae8233d11766a141
Jul 24 14:43:49 imap(tata@mydomain.com)<26539><UZIwqWyOJtBYefsL>: Debug: Loading modules from directory: /usr/lib/dovecot/modules
Jul 24 14:43:49 imap(tata@mydomain.com)<26539><UZIwqWyOJtBYefsL>: Debug: Module loaded: /usr/lib/dovecot/modules/lib10_quota_plugin.so
Jul 24 14:43:49 imap(tata@mydomain.com)<26539><UZIwqWyOJtBYefsL>: Debug: Module loaded: /usr/lib/dovecot/modules/lib11_imap_quota_plugin.so
Jul 24 14:43:49 imap(tata@mydomain.com)<26539><UZIwqWyOJtBYefsL>: Debug: Module loaded: /usr/lib/dovecot/modules/lib95_imap_sieve_plugin.so
Jul 24 14:43:49 imap(tata@mydomain.com)<26539><UZIwqWyOJtBYefsL>: Debug: Effective uid=5000, gid=5000, home=/var/mail/vhosts/mydomain.com/tata
Jul 24 14:43:49 imap(tata@mydomain.com)<26539><UZIwqWyOJtBYefsL>: Debug: Quota root: name=User quota backend=dict args=:proxy::sqlquota
Jul 24 14:43:49 imap(tata@mydomain.com)<26539><UZIwqWyOJtBYefsL>: Debug: Quota rule: root=User quota mailbox=* bytes=104857600 messages=0
Jul 24 14:43:49 imap(tata@mydomain.com)<26539><UZIwqWyOJtBYefsL>: Debug: Quota rule: root=User quota mailbox=Trash bytes=+104857600 messages=0
Jul 24 14:43:49 imap(tata@mydomain.com)<26539><UZIwqWyOJtBYefsL>: Debug: Quota warning: bytes=104857600 (100%) messages=0 reverse=no command=quota-warning 100 tata@mydomain.com
Jul 24 14:43:49 imap(tata@mydomain.com)<26539><UZIwqWyOJtBYefsL>: Debug: Quota warning: bytes=99614720 (95%) messages=0 reverse=no command=quota-warning 95 tata@mydomain.com
Jul 24 14:43:49 imap(tata@mydomain.com)<26539><UZIwqWyOJtBYefsL>: Debug: Quota warning: bytes=94371840 (90%) messages=0 reverse=no command=quota-warning 90 tata@mydomain.com
Jul 24 14:43:49 imap(tata@mydomain.com)<26539><UZIwqWyOJtBYefsL>: Debug: Quota warning: bytes=89128960 (85%) messages=0 reverse=no command=quota-warning 85 tata@mydomain.com
Jul 24 14:43:49 imap(tata@mydomain.com)<26539><UZIwqWyOJtBYefsL>: Debug: Quota grace: root=User quota bytes=10485760 (10%)
Jul 24 14:43:49 imap(tata@mydomain.com)<26539><UZIwqWyOJtBYefsL>: Debug: dict quota: user=tata@mydomain.com, uri=proxy::sqlquota, noenforcing=0
Jul 24 14:43:49 imap(tata@mydomain.com)<26539><UZIwqWyOJtBYefsL>: Debug: Namespace inbox: type=private, prefix=, sep=, inbox=yes, hidden=no, list=yes, subscriptions=yes location=maildir:/var/mail/vhosts/mydomain.com/tata/mail
Jul 24 14:43:49 imap(tata@mydomain.com)<26539><UZIwqWyOJtBYefsL>: Debug: maildir++: root=/var/mail/vhosts/mydomain.com/tata/mail, index=, indexpvt=, control=, inbox=/var/mail/vhosts/mydomain.com/tata/mail, alt=
Jul 24 14:43:49 imap(tata@mydomain.com)<26539><UZIwqWyOJtBYefsL>: Debug: quota: quota_over_flag check: quota_over_script unset - skipping
Jul 24 14:43:49 auth: Debug: client in: CONT 4 AGFkbWluQGdsLWFtZi5vcmcAMzJmM3Bidw== (previous base64 data may contain sensitive data)
Jul 24 14:43:49 auth-worker(26532): Debug: sql(admin@mydomain.org,y.y.y.y,<49YwqWyOJ9BYefsL>): query: SELECT username AS user,password FROM mailbox WHERE username = 'admin@mydomain.org' AND active='1'
Jul 24 14:43:49 auth: Debug: client passdb out: OK 4 user=admin@mydomain.org
Jul 24 14:43:49 auth: Debug: master in: REQUEST 1024589825 26534 4 2df404d6ed568a04d1f147d0e6f753fa session_pid=26540 request_auth_token
Jul 24 14:43:49 auth: Debug: master userdb out: USER 1024589825 admin@mydomain.org uid=5000 gid=5000 home=/var/mail/vhosts/mydomain.org/admin auth_token=6d91e986e45c0f62ff1e1299f0d9676ebf4ba55b
Jul 24 14:43:49 imap(admin@mydomain.org)<26540><49YwqWyOJ9BYefsL>: Debug: Loading modules from directory: /usr/lib/dovecot/modules
Jul 24 14:43:49 imap(admin@mydomain.org)<26540><49YwqWyOJ9BYefsL>: Debug: Module loaded: /usr/lib/dovecot/modules/lib10_quota_plugin.so
Jul 24 14:43:49 imap(admin@mydomain.org)<26540><49YwqWyOJ9BYefsL>: Debug: Module loaded: /usr/lib/dovecot/modules/lib11_imap_quota_plugin.so
Jul 24 14:43:49 imap(admin@mydomain.org)<26540><49YwqWyOJ9BYefsL>: Debug: Module loaded: /usr/lib/dovecot/modules/lib95_imap_sieve_plugin.so
Jul 24 14:43:49 imap(admin@mydomain.org)<26540><49YwqWyOJ9BYefsL>: Debug: Effective uid=5000, gid=5000, home=/var/mail/vhosts/mydomain.org/admin
Jul 24 14:43:49 imap(admin@mydomain.org)<26540><49YwqWyOJ9BYefsL>: Debug: Quota root: name=User quota backend=dict args=:proxy::sqlquota
Jul 24 14:43:49 imap(admin@mydomain.org)<26540><49YwqWyOJ9BYefsL>: Debug: Quota rule: root=User quota mailbox=* bytes=104857600 messages=0
Jul 24 14:43:49 imap(admin@mydomain.org)<26540><49YwqWyOJ9BYefsL>: Debug: Quota rule: root=User quota mailbox=Trash bytes=+104857600 messages=0
Jul 24 14:43:49 imap(admin@mydomain.org)<26540><49YwqWyOJ9BYefsL>: Debug: Quota warning: bytes=104857600 (100%) messages=0 reverse=no command=quota-warning 100 admin@mydomain.org
Jul 24 14:43:49 imap(admin@mydomain.org)<26540><49YwqWyOJ9BYefsL>: Debug: Quota warning: bytes=99614720 (95%) messages=0 reverse=no command=quota-warning 95 admin@mydomain.org
Jul 24 14:43:49 imap(admin@mydomain.org)<26540><49YwqWyOJ9BYefsL>: Debug: Quota warning: bytes=94371840 (90%) messages=0 reverse=no command=quota-warning 90 admin@mydomain.org
Jul 24 14:43:49 imap(admin@mydomain.org)<26540><49YwqWyOJ9BYefsL>: Debug: Quota warning: bytes=89128960 (85%) messages=0 reverse=no command=quota-warning 85 admin@mydomain.org
Jul 24 14:43:49 imap(admin@mydomain.org)<26540><49YwqWyOJ9BYefsL>: Debug: Quota grace: root=User quota bytes=10485760 (10%)
Jul 24 14:43:49 imap(admin@mydomain.org)<26540><49YwqWyOJ9BYefsL>: Debug: dict quota: user=admin@mydomain.org, uri=proxy::sqlquota, noenforcing=0
Jul 24 14:43:49 imap(admin@mydomain.org)<26540><49YwqWyOJ9BYefsL>: Debug: Namespace inbox: type=private, prefix=, sep=, inbox=yes, hidden=no, list=yes, subscriptions=yes location=maildir:/var/mail/vhosts/mydomain.org/admin/mail
Jul 24 14:43:49 imap(admin@mydomain.org)<26540><49YwqWyOJ9BYefsL>: Debug: maildir++: root=/var/mail/vhosts/mydomain.org/admin/mail, index=, indexpvt=, control=, inbox=/var/mail/vhosts/mydomain.org/admin/mail, alt=
Jul 24 14:43:49 imap(admin@mydomain.org)<26540><49YwqWyOJ9BYefsL>: Debug: quota: quota_over_flag check: quota_over_script unset - skipping
Jul 24 14:43:49 imap(tata@mydomain.com)<26539><UZIwqWyOJtBYefsL>: Debug: Mailbox INBOX: Mailbox opened because: SELECT
Jul 24 14:43:49 imap(admin@mydomain.org)<26540><49YwqWyOJ9BYefsL>: Debug: Mailbox INBOX: Mailbox opened because: SELECT
On voit bien que le coquin va chercher les infos utilisateurs en base de donnée, mais pour ce qui est du quota, il se base sur le fichier plat.
/edit : bon, j’avance, en fait j’avais un problème dans mon conf.d/auth-sql.conf.ext, pour userdb j’étais sur du static au lieu de SQL, du coup il interrogeait les limites du fichier plat et non de la base de donnée :
grep -A1 -B1 driver conf.d/auth-sql.conf.ext
passdb {
driver = sql
args = /etc/dovecot/dovecot-sql.conf.ext
--
#userdb {
# driver = static
# args = uid=vmail gid=vmail home=/var/mail/vhosts/%d/%n
--
userdb {
driver = sql
args = /etc/dovecot/dovecot-sql.conf.ext
Par contre j’ai encore un problème de taille…
doveadm quota get -u tata@monsite.com
Quota name Type Value Limit %
User quota STORAGE 16 2048000 0
User quota MESSAGE 2 - 0
Parfait, c’est bien ça, là nous avons un résultat en Kbytes, soit 2go. Le problème est que j’ai seté 2 Mo dans Postfixadmin… pourquoi m’a-t-il mis 2 go ?
/edit2: la requête user_query n’était pas bonne, le contact ne pointait pas vers les bonnes données (j’ai corrigé ici si ça sert à quelqu’un…)