Commits

Zhang Huangbin  committed 2ebbddd

PostgreSQL is now working as expected, still need some work in other applications.
Code cleanup.

  • Participants
  • Parent commits 7e5755c

Comments (0)

Files changed (24)

File iRedMail/conf/amavisd

 export AMAVISD_DB_USER='amavisd'
 export AMAVISD_DB_PASSWD="$(${RANDOM_STRING})"
 export AMAVISD_DB_SQL_TMPL="${SAMPLE_DIR}/amavisd.mysql"
-export BACKUP_DATABASES="${BACKUP_DATABASES} ${AMAVISD_DB_NAME}"
+export MYSQL_BACKUP_DATABASES="${MYSQL_BACKUP_DATABASES} ${AMAVISD_DB_NAME}"
 
 # Altermime.
 export ALTERMIME_BIN='/usr/bin/altermime'

File iRedMail/conf/cluebringer

     :
 fi
 
-export BACKUP_DATABASES="${BACKUP_DATABASES} ${CLUEBRINGER_DB_NAME}"
+export MYSQL_BACKUP_DATABASES="${MYSQL_BACKUP_DATABASES} ${CLUEBRINGER_DB_NAME}"

File iRedMail/conf/global

 # functions/user_vmail.sh
 export VMAIL_USER_NAME='vmail'
 export VMAIL_GROUP_NAME='vmail'
-export VMAIL_ADMIN_USER_NAME="${VMAIL_USER_NAME}admin"
+export VMAIL_DB_ADMIN_USER="${VMAIL_USER_NAME}admin"
 
 # Default SQL database name used to store mail accounts.
 export VMAIL_DB='vmail'
+export VMAIL_DB_BIND_USER='vmail'
+export VMAIL_DB_ADMIN_USER='vmailadmin'
 
 # Default virtual domain admin name without domain name (@example.com).
 export DOMAIN_ADMIN_NAME='postmaster'
 export LC_ALL=C
 
 # Debug mode: YES, NO.
-export DEBUG='NO'
+if [ X"${DEBUG}" != X'YES' ]; then
+    export DEBUG='NO'
+fi
 
 # Genrate a random string.
 # Usage:
 export SSHD_CONFIG='/etc/ssh/sshd_config'
 
 # SQL databases which need to be backed up.
-export BACKUP_DATABASES=''
+export MYSQL_BACKUP_DATABASES=''
 
 # Command use to fetch source tarballs.
 if [ X"${DISTRO}" == X"FREEBSD" ]; then

File iRedMail/conf/mysql

 # SQL structure of MySQL vmail database.
 export MYSQL_VMAIL_STRUCTURE_SAMPLE="${SAMPLE_DIR}/iredmail.mysql"
 
-export BACKUP_DATABASES="${BACKUP_DATABASES} mysql"
+export MYSQL_BACKUP_DATABASES="${MYSQL_BACKUP_DATABASES} mysql"
 if [ X"${BACKEND}" == X"MYSQL" ]; then
-    export BACKUP_DATABASES="${BACKUP_DATABASES} ${VMAIL_DB}"
+    export MYSQL_BACKUP_DATABASES="${MYSQL_BACKUP_DATABASES} ${VMAIL_DB}"
 fi

File iRedMail/conf/policyd

     :
 fi
 
-export BACKUP_DATABASES="${BACKUP_DATABASES} ${POLICYD_DB_NAME}"
+export MYSQL_MYSQL_BACKUP_DATABASES="${MYSQL_MYSQL_BACKUP_DATABASES} ${POLICYD_DB_NAME}"

File iRedMail/conf/postgresql

 
 export PGSQL_SYS_USER='postgres'
 export PGSQL_SYS_GROUP='postgres'
+
+export PGSQL_SERVER='localhost'
 export PGSQL_ROOT_USER='postgres'
+
+export PGSQL_SYS_USER_HOME='/var/lib/postgresql'
+export PGSQL_DOT_PGPASS="${PGSQL_SYS_USER_HOME}/.pgpass"
+
 export PGSQL_INIT_SCRIPT="${DIR_RC_SCRIPTS}/postgresql"
 export PGSQL_VMAIL_STRUCTURE_SAMPLE="${SAMPLE_DIR}/iredmail.pgsql"
+export PGSQL_INIT_SQL_SAMPLE="${CONF_DIR}/pgsql_init.pgsql"
 
-if [ X"${DISTRO}" == X"UBUNTU" ]; then
-    export PGSQL_SYS_USER='postgres'
-    export PGSQL_SYS_GROUP='postgres'
+export PGSQL_VERSION='9.1'
+export PGSQL_CONF_DIR="/etc/postgresql/${PGSQL_VERSION}/main"
+export PGSQL_CONF_POSTGRESQL="${PGSQL_CONF_DIR}/postgresql.conf"
+export PGSQL_CONF_PG_HBA="${PGSQL_CONF_DIR}/pg_hba.conf"
+
+export PGSQL_DATA_DIR="/var/lib/postgresql/${PGSQL_VERSION}/main"
+
+# SSL cert/key
+export PGSQL_SSL_CERT="${SSL_CERT_DIR}/iRedMail_CA_PostgreSQL.pem"
+export PGSQL_SSL_KEY="${SSL_KEY_DIR}/iRedMail_PostgreSQL.key"
+
+export PGSQL_BACKUP_DATABASES="${PGSQL_BACKUP_DATABASES}"
+if [ X"${BACKEND}" == X"PGSQL" ]; then
+    export PGSQL_BACKUP_DATABASES="${PGSQL_BACKUP_DATABASES} ${VMAIL_DB}"
 fi
