Zhang Huangbin avatar Zhang Huangbin committed 517824a

Update iRedMail MySQL template: column type 'TEXT' (and BLOB) cannot have default value.

Comments (0)

Files changed (9)

iRedMail/conf/mysql

 # A temporary file to store MySQL root password to avoid MySQL client warning:
 # "Warning: Using a password on the command line interface can be insecure."
 # File will be removed automatically after iRedMail installation.
-export MYSQL_DEFAULTS_FILE="${ROOTDIR}/.mysql-defaults-file"
-export MYSQL_CLIENT_ROOT="mysql --defaults-file=${MYSQL_DEFAULTS_FILE}"
+export MYSQL_DEFAULTS_FILE_ROOT="${ROOTDIR}/.mysql-root-defaults-file"
+export MYSQL_CLIENT_ROOT="mysql --defaults-file=${MYSQL_DEFAULTS_FILE_ROOT}"

iRedMail/functions/apache_php.sh

         [ X"${BACKEND}" == X"PGSQL" ] && a2enmod 000_auth_pgsql > /dev/null
 
     elif [ X"${DISTRO}" == X"SUSE" ]; then
+        a2enmod authz_core &>/dev/null
         a2enmod deflate &>/dev/null
 
         # Enable SSL.

iRedMail/functions/awstats.sh

         [ X"${LDAP_USE_TLS}" == X"YES" ] && \
             perl -pi -e 's#(AuthLDAPUrl.*)(ldap://)(.*)#${1}ldaps://${3}#' ${AWSTATS_HTTPD_CONF}
 
+        # openSUSE-13.1 (bottle) ships Apache-2.4 which removes directive 'AuthzLDAPAuthoritative'.
+        [ X"${DISTRO}" == X'SUSE' -a X"${DISTRO_CODENAME}" == X'bottle' ] && \
+            perl -pi -e 's/(.*)(AuthzLDAPAuthoritative.*)/${1}#${2}/g' ${AWSTATS_HTTPD_CONF}
+
     elif [ X"${BACKEND}" == X"MYSQL" ]; then
         # Use mod_auth_mysql.
         if [ X"${DISTRO}" == X"RHEL" \

iRedMail/functions/cleanup.sh

 ${PROG_VERSION}
 EOF
 
-    rm -f ${MYSQL_DEFAULTS_FILE} &>/dev/null
+    rm -f ${MYSQL_DEFAULTS_FILE_ROOT} &>/dev/null
 
     cat <<EOF
 

iRedMail/functions/cluebringer.sh

     perl -pi -e 's#^(bypass_mode=).*#${1}pass#' ${CLUEBRINGER_CONF}
 
     # DSN
-    if [ X"${BACKEND}" == X"OPENLDAP" -o X"${BACKEND}" == X"MYSQL" ]; then
+    if [ X"${BACKEND}" == X"OPENLDAP" -o X"${BACKEND}" == X'MYSQL' ]; then
         perl -pi -e 's/^(#*)(DSN=DBI:mysql:).*/${2}host=$ENV{SQL_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}
 
-    elif [ X"${BACKEND}" == X"PGSQL" ]; then
+    elif [ X"${BACKEND}" == X'PGSQL' ]; then
         # Comment out all default DSN settings
         perl -pi -e 's/^(DB_Type=).*/${1}pgsql/' ${CLUEBRINGER_CONF}
         perl -pi -e 's/^(DSN=.*)/#${1}/g' ${CLUEBRINGER_CONF}
 
     # Get SQL structure template file.
     tmp_sql="/tmp/cluebringer_init_sql.${RANDOM}${RANDOM}"
+    echo '' > ${tmp_sql}
+
     if [ X"${DISTRO}" == X'RHEL' -o X"${DISTRO}" == X'SUSE' ]; then
         if [ X"${BACKEND}" == X'OPENLDAP' -o X"${BACKEND}" == X'MYSQL' ]; then
             tmp_db_sample_file_name='policyd.mysql.sql'
 
         if [ X"${BACKEND}" == X'OPENLDAP' -o X"${BACKEND}" == X'MYSQL' ]; then
             perl -pi -e 's#TYPE=#ENGINE=#g' ${DB_SAMPLE_FILE}
-
             # Required by MySQL-5.6: 'NOT NULL' must has a default value.
             perl -pi -e 's#(.*Track.*NOT.*NULL)(.*)#${1} DEFAULT ""${2}#g' ${DB_SAMPLE_FILE}
-        elif [ X"${BACKEND}" == X'PGSQL' ]; then
-            perl -pi -e 's=^(#.*)=/*${1}*/=' ${DB_SAMPLE_FILE}
-        fi
 
-        if [ X"${BACKEND}" == X'OPENLDAP' -o X'${BACKEND}' == X'MYSQL' ]; then
-            cat > ${tmp_sql} <<EOF
+            cat >> ${tmp_sql} <<EOF
 CREATE DATABASE ${CLUEBRINGER_DB_NAME};
 USE ${CLUEBRINGER_DB_NAME};
 
 -- Grant privileges.
 GRANT SELECT,INSERT,UPDATE,DELETE ON ${CLUEBRINGER_DB_NAME}.* TO "${CLUEBRINGER_DB_USER}"@"${SQL_HOSTNAME}" IDENTIFIED BY "${CLUEBRINGER_DB_PASSWD}";
 FLUSH PRIVILEGES;
+USE ${CLUEBRINGER_DB_NAME};
 EOF
-        elif [ X"${BACKEND}" == X"PGSQL" ]; then
-            cat > ${tmp_sql} <<EOF
+        elif [ X"${BACKEND}" == X'PGSQL' ]; then
+            perl -pi -e 's=^(#.*)=/*${1}*/=' ${DB_SAMPLE_FILE}
+
+            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};
 -- Import SQL structure template.
 \i ${DB_SAMPLE_FILE};
 EOF
