Commits

Zhang Huangbin committed 9fc0269

Dovecot, Roundcube, Policyd-2 (cluebringer) now work with PostgreSQL.

Comments (0)

Files changed (11)

iRedMail/conf/apache_php

 else
     export PHP_CONN_TYPE='mysqli'
 fi
+
+[ X"${BACKEND}" == X"PGSQL" ] && export PHP_CONN_TYPE='pgsql'

iRedMail/conf/dovecot

     export DOVECOT_CONF='/etc/dovecot/dovecot.conf'
     export DOVECOT_LDAP_CONF='/etc/dovecot/dovecot-ldap.conf'
     export DOVECOT_MYSQL_CONF='/etc/dovecot/dovecot-mysql.conf'
+    export DOVECOT_PGSQL_CONF='/etc/dovecot/dovecot-pgsql.conf'
     export DOVECOT_REALTIME_QUOTA_CONF='/etc/dovecot/used-quota.conf'
     export DOVECOT_SHARE_FOLDER_CONF='/etc/dovecot/share-folder.conf'
 
         export DOVECOT_CONF='/etc/dovecot.conf'
         export DOVECOT_LDAP_CONF='/etc/dovecot-ldap.conf'
         export DOVECOT_MYSQL_CONF='/etc/dovecot-mysql.conf'
+        export DOVECOT_PGSQL_CONF='/etc/dovecot-pgsql.conf'
         export DOVECOT_REALTIME_QUOTA_CONF='/etc/dovecot-used-quota.conf'
         export DOVECOT_SHARE_FOLDER_CONF='/etc/dovecot-share-folder.conf'
     fi
     export DOVECOT_CONF='/etc/dovecot/dovecot.conf'
     export DOVECOT_LDAP_CONF='/etc/dovecot/dovecot-ldap.conf'
     export DOVECOT_MYSQL_CONF='/etc/dovecot/dovecot-mysql.conf'
+    export DOVECOT_PGSQL_CONF='/etc/dovecot/dovecot-pgsql.conf'
     export DOVECOT_REALTIME_QUOTA_CONF='/etc/dovecot/dovecot-used-quota.conf'
     export DOVECOT_SHARE_FOLDER_CONF='/etc/dovecot/dovecot-share-folder.conf'
     export DOVECOT_DELIVER='/usr/lib/dovecot/deliver'
     export DOVECOT_CONF='/etc/dovecot/dovecot.conf'
     export DOVECOT_LDAP_CONF='/etc/dovecot/dovecot-ldap.conf'
     export DOVECOT_MYSQL_CONF='/etc/dovecot/dovecot-mysql.conf'
+    export DOVECOT_PGSQL_CONF='/etc/dovecot/dovecot-pgsql.conf'
     export DOVECOT_REALTIME_QUOTA_CONF='/etc/dovecot/dovecot-used-quota.conf'
     export DOVECOT_SHARE_FOLDER_CONF='/etc/dovecot/dovecot-share-folder.conf'
     export DOVECOT_DELIVER='/usr/lib/dovecot/deliver'
     export DOVECOT_CONF='/usr/local/etc/dovecot.conf'
     export DOVECOT_LDAP_CONF='/usr/local/etc/dovecot-ldap.conf'
     export DOVECOT_MYSQL_CONF='/usr/local/etc/dovecot-mysql.conf'
+    export DOVECOT_PGSQL_CONF='/usr/local/etc/dovecot-pgsql.conf'
     export DOVECOT_REALTIME_QUOTA_CONF='/usr/local/etc/dovecot-used-quota.conf'
     export DOVECOT_SHARE_FOLDER_CONF='/usr/local/etc/dovecot-share-folder.conf'
     export DOVECOT_DELIVER='/usr/local/libexec/dovecot/deliver'

iRedMail/conf/policyd

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

iRedMail/conf/postgresql

 export PGSQL_SYS_GROUP='postgres'
 
 export PGSQL_SERVER='localhost'