-
-export BACKUP_PGSQL_DATABASES="${BACKUP_PGSQL_DATABASES}"
-if [ X"${BACKEND}" == X"PGSQL" ]; then
-    export BACKUP_PGSQL_DATABASES="${BACKUP_PGSQL_DATABASES} ${VMAIL_DB}"
-fi

File iRedMail/conf/roundcube

 export RCM_DB="roundcubemail"
 export RCM_DB_USER="roundcube"
 export RCM_DB_PASSWD="$(${RANDOM_STRING})"
-export BACKUP_DATABASES="${BACKUP_DATABASES} ${RCM_DB}"
+export MYSQL_BACKUP_DATABASES="${MYSQL_BACKUP_DATABASES} ${RCM_DB}"
 
 # this key is used to encrypt the users imap password which is stored
 # in the session record (and the client cookie if remember password is enabled).

File iRedMail/dialog/config_via_dialog.sh

 echo "export BACKEND='${BACKEND}'" >> ${CONFIG_FILE}
 rm -f /tmp/backend
 
-# For virtual user query in Postfix, Dovecot.
-export MYSQL_BIND_USER="${VMAIL_USER_NAME}"
-export MYSQL_BIND_PW="$(${RANDOM_STRING})"
-echo "export MYSQL_BIND_USER='${MYSQL_BIND_USER}'" >> ${CONFIG_FILE}
-echo "export MYSQL_BIND_PW='${MYSQL_BIND_PW}'" >> ${CONFIG_FILE}
+# Read-only SQL user/role, used to query mail accounts in Postfix, Dovecot.
+export VMAIL_DB_BIND_PASSWD="$(${RANDOM_STRING})"
+echo "export VMAIL_DB_BIND_PASSWD='${VMAIL_DB_BIND_PASSWD}'" >> ${CONFIG_FILE}
 
 # For database management: vmail.
-export MYSQL_ADMIN_USER="${VMAIL_ADMIN_USER_NAME}"
-export MYSQL_ADMIN_PW="$(${RANDOM_STRING})"
-echo "export MYSQL_ADMIN_USER='${MYSQL_ADMIN_USER}'" >> ${CONFIG_FILE}
-echo "export MYSQL_ADMIN_PW='${MYSQL_ADMIN_PW}'" >> ${CONFIG_FILE}
+export VMAIL_DB_ADMIN_PASSWD="$(${RANDOM_STRING})"
+echo "export VMAIL_DB_ADMIN_PASSWD='${VMAIL_DB_ADMIN_PASSWD}'" >> ${CONFIG_FILE}
 
 # LDAP bind dn & password.
 export LDAP_BINDPW="$(${RANDOM_STRING})"
 
 if [ X"${BACKEND}" == X"OPENLDAP" ]; then
     . ${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
 else
     :
 fi
 
-# MySQL server is required as backend or used to store policyd/roundcube data.
-. ${DIALOG_DIR}/mysql_config.sh
-
-#
 # Virtual domain configuration.
-#
 . ${DIALOG_DIR}/virtual_domain_config.sh
 
-#
-# For optional components.
-#
+# Optional components.
 . ${DIALOG_DIR}/optional_components.sh
 
 # Append EOF tag in config file.

File iRedMail/dialog/ldap_config.sh

 
 export LDAP_SUFFIX_MAJOR="$( echo ${dn2dnsname} | awk -F'.' '{print $1}')"
 export LDAP_BINDDN="cn=${VMAIL_USER_NAME},${LDAP_SUFFIX}"
-export LDAP_ADMIN_DN="cn=${VMAIL_ADMIN_USER_NAME},${LDAP_SUFFIX}"
+export LDAP_ADMIN_DN="cn=${VMAIL_DB_ADMIN_USER},${LDAP_SUFFIX}"
 export LDAP_ROOTDN="cn=Manager,${LDAP_SUFFIX}"
 export LDAP_BASEDN_NAME='domains'
 export LDAP_BASEDN="o=${LDAP_BASEDN_NAME},${LDAP_SUFFIX}"

File iRedMail/functions/amavisd.sh

         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}', '${MYSQL_BIND_USER}', '${MYSQL_BIND_PW}'],
+#    ['DBI:mysql:database=${VMAIL_DB};host=${MYSQL_SERVER};port=${MYSQL_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'";

File iRedMail/functions/awstats.sh

     AuthMYSQLEnable On
     AuthMySQLHost ${MYSQL_SERVER}
     AuthMySQLPort ${MYSQL_PORT}
-    AuthMySQLUser ${MYSQL_BIND_USER}
-    AuthMySQLPassword ${MYSQL_BIND_PW}
+    AuthMySQLUser ${VMAIL_DB_BIND_USER}
+    AuthMySQLPassword ${VMAIL_DB_BIND_PASSWD}
     AuthMySQLDB ${VMAIL_DB}
     AuthMySQLUserTable admin
     AuthMySQLNameField username
             cat >> ${HTTPD_CONF} <<EOF
 # MySQL auth (libapache2-mod-auth-apache2).
 # Global config of MySQL server, username, password.