-
         fi
 
     elif [ X"${DISTRO}" == X"DEBIAN" -o X"${DISTRO}" == X"UBUNTU" ]; then
         if [ X"${BACKEND}" == X"OPENLDAP" -o X"${BACKEND}" == X"MYSQL" ]; then
-            cat > ${tmp_sql} <<EOF
+            cat >> ${tmp_sql} <<EOF
 CREATE DATABASE ${CLUEBRINGER_DB_NAME};
 GRANT SELECT,INSERT,UPDATE,DELETE ON ${CLUEBRINGER_DB_NAME}.* TO "${CLUEBRINGER_DB_USER}"@"${SQL_HOSTNAME}" IDENTIFIED BY "${CLUEBRINGER_DB_PASSWD}";
 USE ${CLUEBRINGER_DB_NAME};
             gunzip -c /usr/share/doc/postfix-cluebringer/database/policyd-db.mysql.gz >> ${tmp_sql}
             perl -pi -e 's#TYPE=#ENGINE=#g' ${tmp_sql}
 
-        elif [ X"${BACKEND}" == X"PGSQL" ]; then
-            cat > ${tmp_sql} <<EOF
+        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};
             gunzip -c /usr/share/doc/postfix-cluebringer/database/policyd-db.pgsql.gz >> ${tmp_sql}
         fi
 
-    elif [ X"${DISTRO}" == X"FREEBSD" ]; then
+    elif [ X"${DISTRO}" == X'FREEBSD' ]; then
         # Template file will create database: policyd.
         cd /usr/local/share/policyd2/database/
         chmod +x ./convert-tsql
 
         if [ X"${BACKEND}" == X"OPENLDAP" -o X"${BACKEND}" == X"MYSQL" ]; then
             policyd_sql_type='mysql'
-            cat > ${tmp_sql} <<EOF
+            cat >> ${tmp_sql} <<EOF
 CREATE DATABASE ${CLUEBRINGER_DB_NAME};
 GRANT SELECT,INSERT,UPDATE,DELETE ON ${CLUEBRINGER_DB_NAME}.* TO "${CLUEBRINGER_DB_USER}"@"${SQL_HOSTNAME}" IDENTIFIED BY "${CLUEBRINGER_DB_PASSWD}";
 USE ${CLUEBRINGER_DB_NAME};
 EOF
         elif [ X"${BACKEND}" == X"PGSQL" ]; then
             policyd_sql_type='pgsql'