+export PGSQL_PORT='5432'
 export PGSQL_ROOT_USER='postgres'
 
 export PGSQL_SYS_USER_HOME='/var/lib/postgresql'

iRedMail/functions/cluebringer.sh

     # Configure '[database]' section.
     #
     # DSN
-    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}
+    if [ X"${BACKEND}" == X"OPENLDAP" -o X"${BACKEND}" == X"MYSQL" ]; then
+        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}
+    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}
+    fi
+
     # Database
-    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_Name=).*/${1}$ENV{CLUEBRINGER_DB_NAME}/' ${CLUEBRINGER_CONF}
     perl -pi -e 's/^(Username=).*/${1}$ENV{CLUEBRINGER_DB_USER}/' ${CLUEBRINGER_CONF}
     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}"
     if [ X"${DISTRO}" == X"RHEL" -o X"${DISTRO}" == X"SUSE" ]; then
-        cat > ${tmp_sql} <<EOF
+        if [ X"${BACKEND}" == X"OPENLDAP" -o X"${BACKEND}" == X"MYSQL" ]; then
+            cat > ${tmp_sql} <<EOF
 # Import SQL structure template.
 SOURCE $(eval ${LIST_FILES_IN_PKG} ${PKG_CLUEBRINGER} | grep '/DATABASE.mysql$');
 
 GRANT SELECT,INSERT,UPDATE,DELETE ON ${CLUEBRINGER_DB_NAME}.* TO "${CLUEBRINGER_DB_USER}"@localhost IDENTIFIED BY "${CLUEBRINGER_DB_PASSWD}";
 FLUSH PRIVILEGES;
 EOF
+        elif [ X"${BACKEND}" == X"PGSQL" ]; then
+            :
+        fi
 
     elif [ X"${DISTRO}" == X"DEBIAN" -o X"${DISTRO}" == X"UBUNTU" ]; then
-        cat > ${tmp_sql} <<EOF
+        if [ X"${BACKEND}" == X"OPENLDAP" -o X"${BACKEND}" == X"MYSQL" ]; then
+            cat > ${tmp_sql} <<EOF
 CREATE DATABASE ${CLUEBRINGER_DB_NAME};
 GRANT SELECT,INSERT,UPDATE,DELETE ON ${CLUEBRINGER_DB_NAME}.* TO "${CLUEBRINGER_DB_USER}"@localhost IDENTIFIED BY "${CLUEBRINGER_DB_PASSWD}";
 USE ${CLUEBRINGER_DB_NAME};
 EOF
 
-        if [ X"${BACKEND}" == X"OPENLDAP" -o X"${BACKEND}" == X"MYSQL" ]; then
+            # Append cluebringer default sql template.
             gunzip -c /usr/share/doc/postfix-cluebringer/database/policyd-db.mysql.gz >> ${tmp_sql}
+
         elif [ X"${BACKEND}" == X"PGSQL" ]; then
+            cat > ${tmp_sql} <<EOF
+CREATE DATABASE ${CLUEBRINGER_DB_NAME} WITH TEMPLATE template0 ENCODING 'UTF8';
+CREATE USER ${CLUEBRINGER_DB_USER} WITH ENCRYPTED PASSWORD '${CLUEBRINGER_DB_PASSWD}' NOSUPERUSER NOCREATEDB NOCREATEROLE;
+\c ${CLUEBRINGER_DB_NAME};
+EOF
+
+            # Append cluebringer default sql template.
             gunzip -c /usr/share/doc/postfix-cluebringer/database/policyd-db.pgsql.gz >> ${tmp_sql}
+
+            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};
+EOF
         fi
 
     elif [ X"${DISTRO}" == X"FREEBSD" ]; then
         # Template file will create database: policyd.
-        cat > ${tmp_sql} <<EOF
+        if [ X"${BACKEND}" == X"OPENLDAP" -o X"${BACKEND}" == X"MYSQL" ]; then
+            cat > ${tmp_sql} <<EOF
 # Import SQL structure template.
 SOURCE $(eval ${LIST_FILES_IN_PKG} "${PKG_CLUEBRINGER}*" | grep '/DATABASE.mysql$');
 
 FLUSH PRIVILEGES;
 EOF
 
