quotas configuration a few questions

[niya levi]

hi everyone
need a few questions answering if possible before i can complete some
modifications on my mailserver

1    how do i modify my user_query line in dovecot-sql.conf for quotas
2     is my iterate_query line ok
3    should i use lmtp or lda to deliver the quota warnings in
quota-warning.sh
    my server is using lmtp but i've only seen examples using lda
4    how do i break long lines in dovecot.conf, is it the same as
postgresql
    where you leave a space on the next line before continuing the txt.
    i would like to break the cipher line in dovecot.conf over multiple
lines
5    have i configured my namespace section correctly
    so that i can substitute for the autocreate plugin

also could you give the files a quick look over to make sure
there isn't  any unnecessary additions that shouldn't be there or    
omissions that should be there

thanks shadrock


    ============    /etc/dovecot/dovecot.conf

disable_plaintext_auth = no
mail_privileged_group = vmail

log_timestamp = "%Y-%m-%d %H:%M:%S "
log_path = /var/log/dovecot.log
auth_debug_passwords=yes
mail_debug=yes
verbose_ssl=yes

ssl_prefer_server_ciphers = yes
ssl_dh_parameters_length = 2048
ssl_cert = </etc/ssl/certs/postfix.crt
ssl_key = </etc/ssl/private/postfix.key
ssl_ca = </etc/ssl/ca.crt
ssl_protocols = !SSLv2 !SSLv3
ssl_cipher_list =
ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES256-GCM-SHA384:DHE-RSA-AES128-GCM-SHA256:DHE-DSS-AES128-GCM-SHA256:kEDH+AESGCM:ECDHE-RSA-AES128-SHA256:ECDHE-ECDSA-AES128-SHA256:ECDHE-RSA-AES128-SHA:ECDHE-ECDSA-AES128-SHA:ECDHE-RSA-AES256-SHA384:ECDHE-ECDSA-AES256-SHA384:ECDHE-RSA-AES256-SHA:ECDHE-ECDSA-AES256-SHA:DHE-RSA-AES128-SHA256:DHE-RSA-AES128-SHA:DHE-DSS-AES128-SHA256:DHE-RSA-AES256-SHA256:DHE-DSS-AES256-SHA:DHE-RSA-AES256-SHA:AES128-GCM-SHA256:AES256-GCM-SHA384:AES128-SHA256:AES256-SHA256:AES128-SHA:AES256-SHA:AES:CAMELLIA:DES-CBC3-SHA:!aNULL:!eNULL:!EXPORT:!DES:!RC4:!MD5:!PSK:!aECDH:

auth_mechanisms = plain login

passdb {
  args = /etc/dovecot/dovecot-sql.conf
  driver = sql
}
userdb {
  driver = prefetch
}
userdb {
  args = /etc/dovecot/dovecot-sql.conf
  driver = sql
}

#namespace inbox {
# separator = /
#  type = private
#  inbox = yes
# location = maildir:/home/mailboxes/Maildir
# list = yes
# prefix = INBOX.

#mailbox Drafts {
#  auto = subscribe
#  special_use = \Drafts
#}
#mailbox Junk {
#  auto = subscribe
#  special_use = \Junk
#}
#mailbox Sent {
#  auto = subscribe
#  special_use = \Sent
#}
#mailbox Trash {
#  auto = subscribe
#  special_use = \Trash
#}
#}

mail_plugins = quota

# protocols = "pop3 imap lmtp"
protocols = pop3 imap lmtp sieve

plugin {
  autocreate = Trash
  autocreate2 = Sent
  autosubscribe = Trash
  autosubscribe2 = Sent
    sieve = ~/.dovecot.sieve
    sieve_global_path = /etc/dovecot/sieve/default.sieve
    sieve_dir = ~/sieve
    sieve_global_dir = /etc/dovecot/sieve/global/
       quota = dict:User quota::proxy::quotadict
       quota_rule2 = Trash:storage=+10%%
       quota_warning = storage=100%% quota-warning +100 %u
       quota_warning2 = storage=95%% quota-warning +95 %u
       quota_warning3 = storage=80%% quota-warning +80 %u
       quota_warning4 = -storage=100%% quota-warning -100 %u # user is
no longer over quota
}

service auth {
  unix_listener /var/spool/postfix/private/auth {
    group = postfix
    mode = 0660
    user = postfix
  }
}

service lmtp {
    unix_listener /var/spool/postfix/private/dovecot-lmtp {
    group = postfix
    mode = 0600
    user = postfix
    }
}

service managesieve-login {
  inet_listener sieve {
    port = 4190
  }
}

service managesieve {
}

service dict {
        unix_listener dict {
                group = vmail
                mode = 0660
                user = vmail
        }
        user = root
}

