Commits

Zhang Huangbin committed f7a73ea

PGSQL support in Amavisd. [DONE]
Code cleanup.

  • Participants
  • Parent commits 4b1987a

Comments (0)

Files changed (19)

File iRedMail/conf/amavisd

 export AMAVISD_DB_NAME='amavisd'
 export AMAVISD_DB_USER='amavisd'
 export AMAVISD_DB_PASSWD="$(${RANDOM_STRING})"
-export AMAVISD_DB_SQL_TMPL="${SAMPLE_DIR}/amavisd.mysql"
+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}"
 
 # Altermime.
     # Override default Amavisd MySQL template file
     # openSUSE-12.1 ships Amavisd-new-2.7.0
     if [ X"${DISTRO_VERSION}" != X"11.3" -a X"${DISTRO_VERSION}" != X"11.4" ]; then
-        export AMAVISD_DB_SQL_TMPL="${SAMPLE_DIR}/amavisd-2.7.0.mysql"
+        export AMAVISD_DB_MYSQL_TMPL="${SAMPLE_DIR}/amavisd-2.7.0.mysql"
     fi
 
 elif [ X"${DISTRO}" == X"DEBIAN" -o X"${DISTRO}" == X"UBUNTU" ]; then

File iRedMail/conf/global

 export STORAGE_NODE='vmail1'
 
 # For distribute deployment.
+# MySQL server
 export MYSQL_SERVER='127.0.0.1'
-export MYSQL_PORT='3306'
+export MYSQL_SERVER_PORT='3306'
+# PGSQL server
+export PGSQL_SERVER='localhost'
+export PGSQL_SERVER_PORT='5432'
+# POP3/IMAP server (Dovecot)
 export IMAP_SERVER='127.0.0.1'
+# SMTP server (Postfix)
 export SMTP_SERVER='127.0.0.1'
+# Amavisd
 export AMAVISD_SERVER='127.0.0.1'
 
 # For managesieve service and software.

File iRedMail/conf/mysql

 #---------------------------------------------------------------------
 
 # Variables for MySQL database server and related.
-
-export MYSQL_SOCKET='/var/lib/mysql/mysql.sock'
-export MYSQL_ROOT_USER='root'
+# MYSQL_SERVER and MYSQL_SERVER_PORT are defined in conf/global.
+# SQL_SERVER and SQL_SERVER_PORT are defined in dialog/config_via_dialog.sh.
 
 # Use lowercase variable name for IP address.
-# Will set SQL_SERVER in functions/backend.sh.
 if [ X"${MYSQL_SERVER}" == X"localhost" ]; then
     export mysql_server='127.0.0.1'
 else
     export mysql_server="${MYSQL_SERVER}"
 fi
 
+export MYSQL_SOCKET='/var/lib/mysql/mysql.sock'
+export MYSQL_ROOT_USER='root'
+
 # MySQL config file.
 if [ X"${DISTRO}" == X"RHEL" ]; then
     export MYSQL_MY_CNF='/etc/my.cnf'

File iRedMail/conf/postgresql

 #---------------------------------------------------------------------
 
 # Variables for PostgreSQL database server and related.
+# PGSQL_SERVER and PGSQL_SERVER_PORT are defined in conf/global.
+# SQL_SERVER and SQL_SERVER_PORT are defined in dialog/config_via_dialog.sh.
 
 export PGSQL_SYS_USER='postgres'
 export PGSQL_SYS_GROUP='postgres'
 
-# PGSQL server address
-# Will set SQL_SERVER in functions/backend.sh.
-export PGSQL_SERVER='localhost'
-export PGSQL_PORT='5432'
 export PGSQL_ROOT_USER='postgres'
 
 export PGSQL_SYS_USER_HOME='/var/lib/postgresql'

File 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
+    export SQL_SERVER="${MYSQL_SERVER}"
+    export SQL_SERVER_PORT="${MYSQL_SERVER_PORT}"
+
     . ${DIALOG_DIR}/mysql_config.sh
 elif [ X"${BACKEND}" == X"PGSQL" ]; then
+    export SQL_SERVER="${PGSQL_SERVER}"
+    export SQL_SERVER_PORT="${PGSQL_SERVER_PORT}"
+
     . ${DIALOG_DIR}/pgsql_config.sh
-else
-    :
 fi
 
 # Virtual domain configuration.

File iRedMail/dialog/optional_components.sh

 " 20 76 8 \
     "DKIM signing/verification" "DomainKeys Identified Mail" "on" \
     "Roundcubemail" "WebMail program (PHP, AJAX)" "on" \
-    "phpPgAdmin" "Web-based MySQL management tool" "on" \
-    "Awstats" "Advanced web and mail log analyzer" "on" \
     "Fail2ban" "Ban IP with too many password failures" "on" \
     2>${tmp_config_optional_components}
-else
-    # No hook for other backend yet.
-    :
+
+    #"phpPgAdmin" "Web-based MySQL management tool" "on" \
+    #"Awstats" "Advanced web and mail log analyzer" "on" \
 fi
 
 OPTIONAL_COMPONENTS="$(cat ${tmp_config_optional_components})"

File iRedMail/functions/amavisd.sh

 #\$notify_virus_recips_templ= read_text('/var/amavis/notify_virus_recips.txt');
 #\$notify_spam_sender_templ = read_text('/var/amavis/notify_spam_sender.txt');
 #\$notify_spam_admin_templ  = read_text('/var/amavis/notify_spam_admin.txt');
+
+\$sql_allow_8bit_address = 1;
+\$timestamp_fmt_mysql = 1;
 EOF
 
     # Write dkim settings.
     fi
 
     # Integrate SQL. Used to store incoming & outgoing related mail information.
