Commits

Zhang Huangbin committed f2f31b2

Fixed: Incorrect postrotate command for dovecot log files. Thanks raffayellow@gmail for the report.
Code cleanup.

Comments (0)

Files changed (39)

iRedMail/ChangeLog

     * It now works on Linux Mint 12 (code name: lisa).
     * Add share folder support within Dovecot 2.
     * Fixed:
+        + Incorrect postrotate command for dovecot log files.
+          Thanks raffayellow@gmail for the report.
         + Two Dovecot config files are world readable: used_quota.conf,
           share-folder.conf.
           Thanks Martin Kuchar for the report.

iRedMail/conf/amavisd

 export AMAVISD_DB_NAME='amavisd'
 export AMAVISD_DB_USER='amavisd'
 export AMAVISD_DB_PASSWD="$(${RANDOM_STRING})"
-export AMAVISD_DB_MYSQL_TMPL="${SAMPLE_DIR}/amavisd.mysql"
-export AMAVISD_DB_PGSQL_TMPL="${SAMPLE_DIR}/amavisd.pgsql"
 export MYSQL_BACKUP_DATABASES="${MYSQL_BACKUP_DATABASES} ${AMAVISD_DB_NAME}"
+export AMAVISD_VERSION='2.6'
 
 # Altermime.
 export ALTERMIME_BIN='/usr/bin/altermime'
     export AMAVISD_TEMPDIR="${AMAVISD_MYHOME}/tmp"
 
 elif [ X"${DISTRO}" == X"SUSE" ]; then
+    # openSUSE-12.1 ships Amavisd-new-2.7.0
+    export AMAVISD_VERSION='2.7'
+
     # Override global setting: user/group.
     export AMAVISD_SYS_USER='vscan'
     export AMAVISD_SYS_GROUP='vscan'
     export AMAVISD_LDAP_SCHEMA_NAME='amavisd-new.schema'
     export AMAVISD_VIRUSMAILS_DIR='/var/spool/amavis/virusmails'
 
-    # Override default Amavisd MySQL template file
-    # openSUSE-12.1 ships Amavisd-new-2.7.0
-    export AMAVISD_DB_MYSQL_TMPL="${SAMPLE_DIR}/amavisd-2.7.0.mysql"
-    export AMAVISD_DB_PGSQL_TMPL="${SAMPLE_DIR}/amavisd-2.7.0.pgsql"
-
 elif [ X"${DISTRO}" == X"DEBIAN" -o X"${DISTRO}" == X"UBUNTU" ]; then
     export AMAVISD_BIN='/usr/sbin/amavisd-new'
     export AMAVISD_CONF_DIR='/etc/amavis/conf.d'
     export AMAVISD_VIRUSMAILS_DIR='/var/lib/amavis/virusmails'
 
 elif [ X"${DISTRO}" == X"GENTOO" ]; then
+    export AMAVISD_VERSION='2.7'
+
     export AMAVISD_BIN='/usr/sbin/amavisd'
     export AMAVISD_LDAP_SCHEMA_NAME='amavisd-new.schema'
 
     export AMAVISD_QUARANTINEDIR="${AMAVISD_MYHOME}/quarantine"
     export AMAVISD_TEMPDIR="${AMAVISD_MYHOME}/tmp"
 
-    export AMAVISD_DB_MYSQL_TMPL="${SAMPLE_DIR}/amavisd-2.7.0.mysql"
-    export AMAVISD_DB_PGSQL_TMPL="${SAMPLE_DIR}/amavisd-2.7.0.pgsql"
 elif [ X"${DISTRO}" == X"FREEBSD" ]; then
+    export AMAVISD_VERSION='2.7'
+
     # Override global setting: user/group.
     export AMAVISD_SYS_USER='vscan'
     export AMAVISD_SYS_GROUP='vscan'
 
     # Override location of command 'altermime'
     export ALTERMIME_BIN='/usr/local/bin/altermime'
+fi
 
-    export AMAVISD_DB_MYSQL_TMPL="${SAMPLE_DIR}/amavisd-2.7.0.mysql"
-    export AMAVISD_DB_PGSQL_TMPL="${SAMPLE_DIR}/amavisd-2.7.0.pgsql"
+if [ X"${AMAVISD_VERSION}" == X'2.7' ]; then
+    export AMAVISD_DB_MYSQL_TMPL="${SAMPLE_DIR}/amavisd/amavisd-2.7.0.mysql"
+    export AMAVISD_DB_PGSQL_TMPL="${SAMPLE_DIR}/amavisd/amavisd-2.7.0.pgsql"
+else
+    export AMAVISD_DB_MYSQL_TMPL="${SAMPLE_DIR}/amavisd/amavisd.mysql"
+    export AMAVISD_DB_PGSQL_TMPL="${SAMPLE_DIR}/amavisd/amavisd.pgsql"
 fi
 
 # Syslog setting, 'YES' or 'NO'

iRedMail/conf/cluebringer

     export PKG_CLUEBRINGER='policyd'
     export CLUEBRINGER_CONF='/etc/cluebringer/cluebringer.conf'
     export CLUEBRINGER_WEBUI_CONF="/etc/cluebringer/cluebringer-webui.conf"
-    export CLUEBRINGER_PID_FILE='var/run/cluebringer/cbpolicyd.pid'
+    export CLUEBRINGER_PID_FILE='/var/run/cluebringer/cbpolicyd.pid'
     export CLUEBRINGER_HTTPD_ROOT=''
 
     # Path of policyd-clean
     export CLUEBRINGER_CONF='/etc/cluebringer/cluebringer.conf'
     export CLUEBRINGER_WEBUI_CONF="/etc/cluebringer/cluebringer-webui.conf"
     export CLUEBRINGER_RC_SCRIPT_NAME='postfix-cluebringer'
-    export CLUEBRINGER_PID_FILE='var/run/cluebringer/cbpolicyd.pid'
+    export CLUEBRINGER_PID_FILE='/var/run/cluebringer/cbpolicyd.pid'
     export CLUEBRINGER_HTTPD_ROOT='/usr/share/postfix-cluebringer-webui/webui'
 
     # Path of policyd-clean

iRedMail/conf/dovecot

 export DOVECOT_LOGROTATE_FILE="${LOGROTATE_DIR}/dovecot"
 export SIEVE_LOGROTATE_FILE="${LOGROTATE_DIR}/sieve"
 
-# Dovecot auth socket.
+# Dovecot auth socket and pid file.
 export DOVECOT_SOCKET_MUX='/var/spool/postfix/dovecot-auth'
-export DOVECOT_AUTH_SOCKET_PATH='/var/run/dovecot/auth-master'
+export DOVECOT_AUTH_SOCKET_PATH="${DOVECOT_BASE_DIR}/auth-master"
+export DOVECOT_MASTER_PID="${DOVECOT_BASE_DIR}/master.pid"
 
 export DOVECOT_BIN='/usr/sbin/dovecot'
 export DOVECOT_SIEVEC='/usr/libexec/dovecot/sievec'

iRedMail/conf/global

     # -q: Turn off Wget's output.
     FETCH_CMD="wget -cq"
 fi
-

iRedMail/dialog/config_via_dialog.sh

 echo "export RCM_DB_PASSWD='${RCM_DB_PASSWD}'" >> ${CONFIG_FILE}
 
 if [ X"${BACKEND}" == X"OPENLDAP" ]; then
-    export SQL_SERVER="${MYSQL_SERVER}"
-    export SQL_SERVER_PORT="${MYSQL_SERVER_PORT}"
-
     . ${DIALOG_DIR}/ldap_config.sh
 
     # MySQL server is used to store policyd/roundcube data.
     . ${DIALOG_DIR}/mysql_config.sh
 elif [ X"${BACKEND}" == X"MYSQL" ]; then
+    . ${DIALOG_DIR}/mysql_config.sh
+elif [ X"${BACKEND}" == X"PGSQL" ]; then
+    . ${DIALOG_DIR}/pgsql_config.sh
+fi
+
+if [ X"${BACKEND}" == X"OPENLDAP" -o X"${BACKEND}" == X'MYSQL' ]; then
     export SQL_SERVER="${MYSQL_SERVER}"
     export SQL_SERVER_PORT="${MYSQL_SERVER_PORT}"
-
-    . ${DIALOG_DIR}/mysql_config.sh
-elif [ X"${BACKEND}" == X"PGSQL" ]; then
+elif [ X"${BACKEND}" == X'PGSQL' ]; then
     export SQL_SERVER="${PGSQL_SERVER}"
     export SQL_SERVER_PORT="${PGSQL_SERVER_PORT}"
-
-    . ${DIALOG_DIR}/pgsql_config.sh
 fi
 
 echo "export SQL_SERVER='${SQL_SERVER}'" >> ${CONFIG_FILE}

iRedMail/functions/awstats.sh

     elif [ X"${BACKEND}" == X"PGSQL" ]; then
         # Use PGSQL auth.
         cat >> ${AWSTATS_HTTPD_CONF} <<EOF
+    AuthType Basic
     Auth_PG_authoritative on
     Auth_PG_host ${PGSQL_SERVER}
     Auth_PG_port ${PGSQL_SERVER_PORT}

iRedMail/functions/cluebringer.sh

     perl -pi -e 's/^(Password=).*/${1}$ENV{CLUEBRINGER_DB_PASSWD}/' ${CLUEBRINGER_CONF}
 
     # Get SQL structure template file.
-    tmp_sql="/tmp/policyd_config_tmp.${RANDOM}${RANDOM}"
+    tmp_sql="/tmp/cluebringer_init_sql.${RANDOM}${RANDOM}"
     if [ X"${DISTRO}" == X"RHEL" -o X"${DISTRO}" == X"SUSE" ]; then
         if [ X"${BACKEND}" == X"OPENLDAP" -o X"${BACKEND}" == X"MYSQL" ]; then
             cat > ${tmp_sql} <<EOF
 EOF
     fi
 
-    rm -f ${tmp_sql} 2>/dev/null
+    #rm -f ${tmp_sql} 2>/dev/null
     unset tmp_sql
 
     # Set correct permission.
 
     # Configure webui.
     if [ X"${DISTRO}" == X'UBUNTU' ]; then
-        perl -pi -e 's#(.DB_DSN=).*#${1}"mysql:host=$ENV{MYSQL_SERVER};dbname=${CLUEBRINGER_DB_NAME}";#' ${CLUEBRINGER_WEBUI_CONF}
+        if [ X"${BACKEND}" == X'OPENLDAP' -o X"${BACKEND}" == X'MYSQL' ]; then
+            perl -pi -e 's#(.DB_DSN=).*#${1}"mysql:host=$ENV{SQL_SERVER};dbname=$ENV{CLUEBRINGER_DB_NAME}";#' ${CLUEBRINGER_WEBUI_CONF}
+        elif [ X"${BACKEND}" == X'PGSQL' ]; then
+            perl -pi -e 's#(.DB_DSN=).*#${1}"pgsql:host=$ENV{SQL_SERVER};dbname=$ENV{CLUEBRINGER_DB_NAME}";#' ${CLUEBRINGER_WEBUI_CONF}
+        fi
+
         perl -pi -e 's#(.DB_USER=).*#${1}"$ENV{CLUEBRINGER_DB_USER}";#' ${CLUEBRINGER_WEBUI_CONF}
         perl -pi -e 's#(.DB_PASS=).*#${1}"$ENV{CLUEBRINGER_DB_PASSWD}";#' ${CLUEBRINGER_WEBUI_CONF}
     fi
     allow from 127.0.0.1
     #allow from all
 
+    AuthType basic
     AuthName "Authorization Required"
 EOF
 

iRedMail/functions/dovecot1.sh

 EOF
 
         backup_file ${DOVECOT_LDAP_CONF}
-        cp -f ${SAMPLE_DIR}/conf/dovecot-ldap.conf ${DOVECOT_LDAP_CONF}
+        cp -f ${SAMPLE_DIR}/dovecot/dovecot-ldap.conf ${DOVECOT_LDAP_CONF}
 
         perl -pi -e 's#PH_LDAP_SERVER_HOST#$ENV{LDAP_SERVER_HOST}#' ${DOVECOT_LDAP_CONF}
         perl -pi -e 's#PH_LDAP_SERVER_PORT#$ENV{LDAP_SERVER_PORT}#' ${DOVECOT_LDAP_CONF}
 EOF
 
         backup_file ${DOVECOT_MYSQL_CONF}
-        cp -f ${SAMPLE_DIR}/conf/dovecot-sql.conf ${DOVECOT_MYSQL_CONF}
+        cp -f ${SAMPLE_DIR}/dovecot/dovecot-sql.conf ${DOVECOT_MYSQL_CONF}
 
         perl -pi -e 's#PH_SQL_DRIVER#mysql#' ${DOVECOT_MYSQL_CONF}
         perl -pi -e 's#PH_SQL_SERVER#$ENV{MYSQL_SERVER}#' ${DOVECOT_MYSQL_CONF}
 
 # Import SQL template.
 USE ${IREDADMIN_DB_NAME};
-SOURCE ${SAMPLE_DIR}/used_quota.sql;
+SOURCE ${SAMPLE_DIR}/dovecot/used_quota.sql;
 GRANT SELECT,INSERT,UPDATE,DELETE ON ${IREDADMIN_DB_NAME}.* TO "${IREDADMIN_DB_USER}"@localhost IDENTIFIED BY "${IREDADMIN_DB_PASSWD}";
 
 FLUSH PRIVILEGES;
 
 # Import SQL template.
 USE ${IREDADMIN_DB_NAME};