service quota-warning {
        executable = script /usr/local/bin/quota-warning.sh
        user = vmail
        unix_listener quota-warning {
                group = vmail
                mode = 0660
                user = vmail
        }
}

protocol imap {
mail_plugins = $mail_plugins autocreate imap_quota
}

protocol sieve {
    managesieve_max_line_length = 65536
    managesieve_implementation_string = dovecot
    log_path = /var/log/dovecot-sieve-errors.log
    info_log_path = /var/log/dovecot-sieve.log
}

protocol lmtp {
    postmaster_address=postmaster at thisdomain.co.uk
    hostname=testy.thisdomain.co.uk
    mail_plugins = $mail_plugins sieve quota
    info_log_path = /var/log/dovecot-lmtp.log
}

dict {
    quotadict = pgsql:/etc/dovecot/dovecot-dict-sql.conf
}

protocol pop3 {
     mail_plugins = quota
     pop3_client_workarounds = outlook-no-nuls oe-ns-eoh
     pop3_uidl_format = %08Xu%08Xv
}

--(postgresql stuff)--

CREATE DATABASE mail WITH OWNER mailreader;
CREATE TABLE aliases (
    alias text NOT NULL,
    email text NOT NULL
);

CREATE TABLE users (
    email text NOT NULL,
    password text NOT NULL,
    maildir text NOT NULL,
    created timestamp with time zone DEFAULT now()
);

CREATE TABLE vdomain (
    domain text NOT NULL
);

CREATE TABLE quota (
  username varchar(100) not null,
  bytes bigint not null default 0,
  messages integer not null default 0,
  primary key (username)
);

CREATE OR REPLACE FUNCTION merge_quota() RETURNS TRIGGER AS $$
BEGIN
  IF NEW.messages < 0 OR NEW.messages IS NULL THEN
    -- ugly kludge: we came here from this function, really do try to insert
    IF NEW.messages IS NULL THEN
      NEW.messages = 0;
    ELSE
      NEW.messages = -NEW.messages;
    END IF;
    return NEW;
  END IF;

  LOOP
    UPDATE quota SET bytes = bytes + NEW.bytes,
      messages = messages + NEW.messages
      WHERE username = NEW.username;
    IF found THEN
      RETURN NULL;
    END IF;

    BEGIN
      IF NEW.messages = 0 THEN
        INSERT INTO quota (bytes, messages, username)
          VALUES (NEW.bytes, NULL, NEW.username);
      ELSE
        INSERT INTO quota (bytes, messages, username)
          VALUES (NEW.bytes, -NEW.messages, NEW.username);
      END IF;
      return NULL;
    EXCEPTION WHEN unique_violation THEN
      -- someone just inserted the record, update it
    END;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS mergequota ON quota;
CREATE TRIGGER mergequota BEFORE INSERT ON quota
   FOR EACH ROW EXECUTE PROCEDURE merge_quota();
   
--(postgresql stuff)--

    ============    /etc/dovecot/dovecot-dict-sql.conf

connect = host=/run/postgresql dbname=mail user=mailreader
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
}

    ============    /etc/dovecot/dovecot-sql.conf

driver = pgsql
connect = host=/run/postgresql dbname=mail user=mailreader
default_pass_scheme = SHA512
password_query = SELECT email as user, password FROM users WHERE email =
'%u'
user_query = SELECT email as user,
'maildir:/home/mailboxes/maildir/'||maildir as mail,
'/home/mailboxes/maildir/'||maildir as home, 500 as uid, 500 as gid FROM
users WHERE email = '%u'
iterate_query = SELECT email AS user FROM users


    ============    /usr/local/bin/quota-warning.sh

#!/bin/sh
 BOUNDARY="$1"
 USER="$2"
 MSG=""
 if [[ "$BOUNDARY" = "+100" ]]; then
    MSG="Your mailbox is now overfull (>100%). In order for your account
to continue functioning properly, you need to remove some emails NOW."
 elif [[ "$BOUNDARY" = "+95" ]]; then
    MSG="Your mailbox is now over 95% full. Please remove some emails ASAP."
 elif [[ "$BOUNDARY" = "+80" ]]; then
    MSG="Your mailbox is now over 80% full. Please consider removing
some emails to save space."
 elif [[ "$BOUNDARY" = "-100" ]]; then
    MSG="Your mailbox is now back to normal (<100%)."
 fi

cat << EOF | /usr/lib/dovecot/lmtp -d $USER -o
"plugin/quota=maildir:User quota:noenforcing"
 From: postmaster at yourdomain.com
 Subject: Email Account Quota Warning

 Dear User,

 $MSG

 Best regards,
 Your Mail System
EOF