-Auth_MySQL_Info ${MYSQL_SERVER} ${MYSQL_BIND_USER} ${MYSQL_BIND_PW}
+Auth_MySQL_Info ${MYSQL_SERVER} ${VMAIL_DB_BIND_USER} ${VMAIL_DB_BIND_PASSWD}
 Auth_MySQL_General_DB ${VMAIL_DB}
 EOF
         else

File iRedMail/functions/backend.sh

         # Initialize MySQL.
         check_status_before_run mysql_initialize
         check_status_before_run mysql_import_vmail_users
+    elif [ X"${BACKEND}" == X"PGSQL" ]; then
+        check_status_before_run pgsql_initialize
+        check_status_before_run pgsql_import_vmail_users
     else
         :
     fi

File iRedMail/functions/cleanup.sh

     ECHO_DEBUG "Updating backup script: ${TOOLS_DIR}/backup_mysql.sh."
     perl -pi -e 's#^(MYSQL_USER=).*#${1}"ENV{MYSQL_ROOT_USER}"#' ${TOOLS_DIR}/backup_mysql.sh
     perl -pi -e 's#^(MYSQL_PASSWD=).*#${1}"ENV{MYSQL_ROOT_PASSWD}"#' ${TOOLS_DIR}/backup_mysql.sh
-    perl -pi -e 's#^(DATABASES=).*#${1}"ENV{BACKUP_DATABASES}"#' ${TOOLS_DIR}/backup_mysql.sh
+    perl -pi -e 's#^(DATABASES=).*#${1}"ENV{MYSQL_BACKUP_DATABASES}"#' ${TOOLS_DIR}/backup_mysql.sh
 
     echo 'export status_cleanup_backup_scripts="DONE"' >> ${STATUS_FILE}
 }

File iRedMail/functions/cluebringer.sh

         :
     fi
 
-    mysql -h${MYSQL_SERVER} -P${MYSQL_PORT} -u${MYSQL_ROOT_USER} -p"${MYSQL_ROOT_PASSWD}" <<EOF
+    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
 $(cat ${tmp_sql})
 
 -- Delete default sample domains.
 -- Enable greylisting on all inbound emails by default.
 INSERT INTO greylisting (PolicyID, Name, UseGreylisting, GreylistPeriod, Track, GreylistAuthValidity, GreylistUnAuthValidity, UseAutoWhitelist, AutoWhitelistPeriod, AutoWhitelistCount, AutoWhitelistPercentage, UseAutoBlacklist, AutoBlacklistPeriod, AutoBlacklistCount, AutoBlacklistPercentage, Comment, Disabled) VALUES (1, 'Greylisting Inbound Emails', 1, 240, 'SenderIP:/24', 604800, 86400, 1, 604800, 100, 90, 1, 604800, 100, 20, '', 0);
 EOF
+    elif [ X"${BACKEND}" == X"PGSQL" ]; then
+        su - ${PGSQL_SYS_USER} -c "psql -f ${tmp_sql}" >/dev/null 
+    fi
 
     rm -rf ${tmp_sql} 2>/dev/null
     unset tmp_sql
     AuthMYSQLEnable On
     AuthMySQLHost ${MYSQL_SERVER}
     AuthMySQLPort ${MYSQL_PORT}
-    AuthMySQLUser ${MYSQL_BIND_USER}
-    AuthMySQLPassword ${MYSQL_BIND_PW}
+    AuthMySQLUser ${VMAIL_DB_BIND_USER}
+    AuthMySQLPassword ${VMAIL_DB_BIND_PASSWD}
     AuthMySQLDB ${VMAIL_DB}
     AuthMySQLUserTable admin
     AuthMySQLNameField username
             cat >> ${HTTPD_CONF} <<EOF
 # MySQL auth (libapache2-mod-auth-apache2).
 # Global config of MySQL server, username, password.
-Auth_MySQL_Info ${MYSQL_SERVER} ${MYSQL_BIND_USER} ${MYSQL_BIND_PW}
+Auth_MySQL_Info ${MYSQL_SERVER} ${VMAIL_DB_BIND_USER} ${VMAIL_DB_BIND_PASSWD}
 Auth_MySQL_General_DB ${VMAIL_DB}
 EOF
         else

File iRedMail/functions/dovecot1.sh

 ${CONF_MSG}
 driver = mysql
 default_pass_scheme = CRYPT
-connect = host=${MYSQL_SERVER} dbname=${VMAIL_DB} user=${MYSQL_BIND_USER} password=${MYSQL_BIND_PW}
+connect = host=${MYSQL_SERVER} dbname=${VMAIL_DB} user=${VMAIL_DB_BIND_USER} password=${VMAIL_DB_BIND_PASSWD}
 password_query = SELECT password FROM mailbox WHERE username='%u' AND active='1'
 EOF
         # Maildir format.
             realtime_quota_db_passwd="${IREDADMIN_DB_PASSWD}"
         else
             realtime_quota_db_name="${VMAIL_DB}"