-    else
-        :
+        elif [ X"${BACKEND}" == X"PGSQL" ]; then
+            :
+        fi
     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
+-- Initialize
 $(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 
+        # Initial cluebringer db.
+        su - ${PGSQL_SYS_USER} -c "psql -f ${tmp_sql} >/dev/null" >/dev/null 
+
+        # Enable greylisting on all inbound emails by default.
+        su - ${PGSQL_SYS_USER} -c "psql" >/dev/null  <<EOF
+\c ${CLUEBRINGER_DB_NAME};
+
+-- 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
     fi
 
     rm -rf ${tmp_sql} 2>/dev/null

iRedMail/functions/dovecot2.sh

         chmod 0664 ${DOVECOT_CONF} && \
         ECHO_DEBUG "Configure dovecot: ${DOVECOT_CONF}."
 
-        cat > ${DOVECOT_CONF} <<EOF
-${CONF_MSG}
-EOF
-
-    cat ${SAMPLE_DIR}/conf/dovecot2.conf >> ${DOVECOT_CONF}
+    cp ${SAMPLE_DIR}/conf/dovecot2.conf ${DOVECOT_CONF}
 
     # Base directory.
     perl -pi -e 's#PH_BASE_DIR#$ENV{DOVECOT_BASE_DIR}#' ${DOVECOT_CONF}
         perl -pi -e 's#PH_USERDB_DRIVER#ldap#' ${DOVECOT_CONF}
         perl -pi -e 's#PH_PASSDB_ARGS#$ENV{DOVECOT_LDAP_CONF}#' ${DOVECOT_CONF}
         perl -pi -e 's#PH_PASSDB_DRIVER#ldap#' ${DOVECOT_CONF}
-    else
+    elif [ X"${BACKEND}" == X"MYSQL" ]; then
         # MySQL.
         perl -pi -e 's#PH_USERDB_ARGS#$ENV{DOVECOT_MYSQL_CONF}#' ${DOVECOT_CONF}
         perl -pi -e 's#PH_USERDB_DRIVER#sql#' ${DOVECOT_CONF}
         perl -pi -e 's#PH_PASSDB_ARGS#$ENV{DOVECOT_MYSQL_CONF}#' ${DOVECOT_CONF}
         perl -pi -e 's#PH_PASSDB_DRIVER#sql#' ${DOVECOT_CONF}
+    elif [ X"${BACKEND}" == X"PGSQL" ]; then
+        # PostgreSQL.
+        perl -pi -e 's#PH_USERDB_ARGS#$ENV{DOVECOT_PGSQL_CONF}#' ${DOVECOT_CONF}
+        perl -pi -e 's#PH_USERDB_DRIVER#sql#' ${DOVECOT_CONF}
+        perl -pi -e 's#PH_PASSDB_ARGS#$ENV{DOVECOT_PGSQL_CONF}#' ${DOVECOT_CONF}
+        perl -pi -e 's#PH_PASSDB_DRIVER#sql#' ${DOVECOT_CONF}
     fi
 
     perl -pi -e 's#PH_AUTH_SOCKET_PATH#$ENV{DOVECOT_AUTH_SOCKET_PATH}#' ${DOVECOT_CONF}
         # Set file permission.
         chmod 0500 ${DOVECOT_LDAP_CONF}
 
-    else
+    elif [ X"${BACKEND}" == X"MYSQL" ]; then
 
         backup_file ${DOVECOT_MYSQL_CONF}
-        cat > ${DOVECOT_MYSQL_CONF} <<EOF
-driver = mysql
-default_pass_scheme = CRYPT
-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, \
-CONCAT('*:bytes=', mailbox.quota*1048576) AS quota_rule \
-FROM mailbox,domain \
-WHERE mailbox.username='%u' \
-AND mailbox.domain='%d' \
-AND mailbox.enable%Ls%Lc=1 \
-AND mailbox.domain=domain.domain \
-AND domain.backupmx=0 \
-AND domain.active=1 \
-AND mailbox.active=1
-EOF
+        cp -f ${SAMPLE_DIR}/conf/dovecot2-mysql.conf ${DOVECOT_MYSQL_CONF}
+
+        perl -pi -e 's#PH_MYSQL_SERVER#$ENV{MYSQL_SERVER}#' ${DOVECOT_MYSQL_CONF}
+        perl -pi -e 's#PH_VMAIL_DB#$ENV{VMAIL_DB}#' ${DOVECOT_MYSQL_CONF}
+        perl -pi -e 's#PH_VMAIL_DB_BIND_USER#$ENV{VMAIL_DB_BIND_USER}#' ${DOVECOT_MYSQL_CONF}
+        perl -pi -e 's#PH_VMAIL_DB_BIND_PASSWD#$ENV{VMAIL_DB_BIND_PASSWD}#' ${DOVECOT_MYSQL_CONF}
 
         # Set file permission.
         chmod 0550 ${DOVECOT_MYSQL_CONF}
+    elif [ X"${BACKEND}" == X"PGSQL" ]; then
+
+        backup_file ${DOVECOT_PGSQL_CONF}
+        cp -f ${SAMPLE_DIR}/conf/dovecot2-pgsql.conf ${DOVECOT_PGSQL_CONF}
+
+        perl -pi -e 's#PH_PGSQL_SERVER#$ENV{PGSQL_SERVER}#' ${DOVECOT_PGSQL_CONF}
+        perl -pi -e 's#PH_VMAIL_DB#$ENV{VMAIL_DB}#' ${DOVECOT_PGSQL_CONF}
+        perl -pi -e 's#PH_VMAIL_DB_BIND_USER#$ENV{VMAIL_DB_BIND_USER}#' ${DOVECOT_PGSQL_CONF}
+        perl -pi -e 's#PH_VMAIL_DB_BIND_PASSWD#$ENV{VMAIL_DB_BIND_PASSWD}#' ${DOVECOT_PGSQL_CONF}
+
+        # Set file permission.
+        chmod 0550 ${DOVECOT_PGSQL_CONF}
     fi
 
 

iRedMail/functions/packages.sh

         ENABLED_SERVICES="${ENABLED_SERVICES} apache2"
 
     elif [ X"${DISTRO}" == X"DEBIAN" -o X"${DISTRO}" == X"UBUNTU" ]; then
-        ALL_PKGS="${ALL_PKGS} apache2 apache2-mpm-prefork apache2.2-common libapache2-mod-php5 libapache2-mod-auth-mysql php5-cli php5-imap php5-gd php5-mcrypt php5-mysql php5-ldap"
+        ALL_PKGS="${ALL_PKGS} apache2 apache2-mpm-prefork apache2.2-common libapache2-mod-php5 libapache2-mod-auth-mysql php5-cli php5-imap php5-gd php5-mcrypt php5-mysql php5-ldap php5-pgsql"
 
         if [ X"${DISTRO_CODENAME}" != X"oneiric" ]; then
             ALL_PKGS="${ALL_PKGS} php5-mhash"

iRedMail/functions/postgresql.sh

     cat >> ${TIP_FILE} <<EOF
 PostgreSQL:
     * Bind account (read-only):
-        - Name: ${VMAIL_DB_BIND_USER}, Password: ${VMAIL_DB_BIND_PASSSWD}
+        - Name: ${VMAIL_DB_BIND_USER}, Password: ${VMAIL_DB_BIND_PASSWD}
     * Vmail admin account (read-write):
         - Name: ${VMAIL_DB_ADMIN_USER}, Password: ${VMAIL_DB_ADMIN_PASSWD}
     * Database stored in: ${PGSQL_DATA_DIR}
     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}",#' ${PGSQL_VMAIL_STRUCTURE_SAMPLE}