-    cat >> ${AMAVISD_CONF} <<EOF
-\$sql_allow_8bit_address = 1;
-\$timestamp_fmt_mysql = 1;
+    if [ X"${BACKEND}" == X"OPENLDAP" -o X"${BACKEND}" == X"MYSQL" ]; then
+        cat >> ${AMAVISD_CONF} <<EOF
 @storage_sql_dsn = (
-    ['DBI:mysql:database=${AMAVISD_DB_NAME};host=${MYSQL_SERVER};port=${MYSQL_PORT}', '${AMAVISD_DB_USER}', '${AMAVISD_DB_PASSWD}'],
+    ['DBI:mysql:database=${AMAVISD_DB_NAME};host=${SQL_SERVER};port=${SQL_SERVER_PORT}', '${AMAVISD_DB_USER}', '${AMAVISD_DB_PASSWD}'],
 );
 EOF
+    elif [ X"${BACKEND}" == X"MYSQL" ]; then
+        cat >> ${AMAVISD_CONF} <<EOF
+@storage_sql_dsn = (
+    ['DBI:Pg:database=${AMAVISD_DB_NAME};host=${SQL_SERVER};port=${SQL_SERVER_PORT}', '${AMAVISD_DB_USER}', '${AMAVISD_DB_PASSWD}'],
+);
+EOF
+    fi
 
-    # Lookup agains MySQL, for MySQL backend only.
-    if [ X"${BACKEND}" == X"MYSQL" ]; then
+    # SQL lookup.
+    if [ X"${BACKEND}" == X"OPENLDAP" ]; then
+        cat >> ${AMAVISD_CONF} <<EOF
+#@lookup_sql_dsn = @storage_sql_dsn;
+EOF
+    elif [ X"${BACKEND}" == X"MYSQL" ]; then
+        # MySQL backend
         cat >> ${AMAVISD_CONF} <<EOF
 # Uncomment below two lines to lookup virtual mail domains from MySQL database.
 #@lookup_sql_dsn =  (
-#    ['DBI:mysql:database=${VMAIL_DB};host=${MYSQL_SERVER};port=${MYSQL_PORT}', '${VMAIL_DB_BIND_USER}', '${VMAIL_DB_BIND_PASSWD}'],
+#    ['DBI:mysql:database=${VMAIL_DB};host=${MYSQL_SERVER};port=${MYSQL_SERVER_PORT}', '${VMAIL_DB_BIND_USER}', '${VMAIL_DB_BIND_PASSWD}'],
 #);
 # For Amavisd-new-2.7.0 and later versions. Placeholder '%d' is available in Amavisd-2.7.0+.
 #\$sql_select_policy = "SELECT domain FROM domain WHERE domain='%d'";
 # WARNING: IN() may cause MySQL lookup performance issue.
 #\$sql_select_policy = "SELECT domain FROM domain WHERE CONCAT('@', domain) IN (%k)";
 EOF
-    elif [ X"${BACKEND}" == X"OPENLDAP" ]; then
+    elif [ X"${BACKEND}" == X"PGSQL" ]; then
         cat >> ${AMAVISD_CONF} <<EOF
-#@lookup_sql_dsn = @storage_sql_dsn;
+# Uncomment below two lines to lookup virtual mail domains from PostgreSQL database.
+#@lookup_sql_dsn =  (
+#    ['DBI:Pg:database=${VMAIL_DB};host=${MYSQL_SERVER};port=${MYSQL_SERVER_PORT}', '${VMAIL_DB_BIND_USER}', '${VMAIL_DB_BIND_PASSWD}'],
+#);
+# For Amavisd-new-2.7.0 and later versions. Placeholder '%d' is available in Amavisd-2.7.0+.
+#\$sql_select_policy = "SELECT domain FROM domain WHERE domain='%d'";
+
+# For Amavisd-new-2.6.x.
+# WARNING: IN() may cause MySQL lookup performance issue.
+#\$sql_select_policy = "SELECT domain FROM domain WHERE CONCAT('@', domain) IN (%k)";
 EOF
     fi
 
         - Database name: ${AMAVISD_DB_NAME}
         - Database user: ${AMAVISD_DB_USER}
         - Database password: ${AMAVISD_DB_PASSWD}