-            realtime_quota_db_user="${MYSQL_ADMIN_USER}"
-            realtime_quota_db_passwd="${MYSQL_ADMIN_PW}"
+            realtime_quota_db_user="${VMAIL_DB_ADMIN_USER}"
+            realtime_quota_db_passwd="${VMAIL_DB_ADMIN_PASSWD}"
         fi
 
         cat > ${DOVECOT_REALTIME_QUOTA_CONF} <<EOF
         else
             share_folder_db_name="${VMAIL_DB}"
             share_folder_db_table="share_folder"
-            share_folder_db_user="${MYSQL_ADMIN_USER}"
-            share_folder_db_passwd="${MYSQL_ADMIN_PW}"
+            share_folder_db_user="${VMAIL_DB_ADMIN_USER}"
+            share_folder_db_passwd="${VMAIL_DB_ADMIN_PASSWD}"
         fi
 
         # Enable dict quota in dovecot.

File iRedMail/functions/dovecot2.sh

         cat > ${DOVECOT_MYSQL_CONF} <<EOF
 driver = mysql
 default_pass_scheme = CRYPT
-connect = host=${MYSQL_SERVER} dbname=${VMAIL_DB} user=${MYSQL_BIND_USER} password=${MYSQL_BIND_PW}
+connect = host=${MYSQL_SERVER} dbname=${VMAIL_DB} user=${VMAIL_DB_BIND_USER} password=${VMAIL_DB_BIND_PASSWD}
 password_query = SELECT password FROM mailbox WHERE username='%u' AND active='1'
 user_query = SELECT \
 CONCAT(mailbox.storagebasedirectory, '/', mailbox.storagenode, '/', mailbox.maildir) AS home, \
             realtime_quota_db_passwd="${IREDADMIN_DB_PASSWD}"
         else
             realtime_quota_db_name="${VMAIL_DB}"
-            realtime_quota_db_user="${MYSQL_ADMIN_USER}"
-            realtime_quota_db_passwd="${MYSQL_ADMIN_PW}"
+            realtime_quota_db_user="${VMAIL_DB_ADMIN_USER}"
+            realtime_quota_db_passwd="${VMAIL_DB_ADMIN_PASSWD}"
         fi
 
         cat > ${DOVECOT_REALTIME_QUOTA_CONF} <<EOF
         else
             share_folder_db_name="${VMAIL_DB}"
             share_folder_db_table="share_folder"
-            share_folder_db_user="${MYSQL_ADMIN_USER}"
-            share_folder_db_passwd="${MYSQL_ADMIN_PW}"
+            share_folder_db_user="${VMAIL_DB_ADMIN_USER}"
+            share_folder_db_passwd="${VMAIL_DB_ADMIN_PASSWD}"
         fi
 
         # Enable dict quota in dovecot.

File iRedMail/functions/iredadmin.sh

     ECHO_INFO "Configure iRedAdmin (official web-based admin panel)."
 
     # Backup database.
-    export BACKUP_DATABASES="${BACKUP_DATABASES} ${IREDADMIN_DB_NAME}"
+    export MYSQL_BACKUP_DATABASES="${MYSQL_BACKUP_DATABASES} ${IREDADMIN_DB_NAME}"
 
     # Create a low privilege user as httpd daemon user.
     if [ X"${KERNEL_NAME}" == X"FreeBSD" ]; then
             -e "/\[vmaildb\]/,/\[/ s#\(^host =\).*#\1 ${MYSQL_SERVER}#" \
             -e "/\[vmaildb\]/,/\[/ s#\(^port =\).*#\1 ${MYSQL_PORT}#" \
             -e "/\[vmaildb\]/,/\[/ s#\(^db =\).*#\1 ${VMAIL_DB}#" \
-            -e "/\[vmaildb\]/,/\[/ s#\(^user =\).*#\1 ${MYSQL_ADMIN_USER}#" \
-            -e "/\[vmaildb\]/,/\[/ s#\(^passwd =\).*#\1 ${MYSQL_ADMIN_PW}#" \
+            -e "/\[vmaildb\]/,/\[/ s#\(^user =\).*#\1 ${VMAIL_DB_ADMIN_USER}#" \
+            -e "/\[vmaildb\]/,/\[/ s#\(^passwd =\).*#\1 ${VMAIL_DB_ADMIN_PASSWD}#" \
             settings.ini
     fi
 

File iRedMail/functions/iredapd.sh

         # Configure MySQL server related stuffs.
         perl -pi -e 's#^(server).*#${1} = $ENV{MYSQL_SERVER}#' iredapd.ini
         perl -pi -e 's#^(db).*#${1} = $ENV{VMAIL_DB}#' iredapd.ini
-        perl -pi -e 's#^(user).*#${1} = $ENV{MYSQL_BIND_USER}#' iredapd.ini
-        perl -pi -e 's#^(password).*#${1} = $ENV{MYSQL_BIND_PW}#' iredapd.ini
+        perl -pi -e 's#^(user).*#${1} = $ENV{VMAIL_DB_BIND_USER}#' iredapd.ini
+        perl -pi -e 's#^(password).*#${1} = $ENV{VMAIL_DB_BIND_PASSWD}#' iredapd.ini
 
         # Enable plugins.
         perl -pi -e 's#^(plugins).*#${1} = sql_alias_access_policy#' iredapd.ini

