sql delete problem

[Timo Sirainen]

On 24 Jan 2016, at 19:40, Tóth Csaba <tsabi at 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 at 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 at mail ERROR:  permission
> denied for relation i3sysadm_email_account
> 2016-01-24 16:34:43 CET [26105-2] maildata at mail STATEMENT:  DELETE FROM
> i3sysadm_email_account WHERE userid = 'account at mydomain.com'
> 
> This is the error message from dovecot side (i have a lot, so this is
> from another date):
> 
> root at 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 at 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.