quotas configuration a few questions

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 =

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@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@yourdomain.com Subject: Email Account Quota Warning

Dear User,

$MSG

Best regards, Your Mail System EOF