File iRedMail/functions/mysql.sh

 
     ${MYSQLD_INIT_SCRIPT} restart >/dev/null 2>&1
 
-    ECHO_DEBUG -n "Sleep 5 seconds for MySQL daemon initialize:"
+    ECHO_INFO -n "Sleep 5 seconds for MySQL daemon initialize:"
     for i in 5 4 3 2 1; do
-        ECHO_DEBUG -n " ${i}" && sleep 1
+        echo -n " ${i}" && sleep 1
     done
-    ECHO_DEBUG '.'
+    echo '.'
 
     echo '' > ${MYSQL_INIT_SQL}
 
     cat >> ${TIP_FILE} <<EOF
 MySQL:
     * Bind account (read-only):
-        - Name: ${MYSQL_BIND_USER}, Password: ${MYSQL_BIND_PW}
+        - Name: ${VMAIL_DB_BIND_USER}, Password: ${VMAIL_DB_BIND_PASSWD}
     * Vmail admin account (read-write):
-        - Name: ${MYSQL_ADMIN_USER}, Password: ${MYSQL_ADMIN_PW}
+        - Name: ${VMAIL_DB_ADMIN_USER}, Password: ${VMAIL_DB_ADMIN_PASSWD}
     * Database stored in: /var/lib/mysql
     * RC script: ${MYSQLD_INIT_SCRIPT}
     * Log file: /var/log/mysqld.log
 CREATE DATABASE IF NOT EXISTS ${VMAIL_DB} CHARACTER SET utf8;
 
 /* Permissions. */
-GRANT SELECT ON ${VMAIL_DB}.* TO "${MYSQL_BIND_USER}"@localhost IDENTIFIED BY "${MYSQL_BIND_PW}";
-GRANT SELECT,INSERT,DELETE,UPDATE ON ${VMAIL_DB}.* TO "${MYSQL_ADMIN_USER}"@localhost IDENTIFIED BY "${MYSQL_ADMIN_PW}";
+GRANT SELECT ON ${VMAIL_DB}.* TO "${VMAIL_DB_BIND_USER}"@localhost IDENTIFIED BY "${VMAIL_DB_BIND_PASSWD}";
+GRANT SELECT,INSERT,DELETE,UPDATE ON ${VMAIL_DB}.* TO "${VMAIL_DB_ADMIN_USER}"@localhost IDENTIFIED BY "${VMAIL_DB_ADMIN_PASSWD}";
 
 /* Initialize the database. */
 USE ${VMAIL_DB};

File iRedMail/functions/openldap.sh

     ECHO_DEBUG "Starting OpenLDAP."
     ${LDAP_INIT_SCRIPT} restart >/dev/null
 
-    ECHO_DEBUG -n "Sleep 5 seconds for LDAP daemon initialize:"
+    ECHO_INFO -n "Sleep 5 seconds for LDAP daemon initialize:"
     for i in 5 4 3 2 1; do
-        ECHO_DEBUG -n " ${i}" && sleep 1
+        echo -n " ${i}" && sleep 1
     done
-    ECHO_DEBUG '.'
+    echo '.'
 
     ECHO_DEBUG "Initialize LDAP tree."
     # home_mailbox format is 'maildir/' by default.
 objectClass: person
 objectClass: shadowAccount
 objectClass: top
-cn: ${VMAIL_ADMIN_USER_NAME}
-sn: ${VMAIL_ADMIN_USER_NAME}
-uid: ${VMAIL_ADMIN_USER_NAME}
+cn: ${VMAIL_DB_ADMIN_USER}
+sn: ${VMAIL_DB_ADMIN_USER}
+uid: ${VMAIL_DB_ADMIN_USER}
 ${LDAP_ATTR_USER_PASSWD}: $(gen_ldap_passwd "${LDAP_ADMIN_PW}")
 
 dn: ${LDAP_BASEDN}

File iRedMail/functions/phpmyadmin.sh

         - ${PHPMYADMIN_CONFIG_FILE}
     * Login account:
         - Username: ${MYSQL_ROOT_USER}, password: ${MYSQL_ROOT_PASSWD}
-        - Username: ${MYSQL_ADMIN_USER}, password: ${MYSQL_ADMIN_PW}
-        - Username (read-only): ${MYSQL_BIND_USER}, password: ${MYSQL_BIND_PW}
+        - Username: ${VMAIL_DB_ADMIN_USER}, password: ${VMAIL_DB_ADMIN_PASSWD}
+        - Username (read-only): ${VMAIL_DB_BIND_USER}, password: ${VMAIL_DB_BIND_PASSWD}
     * URL:
         - https://${HOSTNAME}/phpmyadmin
     * See also:

File iRedMail/functions/postfix.sh

     # Per-domain transport maps.
     cat > ${mysql_transport_maps_domain_cf} <<EOF
 ${CONF_MSG}
-user        = ${MYSQL_BIND_USER}
-password    = ${MYSQL_BIND_PW}
+user        = ${VMAIL_DB_BIND_USER}
+password    = ${VMAIL_DB_BIND_PASSWD}
 hosts       = ${mysql_server}
 port        = ${MYSQL_PORT}
 dbname      = ${VMAIL_DB}
     # Per-user transport maps.
     cat > ${mysql_transport_maps_user_cf} <<EOF
 ${CONF_MSG}