-        - SQL template: ${AMAVISD_DB_SQL_TMPL}
+        - SQL template: ${AMAVISD_DB_MYSQL_TMPL}
 
 EOF
 
 {
     ECHO_DEBUG "Import Amavisd database and privileges."
 
-    mysql -h${MYSQL_SERVER} -P${MYSQL_PORT} -u${MYSQL_ROOT_USER} -p"${MYSQL_ROOT_PASSWD}" <<EOF
-/* Create database and grant privileges. */
+    if [ X"${BACKEND}" == X"OPENLDAP" -o X"${BACKEND}" == X"MYSQL" ]; then
+        mysql -h${MYSQL_SERVER} -P${MYSQL_SERVER_PORT} -u${MYSQL_ROOT_USER} -p"${MYSQL_ROOT_PASSWD}" <<EOF
+-- Create database
 CREATE DATABASE ${AMAVISD_DB_NAME} DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
+
+-- Grant privileges
 GRANT SELECT,INSERT,UPDATE,DELETE ON ${AMAVISD_DB_NAME}.* TO "${AMAVISD_DB_USER}"@localhost IDENTIFIED BY '${AMAVISD_DB_PASSWD}';
 
-/* Import Amavisd SQL template. */
+-- Import Amavisd SQL template
 USE ${AMAVISD_DB_NAME};
-SOURCE ${AMAVISD_DB_SQL_TMPL};
+SOURCE ${AMAVISD_DB_MYSQL_TMPL};
 
 FLUSH PRIVILEGES;
 EOF
+    elif [ X"${BACKEND}" == X"PGSQL" ]; then
+        cp -f ${AMAVISD_DB_PGSQL_TMPL} ${PGSQL_SYS_USER_HOME}/amavisd.sql >/dev/null
+        chmod 0777 ${PGSQL_SYS_USER_HOME}/amavisd.sql >/dev/null
+
+        su - ${PGSQL_SYS_USER} -c "psql" >/dev/null  <<EOF
+-- Create database
+CREATE DATABASE ${AMAVISD_DB_NAME} WITH TEMPLATE template0 ENCODING 'UTF8';
+
+-- Create user
+CREATE USER ${AMAVISD_DB_USER} WITH ENCRYPTED PASSWORD '${AMAVISD_DB_PASSWD}' NOSUPERUSER NOCREATEDB NOCREATEROLE;
+
+-- Import Amavisd SQL template
+\c ${AMAVISD_DB_NAME};
+\i ${PGSQL_SYS_USER_HOME}/amavisd.sql;
+
+-- Grant privileges
+GRANT SELECT,INSERT,UPDATE,DELETE ON maddr,mailaddr,msgrcpt,msgs,policy,quarantine,users,wblist TO ${AMAVISD_DB_USER};
+GRANT SELECT,UPDATE,USAGE ON maddr_id_seq,mailaddr_id_seq,policy_id_seq,users_id_seq TO ${AMAVISD_DB_USER};
+EOF
+        rm -f ${PGSQL_SYS_USER_HOME}/amavisd.sql >/dev/null
+    fi
 
     echo 'export status_amavisd_import_sql="DONE"' >> ${STATUS_FILE}
 }

File iRedMail/functions/awstats.sh

 
     AuthMYSQLEnable On
     AuthMySQLHost ${MYSQL_SERVER}
-    AuthMySQLPort ${MYSQL_PORT}
+    AuthMySQLPort ${MYSQL_SERVER_PORT}
     AuthMySQLUser ${VMAIL_DB_BIND_USER}
     AuthMySQLPassword ${VMAIL_DB_BIND_PASSWD}
     AuthMySQLDB ${VMAIL_DB}

File iRedMail/functions/backend.sh

         # Initialize MySQL database server.
         check_status_before_run mysql_initialize
     elif [ X"${BACKEND}" == X"MYSQL" ]; then
-        export SQL_SERVER="${MYSQL_SERVER}"
-
         check_status_before_run mysql_initialize
         check_status_before_run mysql_import_vmail_users
     elif [ X"${BACKEND}" == X"PGSQL" ]; then
-        export SQL_SERVER="${PGSQL_SERVER}"
-
         check_status_before_run pgsql_initialize
         check_status_before_run pgsql_import_vmail_users
     else

File iRedMail/functions/cluebringer.sh

         perl -pi -e 's/^#(DSN=DBI:mysql:).*/${1}host=$ENV{MYSQL_SERVER};database=$ENV{CLUEBRINGER_DB_NAME};user=$ENV{CLUEBRINGER_DB_USER};password=$ENV{CLUEBRINGER_DB_PASSWD}/' ${CLUEBRINGER_CONF}
         perl -pi -e 's/^(DB_Type=).*/${1}mysql/' ${CLUEBRINGER_CONF}
         perl -pi -e 's/^(DB_Host=).*/${1}$ENV{MYSQL_SERVER}/' ${CLUEBRINGER_CONF}
-        perl -pi -e 's/^(DB_Port=).*/${1}$ENV{MYSQL_PORT}/' ${CLUEBRINGER_CONF}
+        perl -pi -e 's/^(DB_Port=).*/${1}$ENV{MYSQL_SERVER_PORT}/' ${CLUEBRINGER_CONF}
     elif [ X"${BACKEND}" == X"PGSQL" ]; then
         perl -pi -e 's/^#(DSN=DBI:Pg:).*/${1}host=$ENV{PGSQL_SERVER};database=$ENV{CLUEBRINGER_DB_NAME};user=$ENV{CLUEBRINGER_DB_USER};password=$ENV{CLUEBRINGER_DB_PASSWD}/' ${CLUEBRINGER_CONF}
         perl -pi -e 's/^(DB_Type=).*/${1}pgsql/' ${CLUEBRINGER_CONF}
         perl -pi -e 's/^(DB_Host=).*/${1}$ENV{PGSQL_SERVER}/' ${CLUEBRINGER_CONF}
-        perl -pi -e 's/^(DB_Port=).*/${1}$ENV{PGSQL_PORT}/' ${CLUEBRINGER_CONF}
+        perl -pi -e 's/^(DB_Port=).*/${1}$ENV{PGSQL_SERVER_PORT}/' ${CLUEBRINGER_CONF}
     fi
 
     # Database
 
             cat >> ${tmp_sql} <<EOF
 GRANT SELECT,INSERT,UPDATE,DELETE ON access_control,amavis_rules,checkhelo,checkhelo_blacklist,checkhelo_tracking,checkhelo_whitelist,checkspf,greylisting,greylisting_autoblacklist,greylisting_autowhitelist,greylisting_tracking,greylisting_whitelist,policies,policy_group_members,policy_groups,policy_members,quotas,quotas_limits,quotas_tracking,session_tracking TO ${CLUEBRINGER_DB_USER};