-    perl -pi -e 's#(.*storagenode.*DEFAULT).*#${1} "$ENV{STORAGE_NODE}",#' ${PGSQL_VMAIL_STRUCTURE_SAMPLE}
+    # Modify default SQL template, set storagebasedirectory, storagenode.
+    perl -pi -e 's#(.*storagebasedirectory.*DEFAULT..)(.*)#${1}$ENV{STORAGE_BASE_DIR}${2}#' ${PGSQL_VMAIL_STRUCTURE_SAMPLE}
+    perl -pi -e 's#(.*storagenode.*DEFAULT..)(.*)#${1}$ENV{STORAGE_NODE}${2}#' ${PGSQL_VMAIL_STRUCTURE_SAMPLE}
 
     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}
+\c ${VMAIL_DB};
+\i ${PGSQL_SYS_USER_HOME}/vmail.sql;
 
 -- Crete roles:
 -- + vmail: read-only
 -- + vmailadmin: read, write
-CREATE ROLE ${VMAIL_DB_BIND_USER} WITH ENCRYPTED PASSWORD '${VMAIL_DB_BIND_PASSSWD}' NOSUPERUSER NOCREATEDB NOCREATEROLE;
+CREATE USER ${VMAIL_DB_BIND_USER} WITH ENCRYPTED PASSWORD '${VMAIL_DB_BIND_PASSWD}' NOSUPERUSER NOCREATEDB NOCREATEROLE;
+CREATE USER ${VMAIL_DB_ADMIN_USER} WITH ENCRYPTED PASSWORD '${VMAIL_DB_ADMIN_PASSWD}' 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 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
+INSERT INTO domain (domain,transport,created) VALUES ('${FIRST_DOMAIN}', '${TRANSPORT}', NOW());
+
 -- Add first domain admin
