Quota count and clone questions

[Bratislav ILIC]

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