+GRANT SELECT,UPDATE,USAGE ON access_control_id_seq,amavis_rules_id_seq,checkhelo_blacklist_id_seq,checkhelo_whitelist_id_seq,checkspf_id_seq,greylisting_autoblacklist_id_seq,greylisting_autowhitelist_id_seq,greylisting_whitelist_id_seq,policy_group_members_id_seq,policy_groups_id_seq,policy_members_id_seq,quotas_id_seq,quotas_limits_id_seq TO ${CLUEBRINGER_DB_USER};
 EOF
         fi
 
     # Initial cluebringer db.
     # Enable greylisting on all inbound emails by default.
     if [ X"${BACKEND}" == X"OPENLDAP" -o X"${BACKEND}" == X"MYSQL" ]; then
-        mysql -h${MYSQL_SERVER} -P${MYSQL_PORT} -u${MYSQL_ROOT_USER} -p"${MYSQL_ROOT_PASSWD}" <<EOF
+        mysql -h${MYSQL_SERVER} -P${MYSQL_SERVER_PORT} -u${MYSQL_ROOT_USER} -p"${MYSQL_ROOT_PASSWD}" <<EOF
 -- Initialize
 $(cat ${tmp_sql})
 
 
     AuthMYSQLEnable On
     AuthMySQLHost ${MYSQL_SERVER}
-    AuthMySQLPort ${MYSQL_PORT}
+    AuthMySQLPort ${MYSQL_SERVER_PORT}
     AuthMySQLUser ${VMAIL_DB_BIND_USER}
     AuthMySQLPassword ${VMAIL_DB_BIND_PASSWD}
     AuthMySQLDB ${VMAIL_DB}

File iRedMail/functions/dovecot1.sh

         # which used to store realtime quota.
         if [ X"${BACKEND}" == X"OPENLDAP" -a X"${USE_IREDADMIN}" != X"YES" ]; then
             # If iRedAdmin is not used, create database and import table here.
-            mysql -h${MYSQL_SERVER} -P${MYSQL_PORT} -u${MYSQL_ROOT_USER} -p"${MYSQL_ROOT_PASSWD}" <<EOF
+            mysql -h${MYSQL_SERVER} -P${MYSQL_SERVER_PORT} -u${MYSQL_ROOT_USER} -p"${MYSQL_ROOT_PASSWD}" <<EOF
 # Create databases.
 CREATE DATABASE IF NOT EXISTS ${IREDADMIN_DB_NAME} DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
 
         # which used to store realtime quota.
         if [ X"${BACKEND}" == X"OPENLDAP" -a X"${USE_IREDADMIN}" != X"YES" ]; then
             # If iRedAdmin is not used, create database and import table here.
-            mysql -h${MYSQL_SERVER} -P${MYSQL_PORT} -u${MYSQL_ROOT_USER} -p"${MYSQL_ROOT_PASSWD}" <<EOF
+            mysql -h${MYSQL_SERVER} -P${MYSQL_SERVER_PORT} -u${MYSQL_ROOT_USER} -p"${MYSQL_ROOT_PASSWD}" <<EOF
 # Create databases.
 CREATE DATABASE IF NOT EXISTS ${IREDADMIN_DB_NAME} DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
 

File iRedMail/functions/dovecot2.sh

     # which used to store realtime quota.
     if [ X"${BACKEND}" == X"OPENLDAP" -a X"${USE_IREDADMIN}" != X"YES" ]; then
         # If iRedAdmin is not used, create database and import table here.
-        mysql -h${MYSQL_SERVER} -P${MYSQL_PORT} -u${MYSQL_ROOT_USER} -p"${MYSQL_ROOT_PASSWD}" <<EOF
+        mysql -h${MYSQL_SERVER} -P${MYSQL_SERVER_PORT} -u${MYSQL_ROOT_USER} -p"${MYSQL_ROOT_PASSWD}" <<EOF
 # Create databases.
 CREATE DATABASE IF NOT EXISTS ${IREDADMIN_DB_NAME} DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
 
         # which used to store realtime quota.
         if [ X"${BACKEND}" == X"OPENLDAP" -a X"${USE_IREDADMIN}" != X"YES" ]; then
             # If iRedAdmin is not used, create database and import table here.
-            mysql -h${MYSQL_SERVER} -P${MYSQL_PORT} -u${MYSQL_ROOT_USER} -p"${MYSQL_ROOT_PASSWD}" <<EOF
+            mysql -h${MYSQL_SERVER} -P${MYSQL_SERVER_PORT} -u${MYSQL_ROOT_USER} -p"${MYSQL_ROOT_PASSWD}" <<EOF
 # Create databases.
 CREATE DATABASE IF NOT EXISTS ${IREDADMIN_DB_NAME} DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
 

File iRedMail/functions/iredadmin.sh

 EOF
 
     ECHO_DEBUG "Import iredadmin database template."
-    mysql -h${MYSQL_SERVER} -P${MYSQL_PORT} -u${MYSQL_ROOT_USER} -p"${MYSQL_ROOT_PASSWD}" <<EOF
+    mysql -h${MYSQL_SERVER} -P${MYSQL_SERVER_PORT} -u${MYSQL_ROOT_USER} -p"${MYSQL_ROOT_PASSWD}" <<EOF
 # Create databases.
 CREATE DATABASE ${IREDADMIN_DB_NAME} DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
 
 
     # Import addition tables.
     if [ X"${BACKEND}" == X"OPENLDAP" ]; then
-        mysql -h${MYSQL_SERVER} -P${MYSQL_PORT} -u${MYSQL_ROOT_USER} -p"${MYSQL_ROOT_PASSWD}" <<EOF
+        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;
     ECHO_DEBUG "Configure iredadmin database related settings."
     sed -i.tmp \
         -e "/\[iredadmin\]/,/\[/ s#\(^host =\).*#\1 ${MYSQL_SERVER}#" \