-SOURCE ${SAMPLE_DIR}/imap_share_folder.sql;
+SOURCE ${SAMPLE_DIR}/dovecot/imap_share_folder.sql;
 GRANT SELECT,INSERT,UPDATE,DELETE ON ${IREDADMIN_DB_NAME}.* TO "${IREDADMIN_DB_USER}"@localhost IDENTIFIED BY "${IREDADMIN_DB_PASSWD}";
 
 FLUSH PRIVILEGES;
     fi
 
     ECHO_DEBUG "Copy sample sieve global filter rule file: ${DOVECOT_GLOBAL_SIEVE_FILE}.sample."
-    cp -f ${SAMPLE_DIR}/dovecot.sieve ${DOVECOT_GLOBAL_SIEVE_FILE}.sample
+    cp -f ${SAMPLE_DIR}/dovecot/dovecot.sieve ${DOVECOT_GLOBAL_SIEVE_FILE}.sample
     chown ${VMAIL_USER_NAME}:${VMAIL_GROUP_NAME} ${DOVECOT_GLOBAL_SIEVE_FILE}.sample
     chmod 0500 ${DOVECOT_GLOBAL_SIEVE_FILE}.sample
 
     compressext .bz2
 
     postrotate
-        ${SYSLOG_POSTROTATE_CMD}
+        /bin/kill -USR1 \$(cat ${DOVECOT_MASTER_PID} 2>/dev/null) 2> /dev/null || true
     endscript
 }
 EOF
     create 0666 ${VMAIL_USER_NAME} ${VMAIL_GROUP_NAME}
     missingok
     postrotate
