On 24 Jan 2016, at 19:40, Tóth Csaba tsabi@tsabi.hu wrote:
Dear List!
I have a dovecot installment with postgresql based user management. My problem is i see this in postgresql's log file:
root@c13:/var/lib/postgresql/9.4/main/pg_log# cat postgresql-2016-01-24_12.log | grep 26105 2016-01-24 16:34:43 CET [26105-1] maildata@mail ERROR: permission denied for relation i3sysadm_email_account 2016-01-24 16:34:43 CET [26105-2] maildata@mail STATEMENT: DELETE FROM i3sysadm_email_account WHERE userid = 'account@mydomain.com'
This is the error message from dovecot side (i have a lot, so this is from another date):
root@c10:/var/log/dovecot# cat mail.log | grep "Jan 24 17:41:29" Jan 24 17:41:29 dict: Info: pgsql(127.0.0.1): Disconnected from database, retrying commit Jan 24 17:41:29 dict: Error: sql dict: commit failed: ERROR: permission denied for relation i3sysadm_email_account (query: DELETE FROM i3sysadm_email_account WHERE userid = 'account@mydomain.com')
Why the hack dovecot want to delete my e-mail account?
You've configured dict-sql quota, which deletes the quota row when it wants to recalculate quota. With Dovecot v2.2.20+ you could work around this by setting a new "no-unset" parameter (http://wiki2.dovecot.org/Quota/Dict), except this can't work with the PostgreSQL merge_quota() function. Although nowadays PostgreSQL supports UPSERT, which could be used to replace the merge_quota() function. That would require some new Dovecot code though.
In short: With the current code you can't store the current quota usage to a generic users table. Dovecot must be able to delete rows when it wants to recalculate quota.
Yet another way to handle this would be to not use dict-sql for quota, but e.g. dict-file. Then you can use http://wiki2.dovecot.org/Plugins/QuotaClone plugin to keep the SQL database updated. I'd actually recommend doing this in any case over using dict-sql for quota. This would still require you to upgrade to v2.2.17 or newer.