-        -e "/\[iredadmin\]/,/\[/ s#\(^port =\).*#\1 ${MYSQL_PORT}#" \
+        -e "/\[iredadmin\]/,/\[/ s#\(^port =\).*#\1 ${MYSQL_SERVER_PORT}#" \
         -e "/\[iredadmin\]/,/\[/ s#\(^db =\).*#\1 ${IREDADMIN_DB_NAME}#" \
         -e "/\[iredadmin\]/,/\[/ s#\(^user =\).*#\1 ${IREDADMIN_DB_USER}#" \
         -e "/\[iredadmin\]/,/\[/ s#\(^passwd =\).*#\1 ${IREDADMIN_DB_PASSWD}#" \
         ECHO_DEBUG "Configure MySQL backend related settings."
         sed -i.tmp \
             -e "/\[vmaildb\]/,/\[/ s#\(^host =\).*#\1 ${MYSQL_SERVER}#" \
-            -e "/\[vmaildb\]/,/\[/ s#\(^port =\).*#\1 ${MYSQL_PORT}#" \
+            -e "/\[vmaildb\]/,/\[/ s#\(^port =\).*#\1 ${MYSQL_SERVER_PORT}#" \
             -e "/\[vmaildb\]/,/\[/ s#\(^db =\).*#\1 ${VMAIL_DB}#" \
             -e "/\[vmaildb\]/,/\[/ s#\(^user =\).*#\1 ${VMAIL_DB_ADMIN_USER}#" \
             -e "/\[vmaildb\]/,/\[/ s#\(^passwd =\).*#\1 ${VMAIL_DB_ADMIN_PASSWD}#" \
     sed -i.tmp \
         -e "/\[policyd\]/,/\[/ s#\(^enabled =\).*#\1 True#" \
         -e "/\[policyd\]/,/\[/ s#\(^host =\).*#\1 ${MYSQL_SERVER}#" \
-        -e "/\[policyd\]/,/\[/ s#\(^port =\).*#\1 ${MYSQL_PORT}#" \
+        -e "/\[policyd\]/,/\[/ s#\(^port =\).*#\1 ${MYSQL_SERVER_PORT}#" \
         -e "/\[policyd\]/,/\[/ s#\(^db =\).*#\1 ${POLICYD_DB_NAME}#" \
         -e "/\[policyd\]/,/\[/ s#\(^user =\).*#\1 ${POLICYD_DB_USER}#" \
         -e "/\[policyd\]/,/\[/ s#\(^passwd =\).*#\1 ${POLICYD_DB_PASSWD}#" \
         -e "/\[amavisd\]/,/\[/ s#\(^quarantine_port =\).*#\1 ${AMAVISD_QUARANTINE_PORT}#" \
         -e "/\[amavisd\]/,/\[/ s#\(^logging_into_sql =\).*#\1 True#" \
         -e "/\[amavisd\]/,/\[/ s#\(^host =\).*#\1 ${MYSQL_SERVER}#" \
-        -e "/\[amavisd\]/,/\[/ s#\(^port =\).*#\1 ${MYSQL_PORT}#" \
+        -e "/\[amavisd\]/,/\[/ s#\(^port =\).*#\1 ${MYSQL_SERVER_PORT}#" \
         -e "/\[amavisd\]/,/\[/ s#\(^db =\).*#\1 ${AMAVISD_DB_NAME}#" \
         -e "/\[amavisd\]/,/\[/ s#\(^user =\).*#\1 ${AMAVISD_DB_USER}#" \
         -e "/\[amavisd\]/,/\[/ s#\(^passwd =\).*#\1 ${AMAVISD_DB_PASSWD}#" \
         [policyd]
         enabled = True
         host = ${MYSQL_SERVER}
-        port = ${MYSQL_PORT}
+        port = ${MYSQL_SERVER_PORT}
         db = ${POLICYD_DB_NAME}
         user = ${POLICYD_DB_USER}
         passwd = ${POLICYD_DB_PASSWD}
         quarantine_port = ${AMAVISD_QUARANTINE_PORT}
         logging_into_sql = True
         host = ${MYSQL_SERVER}
-        port = ${MYSQL_PORT}
+        port = ${MYSQL_SERVER_PORT}
         db = ${AMAVISD_DB_NAME}
         user = ${AMAVISD_DB_USER}
         passwd = ${AMAVISD_DB_PASSWD}

File iRedMail/functions/mysql.sh

 EOF
 
     ECHO_DEBUG "Initialize MySQL database."
-    mysql -h${MYSQL_SERVER} -P${MYSQL_PORT} -u${MYSQL_ROOT_USER} -p"${MYSQL_ROOT_PASSWD}" <<EOF
+    mysql -h${MYSQL_SERVER} -P${MYSQL_SERVER_PORT} -u${MYSQL_ROOT_USER} -p"${MYSQL_ROOT_PASSWD}" <<EOF
 SOURCE ${MYSQL_INIT_SQL};
 FLUSH PRIVILEGES;
 EOF
 EOF
 
     ECHO_DEBUG "Import postfix virtual hosts/users: ${MYSQL_VMAIL_SQL}."
-    mysql -h${MYSQL_SERVER} -P${MYSQL_PORT} -u${MYSQL_ROOT_USER} -p"${MYSQL_ROOT_PASSWD}" <<EOF
+    mysql -h${MYSQL_SERVER} -P${MYSQL_SERVER_PORT} -u${MYSQL_ROOT_USER} -p"${MYSQL_ROOT_PASSWD}" <<EOF
 SOURCE ${MYSQL_VMAIL_SQL};
 FLUSH PRIVILEGES;
 EOF