-user        = ${MYSQL_BIND_USER}
-password    = ${MYSQL_BIND_PW}
+user        = ${VMAIL_DB_BIND_USER}
+password    = ${VMAIL_DB_BIND_PASSWD}
 hosts       = ${mysql_server}
 port        = ${MYSQL_PORT}
 dbname      = ${VMAIL_DB}
 
     cat > ${mysql_virtual_mailbox_domains_cf} <<EOF
 ${CONF_MSG}
-user        = ${MYSQL_BIND_USER}
-password    = ${MYSQL_BIND_PW}
+user        = ${VMAIL_DB_BIND_USER}
+password    = ${VMAIL_DB_BIND_PASSWD}
 hosts       = ${mysql_server}
 port        = ${MYSQL_PORT}
 dbname      = ${VMAIL_DB}
 
     cat > ${mysql_relay_domains_cf} <<EOF
 ${CONF_MSG}
-user        = ${MYSQL_BIND_USER}
-password    = ${MYSQL_BIND_PW}
+user        = ${VMAIL_DB_BIND_USER}
+password    = ${VMAIL_DB_BIND_PASSWD}
 hosts       = ${mysql_server}
 port        = ${MYSQL_PORT}
 dbname      = ${VMAIL_DB}
 
     cat > ${mysql_virtual_mailbox_maps_cf} <<EOF
 ${CONF_MSG}
-user        = ${MYSQL_BIND_USER}
-password    = ${MYSQL_BIND_PW}
+user        = ${VMAIL_DB_BIND_USER}
+password    = ${VMAIL_DB_BIND_PASSWD}
 hosts       = ${mysql_server}
 port        = ${MYSQL_PORT}
 dbname      = ${VMAIL_DB}
 
     cat > ${mysql_virtual_alias_maps_cf} <<EOF
 ${CONF_MSG}
-user        = ${MYSQL_BIND_USER}
-password    = ${MYSQL_BIND_PW}
+user        = ${VMAIL_DB_BIND_USER}
+password    = ${VMAIL_DB_BIND_PASSWD}
 hosts       = ${mysql_server}
 port        = ${MYSQL_PORT}
 dbname      = ${VMAIL_DB}
 
     cat > ${mysql_domain_alias_maps_cf} <<EOF
 ${CONF_MSG}
-user        = ${MYSQL_BIND_USER}
-password    = ${MYSQL_BIND_PW}
+user        = ${VMAIL_DB_BIND_USER}
+password    = ${VMAIL_DB_BIND_PASSWD}
 hosts       = ${mysql_server}
 port        = ${MYSQL_PORT}
 dbname      = ${VMAIL_DB}
 
     cat > ${mysql_catchall_maps_cf} <<EOF
 ${CONF_MSG}
-user        = ${MYSQL_BIND_USER}
-password    = ${MYSQL_BIND_PW}
+user        = ${VMAIL_DB_BIND_USER}
+password    = ${VMAIL_DB_BIND_PASSWD}
 hosts       = ${mysql_server}
 port        = ${MYSQL_PORT}
 dbname      = ${VMAIL_DB}
 
     cat > ${mysql_domain_alias_catchall_maps_cf} <<EOF
 ${CONF_MSG}
-user        = ${MYSQL_BIND_USER}
-password    = ${MYSQL_BIND_PW}
+user        = ${VMAIL_DB_BIND_USER}
+password    = ${VMAIL_DB_BIND_PASSWD}
 hosts       = ${mysql_server}
 port        = ${MYSQL_PORT}
 dbname      = ${VMAIL_DB}
 
     cat > ${mysql_sender_login_maps_cf} <<EOF
 ${CONF_MSG}
-user        = ${MYSQL_BIND_USER}
-password    = ${MYSQL_BIND_PW}
+user        = ${VMAIL_DB_BIND_USER}
+password    = ${VMAIL_DB_BIND_PASSWD}
 hosts       = ${mysql_server}
 port        = ${MYSQL_PORT}
 dbname      = ${VMAIL_DB}
 
     cat > ${mysql_sender_bcc_maps_domain_cf} <<EOF
 ${CONF_MSG}
-user        = ${MYSQL_BIND_USER}
-password    = ${MYSQL_BIND_PW}
+user        = ${VMAIL_DB_BIND_USER}
+password    = ${VMAIL_DB_BIND_PASSWD}
 hosts       = ${mysql_server}
 port        = ${MYSQL_PORT}
 dbname      = ${VMAIL_DB}
 
     cat > ${mysql_sender_bcc_maps_user_cf} <<EOF
 ${CONF_MSG}
-user        = ${MYSQL_BIND_USER}
-password    = ${MYSQL_BIND_PW}
+user        = ${VMAIL_DB_BIND_USER}
+password    = ${VMAIL_DB_BIND_PASSWD}
 hosts       = ${mysql_server}
 port        = ${MYSQL_PORT}
 dbname      = ${VMAIL_DB}
 
     cat > ${mysql_recipient_bcc_maps_domain_cf} <<EOF
 ${CONF_MSG}
-user        = ${MYSQL_BIND_USER}
-password    = ${MYSQL_BIND_PW}
+user        = ${VMAIL_DB_BIND_USER}
+password    = ${VMAIL_DB_BIND_PASSWD}
 hosts       = ${mysql_server}
 port        = ${MYSQL_PORT}
 dbname      = ${VMAIL_DB}
 
     cat > ${mysql_recipient_bcc_maps_user_cf} <<EOF
 ${CONF_MSG}
