diff --git a/conf/global b/conf/global
index e354bad..0ca86f8 100644
--- a/conf/global
+++ b/conf/global
@@ -74,6 +74,11 @@ export MYSQL_ROOT_USER="${MYSQL_ROOT_USER:=root}"
# Use existing MySQL server, no matter it's local or remote.
export USE_EXISTING_MYSQL="${USE_EXISTING_MYSQL:=NO}"
+
+# Create a MySQL user, as some of the latest version don't allow you to use
+# IDENTIFIED BY
+export USE_MYSQL_CREATE_USER="${USE_MYSQL_CREATE_USER:=NO}"
+
# Create required SQL databases and records for applications
# Should be set to NO if you're installing iRedMail with a MySQL cluster, and
# you're installing on second or later nodes.
diff --git a/functions/amavisd.sh b/functions/amavisd.sh
index 29757d8..757614f 100644
--- a/functions/amavisd.sh
+++ b/functions/amavisd.sh
@@ -33,17 +33,29 @@ amavisd_initialize_db()
-- 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}'@'${MYSQL_GRANT_HOST}' IDENTIFIED BY '${AMAVISD_DB_PASSWD}';
--- GRANT SELECT,INSERT,UPDATE,DELETE ON ${AMAVISD_DB_NAME}.* TO '${AMAVISD_DB_USER}'@'${HOSTNAME}' IDENTIFIED BY '${AMAVISD_DB_PASSWD}';
-
-- Import Amavisd SQL template
USE ${AMAVISD_DB_NAME};
SOURCE ${AMAVISD_DB_MYSQL_TMPL};
SOURCE ${SAMPLE_DIR}/amavisd/default_spam_policy.sql;
+EOF
+ if [ X"${USE_MYSQL_CREATE_USER}" == X'YES' ]; then
+ ${MYSQL_CLIENT_ROOT} <<EOF
+-- Grant privileges
+CREATE USER IF NOT EXISTS '${AMAVISD_DB_NAME}'@'${MYSQL_GRANT_HOST}' IDENTIFIED WITH mysql_native_password BY '${AMAVISD_DB_PASSWD}';
+-- CREATE USER IF NOT EXISTS '${AMAVISD_DB_NAME}'@'${HOSTNAME}' IDENTIFIED WITH mysql_native_password BY '${AMAVISD_DB_PASSWD}';
+GRANT SELECT,INSERT,UPDATE,DELETE ON ${AMAVISD_DB_NAME}.* TO '${AMAVISD_DB_USER}'@'${MYSQL_GRANT_HOST}';
+-- GRANT SELECT,INSERT,UPDATE,DELETE ON ${AMAVISD_DB_NAME}.* TO '${AMAVISD_DB_USER}'@'${MYSQL_GRANT_HOST}';
FLUSH PRIVILEGES;
EOF
+ else
+ ${MYSQL_CLIENT_ROOT} <<EOF
+-- Grant privileges
+GRANT SELECT,INSERT,UPDATE,DELETE ON ${AMAVISD_DB_NAME}.* TO '${AMAVISD_DB_USER}'@'${MYSQL_GRANT_HOST}' IDENTIFIED BY '${AMAVISD_DB_PASSWD}';
+-- GRANT SELECT,INSERT,UPDATE,DELETE ON ${AMAVISD_DB_NAME}.* TO '${AMAVISD_DB_USER}'@'${HOSTNAME}' IDENTIFIED BY '${AMAVISD_DB_PASSWD}';
+FLUSH PRIVILEGES;
+EOF
+ fi
# Generate .my.cnf file
cat > /root/.my.cnf-${AMAVISD_DB_USER} <<EOF
diff --git a/functions/iredadmin.sh b/functions/iredadmin.sh
index a23bac1..2541a3d 100644
--- a/functions/iredadmin.sh
+++ b/functions/iredadmin.sh
@@ -72,10 +72,24 @@ CREATE DATABASE IF NOT EXISTS ${IREDADMIN_DB_NAME} DEFAULT CHARACTER SET utf8 CO
# Import SQL template.
USE ${IREDADMIN_DB_NAME};
SOURCE ${IREDADMIN_HTTPD_ROOT}/SQL/iredadmin.mysql;
+EOF
+
+ if [ X"${USE_MYSQL_CREATE_USER}" == X'YES' ]; then
+ ${MYSQL_CLIENT_ROOT} <<EOF
+-- Grant privileges
+CREATE USER IF NOT EXISTS '${IREDADMIN_DB_USER}'@'${MYSQL_GRANT_HOST}' IDENTIFIED WITH mysql_native_password BY '${IREDADMIN_DB_PASSWD}';
+-- CREATE USER IF NOT EXISTS '${IREDADMIN_DB_USER}'@'${HOSTNAME}' IDENTIFIED WITH mysql_native_password BY '${IREDADMIN_DB_PASSWD}';
+GRANT ALL ON ${IREDADMIN_DB_NAME}.* TO '${IREDADMIN_DB_USER}'@'${MYSQL_GRANT_HOST}';
+-- GRANT ALL ON ${IREDADMIN_DB_NAME}.* TO '${IREDADMIN_DB_USER}'@'${HOSTNAME}';
+FLUSH PRIVILEGES;
+EOF
+ else
+ ${MYSQL_CLIENT_ROOT} <<EOF
GRANT ALL ON ${IREDADMIN_DB_NAME}.* TO '${IREDADMIN_DB_USER}'@'${MYSQL_GRANT_HOST}' IDENTIFIED BY '${IREDADMIN_DB_PASSWD}';
-- GRANT ALL ON ${IREDADMIN_DB_NAME}.* TO '${IREDADMIN_DB_USER}'@'${HOSTNAME}' IDENTIFIED BY '${IREDADMIN_DB_PASSWD}';
FLUSH PRIVILEGES;
EOF
+ fi
# Generate .my.cnf file
cat > /root/.my.cnf-${IREDADMIN_DB_USER} <<EOF
diff --git a/functions/iredapd.sh b/functions/iredapd.sh
index e8fbbe4..d52f90d 100644
--- a/functions/iredapd.sh
+++ b/functions/iredapd.sh
@@ -90,9 +90,6 @@ CREATE DATABASE IF NOT EXISTS ${IREDAPD_DB_NAME} DEFAULT CHARACTER SET utf8 COLL
-- Import SQL template.
USE ${IREDAPD_DB_NAME};
SOURCE ${IREDAPD_ROOT_DIR_SYMBOL_LINK}/SQL/iredapd.mysql;
-GRANT ALL ON ${IREDAPD_DB_NAME}.* TO '${IREDAPD_DB_USER}'@'${MYSQL_GRANT_HOST}' IDENTIFIED BY '${IREDAPD_DB_PASSWD}';
--- GRANT ALL ON ${IREDAPD_DB_NAME}.* TO '${IREDAPD_DB_USER}'@'${HOSTNAME}' IDENTIFIED BY '${IREDAPD_DB_PASSWD}';
-FLUSH PRIVILEGES;
-- Enable greylisting by default.
SOURCE ${IREDAPD_ROOT_DIR_SYMBOL_LINK}/SQL/enable_global_greylisting.sql;
@@ -104,6 +101,23 @@ SOURCE ${IREDAPD_ROOT_DIR_SYMBOL_LINK}/SQL/greylisting_whitelist_domains.sql;
SOURCE ${IREDAPD_ROOT_DIR_SYMBOL_LINK}/SQL/wblist_rdns.sql;
EOF
+ if [ X"${USE_MYSQL_CREATE_USER}" == X'YES' ]; then
+ ${MYSQL_CLIENT_ROOT} <<EOF
+ -- Grant privileges
+CREATE USER IF NOT EXISTS '${IREDAPD_DB_USER}'@'${MYSQL_GRANT_HOST}' IDENTIFIED WITH mysql_native_password BY '${IREDAPD_DB_PASSWD}';
+-- CREATE USER IF NOT EXISTS '${IREDAPD_DB_USER}'@'${HOSTNAME}' IDENTIFIED WITH mysql_native_password BY '${IREDAPD_DB_PASSWD}';
+GRANT ALL ON ${IREDAPD_DB_NAME}.* TO '${IREDAPD_DB_USER}'@'${MYSQL_GRANT_HOST}';
+-- GRANT ALL ON '${IREDAPD_DB_NAME}'.* TO '${IREDAPD_DB_USER}'@'${HOSTNAME}';
+FLUSH PRIVILEGES;
+EOF
+ else
+ ${MYSQL_CLIENT_ROOT} <<EOF
+GRANT ALL ON ${IREDAPD_DB_NAME}.* TO '${IREDAPD_DB_USER}'@'${MYSQL_GRANT_HOST}' IDENTIFIED BY '${IREDAPD_DB_PASSWD}';
+-- GRANT ALL ON ${IREDAPD_DB_NAME}.* TO '${IREDAPD_DB_USER}'@'${HOSTNAME}' IDENTIFIED BY '${IREDAPD_DB_PASSWD}';
+FLUSH PRIVILEGES;
+EOF
+ fi
+
# Generate .my.cnf file
cat > /root/.my.cnf-${IREDAPD_DB_USER} <<EOF
[client]
diff --git a/functions/mysql.sh b/functions/mysql.sh
index 4f0cff8..1ad1f7b 100644
--- a/functions/mysql.sh
+++ b/functions/mysql.sh
@@ -208,7 +208,12 @@ mysql_grant_permission_on_remote_server()
if [ X"${USE_EXISTING_MYSQL}" == X'YES' -a X"${MYSQL_SERVER_ADDRESS}" != X'127.0.0.1' ]; then
ECHO_DEBUG "Grant access privilege to ${MYSQL_ROOT_USER}@${MYSQL_GRANT_HOST} ..."
+ if [ X"${USE_MYSQL_CREATE_USER}" == X'YES' ]; then
+ cp -f ${SAMPLE_DIR}/mysql/sql/remote_grant_permission_user.sql ${RUNTIME_DIR}/remote_grant_permission.sql
+ else
cp -f ${SAMPLE_DIR}/mysql/sql/remote_grant_permission.sql ${RUNTIME_DIR}/
+ fi
+
perl -pi -e 's#PH_MYSQL_ROOT_USER#$ENV{MYSQL_ROOT_USER}#g' ${RUNTIME_DIR}/remote_grant_permission.sql
perl -pi -e 's#PH_MYSQL_GRANT_HOST#$ENV{MYSQL_GRANT_HOST}#g' ${RUNTIME_DIR}/remote_grant_permission.sql
perl -pi -e 's#PH_HOSTNAME#$ENV{HOSTNAME}#g' ${RUNTIME_DIR}/remote_grant_permission.sql
@@ -250,7 +255,12 @@ mysql_remove_insecure_data()
mysql_import_vmail_users()
{
ECHO_DEBUG "Generate sample SQL templates."
+ if [ X"${USE_MYSQL_CREATE_USER}" == X'YES' ]; then
+ cp -f ${SAMPLE_DIR}/mysql/sql/init_vmail_db_user.sql ${RUNTIME_DIR}/init_vmail_db.sql
+ else
cp -f ${SAMPLE_DIR}/mysql/sql/init_vmail_db.sql ${RUNTIME_DIR}/
+ fi
+
cp -f ${SAMPLE_DIR}/iredmail/iredmail.mysql ${RUNTIME_DIR}/iredmail.sql
cp -f ${SAMPLE_DIR}/mysql/sql/add_first_domain_and_user.sql ${RUNTIME_DIR}/
diff --git a/functions/netdata.sh b/functions/netdata.sh
index 9b3fe47..5c31193 100644
--- a/functions/netdata.sh
+++ b/functions/netdata.sh
@@ -90,10 +90,18 @@ netdata_module_config()
# MySQL & PostgreSQL
if [ X"${BACKEND}" == X'OPENLDAP' -o X"${BACKEND}" == X'MYSQL' ]; then
ECHO_DEBUG "Create MySQL user ${NETDATA_DB_USER} with minimal privilege: USAGE."
+ if [ X"${USE_EXISTING_MYSQL}" == X'YES' ]; then
+ ${MYSQL_CLIENT_ROOT} >> ${INSTALL_LOG} 2>&1 <<EOF
+CREATE USER IF NOT EXISTS '${NETDATA_DB_USER}'@'${MYSQL_GRANT_HOST}' IDENTIFIED WITH mysql_native_password BY '${NETDATA_DB_PASSWD}';
+GRANT USAGE ON *.* TO ${NETDATA_DB_USER}@${MYSQL_GRANT_HOST};
+FLUSH PRIVILEGES;
+EOF
+ else
${MYSQL_CLIENT_ROOT} >> ${INSTALL_LOG} 2>&1 <<EOF
GRANT USAGE ON *.* TO ${NETDATA_DB_USER}@${MYSQL_GRANT_HOST} IDENTIFIED BY '${NETDATA_DB_PASSWD}';
FLUSH PRIVILEGES;
EOF
+ fi
ECHO_DEBUG "Generate ${NETDATA_CONF_PLUGIN_MYSQL}."
backup_file ${NETDATA_CONF_PLUGIN_MYSQL}
diff --git a/functions/roundcubemail.sh b/functions/roundcubemail.sh
index b845e81..2fe3613 100644
--- a/functions/roundcubemail.sh
+++ b/functions/roundcubemail.sh
@@ -42,8 +42,6 @@ rcm_initialize_db()
${MYSQL_CLIENT_ROOT} <<EOF
-- Create database and grant privileges
CREATE DATABASE ${RCM_DB_NAME} DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
-GRANT ALL ON ${RCM_DB_NAME}.* TO "${RCM_DB_USER}"@"${MYSQL_GRANT_HOST}" IDENTIFIED BY '${RCM_DB_PASSWD}';
--- GRANT ALL ON ${RCM_DB_NAME}.* TO "${RCM_DB_USER}"@"${HOSTNAME}" IDENTIFIED BY '${RCM_DB_PASSWD}';
-- Import Roundcubemail SQL template
USE ${RCM_DB_NAME};
@@ -52,6 +50,22 @@ SOURCE ${RCM_HTTPD_ROOT}/SQL/mysql.initial.sql;
FLUSH PRIVILEGES;
EOF
+ if [ X"${USE_MYSQL_CREATE_USER}" == X'YES' ]; then
+ ${MYSQL_CLIENT_ROOT} <<EOF
+-- Grant privileges
+CREATE USER IF NOT EXISTS '${RCM_DB_USER}'@'${MYSQL_GRANT_HOST}' IDENTIFIED WITH mysql_native_password BY '${RCM_DB_PASSWD}';
+-- CREATE USER IF NOT EXISTS '${RCM_DB_USER}'@'${HOSTNAME}' IDENTIFIED WITH mysql_native_password BY '${RCM_DB_PASSWD}';
+GRANT ALL ON ${RCM_DB_NAME}.* TO '${RCM_DB_USER}'@'${MYSQL_GRANT_HOST}';
+-- GRANT ALL ON ${RCM_DB_NAME}.* TO '${RCM_DB_USER}'@'${HOSTNAME}';
+FLUSH PRIVILEGES;
+EOF
+ else
+ ${MYSQL_CLIENT_ROOT} <<EOF
+GRANT ALL ON ${RCM_DB_NAME}.* TO "${RCM_DB_USER}"@"${MYSQL_GRANT_HOST}" IDENTIFIED BY '${RCM_DB_PASSWD}';
+-- GRANT ALL ON ${RCM_DB_NAME}.* TO "${RCM_DB_USER}"@"${HOSTNAME}" IDENTIFIED BY '${RCM_DB_PASSWD}';
+EOF
+ fi
+
# Generate .my.cnf file
cat > /root/.my.cnf-${RCM_DB_USER} <<EOF
[client]
diff --git a/functions/sogo.sh b/functions/sogo.sh
index 6cf538b..9293d34 100644
--- a/functions/sogo.sh
+++ b/functions/sogo.sh
@@ -34,15 +34,27 @@ sogo_initialize_db()
CREATE DATABASE ${SOGO_DB_NAME} CHARSET='UTF8';
EOF
+ if [ X"${USE_MYSQL_CREATE_USER}" == X'YES' ]; then
+ cat >> ${tmp_sql} <<EOF
+ -- Grant privileges
+CREATE USER IF NOT EXISTS '${SOGO_DB_USER}'@'${MYSQL_GRANT_HOST}' IDENTIFIED WITH mysql_native_password BY '${SOGO_DB_PASSWD}';
+-- CREATE USER IF NOT EXISTS '${SOGO_DB_USER}'@'${HOSTNAME}' IDENTIFIED WITH mysql_native_password BY '${SOGO_DB_PASSWD}';
+GRANT ALL ON ${SOGO_DB_NAME}.* TO '${SOGO_DB_USER}'@'${MYSQL_GRANT_HOST}';
+-- GRANT ALL ON ${SOGO_DB_NAME}.* TO '${SOGO_DB_USER}'@'${HOSTNAME}';
+FLUSH PRIVILEGES;
+EOF
+ else
cat >> ${tmp_sql} <<EOF
GRANT ALL ON ${SOGO_DB_NAME}.* TO ${SOGO_DB_USER}@"${MYSQL_GRANT_HOST}" IDENTIFIED BY "${SOGO_DB_PASSWD}";
-- GRANT ALL ON ${SOGO_DB_NAME}.* TO ${SOGO_DB_USER}@"${HOSTNAME}" IDENTIFIED BY "${SOGO_DB_PASSWD}";
EOF
+ fi
+
if [ X"${BACKEND}" == X'MYSQL' ]; then
cat >> ${tmp_sql} <<EOF
-GRANT SELECT ON ${VMAIL_DB_NAME}.mailbox TO ${SOGO_DB_USER}@"${MYSQL_GRANT_HOST}";
--- GRANT SELECT ON ${VMAIL_DB_NAME}.mailbox TO ${SOGO_DB_USER}@"${HOSTNAME}";
+GRANT SELECT ON ${VMAIL_DB_NAME}.mailbox TO ${SOGO_DB_USER}@'${MYSQL_GRANT_HOST}';
+-- GRANT SELECT ON ${VMAIL_DB_NAME}.mailbox TO ${SOGO_DB_USER}@'${HOSTNAME}';
CREATE VIEW ${SOGO_DB_NAME}.${SOGO_DB_VIEW_AUTH} (c_uid, c_name, c_password, c_cn, mail, domain) AS SELECT username, name, password, name, username, domain FROM ${VMAIL_DB_NAME}.mailbox WHERE enablesogo=1 AND active=1;
EOF
fi
diff --git a/samples/iredmail/iredmail.mysql b/samples/iredmail/iredmail.mysql
index f176528..5fe7427 100644
--- a/samples/iredmail/iredmail.mysql
+++ b/samples/iredmail/iredmail.mysql
@@ -186,7 +186,7 @@ CREATE TABLE IF NOT EXISTS mailbox (
domain VARCHAR(255) NOT NULL DEFAULT '',
transport VARCHAR(255) NOT NULL DEFAULT '',
department VARCHAR(255) NOT NULL DEFAULT '',
- rank VARCHAR(255) NOT NULL DEFAULT 'normal',
+ `rank` VARCHAR(255) NOT NULL DEFAULT 'normal',
employeeid VARCHAR(255) DEFAULT '',
isadmin TINYINT(1) NOT NULL DEFAULT 0,
isglobaladmin TINYINT(1) NOT NULL DEFAULT 0,
@@ -434,7 +434,7 @@ CREATE TABLE IF NOT EXISTS `used_quota` (
`domain` VARCHAR(255) NOT NULL DEFAULT '',
PRIMARY KEY (`username`),
INDEX (domain)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- Trigger `used_quota_before_insert` is used to set `used_quota.domain`.
-- NOTE: `used_quota.domain` is not used by Dovecot, but used in iRedAdmin to
diff --git a/samples/mysql/sql/init_vmail_db_user.sql b/samples/mysql/sql/init_vmail_db_user.sql
new file mode 100644
index 0000000..10e106d
--- /dev/null
+++ b/samples/mysql/sql/init_vmail_db_user.sql
@@ -0,0 +1,22 @@
+-- Create database for virtual hosts
+CREATE SCHEMA IF NOT EXISTS `PH_VMAIL_DB_NAME` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
+
+-- START Permissions: PH_VMAIL_DB_BIND_USER
+
+CREATE USER IF NOT EXISTS 'PH_VMAIL_DB_BIND_USER'@'PH_MYSQL_GRANT_HOST' IDENTIFIED WITH mysql_native_password BY 'PH_VMAIL_DB_BIND_PASSWD';
+-- CREATE USER IF NOT EXISTS 'PH_VMAIL_DB_BIND_USER'@'PH_MYSQL_GRANT_HOST' IDENTIFIED WITH mysql_native_password BY 'PH_VMAIL_DB_BIND_PASSWD';
+GRANT SELECT ON `PH_VMAIL_DB_NAME`.* TO 'PH_VMAIL_DB_BIND_USER'@'PH_MYSQL_GRANT_HOST';
+-- GRANT SELECT ON 'PH_VMAIL_DB_NAME'.* TO 'PH_VMAIL_DB_BIND_USER'@'PH_MYSQL_GRANT_HOST';
+
+-- END Permissions: PH_VMAIL_DB_BIND_USER
+
+-- START Permissions: PH_VMAIL_DB_ADMIN_USER
+
+CREATE USER IF NOT EXISTS 'PH_VMAIL_DB_ADMIN_USER'@'PH_MYSQL_GRANT_HOST' IDENTIFIED WITH mysql_native_password BY 'PH_VMAIL_DB_ADMIN_PASSWD';
+-- CREATE USER IF NOT EXISTS 'PH_VMAIL_DB_ADMIN_USER'@'PH_MYSQL_GRANT_HOST' IDENTIFIED WITH mysql_native_password BY 'PH_VMAIL_DB_ADMIN_PASSWD';
+GRANT SELECT,INSERT,DELETE,UPDATE ON `PH_VMAIL_DB_NAME`.* TO 'PH_VMAIL_DB_ADMIN_USER'@'PH_MYSQL_GRANT_HOST';
+-- GRANT SELECT,INSERT,DELETE,UPDATE ON 'PH_VMAIL_DB_NAME'.* TO 'PH_VMAIL_DB_ADMIN_USER'@'PH_MYSQL_GRANT_HOST';
+
+-- END Permissions: PH_VMAIL_DB_ADMIN_USER
+
+FLUSH PRIVILEGES;
diff --git a/samples/mysql/sql/remote_grant_permission_user.sql b/samples/mysql/sql/remote_grant_permission_user.sql
new file mode 100644
index 0000000..3e1b3e3
--- /dev/null
+++ b/samples/mysql/sql/remote_grant_permission_user.sql
@@ -0,0 +1,9 @@
+-- Grant access privilege to ${MYSQL_ROOT_USER}@${MYSQL_GRANT_HOST} ...'
+
+USE mysql;
+
+-- Allow access from MYSQL_GRANT_HOST with password
+GRANT ALL PRIVILEGES ON *.* TO 'PH_MYSQL_ROOT_USER'@'PH_MYSQL_GRANT_HOST' WITH GRANT OPTION;
+-- GRANT ALL PRIVILEGES ON *.* TO 'PH_MYSQL_ROOT_USER'@'PH_HOSTNAME' WITH GRANT OPTION;
+
+FLUSH PRIVILEGES;
\ No newline at end of file
