Keywords: dovecot, dict, quota, postgre sql, broken pipe, remote disconnected
Having Dovecot 2.2.22 (fe789d2) with Postgre SQL 9.5 (9.5.5-0ubuntu0.16.04) as the backend. I do not understand why quota service is not working, not seeing it as a configuration error at least. My quotas are DICT/SQL based. OS: Ubuntu 16.0.4.1 32-bit (Linux XXX 4.4.0-59-generic #80-Ubuntu SMP Fri Jan 6 17:36:54 UTC 2017 i686 i686 i686 GNU/Linux)
dovecot --build-options:
Build options: ioloop=epoll notify=inotify ipv6 openssl io_block_size=8192 Mail storages: shared mdbox sdbox maildir mbox cydir imapc pop3c raw fail SQL driver plugins: mysql postgresql sqlite Passdb: checkpassword ldap pam passwd passwd-file shadow sql Userdb: checkpassword ldap(plugin) nss passwd prefetch passwd-file sql
/etc/dovecot/conf.d/10-master.conf:
service quota-warning { executable = script /etc/dovecot/some-script.sh unix_listener quota-warning { user = Debian-exim mode = 0660 } } service dict { unix_listener dict { mode = 0660 user = Debian-exim group = Debian-exim } }
/etc/dovecot/conf.d/90-quota.conf:
plugin { quota = dict:user_quota::proxy::sqlquota quota_rule2 = Trash:storage=+10%% quota_rule3 = Junk:storage=+10%% quota_grace = 10%% quota_warning = storage=100%% quota-exceeded 100 %u quota_warning2 = storage=95%% quota-warning 95 %u quota_warning3 = storage=90%% quota-warning 90 %u quota_warning4 = storage=75%% quota-warning 75 %u } dict { sqlquota = pgsql:/etc/dovecot/dovecot-dict-sql-user.conf }
/etc/dovecot/dovecot-dict-sql-user.conf:
connect = host=A.B.C.D dbname=db user=DDD password=YYY 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 }
I will not be able to provide full "doveconf -n" output unfortunately.
Logging ALL incoming DB queries:
... 2017-02-04 12:03:12 MST [29500-10] DDD@db LOG: statement: SELECT password FROM mailbox WHERE local_part = 'YYY' AND domain = 'XXX' AND active ='t' LIMIT 1; 2017-02-04 12:03:12 MST [29501-10] DDD@db LOG: statement: SELECT 111 AS uid, 222 AS gid, '/var/mail/AAA/' || 'BBB' || '/' || 'YYY' AS home, '*:bytes=' || mailbox.quota AS quota_rule FROM mailbox WHERE local_part = 'YYY' AND active ='t' LIMIT 1; <END OF FILE HERE>
And seeing that BOTH dict statements are missing: SELECT and UPDATE.
dovecot-lda-erros.log:
Feb 04 14:23:33 lda(testuser@XXX): Error: read(/var/run/dovecot/dict) failed: Remote disconnected Feb 04 14:23:33 lda(testuser@XXX): Error: Internal quota calculation error Feb 04 14:23:33 lda(testuser@XXX): Error: Internal quota calculation error
dovecot.log:
Feb 04 13:57:06 imap(YYY@XXX): Error: write(/var/run/dovecot/dict) failed: Broken pipe Feb 04 13:57:06 imap(YYY@XXX): Error: write(/var/run/dovecot/dict) failed: Broken pipe ... Feb 04 13:57:07 imap(YYY@XXX): Error: write(/var/run/dovecot/dict) failed: Broken pipe ... Feb 04 13:57:10 imap(YYY@XXX): Error: write(/var/run/dovecot/dict) failed: Broken pipe
I have tried stopping dovecot, removing /var/run/dovecot/dict manually and starting dovecot. This has not changed the behavior.
dovecot-debug.log:
Feb 04 13:18:12 lda(YYY@XXX): Error: read(/var/run/dovecot/dict) failed: Remote disconnected Feb 04 13:18:12 lda(YYY@XXX): Error: dict quota: Quota update failed, it's now desynced Feb 04 13:57:07 lda(testuser@XXX): Error: write(/var/run/dovecot/dict) failed: Broken pipe Feb 04 13:57:07 lda(testuser@XXX): Error: Internal quota calculation error Feb 04 13:57:07 lda(testuser@XXX): Error: Internal quota calculation error
Running 'user_query' manually on the server:
db=> SELECT 111 AS uid, 222 AS gid, '/var/mail/AAA/' || 'XXX' || '/' ||
'testuser' AS home, '*:bytes=' || mailbox.quota AS quota_rule
FROM mailbox WHERE local_part = 'testuser' AND active ='t' LIMIT 1;
uid | gid | home | quota_rule
-----+-----+-----------------------------------+------------------
111 | 222 | /var/mail/AAA/BBB/testuser | *:bytes=10485760
(1 row)
The identity had been granted 'all' privilege for 'quota2' table:
db=> SELECT table_catalog, table_schema, table_name, privilege_type db-> FROM information_schema.table_privileges db-> WHERE grantee='DDD'; table_catalog | table_schema | table_name | privilege_type ---------------+--------------+------------+---------------- mail | public | quota2 | INSERT mail | public | quota2 | SELECT mail | public | quota2 | UPDATE mail | public | quota2 | DELETE mail | public | quota2 | TRUNCATE mail | public | quota2 | REFERENCES mail | public | quota2 | TRIGGER mail | public | mailbox | SELECT mail | public | quota | INSERT mail | public | quota | SELECT mail | public | quota | UPDATE mail | public | quota | DELETE mail | public | quota | TRUNCATE mail | public | quota | REFERENCES mail | public | quota | TRIGGER (15 rows)
Rows are obviously not getting inserted:
db=> select * from quota2; username | bytes | messages ----------+-------+---------- (0 rows)
I have checked logs for any crash-related messages, there's none.
Also, I have tried to deploy 2.27-release version on the server to see whether the behavior is gone. I set-up a testing environment which consists of ubuntu-16.0.4-i386 server, installed build-essential package and unpacked the dovecot-2.2.27.tar.gz there. Getting exactly same issue as Ricardo Machini was having in his post to "v2.2.27 released" thread on Sat Dec 3 21:20:05 UTC 2016:
checking that generated files are newer than configure... done configure: error: conditional "SSL_VERSION_GE_102" was never defined. Usually this means the macro was only invoked conditionally.
All I ran was ./configure 'openssl' package is installed as well.