-user        = ${MYSQL_BIND_USER}
-password    = ${MYSQL_BIND_PW}
+user        = ${VMAIL_DB_BIND_USER}
+password    = ${VMAIL_DB_BIND_PASSWD}
 hosts       = ${mysql_server}
 port        = ${MYSQL_PORT}
 dbname      = ${VMAIL_DB}

File iRedMail/functions/postgresql.sh

 # -------------------------------------------------------
 pgsql_initialize()
 {
-    ECHO_DEBUG "Configure PostgreSQL database server." 
-
-    ECHO_DEBUG "Starting PostgreSQL"
+    ECHO_INFO "Configure PostgreSQL database server." 
 
     # FreeBSD: Start pgsql when system start up.
     # Warning: We must have 'postgresql_enable=YES' before start/stop mysql daemon.
 postgresql_enable="YES"
 EOF
 
+    backup_file ${PGSQL_CONF_PG_HBA} ${PGSQL_CONF_POSTGRESQL}
+
+    #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 "Copy iRedMail SSL cert/key with strict permission."
+    # SSL is enabled by default.
+    backup_file ${PGSQL_DATA_DIR}/server.{crt,key}
+    rm -f ${PGSQL_DATA_DIR}/server.{crt,key} >/dev/null
+    cp -f ${SSL_CERT_FILE} ${PGSQL_SSL_CERT} >/dev/null
+    cp -f ${SSL_KEY_FILE} ${PGSQL_SSL_KEY} >/dev/null
+    chown ${PGSQL_SYS_USER}:${PGSQL_SYS_GROUP} ${PGSQL_SSL_CERT} ${PGSQL_SSL_KEY}
+    chmod 0600 ${PGSQL_SSL_CERT} ${PGSQL_SSL_KEY}
+    ln -s ${PGSQL_SSL_CERT} ${PGSQL_DATA_DIR}/server.crt >/dev/null
+    ln -s ${PGSQL_SSL_KEY} ${PGSQL_DATA_DIR}/server.key >/dev/null
+
+    ECHO_DEBUG "Start PostgreSQL server"
     ${PGSQL_INIT_SCRIPT} restart >/dev/null 2>&1
 
-    ECHO_DEBUG -n "Sleep 5 seconds for PostgreSQL daemon initialize:"
+    ECHO_INFO -n "Sleep 5 seconds for PostgreSQL daemon initialize:"
     for i in 5 4 3 2 1; do
-        ECHO_DEBUG -n " ${i}" && sleep 1
+        echo -n " ${i}" && sleep 1
     done
-    ECHO_DEBUG '.'
+    echo '.'
 
-    ECHO_DEBUG "Setting password for PostgreSQL admin (${PGSQL_ADMIN_USER})."
-    # TODO
+    ECHO_DEBUG "Setting password for PostgreSQL admin: (${PGSQL_ROOT_USER})."
+    su - ${PGSQL_SYS_USER} -c "psql -d template1" >/dev/null <<EOF
+ALTER USER ${PGSQL_ROOT_USER} WITH ENCRYPTED PASSWORD '${PGSQL_ROOT_PASSWD}';
+EOF
 
-    ECHO_DEBUG "Initialize MySQL database."
-    # TODO
+    ECHO_DEBUG "Generate ${PGSQL_DOT_PGPASS}."
+    cat > ${PGSQL_DOT_PGPASS} <<EOF
+localhost:*:*:${PGSQL_ROOT_USER}:${PGSQL_ROOT_PASSWD}
+EOF
 
-    # Generate PGSQL_INIT_SQL
-    # TODO
+    chown ${PGSQL_SYS_USER}:${PGSQL_SYS_GROUP} ${PGSQL_DOT_PGPASS}
+    chmod 0600 ${PGSQL_DOT_PGPASS} >/dev/null
 
     cat >> ${TIP_FILE} <<EOF
 PostgreSQL:
     * Bind account (read-only):
-        - Name: ${PGSQL_BIND_USER}, Password: ${PGSQL_BIND_PW}
+        - Name: ${VMAIL_DB_BIND_USER}, Password: ${VMAIL_DB_BIND_PASSSWD}
     * Vmail admin account (read-write):
-        - Name: ${PGSQL_ADMIN_USER}, Password: ${PGSQL_ADMIN_PW}
-    * Database stored in: /var/lib/mysql
+        - Name: ${VMAIL_DB_ADMIN_USER}, Password: ${VMAIL_DB_ADMIN_PASSWD}
+    * Database stored in: ${PGSQL_DATA_DIR}
     * RC script: ${PGSQL_INIT_SCRIPT}
-    * Log file: /var/log/mysqld.log
+    * Log file: /var/log/postgresql/
     * See also:
         - ${PGSQL_INIT_SQL}
+        - ${PGSQL_DOT_PGPASS}
 
 EOF
 
 
 pgsql_import_vmail_users()
 {
-    ECHO_DEBUG "Generating SQL template for postfix virtual hosts: ${PGSQL_VMAIL_SQL}."
     export DOMAIN_ADMIN_PASSWD="$(openssl passwd -1 ${DOMAIN_ADMIN_PASSWD})"
     export FIRST_USER_PASSWD="$(openssl passwd -1 ${FIRST_USER_PASSWD})"
 
     # Generate SQL.
     # Modify default SQL template, set storagebasedirectory.
-    #perl -pi -e 's#(.*storagebasedirectory.*DEFAULT).*#${1} "$ENV{STORAGE_BASE_DIR}",#' ${SAMPLE_SQL}
-    #perl -pi -e 's#(.*storagenode.*DEFAULT).*#${1} "$ENV{STORAGE_NODE}",#' ${SAMPLE_SQL}
+    perl -pi -e 's#(.*storagebasedirectory.*DEFAULT).*#${1} "$ENV{STORAGE_BASE_DIR}",#' ${PGSQL_VMAIL_STRUCTURE_SAMPLE}
+    perl -pi -e 's#(.*storagenode.*DEFAULT).*#${1} "$ENV{STORAGE_NODE}",#' ${PGSQL_VMAIL_STRUCTURE_SAMPLE}
 
-    # Mailbox format is 'Maildir/' by default.
-    # TODO:
-    # - Create database to store mail accounts
-    # - Set correct privilege for both ROLEs: vmail, vmailadmin
-    # - Initialize database
-    # - Add first mail domain
-    # - Add first domain admin
-    # - Assign mail domain to admin
-    # - Add first mail user
-    cat >> ${PGSQL_VMAIL_SQL} <<EOF
+    ECHO_DEBUG "Generating SQL template for postfix virtual hosts: ${PGSQL_INIT_SQL_SAMPLE}."
+    cat > ${PGSQL_INIT_SQL_SAMPLE} <<EOF
+-- Create database to store mail accounts
+CREATE DATABASE ${VMAIL_DB} WITH TEMPLATE template0 ENCODING 'UTF8';
+\c vmail;
+\i ${PGSQL_VMAIL_STRUCTURE_SAMPLE}
+
+-- Crete roles:
+-- + vmail: read-only
+-- + vmailadmin: read, write
+CREATE ROLE ${VMAIL_DB_BIND_USER} WITH ENCRYPTED PASSWORD '${VMAIL_DB_BIND_PASSSWD}' NOSUPERUSER NOCREATEDB NOCREATEROLE;
+
+-- Set correct privilege for ROLE: vmail
+GRANT SELECT ON admin,alias,alias_domain,domain,domain_admins,mailbox,mailbox,recipient_bcc_domain,recipient_bcc_user,sender_bcc_domain,sender_bcc_user TO ${VMAIL_DB_BIND_USER};
+GRANT SELECT,UPDATE,INSERT,DELETE ON share_folder,used_quota TO ${VMAIL_DB_BIND_USER};
+
+-- Set correct privilege for ROLE: vmailadmin
+GRANT SELECT,UPDATE,INSERT ON admin,alias,alias_domain,domain,domain_admins,mailbox,mailbox,recipient_bcc_domain,recipient_bcc_user,sender_bcc_domain,sender_bcc_user,share_folder,used_quota TO ${VMAIL_DB_ADMIN_USER};
+
+-- Add first mail domain
+-- Add first domain admin
+-- Assign mail domain to admin
+-- Add first mail user
 EOF
 
-    ECHO_DEBUG "Import postfix virtual hosts/users: ${PGSQL_VMAIL_SQL}."
-    # TODO
+    ECHO_DEBUG "Import postfix virtual hosts/users: ${PGSQL_INIT_SQL_SAMPLE}."
+    su - ${PGSQL_SYS_USER} -c "psql -f ${PGSQL_INIT_SQL_SAMPLE}" >/dev/null
 
     cat >> ${TIP_FILE} <<EOF
 Virtual Users:
-    - ${PGSQL_VMAIL_SQL}
+    - ${PGSQL_INIT_SQL_SAMPLE}
     - ${PGSQL_VMAIL_STRUCTURE_SAMPLE}
 
 EOF
 
-    echo 'export status_mysql_import_vmail_users="DONE"' >> ${STATUS_FILE}
+    echo 'export status_pgsql_import_vmail_users="DONE"' >> ${STATUS_FILE}
 }

File iRedMail/iRedMail.sh

 . ${CONF_DIR}/openldap
 . ${CONF_DIR}/phpldapadmin
 . ${CONF_DIR}/mysql
+. ${CONF_DIR}/postgresql
 . ${CONF_DIR}/postfix
 . ${CONF_DIR}/policy_server
 . ${CONF_DIR}/iredapd
 
 # User/Group: vmail. We will export vmail uid/gid here.
 . ${FUNCTIONS_DIR}/user_vmail.sh
+
 . ${FUNCTIONS_DIR}/apache_php.sh
+
+# LDAP
 . ${FUNCTIONS_DIR}/openldap.sh
 . ${FUNCTIONS_DIR}/phpldapadmin.sh
+
+# MySQL
 . ${FUNCTIONS_DIR}/mysql.sh
 . ${FUNCTIONS_DIR}/phpmyadmin.sh
 
+# PostgreSQL
+. ${FUNCTIONS_DIR}/postgresql.sh
+
 # Switch backend
 . ${FUNCTIONS_DIR}/backend.sh