File iRedMail/functions/policyd.sh

         :
     fi
 
-    mysql -h${MYSQL_SERVER} -P${MYSQL_PORT} -u${MYSQL_ROOT_USER} -p"${MYSQL_ROOT_PASSWD}" <<EOF
+    mysql -h${MYSQL_SERVER} -P${MYSQL_SERVER_PORT} -u${MYSQL_ROOT_USER} -p"${MYSQL_ROOT_PASSWD}" <<EOF
 $(cat ${tmp_sql})
 USE ${POLICYD_DB_NAME};
 ALTER TABLE blacklist MODIFY COLUMN _description CHAR(60) CHARACTER SET utf8;
     # Policyd doesn't work while mysql server is 'localhost', should be
     # changed to '127.0.0.1'.
 
-    perl -pi -e 's#^(MYSQLHOST=)(.*)#${1}"$ENV{mysql_server}"#' ${POLICYD_CONF} ${POLICYD_THROTTLE_CONF}
+    perl -pi -e 's#^(MYSQLHOST=)(.*)#${1}"$ENV{MYSQL_SERVER}"#' ${POLICYD_CONF} ${POLICYD_THROTTLE_CONF}
     perl -pi -e 's#^(MYSQLDBASE=)(.*)#${1}"$ENV{POLICYD_DB_NAME}"#' ${POLICYD_CONF} ${POLICYD_THROTTLE_CONF}
     perl -pi -e 's#^(MYSQLUSER=)(.*)#${1}"$ENV{POLICYD_DB_USER}"#' ${POLICYD_CONF} ${POLICYD_THROTTLE_CONF}
     perl -pi -e 's#^(MYSQLPASS=)(.*)#${1}"$ENV{POLICYD_DB_PASSWD}"#' ${POLICYD_CONF} ${POLICYD_THROTTLE_CONF}

File iRedMail/functions/postfix.sh

 ${CONF_MSG}
 user        = ${VMAIL_DB_BIND_USER}
 password    = ${VMAIL_DB_BIND_PASSWD}
-hosts       = ${mysql_server}
-port        = ${MYSQL_PORT}
+hosts       = ${MYSQL_SERVER}
+port        = ${MYSQL_SERVER_PORT}
 dbname      = ${VMAIL_DB}
 query       = SELECT transport FROM domain WHERE domain='%s' AND active=1
 EOF
 ${CONF_MSG}
 user        = ${VMAIL_DB_BIND_USER}
 password    = ${VMAIL_DB_BIND_PASSWD}
-hosts       = ${mysql_server}
-port        = ${MYSQL_PORT}
+hosts       = ${MYSQL_SERVER}
+port        = ${MYSQL_SERVER_PORT}
 dbname      = ${VMAIL_DB}
 query       = SELECT mailbox.transport FROM mailbox,domain WHERE mailbox.username='%s' AND mailbox.domain='%d' AND mailbox.domain=domain.domain AND mailbox.transport<>'' AND mailbox.active=1 AND mailbox.enabledeliver=1 AND domain.backupmx=0 AND domain.active=1
 EOF
 ${CONF_MSG}
 user        = ${VMAIL_DB_BIND_USER}
 password    = ${VMAIL_DB_BIND_PASSWD}
-hosts       = ${mysql_server}
-port        = ${MYSQL_PORT}
+hosts       = ${MYSQL_SERVER}
+port        = ${MYSQL_SERVER_PORT}
 dbname      = ${VMAIL_DB}
 query       = SELECT domain FROM domain WHERE domain='%s' AND backupmx=0 AND active=1 UNION SELECT alias_domain.alias_domain FROM alias_domain,domain WHERE alias_domain.alias_domain='%s' AND alias_domain.active=1 AND alias_domain.target_domain=domain.domain AND domain.active=1 AND domain.backupmx=0
 EOF
 ${CONF_MSG}
 user        = ${VMAIL_DB_BIND_USER}
 password    = ${VMAIL_DB_BIND_PASSWD}
-hosts       = ${mysql_server}
-port        = ${MYSQL_PORT}
+hosts       = ${MYSQL_SERVER}
+port        = ${MYSQL_SERVER_PORT}
 dbname      = ${VMAIL_DB}
 query       = SELECT domain FROM domain WHERE domain='%s' AND backupmx=1 AND active=1
 EOF
 ${CONF_MSG}
 user        = ${VMAIL_DB_BIND_USER}
 password    = ${VMAIL_DB_BIND_PASSWD}
-hosts       = ${mysql_server}
-port        = ${MYSQL_PORT}
+hosts       = ${MYSQL_SERVER}
+port        = ${MYSQL_SERVER_PORT}
 dbname      = ${VMAIL_DB}
 query       = SELECT CONCAT(mailbox.storagenode, '/', mailbox.maildir) FROM mailbox,domain WHERE mailbox.username='%s' AND mailbox.active=1 AND mailbox.enabledeliver=1 AND domain.domain = mailbox.domain AND domain.active=1
 EOF
 ${CONF_MSG}
 user        = ${VMAIL_DB_BIND_USER}
 password    = ${VMAIL_DB_BIND_PASSWD}
-hosts       = ${mysql_server}
-port        = ${MYSQL_PORT}
+hosts       = ${MYSQL_SERVER}
+port        = ${MYSQL_SERVER_PORT}
 dbname      = ${VMAIL_DB}
 query       = SELECT alias.goto FROM alias,domain WHERE alias.address='%s' AND alias.domain='%d' AND alias.domain=domain.domain AND alias.active=1 AND domain.backupmx=0 AND domain.active=1
 EOF
 ${CONF_MSG}
 user        = ${VMAIL_DB_BIND_USER}
 password    = ${VMAIL_DB_BIND_PASSWD}