--- Assign mail domain to admin
+INSERT INTO admin (username,password,created) VALUES ('${DOMAIN_ADMIN_NAME}@${FIRST_DOMAIN}','${DOMAIN_ADMIN_PASSWD}', NOW());
+INSERT INTO domain_admins (username,domain,created) VALUES ('${DOMAIN_ADMIN_NAME}@${FIRST_DOMAIN}','ALL', NOW());
+
 -- Add first mail user
+INSERT INTO mailbox (username,password,name,maildir,quota,domain,created) VALUES ('${FIRST_USER}@${FIRST_DOMAIN}','${FIRST_USER_PASSWD}','${FIRST_USER}','$( hash_domain ${FIRST_DOMAIN})/$( hash_maildir ${FIRST_USER} )',100, '${FIRST_DOMAIN}', NOW());
+INSERT INTO alias (address,goto,domain,created) VALUES ('${FIRST_USER}@${FIRST_DOMAIN}', '${FIRST_USER}@${FIRST_DOMAIN}', '${FIRST_DOMAIN}', NOW());
 EOF
 
     ECHO_DEBUG "Import postfix virtual hosts/users: ${PGSQL_INIT_SQL_SAMPLE}."
-    su - ${PGSQL_SYS_USER} -c "psql -f ${PGSQL_INIT_SQL_SAMPLE}" >/dev/null
+    cp -f ${PGSQL_VMAIL_STRUCTURE_SAMPLE} ${PGSQL_SYS_USER_HOME}/vmail.sql >/dev/null
+    cp -f ${PGSQL_INIT_SQL_SAMPLE} ${PGSQL_SYS_USER_HOME}/init.sql >/dev/null
+    chmod 0777 ${PGSQL_SYS_USER_HOME}/{vmail,init}.sql >/dev/null
+    su - ${PGSQL_SYS_USER} -c "psql -f ${PGSQL_SYS_USER_HOME}/init.sql" >/dev/null
+    rm -f ${PGSQL_SYS_USER_HOME}/{vmail,init}.sql >/dev/null
 
     cat >> ${TIP_FILE} <<EOF
 Virtual Users:

iRedMail/functions/roundcubemail.sh

 {
     ECHO_DEBUG "Import MySQL database and privileges for Roundcubemail."
 
-    mysql -h${MYSQL_SERVER} -P${MYSQL_PORT} -u${MYSQL_ROOT_USER} -p"${MYSQL_ROOT_PASSWD}" <<EOF
-/* Create database and grant privileges. */
+    # 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
+-- 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}';
 
-/* Import Roundcubemail SQL template. */
+-- Import Roundcubemail SQL template
 USE ${RCM_DB};
 SOURCE ${RCM_HTTPD_ROOT}/SQL/mysql.initial.sql;
 
 FLUSH PRIVILEGES;
 EOF
+    elif [ X"${BACKEND}" == X"PGSQL" ]; then
+        cp -f ${RCM_HTTPD_ROOT}/SQL/postgres.initial.sql ${PGSQL_SYS_USER_HOME}/rcm.sql >/dev/null
+        chmod 0777 ${PGSQL_SYS_USER_HOME}/rcm.sql >/dev/null
+
+        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;
+
+-- Import Roundcubemail SQL template
+\c ${RCM_DB};
+\i ${PGSQL_SYS_USER_HOME}/rcm.sql;
+
+-- 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 privilege to update password through roundcube webmail
+\c ${VMAIL_DB};
+GRANT UPDATE,SELECT ON mailbox TO ${RCM_DB_USER};
+EOF
+        rm -f ${PGSQL_SYS_USER_HOME}/rcm.sql >/dev/null
+    fi
+
 
     # Do not grant privileges while backend is not MySQL.
     if [ X"${BACKEND}" == X"MYSQL" ]; then
 
     export RCM_DB_USER RCM_DB_PASSWD RCMD_DB MYSQL_SERVER FIRST_DOMAIN
 
-    perl -pi -e 's#(.*db_dsnw.*= )(.*)#${1}"$ENV{'PHP_CONN_TYPE'}://$ENV{'RCM_DB_USER'}:$ENV{'RCM_DB_PASSWD'}\@$ENV{'MYSQL_SERVER'}/$ENV{'RCM_DB'}";#' db.inc.php
+    perl -pi -e 's#(.*db_dsnw.*= )(.*)#${1}"$ENV{PHP_CONN_TYPE}://$ENV{RCM_DB_USER}:$ENV{RCM_DB_PASSWD}\@$ENV{MYSQL_SERVER}/$ENV{RCM_DB}";#' db.inc.php
 
     # ----------------------------------
     # LOGGING/DEBUGGING

iRedMail/samples/conf/dovecot2-mysql.conf

+driver = mysql
+default_pass_scheme = CRYPT
+connect = host=PH_MYSQL_SERVER dbname=PH_VMAIL_DB user=PH_VMAIL_DB_BIND_USER password=PH_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, \
+    CONCAT('*:bytes=', mailbox.quota*1048576) AS quota_rule \
+FROM mailbox,domain \
+WHERE mailbox.username='%u' \
+    AND mailbox.domain='%d' \
+    AND mailbox.enable%Ls%Lc=1 \
+    AND mailbox.domain=domain.domain \
+    AND domain.backupmx=0 \
+    AND domain.active=1 \
+    AND mailbox.active=1

iRedMail/samples/conf/dovecot2-pgsql.conf

+driver = pgsql
+default_pass_scheme = CRYPT
+connect = host=PH_PGSQL_SERVER dbname=PH_VMAIL_DB user=PH_VMAIL_DB_BIND_USER password=PH_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, \
+    CONCAT('*:bytes=', mailbox.quota*1048576) AS quota_rule \
+FROM mailbox,domain \
+WHERE mailbox.username='%u' \
+    AND mailbox.domain='%d' \
+    AND mailbox.enable%Ls%Lc=1 \
+    AND mailbox.domain=domain.domain \
+    AND domain.backupmx=0 \
+    AND domain.active=1 \
+    AND mailbox.active=1