-        ${SYSLOG_POSTROTATE_CMD}
+        /bin/kill -USR1 \`cat ${DOVECOT_MASTER_PID} 2>/dev/null\` 2> /dev/null || true
     endscript
 }
 EOF

iRedMail/functions/dovecot2.sh

 
     ECHO_DEBUG "Configure dovecot: ${DOVECOT_CONF}."
 
-    cp ${SAMPLE_DIR}/conf/dovecot2.conf ${DOVECOT_CONF}
+    cp ${SAMPLE_DIR}/dovecot/dovecot2.conf ${DOVECOT_CONF}
     chmod 0664 ${DOVECOT_CONF}
 
     # Base directory.
 
     backup_file ${DOVECOT_QUOTA_WARNING_SCRIPT}
     rm -f ${DOVECOT_QUOTA_WARNING_SCRIPT} 2>/dev/null
-    cp -f ${SAMPLE_DIR}/conf/dovecot2-quota-warning.sh ${DOVECOT_QUOTA_WARNING_SCRIPT}
+    cp -f ${SAMPLE_DIR}/dovecot/dovecot2-quota-warning.sh ${DOVECOT_QUOTA_WARNING_SCRIPT}
 
     export DOVECOT_DELIVER HOSTNAME
     perl -pi -e 's#PH_DOVECOT_DELIVER#$ENV{DOVECOT_DELIVER}#' ${DOVECOT_QUOTA_WARNING_SCRIPT}
 
     if [ X"${BACKEND}" == X"OPENLDAP" ]; then
         backup_file ${DOVECOT_LDAP_CONF}
-        cp -f ${SAMPLE_DIR}/conf/dovecot-ldap.conf ${DOVECOT_LDAP_CONF}
+        cp -f ${SAMPLE_DIR}/dovecot/dovecot-ldap.conf ${DOVECOT_LDAP_CONF}
 
         perl -pi -e 's#PH_LDAP_SERVER_HOST#$ENV{LDAP_SERVER_HOST}#' ${DOVECOT_LDAP_CONF}
         perl -pi -e 's#PH_LDAP_SERVER_PORT#$ENV{LDAP_SERVER_PORT}#' ${DOVECOT_LDAP_CONF}
     elif [ X"${BACKEND}" == X"MYSQL" ]; then
 
         backup_file ${DOVECOT_MYSQL_CONF}
-        cp -f ${SAMPLE_DIR}/conf/dovecot-sql.conf ${DOVECOT_MYSQL_CONF}
+        cp -f ${SAMPLE_DIR}/dovecot/dovecot-sql.conf ${DOVECOT_MYSQL_CONF}
 
         perl -pi -e 's#PH_SQL_DRIVER#mysql#' ${DOVECOT_MYSQL_CONF}
         perl -pi -e 's#PH_SQL_SERVER#$ENV{MYSQL_SERVER}#' ${DOVECOT_MYSQL_CONF}
     elif [ X"${BACKEND}" == X"PGSQL" ]; then
 
         backup_file ${DOVECOT_PGSQL_CONF}
-        cp -f ${SAMPLE_DIR}/conf/dovecot-sql.conf ${DOVECOT_PGSQL_CONF}
+        cp -f ${SAMPLE_DIR}/dovecot/dovecot-sql.conf ${DOVECOT_PGSQL_CONF}
 
         perl -pi -e 's#PH_SQL_DRIVER#pgsql#' ${DOVECOT_PGSQL_CONF}
         perl -pi -e 's#PH_SQL_SERVER#$ENV{PGSQL_SERVER}#' ${DOVECOT_PGSQL_CONF}
 
 # Import SQL template.
 USE ${IREDADMIN_DB_NAME};
-SOURCE ${SAMPLE_DIR}/used_quota.sql;
+SOURCE ${SAMPLE_DIR}/dovecot/used_quota.sql;
 GRANT SELECT,INSERT,UPDATE,DELETE ON ${IREDADMIN_DB_NAME}.* TO "${IREDADMIN_DB_USER}"@localhost IDENTIFIED BY "${IREDADMIN_DB_PASSWD}";
 
 FLUSH PRIVILEGES;
 
 # Import SQL template.
 USE ${IREDADMIN_DB_NAME};
-SOURCE ${SAMPLE_DIR}/imap_share_folder.sql;
+SOURCE ${SAMPLE_DIR}/dovecot/imap_share_folder.sql;
 GRANT SELECT,INSERT,UPDATE,DELETE ON ${IREDADMIN_DB_NAME}.* TO "${IREDADMIN_DB_USER}"@localhost IDENTIFIED BY "${IREDADMIN_DB_PASSWD}";
 
 FLUSH PRIVILEGES;
     fi
 
     ECHO_DEBUG "Copy sample sieve global filter rule file: ${DOVECOT_GLOBAL_SIEVE_FILE}.sample."
-    cp -f ${SAMPLE_DIR}/dovecot.sieve ${DOVECOT_GLOBAL_SIEVE_FILE}.sample
+    cp -f ${SAMPLE_DIR}/dovecot/dovecot.sieve ${DOVECOT_GLOBAL_SIEVE_FILE}.sample
     chown ${VMAIL_USER_NAME}:${VMAIL_GROUP_NAME} ${DOVECOT_GLOBAL_SIEVE_FILE}.sample
     chmod 0500 ${DOVECOT_GLOBAL_SIEVE_FILE}.sample
 
     compressext .bz2
 
     postrotate
-        ${SYSLOG_POSTROTATE_CMD}
+        doveadm log reopen
     endscript
 }
 EOF
     create 0666 ${VMAIL_USER_NAME} ${VMAIL_GROUP_NAME}
     missingok
     postrotate
-        ${SYSLOG_POSTROTATE_CMD}
+        doveadm log reopen
     endscript
 }
 EOF

iRedMail/functions/fail2ban.sh

 
 fail2ban_config()
 {
-    ECHO_INFO "Configure Fail2ban."
+    ECHO_INFO "Configure Fail2ban (authentication failure monitor)."
 
     ECHO_DEBUG "Log into syslog instead of log file."
     perl -pi -e 's#^(logtarget).*#${1} = $ENV{FAIL2BAN_LOGTARGET}#' ${FAIL2BAN_MAIN_CONF}

iRedMail/functions/iredadmin.sh

     if [ X"${BACKEND}" == X"OPENLDAP" ]; then
         mysql -h${MYSQL_SERVER} -P${MYSQL_SERVER_PORT} -u${MYSQL_ROOT_USER} -p"${MYSQL_ROOT_PASSWD}" <<EOF
 USE ${IREDADMIN_DB_NAME};
-SOURCE ${SAMPLE_DIR}/used_quota.sql;
-SOURCE ${SAMPLE_DIR}/imap_share_folder.sql;
+SOURCE ${SAMPLE_DIR}/dovecot/used_quota.sql;
+SOURCE ${SAMPLE_DIR}/dovecot/imap_share_folder.sql;
 FLUSH PRIVILEGES;
 EOF
     fi

iRedMail/functions/phppgadmin.sh

 
     perl -pi -e 's#(.*Servers.*connect_type.*=).*#${1}"socket";#' ${PHPPGADMIN_CONFIG_FILE}
 
+    if [ X"${DISTRO}" == X'UBUNTU' ]; then
+        # Create a symbol link
+        if [ -f /etc/phppgadmin/config.inc.php ]; then
+            backup_file /etc/phppgadmin/config.inc.php
+            rm -f /etc/phppgadmin/config.inc.php &>/dev/null
+            ln -s ${PHPPGADMIN_CONFIG_FILE} /etc/phppgadmin/config.inc.php
+        fi
+    fi
+
     cat >> ${TIP_FILE} <<EOF
 phpPgAdmin:
     * Configuration files:

iRedMail/functions/roundcubemail.sh

     cd ${RCM_HTTPD_ROOT}/config/
     cp -f db.inc.php.dist db.inc.php
     cp -f main.inc.php.dist main.inc.php
-    cp -f ${SAMPLE_DIR}/dovecot.sieve.roundcube ${RCM_SIEVE_SAMPLE_FILE}
+    cp -f ${SAMPLE_DIR}/dovecot/dovecot.sieve.roundcube ${RCM_SIEVE_SAMPLE_FILE}
     chown ${HTTPD_USER}:${HTTPD_GROUP} db.inc.php main.inc.php ${RCM_SIEVE_SAMPLE_FILE}
     chmod 0640 db.inc.php main.inc.php ${RCM_SIEVE_SAMPLE_FILE}
 

iRedMail/pkgs/get_all.sh

 . ${CONF_DIR}/iredadmin
 
 # Re-define @STATUS_FILE, so that iRedMail.sh can read it.
-export STATUS_FILE="${ROOTDIR}/../.${PROG_NAME}.installation.status"
+export STATUS_FILE="${ROOTDIR}/../.status"
 
 check_user root
 check_hostname
     done
 }
 
-fetch_pkgs_debian()
-{
-    cd ${PKG_DIR}
-
-    if [ X"${PKGLIST}" != X"0" ]; then
-        ECHO_INFO "Fetching binary packages ..."
-        for i in ${PKGLIST}; do
-            ECHO_INFO "+ ${pkg_counter} of ${pkg_total}: ${url}"
-            ${FETCH_CMD} "${url}"
-
-            pkg_counter=$((pkg_counter+1))
-        done
-    else
-        :
-    fi
-}
-
 fetch_misc()
 {
     # Fetch all misc packages.
             exit 255
         else
             echo -e "\t[ OK ]"
-            echo 'export status_fetch_pkgs="DONE"' >> ${STATUS_FILE}
             echo 'export status_fetch_misc="DONE"' >> ${STATUS_FILE}
             echo 'export status_check_md5="DONE"' >> ${STATUS_FILE}
         fi
 
 }
 
-create_repo_ubuntu()
-{
-    if [ X"${DISTRO}" == X"UBUNTU" ]; then
-        # Force update
-        ECHO_INFO "Execute 'apt-get update' ..."
-        ${APTGET} update
-    fi
-}
-
 track_iredmail_info()
 {
     # Help track basic information, used to help iRedMail team understand
     ECHO_INFO "Clean and refresh metadata of zypper repositories."
     zypper clean --metadata --raw-metadata
     zypper refresh
-elif [ X"${DISTRO}" == X"UBUNTU" ]; then
-    create_repo_ubuntu
-elif [ X"${DISTRO}" == X"DEBIAN" ]; then
+elif [ X"${DISTRO}" == X'DEBIAN' -o X"${DISTRO}" == X'UBUNTU' ]; then
     # Force update.
     ECHO_INFO "Execute 'apt-get update' ..."
     ${APTGET} update
 fi
 
 check_status_before_run track_iredmail_info
-fetch_misc && \
+check_status_before_run fetch_misc && \
 check_md5 && \
 check_pkg ${BIN_DIALOG} ${PKG_DIALOG} && \
 echo_end_msg && \

iRedMail/samples/amavisd-2.7.0.mysql

--- MySQL template for Amavisd-new-2.7.0. Extracted from Amavisd docs/README.sql-mysql.
--- Used by @lookup_sql_dsn and @storage_sql_dsn
-
--- local users
-CREATE TABLE users (
-  id         int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,  -- unique id
-  priority   integer      NOT NULL DEFAULT '7',  -- sort field, 0 is low prior.
-  policy_id  integer unsigned NOT NULL DEFAULT '1',  -- JOINs with policy.id
-  email      varbinary(255) NOT NULL UNIQUE,
-  fullname   varchar(255) DEFAULT NULL    -- not used by amavisd-new
-  -- local   char(1)      -- Y/N  (optional field, see note further down)
-);
-
--- any e-mail address (non- rfc2822-quoted), external or local,
--- used as senders in wblist
-CREATE TABLE mailaddr (
-  id         int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
-  priority   integer      NOT NULL DEFAULT '7',  -- 0 is low priority
-  email      varbinary(255) NOT NULL UNIQUE
-);
-
--- per-recipient whitelist and/or blacklist,
--- puts sender and recipient in relation wb  (white or blacklisted sender)
-CREATE TABLE wblist (
-  rid        integer unsigned NOT NULL,  -- recipient: users.id
-  sid        integer unsigned NOT NULL,  -- sender: mailaddr.id
-  wb         varchar(10)  NOT NULL,  -- W or Y / B or N / space=neutral / score
-  PRIMARY KEY (rid,sid)
-);
-
-CREATE TABLE policy (
-  id  int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
-                                    -- 'id' this is the _only_ required field
-  policy_name      varchar(32),     -- not used by amavisd-new, a comment
-
-  virus_lover           char(1) default NULL,     -- Y/N
-  spam_lover            char(1) default NULL,     -- Y/N
-  unchecked_lovers_maps char(1) default NULL,     -- Y/N
-  banned_files_lover    char(1) default NULL,     -- Y/N
-  bad_header_lover      char(1) default NULL,     -- Y/N
-
-  bypass_virus_checks   char(1) default NULL,     -- Y/N
-  bypass_spam_checks    char(1) default NULL,     -- Y/N
-  bypass_banned_checks  char(1) default NULL,     -- Y/N
-  bypass_header_checks  char(1) default NULL,     -- Y/N
-
-  spam_modifies_subj    char(1) default NULL,     -- Y/N
-
-  virus_quarantine_to      varchar(64) default NULL,
-  spam_quarantine_to       varchar(64) default NULL,
-  banned_quarantine_to     varchar(64) default NULL,
-  unchecked_quarantine_to  varchar(64) default NULL,
-  bad_header_quarantine_to varchar(64) default NULL,
-  clean_quarantine_to      varchar(64) default NULL,
-  archive_quarantine_to    varchar(64) default NULL,
-
-  spam_tag_level  float default NULL, -- higher score inserts spam info headers
-  spam_tag2_level float default NULL, -- inserts 'declared spam' header fields
-  spam_tag3_level float default NULL, -- inserts 'blatant spam' header fields
-  spam_kill_level float default NULL, -- higher score triggers evasive actions
-                                      -- e.g. reject/drop, quarantine, ...
-                                     -- (subject to final_spam_destiny setting)
-  spam_dsn_cutoff_level        float default NULL,
-  spam_quarantine_cutoff_level float default NULL,
-
-  addr_extension_virus      varchar(64) default NULL,
-  addr_extension_spam       varchar(64) default NULL,
-  addr_extension_banned     varchar(64) default NULL,
-  addr_extension_bad_header varchar(64) default NULL,
-
-  warnvirusrecip      char(1)     default NULL, -- Y/N
-  warnbannedrecip     char(1)     default NULL, -- Y/N
-  warnbadhrecip       char(1)     default NULL, -- Y/N
-  newvirus_admin      varchar(64) default NULL,
-  virus_admin         varchar(64) default NULL,
-  banned_admin        varchar(64) default NULL,
-  bad_header_admin    varchar(64) default NULL,
-  spam_admin          varchar(64) default NULL,
-  spam_subject_tag    varchar(64) default NULL,
-  spam_subject_tag2   varchar(64) default NULL,
-  spam_subject_tag3   varchar(64) default NULL,
-  message_size_limit  integer     default NULL, -- max size in bytes, 0 disable
-  banned_rulenames    varchar(64) default NULL, -- comma-separated list of ...
-        -- names mapped through %banned_rules to actual banned_filename tables
-  disclaimer_options  varchar(64) default NULL,
-  forward_method      varchar(64) default NULL,
-  sa_userconf         varchar(64) default NULL,
-  sa_username         varchar(64) default NULL
-);
-
-
--- R/W part of the dataset (optional)
---   May reside in the same or in a separate database as lookups database;
---   REQUIRES SUPPORT FOR TRANSACTIONS; specified in @storage_sql_dsn
---
---   MySQL note ( http://dev.mysql.com/doc/mysql/en/storage-engines.html ):
---     ENGINE is the preferred term, but cannot be used before MySQL 4.0.18.
---     TYPE is available beginning with MySQL 3.23.0, the first version of
---     MySQL for which multiple storage engines were available. If you omit
---     the ENGINE or TYPE option, the default storage engine is used.
---     By default this is MyISAM.
---
---  Please create additional indexes on keys when needed, or drop suggested
---  ones as appropriate to optimize queries needed by a management application.
---  See your database documentation for further optimization hints. With MySQL
---  see Chapter 15 of the reference manual. For example the chapter 15.17 says:
---  InnoDB does not keep an internal count of rows in a table. To process a
---  SELECT COUNT(*) FROM T statement, InnoDB must scan an index of the table,
---  which takes some time if the index is not entirely in the buffer pool.
---
---  Wayne Smith adds: When using MySQL with InnoDB one might want to
---  increase buffer size for both pool and log, and might also want
---  to change flush settings for a little better performance. Example:
---    innodb_buffer_pool_size = 384M
---    innodb_log_buffer_size = 8M
---    innodb_flush_log_at_trx_commit = 0
---  The big performance increase is the first two, the third just helps with
---  lowering disk activity. Consider also adjusting the key_buffer_size.
-
--- provide unique id for each e-mail address, avoids storing copies
-CREATE TABLE maddr (
-  partition_tag integer      DEFAULT 0, -- see $partition_tag
-  id         bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
-  email      varbinary(255)  NOT NULL,  -- full mail address
-  domain     varchar(255)    NOT NULL,  -- only domain part of the email address
-                                        -- with subdomain fields in reverse
-  CONSTRAINT part_email UNIQUE (partition_tag,email)
-) ENGINE=InnoDB;
-
-CREATE INDEX maddr_idx_email ON maddr (email);
-CREATE INDEX maddr_idx_domain ON maddr (domain);
-
--- information pertaining to each processed message as a whole;
--- NOTE: records with NULL msgs.content should be ignored by utilities,
---   as such records correspond to messages just being processes, or were lost
--- NOTE: instead of a character field time_iso, one might prefer:
---   time_iso TIMESTAMP NOT NULL DEFAULT 0,
---   but the following MUST then be set in amavisd.conf: $timestamp_fmt_mysql=1
-CREATE TABLE msgs (
-  partition_tag integer     DEFAULT 0,   -- see $partition_tag
-  mail_id     varbinary(16) NOT NULL,    -- long-term unique mail id, dflt 12 ch
-  secret_id   varbinary(16) DEFAULT '',  -- authorizes release of mail_id, 12 ch
-  am_id       varchar(20)   NOT NULL,    -- id used in the log
-  time_num    integer unsigned NOT NULL, -- rx_time: seconds since Unix epoch
-  time_iso    char(16)      NOT NULL,    -- rx_time: ISO8601 UTC ascii time
-  sid         bigint unsigned NOT NULL,  -- sender: maddr.id
-  policy      varchar(255)  DEFAULT '',  -- policy bank path (like macro %p)
-  client_addr varchar(255)  DEFAULT '',  -- SMTP client IP address (IPv4 or v6)
-  size        integer unsigned NOT NULL, -- message size in bytes
-  originating char(1) DEFAULT ' ' NOT NULL,  -- sender from inside or auth'd
-  content     char(1),                   -- content type: V/B/U/S/Y/M/H/O/T/C
-    -- virus/banned/unchecked/spam(kill)/spammy(tag2)/
-    -- /bad-mime/bad-header/oversized/mta-err/clean
-    -- is NULL on partially processed mail
-    -- (prior to 2.7.0 the CC_SPAMMY was logged as 's', now 'Y' is used;
-    -- to avoid a need for case-insenstivity in queries)
-  quar_type  char(1),                   -- quarantined as: ' '/F/Z/B/Q/M/L
-                                        --  none/file/zipfile/bsmtp/sql/
-                                        --  /mailbox(smtp)/mailbox(lmtp)
-  quar_loc   varbinary(255) DEFAULT '', -- quarantine location (e.g. file)
-  dsn_sent   char(1),                   -- was DSN sent? Y/N/q (q=quenched)
-  spam_level float,                     -- SA spam level (no boosts)
-  message_id varchar(255)  DEFAULT '',  -- mail Message-ID header field
-  from_addr  varchar(255)  CHARACTER SET utf8 COLLATE utf8_bin  DEFAULT '',
-                                        -- mail From header field,    UTF8
-  subject    varchar(255)  CHARACTER SET utf8 COLLATE utf8_bin  DEFAULT '',
-                                        -- mail Subject header field, UTF8
-  host       varchar(255)  NOT NULL,    -- hostname where amavisd is running
-  PRIMARY KEY (partition_tag,mail_id)
--- FOREIGN KEY (sid) REFERENCES maddr(id) ON DELETE RESTRICT
-) ENGINE=InnoDB;
-CREATE INDEX msgs_idx_sid      ON msgs (sid);
-CREATE INDEX msgs_idx_mess_id  ON msgs (message_id); -- useful with pen pals
-CREATE INDEX msgs_idx_time_num ON msgs (time_num);
-
-CREATE INDEX msgs_idx_mail_id   ON msgs (mail_id);
-CREATE INDEX msgs_idx_content   ON msgs (content);
-CREATE INDEX msgs_idx_content_time_num ON msgs (content, time_num);
-
--- alternatively when purging based on time_iso (instead of msgs_idx_time_num):
--- CREATE INDEX msgs_idx_time_iso ON msgs (time_iso);
-
--- per-recipient information related to each processed message;
--- NOTE: records in msgrcpt without corresponding msgs.mail_id record are
---  orphaned and should be ignored and eventually deleted by external utilities
-CREATE TABLE msgrcpt (
-  partition_tag integer    DEFAULT 0,    -- see $partition_tag
-  mail_id    varbinary(16) NOT NULL,     -- (must allow duplicates)
-  rseqnum    integer  DEFAULT 0   NOT NULL, -- recip's enumeration within msg
-  rid        bigint unsigned NOT NULL,   -- recipient: maddr.id (dupl. allowed)
-  is_local   char(1)  DEFAULT ' ' NOT NULL, -- recip is: Y=local, N=foreign
-  content    char(1)  DEFAULT ' ' NOT NULL, -- content type V/B/U/S/Y/M/H/O/T/C
-  ds         char(1)  NOT NULL,          -- delivery status: P/R/B/D/T
-                                         -- pass/reject/bounce/discard/tempfail
-  rs         char(1)  NOT NULL,          -- release status: initialized to ' '
-  bl         char(1)  DEFAULT ' ',       -- sender blacklisted by this recip
-  wl         char(1)  DEFAULT ' ',       -- sender whitelisted by this recip
-  bspam_level float,                     -- per-recipient (total) spam level
-  smtp_resp  varchar(255)  DEFAULT '',   -- SMTP response given to MTA
-  PRIMARY KEY (partition_tag,mail_id,rseqnum)
--- FOREIGN KEY (rid)     REFERENCES maddr(id)     ON DELETE RESTRICT,
--- FOREIGN KEY (mail_id) REFERENCES msgs(mail_id) ON DELETE CASCADE
-) ENGINE=InnoDB;
-CREATE INDEX msgrcpt_idx_mail_id  ON msgrcpt (mail_id);
-CREATE INDEX msgrcpt_idx_rid      ON msgrcpt (rid);
-
--- mail quarantine in SQL, enabled by $*_quarantine_method='sql:'
--- NOTE: records in quarantine without corresponding msgs.mail_id record are
---  orphaned and should be ignored and eventually deleted by external utilities
-CREATE TABLE quarantine (
-  partition_tag integer    DEFAULT 0,    -- see $partition_tag
-  mail_id    varbinary(16) NOT NULL,     -- long-term unique mail id
-  chunk_ind  integer unsigned NOT NULL,  -- chunk number, starting with 1
-  mail_text  blob          NOT NULL,     -- store mail as chunks of octets
-  PRIMARY KEY (partition_tag,mail_id,chunk_ind)
--- FOREIGN KEY (mail_id) REFERENCES msgs(mail_id) ON DELETE CASCADE
-) ENGINE=InnoDB;
-
-CREATE INDEX quar_idx_mail_id   ON quarantine (mail_id);
-
--- field msgrcpt.rs is primarily intended for use by quarantine management
--- software; the value assigned by amavisd is a space;
--- a short _preliminary_ list of possible values:
---   'V' => viewed (marked as read)
---   'R' => released (delivered) to this recipient
---   'p' => pending (a status given to messages when the admin received the
---                   request but not yet released; targeted to banned parts)
---   'D' => marked for deletion; a cleanup script may delete it
-

iRedMail/samples/amavisd-2.7.0.pgsql

--- local users
-CREATE TABLE users (
-  id         serial  PRIMARY KEY,  -- unique id
-  priority   integer NOT NULL DEFAULT '7',  -- sort field, 0 is low prior.
-  policy_id  integer NOT NULL DEFAULT '1' CHECK (policy_id >= 0),
-                                         -- JOINs with policy.id
-  email      bytea   NOT NULL UNIQUE,    -- email address, non-rfc2822-quoted
-  fullname   varchar(255) DEFAULT NULL  -- not used by amavisd-new
-  -- local   char(1)      -- Y/N  (optional field, see note further down)
-);
-
--- any e-mail address (non- rfc2822-quoted), external or local,
--- used as senders in wblist
-CREATE TABLE mailaddr (
-  id         serial  PRIMARY KEY,
-  priority   integer NOT NULL DEFAULT '7',  -- 0 is low priority
-  email      bytea   NOT NULL UNIQUE
-);
-
--- per-recipient whitelist and/or blacklist,
--- puts sender and recipient in relation wb  (white or blacklisted sender)
-CREATE TABLE wblist (
-  rid        integer NOT NULL CHECK (rid >= 0),  -- recipient: users.id
-  sid        integer NOT NULL CHECK (sid >= 0),  -- sender: mailaddr.id
-  wb         varchar(10) NOT NULL,  -- W or Y / B or N / space=neutral / score
-  PRIMARY KEY (rid,sid)
-);
-
-CREATE TABLE policy (
-  id  serial PRIMARY KEY,           -- 'id' this is the _only_ required field
-  policy_name      varchar(32),     -- not used by amavisd-new, a comment
-
-  virus_lover           char(1) default NULL,     -- Y/N
-  spam_lover            char(1) default NULL,     -- Y/N
-  unchecked_lovers_maps char(1) default NULL,     -- Y/N
-  banned_files_lover    char(1) default NULL,     -- Y/N
-  bad_header_lover      char(1) default NULL,     -- Y/N
-
-  bypass_virus_checks   char(1) default NULL,     -- Y/N
-  bypass_spam_checks    char(1) default NULL,     -- Y/N
-  bypass_banned_checks  char(1) default NULL,     -- Y/N
-  bypass_header_checks  char(1) default NULL,     -- Y/N
-
-  virus_quarantine_to      varchar(64) default NULL,
-  spam_quarantine_to       varchar(64) default NULL,
-  banned_quarantine_to     varchar(64) default NULL,
-  unchecked_quarantine_to  varchar(64) default NULL,
-  bad_header_quarantine_to varchar(64) default NULL,
-  clean_quarantine_to      varchar(64) default NULL,
-  archive_quarantine_to    varchar(64) default NULL,
-
-  spam_tag_level  real default NULL, -- higher score inserts spam info headers
-  spam_tag2_level real default NULL, -- inserts 'declared spam' header fields
-  spam_tag3_level real default NULL, -- inserts 'blatant spam' header fields
-  spam_kill_level real default NULL, -- higher score triggers evasive actions
-                                     -- e.g. reject/drop, quarantine, ...
-                                     -- (subject to final_spam_destiny setting)
-  spam_dsn_cutoff_level        real default NULL,
-  spam_quarantine_cutoff_level real default NULL,
-
-  addr_extension_virus      varchar(64) default NULL,
-  addr_extension_spam       varchar(64) default NULL,
-  addr_extension_banned     varchar(64) default NULL,
-  addr_extension_bad_header varchar(64) default NULL,
-
-  warnvirusrecip      char(1)     default NULL, -- Y/N
-  warnbannedrecip     char(1)     default NULL, -- Y/N
-  warnbadhrecip       char(1)     default NULL, -- Y/N
-  newvirus_admin      varchar(64) default NULL,
-  virus_admin         varchar(64) default NULL,
-  banned_admin        varchar(64) default NULL,
-  bad_header_admin    varchar(64) default NULL,
-  spam_admin          varchar(64) default NULL,
-  spam_subject_tag    varchar(64) default NULL,
-  spam_subject_tag2   varchar(64) default NULL,
-  spam_subject_tag3   varchar(64) default NULL,
-  message_size_limit  integer     default NULL, -- max size in bytes, 0 disable
-  banned_rulenames    varchar(64) default NULL, -- comma-separated list of ...
-        -- names mapped through %banned_rules to actual banned_filename tables
-  disclaimer_options  varchar(64) default NULL,
-  forward_method      varchar(64) default NULL,
-  sa_userconf         varchar(64) default NULL,
-  sa_username         varchar(64) default NULL
-);
-
-
--- R/W part of the dataset (optional)
---   May reside in the same or in a separate database as lookups database;
---   REQUIRES SUPPORT FOR TRANSACTIONS; specified in @storage_sql_dsn
---
---  Please create additional indexes on keys when needed, or drop suggested
---  ones as appropriate to optimize queries needed by a management application.
---  See your database documentation for further optimization hints.
-
--- provide unique id for each e-mail address, avoids storing copies
-CREATE TABLE maddr (
-  partition_tag integer   DEFAULT 0,   -- see $partition_tag
-  id         serial       PRIMARY KEY,
-  email      bytea        NOT NULL,    -- full e-mail address
-  domain     varchar(255) NOT NULL,    -- only domain part of the email address
-                                       -- with subdomain fields in reverse
-  CONSTRAINT part_email UNIQUE (partition_tag,email)
-);
-
--- information pertaining to each processed message as a whole;
--- NOTE: records with NULL msgs.content should be ignored by utilities,
---   as such records correspond to messages just being processes, or were lost
-CREATE TABLE msgs (
-  partition_tag integer     DEFAULT 0,  -- see $partition_tag
-  mail_id     bytea         NOT NULL,   -- long-term unique mail id, dflt 12 ch
-  secret_id   bytea         DEFAULT '', -- authorizes release of mail_id, 12 ch
-  am_id       varchar(20)   NOT NULL,   -- id used in the log
-  time_num    integer NOT NULL CHECK (time_num >= 0),
-                                        -- rx_time: seconds since Unix epoch
-  time_iso timestamp WITH TIME ZONE NOT NULL,-- rx_time: ISO8601 UTC ascii time
-  sid         integer NOT NULL CHECK (sid >= 0), -- sender: maddr.id
-  policy      varchar(255)  DEFAULT '', -- policy bank path (like macro %p)
-  client_addr varchar(255)  DEFAULT '', -- SMTP client IP address (IPv4 or v6)
-  size        integer NOT NULL CHECK (size >= 0), -- message size in bytes
-  originating char(1) DEFAULT ' ' NOT NULL,  -- sender from inside or auth'd
-  content     char(1),                   -- content type: V/B/U/S/Y/M/H/O/T/C
-    -- virus/banned/unchecked/spam(kill)/spammy(tag2)/
-    -- /bad-mime/bad-header/oversized/mta-err/clean
-    -- is NULL on partially processed mail
-    -- (prior to 2.7.0 the CC_SPAMMY was logged as 's', now 'Y' is used;
-    --- to avoid a need for case-insenstivity in queries)
-  quar_type  char(1),                   -- quarantined as: ' '/F/Z/B/Q/M/L
-                                        --  none/file/zipfile/bsmtp/sql/
-                                        --  /mailbox(smtp)/mailbox(lmtp)
-  quar_loc   varchar(255)  DEFAULT '',  -- quarantine location (e.g. file)
-  dsn_sent   char(1),                   -- was DSN sent? Y/N/q (q=quenched)
-  spam_level real,                      -- SA spam level (no boosts)
-  message_id varchar(255)  DEFAULT '',  -- mail Message-ID header field
-  from_addr  varchar(255)  DEFAULT '',  -- mail From header field,    UTF8
-  subject    varchar(255)  DEFAULT '',  -- mail Subject header field, UTF8
-  host       varchar(255)  NOT NULL,    -- hostname where amavisd is running
-  PRIMARY KEY (partition_tag,mail_id)
---FOREIGN KEY (sid) REFERENCES maddr(id) ON DELETE RESTRICT
-);
-CREATE INDEX msgs_idx_sid      ON msgs (sid);
-CREATE INDEX msgs_idx_mess_id  ON msgs (message_id); -- useful with pen pals
-CREATE INDEX msgs_idx_time_iso ON msgs (time_iso);
-CREATE INDEX msgs_idx_time_num ON msgs (time_num);   -- optional
-
--- per-recipient information related to each processed message;
--- NOTE: records in msgrcpt without corresponding msgs.mail_id record are
---  orphaned and should be ignored and eventually deleted by external utilities
-CREATE TABLE msgrcpt (
-  partition_tag integer DEFAULT 0,  -- see $partition_tag
-  mail_id    bytea    NOT NULL,     -- (must allow duplicates)
-  rseqnum    integer  DEFAULT 0   NOT NULL, -- recip's enumeration within msg
-  rid        integer  NOT NULL,     -- recipient: maddr.id (duplicates allowed)
-  is_local   char(1)  DEFAULT ' ' NOT NULL, -- recip is: Y=local, N=foreign
-  content    char(1)  DEFAULT ' ' NOT NULL, -- content type V/B/U/S/Y/M/H/O/T/C
-  ds         char(1)  NOT NULL,     -- delivery status: P/R/B/D/T
-                                    -- pass/reject/bounce/discard/tempfail
-  rs         char(1)  NOT NULL,     -- release status: initialized to ' '
-  bl         char(1)  DEFAULT ' ',  -- sender blacklisted by this recip
-  wl         char(1)  DEFAULT ' ',  -- sender whitelisted by this recip
-  bspam_level real,                 -- per-recipient (total) spam level
-  smtp_resp  varchar(255) DEFAULT '', -- SMTP response given to MTA
-  PRIMARY KEY (partition_tag,mail_id,rseqnum)
---FOREIGN KEY (rid)     REFERENCES maddr(id)     ON DELETE RESTRICT,
---FOREIGN KEY (mail_id) REFERENCES msgs(mail_id) ON DELETE CASCADE
-);
-CREATE INDEX msgrcpt_idx_mail_id  ON msgrcpt (mail_id);
-CREATE INDEX msgrcpt_idx_rid      ON msgrcpt (rid);
-
--- mail quarantine in SQL, enabled by $*_quarantine_method='sql:'
--- NOTE: records in quarantine without corresponding msgs.mail_id record are
---  orphaned and should be ignored and eventually deleted by external utilities
-CREATE TABLE quarantine (
-  partition_tag integer  DEFAULT 0,      -- see $partition_tag
-  mail_id    bytea   NOT NULL,           -- long-term unique mail id
-  chunk_ind  integer NOT NULL CHECK (chunk_ind >= 0), -- chunk number, 1..
-  mail_text  bytea   NOT NULL,           -- store mail as chunks of octects
-  PRIMARY KEY (partition_tag,mail_id,chunk_ind)
---FOREIGN KEY (mail_id) REFERENCES msgs(mail_id) ON DELETE CASCADE
-);
-
--- field msgrcpt.rs is primarily intended for use by quarantine management
--- software; the value assigned by amavisd is a space;
--- a short _preliminary_ list of possible values:
---   'V' => viewed (marked as read)
---   'R' => released (delivered) to this recipient
---   'p' => pending (a status given to messages when the admin received the
---                   request but not yet released; targeted to banned parts)
---   'D' => marked for deletion; a cleanup script may delete it

iRedMail/samples/amavisd.mysql

--- MySQL template for Amavisd. Extracted from Amavisd docs/README.sql-mysql.
--- Used by @lookup_sql_dsn and @storage_sql_dsn
-
--- local users
-CREATE TABLE users (
-  id         int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,  -- unique id
-  priority   integer      NOT NULL DEFAULT '7',  -- sort field, 0 is low prior.
-  policy_id  integer unsigned NOT NULL DEFAULT '1',  -- JOINs with policy.id
-  email      varbinary(255) NOT NULL UNIQUE,
-  fullname   varchar(255) DEFAULT NULL    -- not used by amavisd-new
-  -- local   char(1)      -- Y/N  (optional field, see note further down)
-);
-
--- any e-mail address (non- rfc2822-quoted), external or local,
--- used as senders in wblist
-CREATE TABLE mailaddr (
-  id         int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
-  priority   integer      NOT NULL DEFAULT '7',  -- 0 is low priority
-  email      varbinary(255) NOT NULL UNIQUE
-);
-
--- per-recipient whitelist and/or blacklist,
--- puts sender and recipient in relation wb  (white or blacklisted sender)
-CREATE TABLE wblist (
-  rid        integer unsigned NOT NULL,  -- recipient: users.id
-  sid        integer unsigned NOT NULL,  -- sender: mailaddr.id
-  wb         varchar(10)  NOT NULL,  -- W or Y / B or N / space=neutral / score
-  PRIMARY KEY (rid,sid)
-);
-
-CREATE TABLE policy (
-  id  int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
-                                    -- 'id' this is the _only_ required field
-  policy_name      varchar(32),     -- not used by amavisd-new, a comment
-
-  virus_lover          char(1) default NULL,     -- Y/N
-  spam_lover           char(1) default NULL,     -- Y/N
-  banned_files_lover   char(1) default NULL,     -- Y/N
-  bad_header_lover     char(1) default NULL,     -- Y/N
-
-  bypass_virus_checks  char(1) default NULL,     -- Y/N
-  bypass_spam_checks   char(1) default NULL,     -- Y/N
-  bypass_banned_checks char(1) default NULL,     -- Y/N
-  bypass_header_checks char(1) default NULL,     -- Y/N
-
-  spam_modifies_subj   char(1) default NULL,     -- Y/N
-
-  virus_quarantine_to      varchar(64) default NULL,
-  spam_quarantine_to       varchar(64) default NULL,
-  banned_quarantine_to     varchar(64) default NULL,
-  bad_header_quarantine_to varchar(64) default NULL,
-  clean_quarantine_to      varchar(64) default NULL,
-  other_quarantine_to      varchar(64) default NULL,
-
-  spam_tag_level  float default NULL, -- higher score inserts spam info headers
-  spam_tag2_level float default NULL, -- inserts 'declared spam' header fields
-  spam_kill_level float default NULL, -- higher score triggers evasive actions
-                                      -- e.g. reject/drop, quarantine, ...
-                                     -- (subject to final_spam_destiny setting)
-  spam_dsn_cutoff_level        float default NULL,
-  spam_quarantine_cutoff_level float default NULL,
-
-  addr_extension_virus      varchar(64) default NULL,
-  addr_extension_spam       varchar(64) default NULL,
-  addr_extension_banned     varchar(64) default NULL,
-  addr_extension_bad_header varchar(64) default NULL,
-
-  warnvirusrecip      char(1)     default NULL, -- Y/N
-  warnbannedrecip     char(1)     default NULL, -- Y/N
-  warnbadhrecip       char(1)     default NULL, -- Y/N
-  newvirus_admin      varchar(64) default NULL,
-  virus_admin         varchar(64) default NULL,
-  banned_admin        varchar(64) default NULL,
-  bad_header_admin    varchar(64) default NULL,
-  spam_admin          varchar(64) default NULL,
-  spam_subject_tag    varchar(64) default NULL,
-  spam_subject_tag2   varchar(64) default NULL,
-  message_size_limit  integer     default NULL, -- max size in bytes, 0 disable
-  banned_rulenames    varchar(64) default NULL  -- comma-separated list of ...
-        -- names mapped through %banned_rules to actual banned_filename tables
-);
-
-
-
--- R/W part of the dataset (optional)
---   May reside in the same or in a separate database as lookups database;
---   REQUIRES SUPPORT FOR TRANSACTIONS; specified in @storage_sql_dsn
---
---   MySQL note ( http://dev.mysql.com/doc/mysql/en/storage-engines.html ):
---     ENGINE is the preferred term, but cannot be used before MySQL 4.0.18.
---     TYPE is available beginning with MySQL 3.23.0, the first version of
---     MySQL for which multiple storage engines were available. If you omit
---     the ENGINE or TYPE option, the default storage engine is used.
---     By default this is MyISAM.
---
---  Please create additional indexes on keys when needed, or drop suggested
---  ones as appropriate to optimize queries needed by a management application.
---  See your database documentation for further optimization hints. With MySQL
---  see Chapter 15 of the reference manual. For example the chapter 15.17 says:
---  InnoDB does not keep an internal count of rows in a table. To process a
---  SELECT COUNT(*) FROM T statement, InnoDB must scan an index of the table,
---  which takes some time if the index is not entirely in the buffer pool.
---
---  Wayne Smith adds: When using MySQL with InnoDB one might want to
---  increase buffer size for both pool and log, and might also want
---  to change flush settings for a little better performance. Example:
---    innodb_buffer_pool_size = 384M
---    innodb_log_buffer_size = 8M
---    innodb_flush_log_at_trx_commit = 0
---  The big performance increase is the first two, the third just helps with
---  lowering disk activity. Consider also adjusting the key_buffer_size.
-
--- provide unique id for each e-mail address, avoids storing copies
-CREATE TABLE maddr (
-  partition_tag integer      DEFAULT 0, -- see $partition_tag
-  id         bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
-  email      varbinary(255)  NOT NULL,  -- full mail address
-  domain     varchar(255)    NOT NULL,  -- only domain part of the email address
-                                        -- with subdomain fields in reverse
-  CONSTRAINT part_email UNIQUE (partition_tag,email)
-) ENGINE=InnoDB;
-
-CREATE INDEX maddr_idx_email ON maddr (email);
-CREATE INDEX maddr_idx_domain ON maddr (domain);
-
--- information pertaining to each processed message as a whole;
--- NOTE: records with NULL msgs.content should be ignored by utilities,
---   as such records correspond to messages just being processes, or were lost
--- NOTE: instead of a character field time_iso, one might prefer:
---   time_iso TIMESTAMP NOT NULL DEFAULT 0,
---   but the following MUST then be set in amavisd.conf: $timestamp_fmt_mysql=1
-CREATE TABLE msgs (
-  partition_tag integer    DEFAULT 0,   -- see $partition_tag
-  mail_id     varbinary(16) NOT NULL,    -- long-term unique mail id, dflt 12 ch
-  secret_id   varbinary(16) DEFAULT '',  -- authorizes release of mail_id, 12 ch
-  am_id       varchar(20)   NOT NULL,    -- id used in the log
-  time_num    integer unsigned NOT NULL, -- rx_time: seconds since Unix epoch
-  time_iso    char(16)      NOT NULL,    -- rx_time: ISO8601 UTC ascii time
-  sid         bigint unsigned NOT NULL,  -- sender: maddr.id
-  policy      varchar(255) DEFAULT '',   -- policy bank path (like macro %p)
-  client_addr varchar(255) DEFAULT '',   -- SMTP client IP address (IPv4 or v6)
-  size        integer unsigned NOT NULL, -- message size in bytes
-  originating char(1) DEFAULT ' ' NOT NULL,  -- sender from inside or auth'd
-  content     char(1),                   -- content type: V/B/U/S/Y/M/H/O/T/C
-    -- virus/banned/unchecked/spam(kill)/spammy(tag2)/
-    -- /bad-mime/bad-header/oversized/mta-err/clean
-    -- is NULL on partially processed mail
-    -- (prior to 2.7.0 the CC_SPAMMY was logged as 's', now 'Y' is used;
-    -- to avoid a need for case-insenstivity in queries)
-  quar_type  char(1),                   -- quarantined as: ' '/F/Z/B/Q/M/L
-                                        --  none/file/zipfile/bsmtp/sql/
-                                        --  /mailbox(smtp)/mailbox(lmtp)
-  quar_loc   varbinary(255) DEFAULT '', -- quarantine location (e.g. file)
-  dsn_sent   char(1),                   -- was DSN sent? Y/N/q (q=quenched)
-  spam_level float,                     -- SA spam level (no boosts)
-  message_id varchar(255)  DEFAULT '',  -- mail Message-ID header field
-  from_addr  varchar(255)  CHARACTER SET utf8 COLLATE utf8_bin  DEFAULT '',
-                                        -- mail From header field,    UTF8
-  subject    varchar(255)  CHARACTER SET utf8 COLLATE utf8_bin  DEFAULT '',
-                                        -- mail Subject header field, UTF8
-  host       varchar(255)  NOT NULL,    -- hostname where amavisd is running
-  PRIMARY KEY (partition_tag,mail_id)
--- FOREIGN KEY (sid) REFERENCES maddr(id) ON DELETE RESTRICT
-) ENGINE=InnoDB;
-CREATE INDEX msgs_idx_sid      ON msgs (sid);
-CREATE INDEX msgs_idx_mess_id  ON msgs (message_id); -- useful with pen pals
-CREATE INDEX msgs_idx_time_num ON msgs (time_num);
-
-CREATE INDEX msgs_idx_mail_id   ON msgs (mail_id);
-CREATE INDEX msgs_idx_content   ON msgs (content);
-CREATE INDEX msgs_idx_content_time_num ON msgs (content, time_num);
-
--- alternatively when purging based on time_iso (instead of msgs_idx_time_num):
--- CREATE INDEX msgs_idx_time_iso ON msgs (time_iso);
-
--- per-recipient information related to each processed message;
--- NOTE: records in msgrcpt without corresponding msgs.mail_id record are
---  orphaned and should be ignored and eventually deleted by external utilities
-CREATE TABLE msgrcpt (
-  partition_tag integer    DEFAULT 0,    -- see $partition_tag
-  mail_id    varbinary(16) NOT NULL,     -- (must allow duplicates)
-  rseqnum    integer  DEFAULT 0   NOT NULL, -- recip's enumeration within msg
-  rid        bigint unsigned NOT NULL,   -- recipient: maddr.id (dupl. allowed)
-  is_local   char(1)  DEFAULT ' ' NOT NULL, -- recip is: Y=local, N=foreign
-  content    char(1)  DEFAULT ' ' NOT NULL, -- content type V/B/U/S/Y/M/H/O/T/C
-  ds         char(1)  NOT NULL,          -- delivery status: P/R/B/D/T
-                                         -- pass/reject/bounce/discard/tempfail
-  rs         char(1)  NOT NULL,          -- release status: initialized to ' '
-  bl         char(1)  DEFAULT ' ',       -- sender blacklisted by this recip
-  wl         char(1)  DEFAULT ' ',       -- sender whitelisted by this recip
-  bspam_level float,                     -- per-recipient (total) spam level
-  smtp_resp  varchar(255)  DEFAULT '',   -- SMTP response given to MTA
-  KEY (mail_id),
-  KEY (rid)
-) ENGINE=InnoDB;
-
-CREATE INDEX msgrcpt_idx_mail_id  ON msgrcpt (mail_id);
-CREATE INDEX msgrcpt_idx_rid      ON msgrcpt (rid);
-
--- mail quarantine in SQL, enabled by $*_quarantine_method='sql:'
--- NOTE: records in quarantine without corresponding msgs.mail_id record are
---  orphaned and should be ignored and eventually deleted by external utilities
-CREATE TABLE quarantine (
-  partition_tag integer    DEFAULT 0,    -- see $partition_tag
-  mail_id    varbinary(16) NOT NULL,     -- long-term unique mail id
-  chunk_ind  integer unsigned NOT NULL,  -- chunk number, starting with 1
-  mail_text  blob          NOT NULL,     -- store mail as chunks of octets
-  PRIMARY KEY (partition_tag,mail_id,chunk_ind)
--- FOREIGN KEY (mail_id) REFERENCES msgs(mail_id) ON DELETE CASCADE
-) ENGINE=InnoDB;
-
-CREATE INDEX quar_idx_mail_id   ON quarantine (mail_id);
-
--- field msgrcpt.rs is primarily intended for use by quarantine management
--- software; the value assigned by amavisd is a space;
--- a short _preliminary_ list of possible values:
---   'V' => viewed (marked as read)
---   'R' => released (delivered) to this recipient
---   'p' => pending (a status given to messages when the admin received the
---                   request but not yet released; targeted to banned parts)
---   'D' => marked for deletion; a cleanup script may delete it

iRedMail/samples/amavisd.pgsql

--- local users
-CREATE TABLE users (
-  id         serial  PRIMARY KEY,  -- unique id
-  priority   integer NOT NULL DEFAULT '7',  -- sort field, 0 is low prior.
-  policy_id  integer NOT NULL DEFAULT '1' CHECK (policy_id >= 0),
-                                           -- JOINs with policy.id
-  email      bytea   NOT NULL UNIQUE, -- email address, non-rfc2822-quoted
-  fullname   varchar(255) DEFAULT NULL,    -- not used by amavisd-new
-  local      char(1)      -- Y/N  (optional field, see note further down)
-);
-
--- any e-mail address (non- rfc2822-quoted), external or local,
--- used as senders in wblist
-CREATE TABLE mailaddr (
-  id         serial  PRIMARY KEY,
-  priority   integer NOT NULL DEFAULT '7',  -- 0 is low priority
-  email      bytea   NOT NULL UNIQUE
-);
-
--- per-recipient whitelist and/or blacklist,
--- puts sender and recipient in relation wb  (white or blacklisted sender)
-CREATE TABLE wblist (
-  rid        integer NOT NULL CHECK (rid >= 0),  -- recipient: users.id
-  sid        integer NOT NULL CHECK (sid >= 0),  -- sender: mailaddr.id
-  wb         varchar(10) NOT NULL,  -- W or Y / B or N / space=neutral / score
-  PRIMARY KEY (rid,sid)
-);
-
-CREATE TABLE policy (
-  id  serial PRIMARY KEY,           -- 'id' this is the _only_ required field
-  policy_name      varchar(32),     -- not used by amavisd-new, a comment
-
-  virus_lover          char(1) default NULL,     -- Y/N
-  spam_lover           char(1) default NULL,     -- Y/N
-  banned_files_lover   char(1) default NULL,     -- Y/N
-  bad_header_lover     char(1) default NULL,     -- Y/N
-
-  bypass_virus_checks  char(1) default NULL,     -- Y/N
-  bypass_spam_checks   char(1) default NULL,     -- Y/N
-  bypass_banned_checks char(1) default NULL,     -- Y/N
-  bypass_header_checks char(1) default NULL,     -- Y/N
-
-  spam_modifies_subj   char(1) default NULL,     -- Y/N
-
-  virus_quarantine_to      varchar(64) default NULL,
-  spam_quarantine_to       varchar(64) default NULL,
-  banned_quarantine_to     varchar(64) default NULL,
-  bad_header_quarantine_to varchar(64) default NULL,
-  clean_quarantine_to      varchar(64) default NULL,
-  other_quarantine_to      varchar(64) default NULL,
-
-  spam_tag_level  real default NULL, -- higher score inserts spam info headers
-  spam_tag2_level real default NULL, -- inserts 'declared spam' header fields
-  spam_kill_level real default NULL, -- higher score triggers evasive actions
-                                     -- e.g. reject/drop, quarantine, ...
-                                     -- (subject to final_spam_destiny setting)
-  spam_dsn_cutoff_level        real default NULL,
-  spam_quarantine_cutoff_level real default NULL,
-
-  addr_extension_virus      varchar(64) default NULL,
-  addr_extension_spam       varchar(64) default NULL,
-  addr_extension_banned     varchar(64) default NULL,
-  addr_extension_bad_header varchar(64) default NULL,
-
-  warnvirusrecip      char(1)     default NULL, -- Y/N
-  warnbannedrecip     char(1)     default NULL, -- Y/N
-  warnbadhrecip       char(1)     default NULL, -- Y/N
-  newvirus_admin      varchar(64) default NULL,
-  virus_admin         varchar(64) default NULL,
-  banned_admin        varchar(64) default NULL,
-  bad_header_admin    varchar(64) default NULL,
-  spam_admin          varchar(64) default NULL,
-  spam_subject_tag    varchar(64) default NULL,
-  spam_subject_tag2   varchar(64) default NULL,
-  message_size_limit  integer     default NULL, -- max size in bytes, 0 disable
-  banned_rulenames    varchar(64) default NULL  -- comma-separated list of ...
-        -- names mapped through %banned_rules to actual banned_filename tables
-);
-
-
--- R/W part of the dataset (optional)
---   May reside in the same or in a separate database as lookups database;
---   REQUIRES SUPPORT FOR TRANSACTIONS; specified in @storage_sql_dsn
---
---  Please create additional indexes on keys when needed, or drop suggested
---  ones as appropriate to optimize queries needed by a management application.
---  See your database documentation for further optimization hints.
-
--- provide unique id for each e-mail address, avoids storing copies
-CREATE TABLE maddr (
-  partition_tag integer   DEFAULT 0,   -- see $sql_partition_tag
-  id         serial       PRIMARY KEY,
-  email      bytea        NOT NULL,    -- full e-mail address
-  domain     varchar(255) NOT NULL,    -- only domain part of the email address
-                                       -- with subdomain fields in reverse
-  CONSTRAINT part_email UNIQUE (partition_tag,email)
-);
-
--- information pertaining to each processed message as a whole;
--- NOTE: records with NULL msgs.content should be ignored by utilities,
---   as such records correspond to messages just being processes, or were lost
-CREATE TABLE msgs (
-  partition_tag integer    DEFAULT 0,   -- see $sql_partition_tag
-  mail_id    varchar(12)   NOT NULL PRIMARY KEY,  -- long-term unique mail id
-  secret_id  varchar(12)   DEFAULT '',  -- authorizes release of mail_id
-  am_id      varchar(20)   NOT NULL,    -- id used in the log
-  time_num   integer NOT NULL CHECK (time_num >= 0),
-                                        -- rx_time: seconds since Unix epoch
-  time_iso timestamp WITH TIME ZONE NOT NULL,-- rx_time: ISO8601 UTC ascii time
-  sid        integer NOT NULL CHECK (sid >= 0), -- sender: maddr.id
-  policy     varchar(255)  DEFAULT '',  -- policy bank path (like macro %p)
-  client_addr varchar(255) DEFAULT '',  -- SMTP client IP address (IPv4 or v6)
-  size       integer NOT NULL CHECK (size >= 0), -- message size in bytes
-  content    char(1),                   -- content type: V/B/S/s/M/H/O/C:
-    -- virus/banned/spam(kill)/spammy(tag2)/bad-mime/bad-header/oversized/clean
-    -- is NULL on partially processed mail
-    -- use binary instead of char for case sensitivity ('S' != 's')
-  quar_type  char(1),                   -- quarantined as: ' '/F/Z/B/Q/M/L
-                                        --  none/file/zipfile/bsmtp/sql/
-                                        --  /mailbox(smtp)/mailbox(lmtp)
-  quar_loc   varchar(255)  DEFAULT '',  -- quarantine location (e.g. file)
-  dsn_sent   char(1),                   -- was DSN sent? Y/N/q (q=quenched)
-  spam_level real,                      -- SA spam level (no boosts)
-  message_id varchar(255)  DEFAULT '',  -- mail Message-ID header field
-  from_addr  varchar(255)  DEFAULT '',  -- mail From header field,    UTF8
-  subject    varchar(255)  DEFAULT '',  -- mail Subject header field, UTF8
-  host       varchar(255)  NOT NULL,    -- hostname where amavisd is running
-  FOREIGN KEY (sid) REFERENCES maddr(id) ON DELETE RESTRICT
-);
-CREATE INDEX msgs_idx_sid      ON msgs (sid);
-CREATE INDEX msgs_idx_mess_id  ON msgs (message_id); -- useful with pen pals
-CREATE INDEX msgs_idx_time_iso ON msgs (time_iso);
-CREATE INDEX msgs_idx_time_num ON msgs (time_num);   -- optional
-
--- per-recipient information related to each processed message;
--- NOTE: records in msgrcpt without corresponding msgs.mail_id record are
---  orphaned and should be ignored and eventually deleted by external utilities
-CREATE TABLE msgrcpt (
-  partition_tag integer    DEFAULT 0,    -- see $sql_partition_tag
-  mail_id    varchar(12)   NOT NULL,     -- (must allow duplicates)
-  rid        integer NOT NULL CHECK (rid >= 0),
-                                    -- recipient: maddr.id (duplicates allowed)
-  ds         char(1)       NOT NULL,     -- delivery status: P/R/B/D/T
-                                         -- pass/reject/bounce/discard/tempfail
-  rs         char(1)       NOT NULL,     -- release status: initialized to ' '
-  bl         char(1)       DEFAULT ' ',  -- sender blacklisted by this recip
-  wl         char(1)       DEFAULT ' ',  -- sender whitelisted by this recip
-  bspam_level real,                      -- spam level + per-recip boost
-  smtp_resp  varchar(255)  DEFAULT '',   -- SMTP response given to MTA
-  FOREIGN KEY (rid)     REFERENCES maddr(id)     ON DELETE RESTRICT,
-  FOREIGN KEY (mail_id) REFERENCES msgs(mail_id) ON DELETE CASCADE
-);
-CREATE INDEX msgrcpt_idx_mail_id  ON msgrcpt (mail_id);
-CREATE INDEX msgrcpt_idx_rid      ON msgrcpt (rid);
-
--- mail quarantine in SQL, enabled by $*_quarantine_method='sql:'
--- NOTE: records in quarantine without corresponding msgs.mail_id record are
---  orphaned and should be ignored and eventually deleted by external utilities
-CREATE TABLE quarantine (
-  partition_tag integer  DEFAULT 0,      -- see $sql_partition_tag
-  mail_id    varchar(12) NOT NULL,       -- long-term unique mail id
-  chunk_ind  integer NOT NULL CHECK (chunk_ind >= 0), -- chunk number, 1..
-  mail_text  bytea   NOT NULL,           -- store mail as chunks of octects
-  PRIMARY KEY (mail_id,chunk_ind),
-  FOREIGN KEY (mail_id) REFERENCES msgs(mail_id) ON DELETE CASCADE
-);
-
--- field msgrcpt.rs is primarily intended for use by quarantine management
--- software; the value assigned by amavisd is a space;
--- a short _preliminary_ list of possible values:
---   'V' => viewed (marked as read)
---   'R' => released (delivered) to this recipient
---   'p' => pending (a status given to messages when the admin received the
---                   request but not yet released; targeted to banned parts)
---   'D' => marked for deletion; a cleanup script may delete it

iRedMail/samples/amavisd/amavisd-2.7.0.mysql

+-- MySQL template for Amavisd-new-2.7.0. Extracted from Amavisd docs/README.sql-mysql.
+-- Used by @lookup_sql_dsn and @storage_sql_dsn
+
+-- local users
+CREATE TABLE users (
+  id         int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,  -- unique id
+  priority   integer      NOT NULL DEFAULT '7',  -- sort field, 0 is low prior.
+  policy_id  integer unsigned NOT NULL DEFAULT '1',  -- JOINs with policy.id
+  email      varbinary(255) NOT NULL UNIQUE,
+  fullname   varchar(255) DEFAULT NULL    -- not used by amavisd-new
+  -- local   char(1)      -- Y/N  (optional field, see note further down)
+);
+
+-- any e-mail address (non- rfc2822-quoted), external or local,
+-- used as senders in wblist
+CREATE TABLE mailaddr (
+  id         int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
+  priority   integer      NOT NULL DEFAULT '7',  -- 0 is low priority
+  email      varbinary(255) NOT NULL UNIQUE
+);
+
+-- per-recipient whitelist and/or blacklist,
+-- puts sender and recipient in relation wb  (white or blacklisted sender)
+CREATE TABLE wblist (
+  rid        integer unsigned NOT NULL,  -- recipient: users.id
+  sid        integer unsigned NOT NULL,  -- sender: mailaddr.id
+  wb         varchar(10)  NOT NULL,  -- W or Y / B or N / space=neutral / score
+  PRIMARY KEY (rid,sid)
+);
+
+CREATE TABLE policy (
+  id  int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
+                                    -- 'id' this is the _only_ required field
+  policy_name      varchar(32),     -- not used by amavisd-new, a comment
+
+  virus_lover           char(1) default NULL,     -- Y/N
+  spam_lover            char(1) default NULL,     -- Y/N
+  unchecked_lovers_maps char(1) default NULL,     -- Y/N
+  banned_files_lover    char(1) default NULL,     -- Y/N
+  bad_header_lover      char(1) default NULL,     -- Y/N
+
+  bypass_virus_checks   char(1) default NULL,     -- Y/N
+  bypass_spam_checks    char(1) default NULL,     -- Y/N
+  bypass_banned_checks  char(1) default NULL,     -- Y/N
+  bypass_header_checks  char(1) default NULL,     -- Y/N
+
+  spam_modifies_subj    char(1) default NULL,     -- Y/N
+
+  virus_quarantine_to      varchar(64) default NULL,
+  spam_quarantine_to       varchar(64) default NULL,
+  banned_quarantine_to     varchar(64) default NULL,
+  unchecked_quarantine_to  varchar(64) default NULL,
+  bad_header_quarantine_to varchar(64) default NULL,
+  clean_quarantine_to      varchar(64) default NULL,
+  archive_quarantine_to    varchar(64) default NULL,
+
+  spam_tag_level  float default NULL, -- higher score inserts spam info headers
+  spam_tag2_level float default NULL, -- inserts 'declared spam' header fields
+  spam_tag3_level float default NULL, -- inserts 'blatant spam' header fields
+  spam_kill_level float default NULL, -- higher score triggers evasive actions
+                                      -- e.g. reject/drop, quarantine, ...
+                                     -- (subject to final_spam_destiny setting)
+  spam_dsn_cutoff_level        float default NULL,
+  spam_quarantine_cutoff_level float default NULL,
+
+  addr_extension_virus      varchar(64) default NULL,
+  addr_extension_spam       varchar(64) default NULL,
+  addr_extension_banned     varchar(64) default NULL,
+  addr_extension_bad_header varchar(64) default NULL,
+
+  warnvirusrecip      char(1)     default NULL, -- Y/N
+  warnbannedrecip     char(1)     default NULL, -- Y/N
+  warnbadhrecip       char(1)     default NULL, -- Y/N
+  newvirus_admin      varchar(64) default NULL,
+  virus_admin         varchar(64) default NULL,
+  banned_admin        varchar(64) default NULL,
+  bad_header_admin    varchar(64) default NULL,
+  spam_admin          varchar(64) default NULL,
+  spam_subject_tag    varchar(64) default NULL,
+  spam_subject_tag2   varchar(64) default NULL,
+  spam_subject_tag3   varchar(64) default NULL,
+  message_size_limit  integer     default NULL, -- max size in bytes, 0 disable
+  banned_rulenames    varchar(64) default NULL, -- comma-separated list of ...
+        -- names mapped through %banned_rules to actual banned_filename tables
+  disclaimer_options  varchar(64) default NULL,
+  forward_method      varchar(64) default NULL,
+  sa_userconf         varchar(64) default NULL,
+  sa_username         varchar(64) default NULL
+);
+
+
+-- R/W part of the dataset (optional)
+--   May reside in the same or in a separate database as lookups database;
+--   REQUIRES SUPPORT FOR TRANSACTIONS; specified in @storage_sql_dsn
+--
+--   MySQL note ( http://dev.mysql.com/doc/mysql/en/storage-engines.html ):
+--     ENGINE is the preferred term, but cannot be used before MySQL 4.0.18.
+--     TYPE is available beginning with MySQL 3.23.0, the first version of
+--     MySQL for which multiple storage engines were available. If you omit
+--     the ENGINE or TYPE option, the default storage engine is used.
+--     By default this is MyISAM.
+--
+--  Please create additional indexes on keys when needed, or drop suggested
+--  ones as appropriate to optimize queries needed by a management application.
+--  See your database documentation for further optimization hints. With MySQL
+--  see Chapter 15 of the reference manual. For example the chapter 15.17 says:
+--  InnoDB does not keep an internal count of rows in a table. To process a
+--  SELECT COUNT(*) FROM T statement, InnoDB must scan an index of the table,
+--  which takes some time if the index is not entirely in the buffer pool.
+--
+--  Wayne Smith adds: When using MySQL with InnoDB one might want to
+--  increase buffer size for both pool and log, and might also want
+--  to change flush settings for a little better performance. Example:
+--    innodb_buffer_pool_size = 384M
+--    innodb_log_buffer_size = 8M
+--    innodb_flush_log_at_trx_commit = 0
+--  The big performance increase is the first two, the third just helps with
+--  lowering disk activity. Consider also adjusting the key_buffer_size.
+
+-- provide unique id for each e-mail address, avoids storing copies
+CREATE TABLE maddr (
+  partition_tag integer      DEFAULT 0, -- see $partition_tag
+  id         bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
+  email      varbinary(255)  NOT NULL,  -- full mail address
+  domain     varchar(255)    NOT NULL,  -- only domain part of the email address
+                                        -- with subdomain fields in reverse
+  CONSTRAINT part_email UNIQUE (partition_tag,email)
+) ENGINE=InnoDB;
+
+CREATE INDEX maddr_idx_email ON maddr (email);
+CREATE INDEX maddr_idx_domain ON maddr (domain);
+
+-- information pertaining to each processed message as a whole;
+-- NOTE: records with NULL msgs.content should be ignored by utilities,
+--   as such records correspond to messages just being processes, or were lost
+-- NOTE: instead of a character field time_iso, one might prefer:
+--   time_iso TIMESTAMP NOT NULL DEFAULT 0,
+--   but the following MUST then be set in amavisd.conf: $timestamp_fmt_mysql=1
+CREATE TABLE msgs (
+  partition_tag integer     DEFAULT 0,   -- see $partition_tag
+  mail_id     varbinary(16) NOT NULL,    -- long-term unique mail id, dflt 12 ch
+  secret_id   varbinary(16) DEFAULT '',  -- authorizes release of mail_id, 12 ch
+  am_id       varchar(20)   NOT NULL,    -- id used in the log
+  time_num    integer unsigned NOT NULL, -- rx_time: seconds since Unix epoch
+  time_iso    char(16)      NOT NULL,    -- rx_time: ISO8601 UTC ascii time
+  sid         bigint unsigned NOT NULL,  -- sender: maddr.id
+  policy      varchar(255)  DEFAULT '',  -- policy bank path (like macro %p)
+  client_addr varchar(255)  DEFAULT '',  -- SMTP client IP address (IPv4 or v6)
+  size        integer unsigned NOT NULL, -- message size in bytes
+  originating char(1) DEFAULT ' ' NOT NULL,  -- sender from inside or auth'd
+  content     char(1),                   -- content type: V/B/U/S/Y/M/H/O/T/C
+    -- virus/banned/unchecked/spam(kill)/spammy(tag2)/
+    -- /bad-mime/bad-header/oversized/mta-err/clean
+    -- is NULL on partially processed mail
+    -- (prior to 2.7.0 the CC_SPAMMY was logged as 's', now 'Y' is used;
+    -- to avoid a need for case-insenstivity in queries)
+  quar_type  char(1),                   -- quarantined as: ' '/F/Z/B/Q/M/L
+                                        --  none/file/zipfile/bsmtp/sql/
+                                        --  /mailbox(smtp)/mailbox(lmtp)
+  quar_loc   varbinary(255) DEFAULT '', -- quarantine location (e.g. file)
+  dsn_sent   char(1),                   -- was DSN sent? Y/N/q (q=quenched)
+  spam_level float,                     -- SA spam level (no boosts)
+  message_id varchar(255)  DEFAULT '',  -- mail Message-ID header field
+  from_addr  varchar(255)  CHARACTER SET utf8 COLLATE utf8_bin  DEFAULT '',
+                                        -- mail From header field,    UTF8
+  subject    varchar(255)  CHARACTER SET utf8 COLLATE utf8_bin  DEFAULT '',
+                                        -- mail Subject header field, UTF8
+  host       varchar(255)  NOT NULL,    -- hostname where amavisd is running
+  PRIMARY KEY (partition_tag,mail_id)
+-- FOREIGN KEY (sid) REFERENCES maddr(id) ON DELETE RESTRICT
+) ENGINE=InnoDB;
+CREATE INDEX msgs_idx_sid      ON msgs (sid);
+CREATE INDEX msgs_idx_mess_id  ON msgs (message_id); -- useful with pen pals
+CREATE INDEX msgs_idx_time_num ON msgs (time_num);
+
+CREATE INDEX msgs_idx_mail_id   ON msgs (mail_id);
+CREATE INDEX msgs_idx_content   ON msgs (content);
+CREATE INDEX msgs_idx_content_time_num ON msgs (content, time_num);
+
+-- alternatively when purging based on time_iso (instead of msgs_idx_time_num):
+-- CREATE INDEX msgs_idx_time_iso ON msgs (time_iso);
+
+-- per-recipient information related to each processed message;
+-- NOTE: records in msgrcpt without corresponding msgs.mail_id record are
+--  orphaned and should be ignored and eventually deleted by external utilities
+CREATE TABLE msgrcpt (
+  partition_tag integer    DEFAULT 0,    -- see $partition_tag
+  mail_id    varbinary(16) NOT NULL,     -- (must allow duplicates)
+  rseqnum    integer  DEFAULT 0   NOT NULL, -- recip's enumeration within msg
+  rid        bigint unsigned NOT NULL,   -- recipient: maddr.id (dupl. allowed)
+  is_local   char(1)  DEFAULT ' ' NOT NULL, -- recip is: Y=local, N=foreign
+  content    char(1)  DEFAULT ' ' NOT NULL, -- content type V/B/U/S/Y/M/H/O/T/C
+  ds         char(1)  NOT NULL,          -- delivery status: P/R/B/D/T
+                                         -- pass/reject/bounce/discard/tempfail
+  rs         char(1)  NOT NULL,          -- release status: initialized to ' '
+  bl         char(1)  DEFAULT ' ',       -- sender blacklisted by this recip
+  wl         char(1)  DEFAULT ' ',       -- sender whitelisted by this recip
+  bspam_level float,                     -- per-recipient (total) spam level
+  smtp_resp  varchar(255)  DEFAULT '',   -- SMTP response given to MTA
+  PRIMARY KEY (partition_tag,mail_id,rseqnum)
+-- FOREIGN KEY (rid)     REFERENCES maddr(id)     ON DELETE RESTRICT,
+-- FOREIGN KEY (mail_id) REFERENCES msgs(mail_id) ON DELETE CASCADE
+) ENGINE=InnoDB;
+CREATE INDEX msgrcpt_idx_mail_id  ON msgrcpt (mail_id);
+CREATE INDEX msgrcpt_idx_rid      ON msgrcpt (rid);
+
+-- mail quarantine in SQL, enabled by $*_quarantine_method='sql:'
+-- NOTE: records in quarantine without corresponding msgs.mail_id record are
+--  orphaned and should be ignored and eventually deleted by external utilities
+CREATE TABLE quarantine (
+  partition_tag integer    DEFAULT 0,    -- see $partition_tag
+  mail_id    varbinary(16) NOT NULL,     -- long-term unique mail id
+  chunk_ind  integer unsigned NOT NULL,  -- chunk number, starting with 1
+  mail_text  blob          NOT NULL,     -- store mail as chunks of octets
+  PRIMARY KEY (partition_tag,mail_id,chunk_ind)
+-- FOREIGN KEY (mail_id) REFERENCES msgs(mail_id) ON DELETE CASCADE
+) ENGINE=InnoDB;
+
+CREATE INDEX quar_idx_mail_id   ON quarantine (mail_id);
+
+-- field msgrcpt.rs is primarily intended for use by quarantine management
+-- software; the value assigned by amavisd is a space;
+-- a short _preliminary_ list of possible values:
+--   'V' => viewed (marked as read)
+--   'R' => released (delivered) to this recipient
+--   'p' => pending (a status given to messages when the admin received the
+--                   request but not yet released; targeted to banned parts)
+--   'D' => marked for deletion; a cleanup script may delete it
+

iRedMail/samples/amavisd/amavisd-2.7.0.pgsql

+-- local users
+CREATE TABLE users (
+  id         serial  PRIMARY KEY,  -- unique id
+  priority   integer NOT NULL DEFAULT '7',  -- sort field, 0 is low prior.
+  policy_id  integer NOT NULL DEFAULT '1' CHECK (policy_id >= 0),
+                                         -- JOINs with policy.id
+  email      bytea   NOT NULL UNIQUE,    -- email address, non-rfc2822-quoted
+  fullname   varchar(255) DEFAULT NULL  -- not used by amavisd-new
+  -- local   char(1)      -- Y/N  (optional field, see note further down)
+);
+
+-- any e-mail address (non- rfc2822-quoted), external or local,
+-- used as senders in wblist
+CREATE TABLE mailaddr (
+  id         serial  PRIMARY KEY,
+  priority   integer NOT NULL DEFAULT '7',  -- 0 is low priority
+  email      bytea   NOT NULL UNIQUE
+);
+
+-- per-recipient whitelist and/or blacklist,
+-- puts sender and recipient in relation wb  (white or blacklisted sender)
+CREATE TABLE wblist (
+  rid        integer NOT NULL CHECK (rid >= 0),  -- recipient: users.id
+  sid        integer NOT NULL CHECK (sid >= 0),  -- sender: mailaddr.id
+  wb         varchar(10) NOT NULL,  -- W or Y / B or N / space=neutral / score
+  PRIMARY KEY (rid,sid)
+);
+
+CREATE TABLE policy (
+  id  serial PRIMARY KEY,           -- 'id' this is the _only_ required field
+  policy_name      varchar(32),     -- not used by amavisd-new, a comment
+
+  virus_lover           char(1) default NULL,     -- Y/N
+  spam_lover            char(1) default NULL,     -- Y/N
+  unchecked_lovers_maps char(1) default NULL,     -- Y/N
+  banned_files_lover    char(1) default NULL,     -- Y/N
+  bad_header_lover      char(1) default NULL,     -- Y/N
+
+  bypass_virus_checks   char(1) default NULL,     -- Y/N
+  bypass_spam_checks    char(1) default NULL,     -- Y/N
+  bypass_banned_checks  char(1) default NULL,     -- Y/N
+  bypass_header_checks  char(1) default NULL,     -- Y/N
+
+  virus_quarantine_to      varchar(64) default NULL,
+  spam_quarantine_to       varchar(64) default NULL,
+  banned_quarantine_to     varchar(64) default NULL,
+  unchecked_quarantine_to  varchar(64) default NULL,
+  bad_header_quarantine_to varchar(64) default NULL,
+  clean_quarantine_to      varchar(64) default NULL,
+  archive_quarantine_to    varchar(64) default NULL,
+
+  spam_tag_level  real default NULL, -- higher score inserts spam info headers
+  spam_tag2_level real default NULL, -- inserts 'declared spam' header fields
+  spam_tag3_level real default NULL, -- inserts 'blatant spam' header fields
+  spam_kill_level real default NULL, -- higher score triggers evasive actions
+                                     -- e.g. reject/drop, quarantine, ...
+                                     -- (subject to final_spam_destiny setting)
+  spam_dsn_cutoff_level        real default NULL,
+  spam_quarantine_cutoff_level real default NULL,
+
+  addr_extension_virus      varchar(64) default NULL,
+  addr_extension_spam       varchar(64) default NULL,
+  addr_extension_banned     varchar(64) default NULL,
+  addr_extension_bad_header varchar(64) default NULL,
+
+  warnvirusrecip      char(1)     default NULL, -- Y/N
+  warnbannedrecip     char(1)     default NULL, -- Y/N
+  warnbadhrecip       char(1)     default NULL, -- Y/N
+  newvirus_admin      varchar(64) default NULL,
+  virus_admin         varchar(64) default NULL,
+  banned_admin        varchar(64) default NULL,
+  bad_header_admin    varchar(64) default NULL,
+  spam_admin          varchar(64) default NULL,
+  spam_subject_tag    varchar(64) default NULL,
+  spam_subject_tag2   varchar(64) default NULL,
+  spam_subject_tag3   varchar(64) default NULL,
+  message_size_limit  integer     default NULL, -- max size in bytes, 0 disable
+  banned_rulenames    varchar(64) default NULL, -- comma-separated list of ...
+        -- names mapped through %banned_rules to actual banned_filename tables
+  disclaimer_options  varchar(64) default NULL,
+  forward_method      varchar(64) default NULL,
+  sa_userconf         varchar(64) default NULL,
+  sa_username         varchar(64) default NULL
+);
+
+
+-- R/W part of the dataset (optional)
+--   May reside in the same or in a separate database as lookups database;
+--   REQUIRES SUPPORT FOR TRANSACTIONS; specified in @storage_sql_dsn
+--
+--  Please create additional indexes on keys when needed, or drop suggested
+--  ones as appropriate to optimize queries needed by a management application.
+--  See your database documentation for further optimization hints.
+
+-- provide unique id for each e-mail address, avoids storing copies
+CREATE TABLE maddr (
+  partition_tag integer   DEFAULT 0,   -- see $partition_tag
+  id         serial       PRIMARY KEY,
+  email      bytea        NOT NULL,    -- full e-mail address
+  domain     varchar(255) NOT NULL,    -- only domain part of the email address
+                                       -- with subdomain fields in reverse
+  CONSTRAINT part_email UNIQUE (partition_tag,email)
+);
+
+-- information pertaining to each processed message as a whole;
+-- NOTE: records with NULL msgs.content should be ignored by utilities,
+--   as such records correspond to messages just being processes, or were lost
+CREATE TABLE msgs (
+  partition_tag integer     DEFAULT 0,  -- see $partition_tag
+  mail_id     bytea         NOT NULL,   -- long-term unique mail id, dflt 12 ch
+  secret_id   bytea         DEFAULT '', -- authorizes release of mail_id, 12 ch
+  am_id       varchar(20)   NOT NULL,   -- id used in the log
+  time_num    integer NOT NULL CHECK (time_num >= 0),
+                                        -- rx_time: seconds since Unix epoch
+  time_iso timestamp WITH TIME ZONE NOT NULL,-- rx_time: ISO8601 UTC ascii time
+  sid         integer NOT NULL CHECK (sid >= 0), -- sender: maddr.id
+  policy      varchar(255)  DEFAULT '', -- policy bank path (like macro %p)
+  client_addr varchar(255)  DEFAULT '', -- SMTP client IP address (IPv4 or v6)
+  size        integer NOT NULL CHECK (size >= 0), -- message size in bytes
+  originating char(1) DEFAULT ' ' NOT NULL,  -- sender from inside or auth'd
+  content     char(1),                   -- content type: V/B/U/S/Y/M/H/O/T/C
+    -- virus/banned/unchecked/spam(kill)/spammy(tag2)/
+    -- /bad-mime/bad-header/oversized/mta-err/clean
+    -- is NULL on partially processed mail
+    -- (prior to 2.7.0 the CC_SPAMMY was logged as 's', now 'Y' is used;
+    --- to avoid a need for case-insenstivity in queries)
+  quar_type  char(1),                   -- quarantined as: ' '/F/Z/B/Q/M/L
+                                        --  none/file/zipfile/bsmtp/sql/
+                                        --  /mailbox(smtp)/mailbox(lmtp)
+  quar_loc   varchar(255)  DEFAULT '',  -- quarantine location (e.g. file)
+  dsn_sent   char(1),                   -- was DSN sent? Y/N/q (q=quenched)
+  spam_level real,                      -- SA spam level (no boosts)
+  message_id varchar(255)  DEFAULT '',  -- mail Message-ID header field
+  from_addr  varchar(255)  DEFAULT '',  -- mail From header field,    UTF8
+  subject    varchar(255)  DEFAULT '',  -- mail Subject header field, UTF8
+  host       varchar(255)  NOT NULL,    -- hostname where amavisd is running
+  PRIMARY KEY (partition_tag,mail_id)
+--FOREIGN KEY (sid) REFERENCES maddr(id) ON DELETE RESTRICT
+);
+CREATE INDEX msgs_idx_sid      ON msgs (sid);
+CREATE INDEX msgs_idx_mess_id  ON msgs (message_id); -- useful with pen pals
+CREATE INDEX msgs_idx_time_iso ON msgs (time_iso);
+CREATE INDEX msgs_idx_time_num ON msgs (time_num);   -- optional
+
+-- per-recipient information related to each processed message;
+-- NOTE: records in msgrcpt without corresponding msgs.mail_id record are
+--  orphaned and should be ignored and eventually deleted by external utilities
+CREATE TABLE msgrcpt (
+  partition_tag integer DEFAULT 0,  -- see $partition_tag
+  mail_id    bytea    NOT NULL,     -- (must allow duplicates)
+  rseqnum    integer  DEFAULT 0   NOT NULL, -- recip's enumeration within msg
+  rid        integer  NOT NULL,     -- recipient: maddr.id (duplicates allowed)
+  is_local   char(1)  DEFAULT ' ' NOT NULL, -- recip is: Y=local, N=foreign
+  content    char(1)  DEFAULT ' ' NOT NULL, -- content type V/B/U/S/Y/M/H/O/T/C
+  ds         char(1)  NOT NULL,     -- delivery status: P/R/B/D/T
+                                    -- pass/reject/bounce/discard/tempfail
+  rs         char(1)  NOT NULL,     -- release status: initialized to ' '
+  bl         char(1)  DEFAULT ' ',  -- sender blacklisted by this recip
+  wl         char(1)  DEFAULT ' ',  -- sender whitelisted by this recip
+  bspam_level real,                 -- per-recipient (total) spam level
+  smtp_resp  varchar(255) DEFAULT '', -- SMTP response given to MTA
+  PRIMARY KEY (partition_tag,mail_id,rseqnum)
+--FOREIGN KEY (rid)     REFERENCES maddr(id)     ON DELETE RESTRICT,
+--FOREIGN KEY (mail_id) REFERENCES msgs(mail_id) ON DELETE CASCADE
+);
+CREATE INDEX msgrcpt_idx_mail_id  ON msgrcpt (mail_id);
+CREATE INDEX msgrcpt_idx_rid      ON msgrcpt (rid);
+
+-- mail quarantine in SQL, enabled by $*_quarantine_method='sql:'
+-- NOTE: records in quarantine without corresponding msgs.mail_id record are
+--  orphaned and should be ignored and eventually deleted by external utilities
+CREATE TABLE quarantine (
+  partition_tag integer  DEFAULT 0,      -- see $partition_tag
+  mail_id    bytea   NOT NULL,           -- long-term unique mail id
+  chunk_ind  integer NOT NULL CHECK (chunk_ind >= 0), -- chunk number, 1..
+  mail_text  bytea   NOT NULL,           -- store mail as chunks of octects
+  PRIMARY KEY (partition_tag,mail_id,chunk_ind)
+--FOREIGN KEY (mail_id) REFERENCES msgs(mail_id) ON DELETE CASCADE
+);
+
+-- field msgrcpt.rs is primarily intended for use by quarantine management
+-- software; the value assigned by amavisd is a space;
+-- a short _preliminary_ list of possible values:
+--   'V' => viewed (marked as read)
+--   'R' => released (delivered) to this recipient
+--   'p' => pending (a status given to messages when the admin received the
+--                   request but not yet released; targeted to banned parts)
+--   'D' => marked for deletion; a cleanup script may delete it

iRedMail/samples/amavisd/amavisd.mysql

+-- MySQL template for Amavisd. Extracted from Amavisd docs/README.sql-mysql.
+-- Used by @lookup_sql_dsn and @storage_sql_dsn
+
+-- local users
+CREATE TABLE users (
+  id         int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,  -- unique id
+  priority   integer      NOT NULL DEFAULT '7',  -- sort field, 0 is low prior.
+  policy_id  integer unsigned NOT NULL DEFAULT '1',  -- JOINs with policy.id
+  email      varbinary(255) NOT NULL UNIQUE,
+  fullname   varchar(255) DEFAULT NULL    -- not used by amavisd-new
+  -- local   char(1)      -- Y/N  (optional field, see note further down)
+);
+
+-- any e-mail address (non- rfc2822-quoted), external or local,
+-- used as senders in wblist
+CREATE TABLE mailaddr (
+  id         int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
+  priority   integer      NOT NULL DEFAULT '7',  -- 0 is low priority
+  email      varbinary(255) NOT NULL UNIQUE
+);
+
+-- per-recipient whitelist and/or blacklist,
+-- puts sender and recipient in relation wb  (white or blacklisted sender)
+CREATE TABLE wblist (
+  rid        integer unsigned NOT NULL,  -- recipient: users.id
+  sid        integer unsigned NOT NULL,  -- sender: mailaddr.id
+  wb         varchar(10)  NOT NULL,  -- W or Y / B or N / space=neutral / score
+  PRIMARY KEY (rid,sid)
+);
+
+CREATE TABLE policy (
+  id  int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
+                                    -- 'id' this is the _only_ required field
+  policy_name      varchar(32),     -- not used by amavisd-new, a comment
+
+  virus_lover          char(1) default NULL,     -- Y/N
+  spam_lover           char(1) default NULL,     -- Y/N
+  banned_files_lover   char(1) default NULL,     -- Y/N
+  bad_header_lover     char(1) default NULL,     -- Y/N
+
+  bypass_virus_checks  char(1) default NULL,     -- Y/N
+  bypass_spam_checks   char(1) default NULL,     -- Y/N
+  bypass_banned_checks char(1) default NULL,     -- Y/N
+  bypass_header_checks char(1) default NULL,     -- Y/N
+
+  spam_modifies_subj   char(1) default NULL,     -- Y/N
+
+  virus_quarantine_to      varchar(64) default NULL,
+  spam_quarantine_to       varchar(64) default NULL,
+  banned_quarantine_to     varchar(64) default NULL,
+  bad_header_quarantine_to varchar(64) default NULL,
+  clean_quarantine_to      varchar(64) default NULL,
+  other_quarantine_to      varchar(64) default NULL,
+
+  spam_tag_level  float default NULL, -- higher score inserts spam info headers
+  spam_tag2_level float default NULL, -- inserts 'declared spam' header fields
+  spam_kill_level float default NULL, -- higher score triggers evasive actions
+                                      -- e.g. reject/drop, quarantine, ...
+                                     -- (subject to final_spam_destiny setting)
+  spam_dsn_cutoff_level        float default NULL,
+  spam_quarantine_cutoff_level float default NULL,
+
+  addr_extension_virus      varchar(64) default NULL,
+  addr_extension_spam       varchar(64) default NULL,
+  addr_extension_banned     varchar(64) default NULL,
+  addr_extension_bad_header varchar(64) default NULL,
+
+  warnvirusrecip      char(1)     default NULL, -- Y/N
+  warnbannedrecip     char(1)     default NULL, -- Y/N
+  warnbadhrecip       char(1)     default NULL, -- Y/N
+  newvirus_admin      varchar(64) default NULL,
+  virus_admin         varchar(64) default NULL,
+  banned_admin        varchar(64) default NULL,
+  bad_header_admin    varchar(64) default NULL,
+  spam_admin          varchar(64) default NULL,
+  spam_subject_tag    varchar(64) default NULL,
+  spam_subject_tag2   varchar(64) default NULL,
+  message_size_limit  integer     default NULL, -- max size in bytes, 0 disable
+  banned_rulenames    varchar(64) default NULL  -- comma-separated list of ...
+        -- names mapped through %banned_rules to actual banned_filename tables
+);
+
+
+
+-- R/W part of the dataset (optional)
+--   May reside in the same or in a separate database as lookups database;
+--   REQUIRES SUPPORT FOR TRANSACTIONS; specified in @storage_sql_dsn
+--
+--   MySQL note ( http://dev.mysql.com/doc/mysql/en/storage-engines.html ):
+--     ENGINE is the preferred term, but cannot be used before MySQL 4.0.18.
+--     TYPE is available beginning with MySQL 3.23.0, the first version of
+--     MySQL for which multiple storage engines were available. If you omit
+--     the ENGINE or TYPE option, the default storage engine is used.
+--     By default this is MyISAM.
+--
+--  Please create additional indexes on keys when needed, or drop suggested
+--  ones as appropriate to optimize queries needed by a management application.
+--  See your database documentation for further optimization hints. With MySQL
+--  see Chapter 15 of the reference manual. For example the chapter 15.17 says:
+--  InnoDB does not keep an internal count of rows in a table. To process a
+--  SELECT COUNT(*) FROM T statement, InnoDB must scan an index of the table,
+--  which takes some time if the index is not entirely in the buffer pool.
+--
+--  Wayne Smith adds: When using MySQL with InnoDB one might want to
+--  increase buffer size for both pool and log, and might also want
+--  to change flush settings for a little better performance. Example:
+--    innodb_buffer_pool_size = 384M
+--    innodb_log_buffer_size = 8M
+--    innodb_flush_log_at_trx_commit = 0
+--  The big performance increase is the first two, the third just helps with
+--  lowering disk activity. Consider also adjusting the key_buffer_size.
+
+-- provide unique id for each e-mail address, avoids storing copies
+CREATE TABLE maddr (
+  partition_tag integer      DEFAULT 0, -- see $partition_tag
+  id         bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
+  email      varbinary(255)  NOT NULL,  -- full mail address
+  domain     varchar(255)    NOT NULL,  -- only domain part of the email address
+                                        -- with subdomain fields in reverse
+  CONSTRAINT part_email UNIQUE (partition_tag,email)
+) ENGINE=InnoDB;
+
+CREATE INDEX maddr_idx_email ON maddr (email);
+CREATE INDEX maddr_idx_domain ON maddr (domain);
+
+-- information pertaining to each processed message as a whole;
+-- NOTE: records with NULL msgs.content should be ignored by utilities,
+--   as such records correspond to messages just being processes, or were lost
+-- NOTE: instead of a character field time_iso, one might prefer:
+--   time_iso TIMESTAMP NOT NULL DEFAULT 0,
+--   but the following MUST then be set in amavisd.conf: $timestamp_fmt_mysql=1
+CREATE TABLE msgs (
+  partition_tag integer    DEFAULT 0,   -- see $partition_tag
+  mail_id     varbinary(16) NOT NULL,    -- long-term unique mail id, dflt 12 ch
+  secret_id   varbinary(16) DEFAULT '',  -- authorizes release of mail_id, 12 ch
+  am_id       varchar(20)   NOT NULL,    -- id used in the log
+  time_num    integer unsigned NOT NULL, -- rx_time: seconds since Unix epoch
+  time_iso    char(16)      NOT NULL,    -- rx_time: ISO8601 UTC ascii time
+  sid         bigint unsigned NOT NULL,  -- sender: maddr.id
+  policy      varchar(255) DEFAULT '',   -- policy bank path (like macro %p)
+  client_addr varchar(255) DEFAULT '',   -- SMTP client IP address (IPv4 or v6)
+  size        integer unsigned NOT NULL, -- message size in bytes
+  originating char(1) DEFAULT ' ' NOT NULL,  -- sender from inside or auth'd
+  content     char(1),                   -- content type: V/B/U/S/Y/M/H/O/T/C
+    -- virus/banned/unchecked/spam(kill)/spammy(tag2)/
+    -- /bad-mime/bad-header/oversized/mta-err/clean
+    -- is NULL on partially processed mail
+    -- (prior to 2.7.0 the CC_SPAMMY was logged as 's', now 'Y' is used;
+    -- to avoid a need for case-insenstivity in queries)
+  quar_type  char(1),                   -- quarantined as: ' '/F/Z/B/Q/M/L
+                                        --  none/file/zipfile/bsmtp/sql/
+                                        --  /mailbox(smtp)/mailbox(lmtp)
+  quar_loc   varbinary(255) DEFAULT '', -- quarantine location (e.g. file)
+  dsn_sent   char(1),                   -- was DSN sent? Y/N/q (q=quenched)
+  spam_level float,                     -- SA spam level (no boosts)
+  message_id varchar(255)  DEFAULT '',  -- mail Message-ID header field
+  from_addr  varchar(255)  CHARACTER SET utf8 COLLATE utf8_bin  DEFAULT '',
+                                        -- mail From header field,    UTF8
+  subject    varchar(255)  CHARACTER SET utf8 COLLATE utf8_bin  DEFAULT '',
+                                        -- mail Subject header field, UTF8
+  host       varchar(255)  NOT NULL,    -- hostname where amavisd is running
+  PRIMARY KEY (partition_tag,mail_id)