-hosts       = ${mysql_server}
-port        = ${MYSQL_PORT}
+hosts       = ${MYSQL_SERVER}
+port        = ${MYSQL_SERVER_PORT}
 dbname      = ${VMAIL_DB}
 query       = SELECT alias.goto FROM alias,alias_domain,domain WHERE alias_domain.alias_domain='%d' AND alias.address=CONCAT('%u', '@', alias_domain.target_domain) AND alias_domain.target_domain=domain.domain AND alias.active=1 AND alias_domain.active=1 AND domain.backupmx=0
 EOF
 ${CONF_MSG}
 user        = ${VMAIL_DB_BIND_USER}
 password    = ${VMAIL_DB_BIND_PASSWD}
-hosts       = ${mysql_server}
-port        = ${MYSQL_PORT}
+hosts       = ${MYSQL_SERVER}
+port        = ${MYSQL_SERVER_PORT}
 dbname      = ${VMAIL_DB}
 query       = SELECT alias.goto FROM alias,domain WHERE alias.address='%d' AND alias.address=domain.domain AND alias.active=1 AND domain.active=1 AND domain.backupmx=0
 EOF
 ${CONF_MSG}
 user        = ${VMAIL_DB_BIND_USER}
 password    = ${VMAIL_DB_BIND_PASSWD}
-hosts       = ${mysql_server}
-port        = ${MYSQL_PORT}
+hosts       = ${MYSQL_SERVER}
+port        = ${MYSQL_SERVER_PORT}
 dbname      = ${VMAIL_DB}
 query       = SELECT alias.goto FROM alias,alias_domain,domain WHERE alias_domain.alias_domain='%d' AND alias.address=alias_domain.target_domain AND alias_domain.target_domain=domain.domain AND alias.active=1 AND alias_domain.active=1
 EOF
 ${CONF_MSG}
 user        = ${VMAIL_DB_BIND_USER}
 password    = ${VMAIL_DB_BIND_PASSWD}
-hosts       = ${mysql_server}
-port        = ${MYSQL_PORT}
+hosts       = ${MYSQL_SERVER}
+port        = ${MYSQL_SERVER_PORT}
 dbname      = ${VMAIL_DB}
 query       = SELECT mailbox.username FROM mailbox,domain WHERE mailbox.username='%s' AND mailbox.domain='%d' AND mailbox.domain=domain.domain AND mailbox.enablesmtp=1 AND mailbox.active=1 AND domain.backupmx=0 AND domain.active=1
 EOF
 ${CONF_MSG}
 user        = ${VMAIL_DB_BIND_USER}
 password    = ${VMAIL_DB_BIND_PASSWD}
-hosts       = ${mysql_server}
-port        = ${MYSQL_PORT}
+hosts       = ${MYSQL_SERVER}
+port        = ${MYSQL_SERVER_PORT}
 dbname      = ${VMAIL_DB}
 query       = SELECT bcc_address FROM sender_bcc_domain WHERE domain='%d' AND active=1
 EOF
 ${CONF_MSG}
 user        = ${VMAIL_DB_BIND_USER}
 password    = ${VMAIL_DB_BIND_PASSWD}
-hosts       = ${mysql_server}
-port        = ${MYSQL_PORT}
+hosts       = ${MYSQL_SERVER}
+port        = ${MYSQL_SERVER_PORT}
 dbname      = ${VMAIL_DB}
 query       = SELECT sender_bcc_user.bcc_address FROM sender_bcc_user,domain WHERE sender_bcc_user.username='%s' AND sender_bcc_user.domain='%d' AND sender_bcc_user.domain=domain.domain AND domain.backupmx=0 AND domain.active=1 AND sender_bcc_user.active=1
 EOF
 ${CONF_MSG}
 user        = ${VMAIL_DB_BIND_USER}
 password    = ${VMAIL_DB_BIND_PASSWD}
-hosts       = ${mysql_server}
-port        = ${MYSQL_PORT}
+hosts       = ${MYSQL_SERVER}
+port        = ${MYSQL_SERVER_PORT}
 dbname      = ${VMAIL_DB}
 query       = SELECT bcc_address FROM recipient_bcc_domain WHERE domain='%d' AND active=1
 EOF
 ${CONF_MSG}
 user        = ${VMAIL_DB_BIND_USER}
 password    = ${VMAIL_DB_BIND_PASSWD}
-hosts       = ${mysql_server}
-port        = ${MYSQL_PORT}
+hosts       = ${MYSQL_SERVER}
+port        = ${MYSQL_SERVER_PORT}
 dbname      = ${VMAIL_DB}
 query       = SELECT recipient_bcc_user.bcc_address FROM recipient_bcc_user,domain WHERE recipient_bcc_user.username='%s' AND recipient_bcc_user.domain='%d' AND recipient_bcc_user.domain=domain.domain AND domain.backupmx=0 AND domain.active=1 AND recipient_bcc_user.active=1
 EOF

File iRedMail/functions/postgresql.sh

     #ECHO_DEBUG "Force all users to connect PGSQL server with password."
     #perl -pi -e 's#^(local.*)peer#${1}md5#' ${PGSQL_CONF_PG_HBA}
 