-            cat > ${tmp_sql} <<EOF
+            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};
         fi
 
         for i in core.tsql \
-            access_control.tsql \
-            quotas.tsql \
-            amavis.tsql \
-            checkhelo.tsql \
-            checkspf.tsql \
-            greylisting.tsql \
-            accounting.tsql; do
+                 access_control.tsql \
+                 quotas.tsql \
+                 amavis.tsql \
+                 checkhelo.tsql \
+                 checkspf.tsql \
+                 greylisting.tsql \
+                 accounting.tsql; do
             [ -f $i ] && bash convert-tsql ${policyd_sql_type} $i >> ${tmp_sql}
-        done >> ${tmp_sql}
+        done
 
         unset policyd_sql_type
     fi
     # Enable greylisting on all inbound emails by default.
     if [ X"${BACKEND}" == X"OPENLDAP" -o X"${BACKEND}" == X"MYSQL" ]; then
         ${MYSQL_CLIENT_ROOT} <<EOF
-$(cat ${tmp_sql})
+SOURCE ${tmp_sql};
 EOF
 
     elif [ X"${BACKEND}" == X"PGSQL" ]; then
         [ X"${LDAP_USE_TLS}" == X"YES" ] && \
             perl -pi -e 's#(AuthLDAPUrl.*)(ldap://)(.*)#${1}ldaps://${3}#' ${CLUEBRINGER_HTTPD_CONF}
 
+        # openSUSE-13.1 (bottle) ships Apache-2.4 which removes directive 'AuthzLDAPAuthoritative'.
+        [ X"${DISTRO}" == X'SUSE' -a X"${DISTRO_CODENAME}" == X'bottle' ] && \
+            perl -pi -e 's/(.*)(AuthzLDAPAuthoritative.*)/${1}#${2}/g' ${CLUEBRINGER_HTTPD_CONF}
+
     elif [ X"${BACKEND}" == X"MYSQL" ]; then
         # Use mod_auth_mysql.
         if [ X"${DISTRO}" == X"RHEL" -o X"${DISTRO}" == X"SUSE" -o X"${DISTRO}" == X"FREEBSD" ]; then

iRedMail/functions/mysql.sh

     sleep 5
 
     # Generate temporary file for MySQL client option --defaults-file.
-    cat >> ${MYSQL_DEFAULTS_FILE} <<EOF
+    cat >> ${MYSQL_DEFAULTS_FILE_ROOT} <<EOF
 [client]
 host=${SQL_SERVER}
 port=${SQL_SERVER_PORT}

iRedMail/samples/cluebringer_extra.sql

     SELECT id, '!%internal_ips,!%internal_domains', '%no_greylisting', 0
     FROM policies WHERE name='no_greylisting' LIMIT 1;
 -- Disable greylisting for %no_greylisting
-INSERT INTO greylisting (PolicyID, Name, UseGreylisting, UseAutoWhitelist, UseAutoBlacklist, Disabled)
-    SELECT id, 'no_greylisting', 0, 0, 0, 0
+INSERT INTO greylisting (PolicyID, Name, UseGreylisting, Track, UseAutoWhitelist, UseAutoBlacklist, Disabled)
+    SELECT id, 'no_greylisting', 0, 'SenderIP:/32', 0, 0, 0
     FROM policies WHERE name='no_greylisting' LIMIT 1;
 
 -- Disable greylisting for certain domain/users:

iRedMail/samples/iredmail.mysql

 #
 CREATE TABLE IF NOT EXISTS alias (
     address VARCHAR(255) NOT NULL DEFAULT '',
-    goto TEXT NOT NULL DEFAULT '',
+    goto TEXT,
     name VARCHAR(255) NOT NULL DEFAULT '',
-    moderators TEXT NOT NULL DEFAULT '',
+    moderators TEXT,
     accesspolicy VARCHAR(30) NOT NULL DEFAULT '',
     domain VARCHAR(255) NOT NULL DEFAULT '',
     created DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
 CREATE TABLE IF NOT EXISTS domain (
     -- mail domain name. e.g. iredmail.org.
     domain VARCHAR(255) NOT NULL DEFAULT '',
-    description TEXT NOT NULL DEFAULT '',
+    description TEXT,
     -- Disclaimer text. Used by Amavisd + AlterMIME.
-    disclaimer TEXT NOT NULL DEFAULT '',
+    disclaimer TEXT,
     -- Max alias accounts in this domain. e.g. 10.
     aliases INT(10) NOT NULL DEFAULT 0,
     -- Max mail accounts in this domain. e.g. 100.
     -- Default quota size for newly created mail account.
     defaultuserquota BIGINT(20) NOT NULL DEFAULT '1024',
     -- List of mail alias addresses assigned to newly created user.
-    defaultuseraliases TEXT NOT NULL DEFAULT '',
+    defaultuseraliases TEXT,
     -- Disabled domain profiles which can not be viewed and updated by normal
     -- domain admin. Multiple values must be separated by comma.
     disableddomainprofiles VARCHAR(255) NOT NULL DEFAULT '',
 # Table structure for table mailbox
 #
 CREATE TABLE IF NOT EXISTS mailbox (
-    username VARCHAR(255) NOT NULL,
+    username VARCHAR(255) NOT NULL DEFAULT '',
     password VARCHAR(255) NOT NULL DEFAULT '',
     name VARCHAR(255) NOT NULL DEFAULT '',
     language VARCHAR(5) NOT NULL DEFAULT 'en_US',
     lastlogindate DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
     lastloginipv4 INT(4) UNSIGNED NOT NULL DEFAULT 0,
     lastloginprotocol CHAR(255) NOT NULL DEFAULT '',
-    disclaimer TEXT NOT NULL DEFAULT '',
+    disclaimer TEXT,
     passwordlastchange DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
-    allowedsenders TEXT NOT NULL DEFAULT '',
-    rejectedsenders TEXT NOT NULL DEFAULT '',
-    allowedrecipients TEXT NOT NULL DEFAULT '',
-    rejectedrecipients TEXT NOT NULL DEFAULT '',
+    allowedsenders TEXT,
+    rejectedsenders TEXT,
+    allowedrecipients TEXT,
+    rejectedrecipients TEXT,
     created DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
     modified DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
     expired DATETIME NOT NULL DEFAULT '9999-12-31 00:00:00',
 ) ENGINE=MyISAM;
 
 -- Used to store basic info of deleted mailboxes.
-CREATE TABLE IF NOT EXISTS `deleted_mailboxes` (
-    `id` BIGINT(20) UNSIGNED AUTO_INCREMENT,
-    `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+CREATE TABLE IF NOT EXISTS deleted_mailboxes (
+    id BIGINT(20) UNSIGNED AUTO_INCREMENT,
+    timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
     -- Email address of deleted user
-    `username` VARCHAR(255) NOT NULL DEFAULT '',
-    -- Domain part of user email address
+    username VARCHAR(255) NOT NULL DEFAULT '',
+    -- Domain part of username
     domain VARCHAR(255) NOT NULL DEFAULT '',
     -- Absolute path of user's mailbox
-    `maildir` TEXT NOT NULL DEFAULT '',
-    -- Which domain admin deleted this user
-    `admin` VARCHAR(255) NOT NULL DEFAULT '',
+    maildir VARCHAR(255) NOT NULL DEFAULT '',
+    -- Deleted by which domain admin
+    admin VARCHAR(255) NOT NULL DEFAULT '',
     KEY id (id),
     INDEX (timestamp),
     INDEX (username),

iRedMail/samples/iredmail.pgsql

     timestamp TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
     -- Email address of deleted user
     username VARCHAR(255) NOT NULL DEFAULT '',
-    -- Domain part of user email address
+    -- Domain part of username
     domain VARCHAR(255) NOT NULL DEFAULT '',
     -- Absolute path of user's mailbox
-    maildir TEXT NOT NULL DEFAULT '',
-    -- Which domain admin deleted this user
+    maildir VARCHAR(255) NOT NULL DEFAULT '',
+    -- Deleted by which domain admin
     admin VARCHAR(255) NOT NULL DEFAULT ''
 );
 
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.