Hi,
I presume you figured this out after all this time, but in any case.
Since quota_clone is not cumulative, it always inserts new record with actual
quota,
to be able to get correct results in postfixadmin one must:
DROP TRIGGER mergequota2 ON quota2;
And to prevent ERROR: duplicate key value violates unique constraint
"quota2_pkey":
CREATE FUNCTION public.clone_quota2() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE quota2 SET bytes = NEW.bytes,
messages = NEW.messages
WHERE username = NEW.username;
IF found THEN
RETURN NULL;
ELSE
RETURN NEW;
END IF;
END;
$$;
And then:
CREATE TRIGGER clonequota2 BEFORE INSERT ON quota2 FOR EACH ROW EXECUTE
PROCEDURE public.clone_quota2() ;
And finally just put:
quota_clone_dict = proxy::sqlquota
Hope this helps somebody trying to figure out this too.
> I figured out that I can't just drop maintaining quota2 if I want
> postfixadmin to report the quota status.
>
> I also figured out a way to run a test on my config guesses. I will try
> to fit it in today, or tomorrow. But any advise to the questions below
> are welcomed!
>
>>On 2/13/19 8:53 PM, Robert Moskowitz via dovecot wrote:
>> all this almost reads like I can drop maintaining the quota2 table?
>>
>> From https://wiki.dovecot.org/Quota/Count
>>
>> mailbox_list_index = yes
>> # Avoid spending excessive time waiting for the quota calculation to
>> finish when
>> # mails' vsizes aren't already cached. If this many mails are opened,
>> finish the
>> # quota calculation on background in indexer-worker process. Mail
>> deliveries will
>> # be assumed to succeed, and explicit quota lookups will return
>> internal error.
>> mail_vsize_bg_after_count = 100
>>
>> seems to belong in 10-mail.conf. That is where those var are shown.
>>
>> But:
>>
>>
>> plugin {
>> # 10MB quota limit
>> quota = count:User quota
>> quota_rule = *:storage=10M
>>
>> # This is required - it uses "virtual sizes" rather than "physical
>> sizes" for quota counting:
>> quota_vsizes = yes
>> }
>>
>> I am having problems with. Right now for quota I have:
>>
>> plugin {
>> quota = dict:user::proxy::sqlquota
>> trash = /etc/dovecot/dovecot-trash.conf.ext
>> }
>>
>> How do I reconcile these two?
>>
>> Then for clone: https://wiki.dovecot.org/Plugins/QuotaClone
>>
>> how does:
>>
>> mail_plugins = $mail_plugins quota quota_clone
>> plugin {
>> quota_clone_dict = redis:host=127.0.0.1:port=6379
>> }
>>
>> get replaced with something for mysql?
>>
>> dovecot-sql.conf.ext:
>>
>> driver = mysql
>> connect = host=/var/lib/mysql/mysql.sock dbname=postfix user=postfix
>> password=$Postfix_Database_Password
>> default_pass_scheme = $cryptsha-CRYPT
>> # following should all be on one line.
>> password_query = SELECT username as user, password,
>> concat('/home/vmail/', maildir) as userdb_home,
>> concat('maildir:/home/vmail/', maildir) as userdb_mail, 101 as
>> userdb_uid, 12 as userdb_gid FROM mailbox WHERE username = '%u' AND
>> active = '1'
>> # following should all be on one line
>> user_query = SELECT concat('/home/vmail/', maildir) as home,
>> concat('maildir:/home/vmail/', maildir) as mail, 101 AS uid, 12 AS
>> gid, CONCAT('*:messages=30000:bytes=', quota) as quota_rule FROM
>> mailbox WHERE username = '%u' AND active = '1'
>>
>> and
>>
>> dovecot-dict-sql.conf.ext:
>>
>> connect = host=/var/lib/mysql/mysql.sock dbname=postfix user=postfix
>> password=$Postfix_Database_Password
>> 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
>> }
>>
>>
>>
>> thanks