-    #ECHO_DEBUG "Listen on only localhost"
-    #perl -pi -e 's/^#(listen_addresses)(.*)/${1} = "localhost"/' ${PGSQL_CONF_POSTGRESQL}
+    ECHO_DEBUG "Listen on only localhost"
+    perl -pi -e 's#.*(listen_addresses.=.)(.).*#${1}${2}localhost${2}#' ${PGSQL_CONF_POSTGRESQL}
+
+    ECHO_DEBUG "Set client_min_messages to ERROR."
+    perl -pi -e 's#.*(client_min_messages =).*#${1} error#' ${PGSQL_CONF_POSTGRESQL}
 
     ECHO_DEBUG "Copy iRedMail SSL cert/key with strict permission."
     # SSL is enabled by default.

File iRedMail/functions/roundcubemail.sh

 
     # Initial roundcube db.
     if [ X"${BACKEND}" == X"OPENLDAP" -o X"${BACKEND}" == X"MYSQL" ]; then
-        mysql -h${MYSQL_SERVER} -P${MYSQL_PORT} -u${MYSQL_ROOT_USER} -p"${MYSQL_ROOT_PASSWD}" <<EOF
+        mysql -h${MYSQL_SERVER} -P${MYSQL_SERVER_PORT} -u${MYSQL_ROOT_USER} -p"${MYSQL_ROOT_PASSWD}" <<EOF
 -- Create database and grant privileges
 CREATE DATABASE ${RCM_DB} DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
 GRANT SELECT,INSERT,UPDATE,DELETE ON ${RCM_DB}.* TO "${RCM_DB_USER}"@localhost IDENTIFIED BY '${RCM_DB_PASSWD}';
         su - ${PGSQL_SYS_USER} -c 'psql >/dev/null' >/dev/null <<EOF
 -- Create database and role
 CREATE DATABASE ${RCM_DB} WITH TEMPLATE template0 ENCODING 'UTF8';
-CREATE USER ${RCM_DB_USER} WITH ENCRYPTED PASSWORD '${RCM_DB_PASSWD}' NOSUPERUSER NOCREATEDB NOCREATEROLE;
+CREATE ROLE ${RCM_DB_USER} WITH LOGIN ENCRYPTED PASSWORD '${RCM_DB_PASSWD}' NOSUPERUSER NOCREATEDB NOCREATEROLE;
 
 -- Import Roundcubemail SQL template
 \c ${RCM_DB};
 
 -- Grant privileges
 GRANT SELECT,INSERT,UPDATE,DELETE ON cache,cache_index,cache_messages,cache_thread,contactgroupmembers,contactgroups,contacts,dictionary,identities,searches,session,users TO ${RCM_DB_USER};
-GRANT SELECT,UPDATE,USAGE ON cache_ids,identity_ids,contact_ids,contactgroups_ids,cache_ids,search_ids TO ${RCM_DB_USER};
+GRANT SELECT,UPDATE,USAGE ON cache_ids,contact_ids,contactgroups_ids,identity_ids,search_ids,user_ids TO ${RCM_DB_USER};
 
 -- Grant privilege to update password through roundcube webmail
 \c ${VMAIL_DB};
 
     # Do not grant privileges while backend is not MySQL.
     if [ X"${BACKEND}" == X"MYSQL" ]; then
-        mysql -h${MYSQL_SERVER} -P${MYSQL_PORT} -u${MYSQL_ROOT_USER} -p"${MYSQL_ROOT_PASSWD}" <<EOF
+        mysql -h${MYSQL_SERVER} -P${MYSQL_SERVER_PORT} -u${MYSQL_ROOT_USER} -p"${MYSQL_ROOT_PASSWD}" <<EOF
 -- Grant privileges for Roundcubemail, so that user can change
 -- their own password and setting mail forwarding.
 GRANT UPDATE,SELECT ON ${VMAIL_DB}.mailbox TO "${RCM_DB_USER}"@localhost;
     if [ X"${BACKEND}" == X"MYSQL" -o X"${BACKEND}" == X"PGSQL" ]; then
         perl -pi -e 's#(.*password_driver.*=).*#${1} "sql";#' config.inc.php
         perl -pi -e 's#(.*password_db_dsn.*= )(.*)#${1}"$ENV{PHP_CONN_TYPE}://$ENV{RCM_DB_USER}:$ENV{RCM_DB_PASSWD}\@$ENV{SQL_SERVER}/$ENV{VMAIL_DB}";#' config.inc.php
-        perl -pi -e 's#(.*password_query.*=).*#${1} "UPDATE $ENV{VMAIL_DB}.mailbox SET password=%c,passwordlastchange=NOW() WHERE username=%u LIMIT 1";#' config.inc.php
         perl -pi -e 's#(.*password_hash_algorithm.*=).*#${1} "md5crypt";#' config.inc.php
         perl -pi -e 's#(.*password_hash_base64.*=).*#${1} false;#' config.inc.php
 
+        if [ X"${BACKEND}" == X"MYSQL" ]; then
+            perl -pi -e 's#(.*password_query.*=).*#${1} "UPDATE $ENV{VMAIL_DB}.mailbox SET password=%c,passwordlastchange=NOW() WHERE username=%u LIMIT 1";#' config.inc.php
+        elif [ X"${BACKEND}" == X"MYSQL" ]; then
+            perl -pi -e 's#(.*password_query.*=).*#${1} "\c ${VMAIL_DB}; UPDATE mailbox SET password=%c,passwordlastchange=NOW() WHERE username=%u";#' config.inc.php
+        fi
+
     elif [ X"${BACKEND}" == X"OPENLDAP" ]; then
         # LDAP backend. Driver: ldap_simple.
         perl -pi -e 's#(.*password_driver.*=).*#${1} "ldap_simple";#' config.inc.php

File 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