Hi there,
I've updated Dovecot from v2.3.14 (cee3cbc0d) to v2.3.17 (e2aa53df5b).
Right after the update, dovecot begun flooding the mail.log and
PostgreSQL's log.
My dict sql configuration:
,--[ .../dovecot/dovecot-dict-sql.conf.ext ]--
| map {
| pattern = priv/quota/storage
| table = userquota
| username_field = uid
| value_field = bytes
| }
| map {
| pattern = priv/quota/messages
| table = userquota
| username_field = uid
| value_field = messages
| }
`--
This worked for decades. Since the update dovecot is passing the user's
email address, instead the user's UID to the query. And therefor I have
lots of errors in the log files: see attached `mail.log' and
`postgresql-14-main.log' excerpts.
The `userquota' table looks like this:
Table "public.userquota"
Column | Type | Collation | Nullable | Default
----------+---------+-----------+----------+---------
uid | bigint | | not null |
bytes | bigint | | not null | 0
messages | integer | | not null | 0
...
Foreign-key constraints:
"fkey_userquota_uid_users" FOREIGN KEY (uid) REFERENCES users(uid)
ON DELETE CASCADE
(full database structure:
https://bitbucket.org/pvo/vmm/src/vmm-0.7.0/pgsql/create_tables.pgsql)
Regards,
Pascal
--
Ubuntu is an ancient African word meaning ?I can?t install Debian.?
-- unknown
-------------- next part --------------
A non-text attachment was scrubbed...
Name: mail.log
Type: text/x-log
Size: 784 bytes
Desc: not available
URL:
<https://dovecot.org/pipermail/dovecot/attachments/20211204/8f314886/attachment.bin>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: postgresql-14-main.log
Type: text/x-log
Size: 259 bytes
Desc: not available
URL:
<https://dovecot.org/pipermail/dovecot/attachments/20211204/8f314886/attachment-0001.bin>
On 2021-12-04 22.18, I wrote:> Hi there, > > I've updated Dovecot from v2.3.14 (cee3cbc0d) to v2.3.17 (e2aa53df5b). > Right after the update, dovecot begun flooding the mail.log and > PostgreSQL's log. > ...just in case someone has missed `doveconf -n` output and SQL related configuration files, they are attached now. Let me know if you have any questions. Regards, Pascal -- The trapper recommends today: face1e55.2135221 at localdomain.org -------------- next part -------------- # 2.3.17 (e2aa53df5b): /usr/local/etc/dovecot/dovecot.conf # Pigeonhole version 0.5.17 (054dddfa) # OS: Linux 5.15.0-2-amd64 x86_64 Debian bookworm/sid # Hostname: host.example.org auth_mechanisms = plain login cram-md5 auth_verbose = yes auth_verbose_passwords = plain base_dir = /var/run/dovecot debug_log_path = /var/log/dovecot_dbg.log dict { quota = pgsql:/usr/local/etc/dovecot/dovecot-dict-sql.conf.ext } first_valid_gid = 70000 first_valid_uid = 70000 hostname = mail.example.org import_environment = TZ CORE_OUTOFMEM=yeah CORE_ERROR=1 listen = 203.0.113.143, 127.0.0.1, ::1, 2001:db8::143 lmtp_save_to_detail_mailbox = yes login_access_sockets = tcpwrap mail_access_groups = dovemail mail_location = mdbox:~/mdbox mail_plugins = quota old_stats zlib 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 Trash { special_use = \Trash } mailbox name { special_use = \Drafts \Junk \Sent \Trash } prefix = separator = / } passdb { args = /usr/local/etc/dovecot/dovecot-sql.conf.ext driver = sql } plugin { old_stats_refresh = 1min quota = dict:user:%{uid}:noenforcing:proxy::quota quota_rule = *:storage=2G:messages=0 quota_rule2 = Trash:storage=+100M recipient_delimiter = + sieve = ~/.dovecot.sieve sieve_dir = ~/sieve zlib_save = bz2 zlib_save_level = 6 } postmaster_address = postmaster at example.org protocols = lmtp pop3 imap sieve service auth-worker { unix_listener auth-worker { group = $default_internal_user mode = 0660 user = doveauth } user = doveauth } service auth { client_limit = 1425 unix_listener /var/spool/postfix/private/dovecot-auth { group = postfix mode = 0600 user = postfix } user = doveauth } service dict { idle_kill = 150 secs unix_listener dict { group = dovemail mode = 0660 } } service imap-login { process_limit = 256 } service imap { process_limit = 256 } service lmtp { executable = lmtp -D inet_listener lmtp { address = ::1 127.0.0.1 port = 24 } unix_listener /var/spool/postfix/private/dovecot-lmtp { group = postfix mode = 0600 user = postfix } } service old-stats { executable = old-stats fifo_listener old-stats-mail { mode = 0666 } fifo_listener old-stats-user { mode = 0666 } unix_listener old-stats { mode = 0666 } } service tcpwrap { unix_listener login/tcpwrap { group = $default_login_user mode = 0600 user = $default_login_user } } ssl = required ssl_cert = </etc/ssl/certs/mail.example.org.crt ssl_dh = # hidden, use -P to show it ssl_key = # hidden, use -P to show it ssl_prefer_server_ciphers = yes state_dir = /var/lib/dovecot userdb { args = /usr/local/etc/dovecot/dovecot-sql.conf.ext driver = sql } verbose_proctitle = yes protocol lda { mail_plugins = quota old_stats zlib sieve } protocol imap { imap_client_workarounds = delay-newmail tb-extra-mailbox-sep tb-lsub-flags imap_id_log = * imap_id_send = name mail_plugins = quota old_stats zlib imap_quota imap_old_stats imap_zlib } protocol lmtp { mail_plugins = quota old_stats zlib sieve } -------------- next part -------------- A non-text attachment was scrubbed... Name: dovecot-dict-sql.conf.ext Type: application/vnd.novadigm.ext Size: 288 bytes Desc: not available URL: <https://dovecot.org/pipermail/dovecot/attachments/20211218/cbf6b0ca/attachment.bin> -------------- next part -------------- A non-text attachment was scrubbed... Name: dovecot-sql.conf.ext Type: application/vnd.novadigm.ext Size: 465 bytes Desc: not available URL: <https://dovecot.org/pipermail/dovecot/attachments/20211218/cbf6b0ca/attachment-0001.bin>
On 18/12/2021 20.56, Pascal Volk wrote:> On 2021-12-04 22.18, I wrote: >> Hi there, >> >> I've updated Dovecot from v2.3.14 (cee3cbc0d) to v2.3.17 (e2aa53df5b). >> Right after the update, dovecot begun flooding the mail.log and >> PostgreSQL's log. >> ...Hi there, I've upgraded Dovecot to v2.3.18 (9dd8408c18). The problem still exists: ERROR: invalid input syntax for type bigint: "user at localhost.localdomain.org" An excerpt from the journal is attached. Regards, Pascal -- The trapper recommends today: beeffeed.2203619 at localdomain.org -------------- next part -------------- host conf.d # journalctl -f -u dovecot.service Feb 05 19:38:48 host systemd[1]: Started Dovecot IMAP/POP3 email server. Feb 05 19:38:54 host dovecot[635]: master: Dovecot v2.3.18 (9dd8408c18) starting up for lmtp, pop3, imap, sieve Feb 05 19:42:00 host systemd[1]: Reloading Dovecot IMAP/POP3 email server... Feb 05 19:42:00 host dovecot[635]: master: Warning: SIGHUP received - reloading configuration Feb 05 19:42:00 host systemd[1]: Reloaded Dovecot IMAP/POP3 email server. Feb 05 19:42:28 host dovecot[1137]: imap-login: Login: user=<user at localhost.localdomain.org>, method=PLAIN, rip=2001:db8:815:1337:1234:5678:9abc, lip=2001:db8::143, mpid=1150, TLS, session=<x> Feb 05 19:42:28 host dovecot[1137]: imap(user at localhost.localdomain.org)<1150><x>: ID sent: name=Thunderbird, version=91.5.1 Feb 05 19:42:29 host dovecot[1137]: dict(1152): Warning: quota: dict(pgsql): sqlpool(pgsql): Query failed, retrying: ERROR: invalid input syntax for type bigint: "user at localhost.localdomain.org" Feb 05 19:42:29 host dovecot[1137]: dict: Error: LINE 1: SELECT bytes FROM userquota WHERE uid = 'user at localhost.loca... Feb 05 19:42:29 host dovecot[1137]: dict: Error: ^ Feb 05 19:42:29 host dovecot[1137]: dict(1152): Error: quota: dict(pgsql): sqlpool(pgsql): Query failed, aborting: SELECT bytes FROM userquota WHERE uid = 'user at localhost.localdomain.org' Feb 05 19:42:29 host dovecot[1137]: dict(1152): Error: quota: Lookup failed: ERROR: invalid input syntax for type bigint: "user at localhost.localdomain.org" Feb 05 19:42:29 host dovecot[1137]: dict: Error: LINE 1: SELECT bytes FROM userquota WHERE uid = 'user at localhost.loca... Feb 05 19:42:29 host dovecot[1137]: dict: Error: ^ Feb 05 19:42:29 host dovecot[1137]: imap(user at localhost.localdomain.org)<1150><x>: Error: Failed to get quota resource STORAGE: quota-dict: dict_lookup(priv/quota/storage) failed: dict-server returned failure: ERROR: invalid input syntax for type bigint: "user at localhost.localdomain.org" Feb 05 19:42:29 host dovecot[1137]: imap: Error: LINE 1: SELECT bytes FROM userquota WHERE uid = 'user at localhost.loca... Feb 05 19:42:29 host dovecot[1137]: imap: Error: ^ (reply took 0.099 secs (0.100 in dict wait, 0.000 in other ioloops, 0.000 in locks, async-id reply 0.010 secs ago, started on dict-server 0.029 secs ago, took 0.029 secs))