git subrepo commit mailcow/src/mailcow-dockerized
subrepo: subdir: "mailcow/src/mailcow-dockerized"
merged: "308860af"
upstream: origin: "https://github.com/mailcow/mailcow-dockerized.git"
branch: "master"
commit: "3f1a5af8"
git-subrepo: version: "0.4.5"
origin: "???"
commit: "???"
Change-Id: I5d51c14b45db54fe706be40a591ddbfcea50d4b0
diff --git a/mailcow/src/mailcow-dockerized/data/web/inc/init_db.inc.php b/mailcow/src/mailcow-dockerized/data/web/inc/init_db.inc.php
index 60a8ead..e781f94 100644
--- a/mailcow/src/mailcow-dockerized/data/web/inc/init_db.inc.php
+++ b/mailcow/src/mailcow-dockerized/data/web/inc/init_db.inc.php
@@ -3,7 +3,7 @@
try {
global $pdo;
- $db_version = "31102021_0620";
+ $db_version = "23122022_1445";
$stmt = $pdo->query("SHOW TABLES LIKE 'versions'");
$num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
@@ -23,35 +23,35 @@
}
$views = array(
- "grouped_mail_aliases" => "CREATE VIEW grouped_mail_aliases (username, aliases) AS
- SELECT goto, IFNULL(GROUP_CONCAT(address ORDER BY address SEPARATOR ' '), '') AS address FROM alias
- WHERE address!=goto
- AND active = '1'
- AND sogo_visible = '1'
- AND address NOT LIKE '@%'
- GROUP BY goto;",
- // START
- // Unused at the moment - we cannot allow to show a foreign mailbox as sender address in SOGo, as SOGo does not like this
- // We need to create delegation in SOGo AND set a sender_acl in mailcow to allow to send as user X
- "grouped_sender_acl" => "CREATE VIEW grouped_sender_acl (username, send_as_acl) AS
- SELECT logged_in_as, IFNULL(GROUP_CONCAT(send_as SEPARATOR ' '), '') AS send_as_acl FROM sender_acl
- WHERE send_as NOT LIKE '@%'
- GROUP BY logged_in_as;",
- // END
- "grouped_sender_acl_external" => "CREATE VIEW grouped_sender_acl_external (username, send_as_acl) AS
- SELECT logged_in_as, IFNULL(GROUP_CONCAT(send_as SEPARATOR ' '), '') AS send_as_acl FROM sender_acl
- WHERE send_as NOT LIKE '@%' AND external = '1'
- GROUP BY logged_in_as;",
- "grouped_domain_alias_address" => "CREATE VIEW grouped_domain_alias_address (username, ad_alias) AS
- SELECT username, IFNULL(GROUP_CONCAT(local_part, '@', alias_domain SEPARATOR ' '), '') AS ad_alias FROM mailbox
- LEFT OUTER JOIN alias_domain ON target_domain=domain
- GROUP BY username;",
- "sieve_before" => "CREATE VIEW sieve_before (id, username, script_name, script_data) AS
- SELECT md5(script_data), username, script_name, script_data FROM sieve_filters
- WHERE filter_type = 'prefilter';",
- "sieve_after" => "CREATE VIEW sieve_after (id, username, script_name, script_data) AS
- SELECT md5(script_data), username, script_name, script_data FROM sieve_filters
- WHERE filter_type = 'postfilter';"
+ "grouped_mail_aliases" => "CREATE VIEW grouped_mail_aliases (username, aliases) AS
+ SELECT goto, IFNULL(GROUP_CONCAT(address ORDER BY address SEPARATOR ' '), '') AS address FROM alias
+ WHERE address!=goto
+ AND active = '1'
+ AND sogo_visible = '1'
+ AND address NOT LIKE '@%'
+ GROUP BY goto;",
+ // START
+ // Unused at the moment - we cannot allow to show a foreign mailbox as sender address in SOGo, as SOGo does not like this
+ // We need to create delegation in SOGo AND set a sender_acl in mailcow to allow to send as user X
+ "grouped_sender_acl" => "CREATE VIEW grouped_sender_acl (username, send_as_acl) AS
+ SELECT logged_in_as, IFNULL(GROUP_CONCAT(send_as SEPARATOR ' '), '') AS send_as_acl FROM sender_acl
+ WHERE send_as NOT LIKE '@%'
+ GROUP BY logged_in_as;",
+ // END
+ "grouped_sender_acl_external" => "CREATE VIEW grouped_sender_acl_external (username, send_as_acl) AS
+ SELECT logged_in_as, IFNULL(GROUP_CONCAT(send_as SEPARATOR ' '), '') AS send_as_acl FROM sender_acl
+ WHERE send_as NOT LIKE '@%' AND external = '1'
+ GROUP BY logged_in_as;",
+ "grouped_domain_alias_address" => "CREATE VIEW grouped_domain_alias_address (username, ad_alias) AS
+ SELECT username, IFNULL(GROUP_CONCAT(local_part, '@', alias_domain SEPARATOR ' '), '') AS ad_alias FROM mailbox
+ LEFT OUTER JOIN alias_domain ON target_domain=domain
+ GROUP BY username;",
+ "sieve_before" => "CREATE VIEW sieve_before (id, username, script_name, script_data) AS
+ SELECT md5(script_data), username, script_name, script_data FROM sieve_filters
+ WHERE filter_type = 'prefilter';",
+ "sieve_after" => "CREATE VIEW sieve_after (id, username, script_name, script_data) AS
+ SELECT md5(script_data), username, script_name, script_data FROM sieve_filters
+ WHERE filter_type = 'postfilter';"
);
$tables = array(
@@ -225,6 +225,22 @@
),
"attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
),
+ "templates" => array(
+ "cols" => array(
+ "id" => "INT NOT NULL AUTO_INCREMENT",
+ "template" => "VARCHAR(255) NOT NULL",
+ "type" => "VARCHAR(255) NOT NULL",
+ "attributes" => "JSON",
+ "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
+ "modified" => "DATETIME ON UPDATE CURRENT_TIMESTAMP"
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("id")
+ )
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
"domain" => array(
// Todo: Move some attributes to json
"cols" => array(
@@ -251,6 +267,26 @@
),
"attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
),
+ "tags_domain" => array(
+ "cols" => array(
+ "tag_name" => "VARCHAR(255) NOT NULL",
+ "domain" => "VARCHAR(255) NOT NULL"
+ ),
+ "keys" => array(
+ "fkey" => array(
+ "fk_tags_domain" => array(
+ "col" => "domain",
+ "ref" => "domain.domain",
+ "delete" => "CASCADE",
+ "update" => "NO ACTION"
+ )
+ ),
+ "unique" => array(
+ "tag_name" => array("tag_name", "domain")
+ )
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
"tls_policy_override" => array(
"cols" => array(
"id" => "INT NOT NULL AUTO_INCREMENT",
@@ -325,6 +361,26 @@
),
"attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
),
+ "tags_mailbox" => array(
+ "cols" => array(
+ "tag_name" => "VARCHAR(255) NOT NULL",
+ "username" => "VARCHAR(255) NOT NULL"
+ ),
+ "keys" => array(
+ "fkey" => array(
+ "fk_tags_mailbox" => array(
+ "col" => "username",
+ "ref" => "mailbox.username",
+ "delete" => "CASCADE",
+ "update" => "NO ACTION"
+ )
+ ),
+ "unique" => array(
+ "tag_name" => array("tag_name", "username")
+ )
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
"sieve_filters" => array(
"cols" => array(
"id" => "INT NOT NULL AUTO_INCREMENT",
@@ -400,7 +456,7 @@
"spam_score" => "TINYINT(1) NOT NULL DEFAULT '1'",
"spam_policy" => "TINYINT(1) NOT NULL DEFAULT '1'",
"delimiter_action" => "TINYINT(1) NOT NULL DEFAULT '1'",
- "syncjobs" => "TINYINT(1) NOT NULL DEFAULT '1'",
+ "syncjobs" => "TINYINT(1) NOT NULL DEFAULT '0'",
"eas_reset" => "TINYINT(1) NOT NULL DEFAULT '1'",
"sogo_profile_reset" => "TINYINT(1) NOT NULL DEFAULT '0'",
"pushover" => "TINYINT(1) NOT NULL DEFAULT '1'",
@@ -696,10 +752,10 @@
"id" => "INT NOT NULL AUTO_INCREMENT",
"key_id" => "VARCHAR(255) NOT NULL",
"username" => "VARCHAR(255) NOT NULL",
- "authmech" => "ENUM('yubi_otp', 'u2f', 'hotp', 'totp')",
+ "authmech" => "ENUM('yubi_otp', 'u2f', 'hotp', 'totp', 'webauthn')",
"secret" => "VARCHAR(255) DEFAULT NULL",
- "keyHandle" => "VARCHAR(255) DEFAULT NULL",
- "publicKey" => "VARCHAR(255) DEFAULT NULL",
+ "keyHandle" => "VARCHAR(1023) DEFAULT NULL",
+ "publicKey" => "VARCHAR(4096) DEFAULT NULL",
"counter" => "INT NOT NULL DEFAULT '0'",
"certificate" => "TEXT",
"active" => "TINYINT(1) NOT NULL DEFAULT '0'"
@@ -864,7 +920,7 @@
"sogo_sessions_folder" => array(
"cols" => array(
"c_id" => "VARCHAR(255) NOT NULL",
- "c_value" => "VARCHAR(255) NOT NULL",
+ "c_value" => "VARCHAR(4096) NOT NULL",
"c_creationdate" => "INT(11) NOT NULL",
"c_lastseen" => "INT(11) NOT NULL"
),
@@ -1187,8 +1243,19 @@
$pdo->query($create);
}
- // Mitigate imapsync pipemess issue
- $pdo->query("UPDATE `imapsync` SET `custom_params` = '' WHERE `custom_params` LIKE '%pipemess%';");
+ // Mitigate imapsync argument injection issue
+ $pdo->query("UPDATE `imapsync` SET `custom_params` = ''
+ WHERE `custom_params` LIKE '%pipemess%'
+ OR custom_params LIKE '%skipmess%'
+ OR custom_params LIKE '%delete2foldersonly%'
+ OR custom_params LIKE '%delete2foldersbutnot%'
+ OR custom_params LIKE '%regexflag%'
+ OR custom_params LIKE '%pipemess%'
+ OR custom_params LIKE '%regextrans2%'
+ OR custom_params LIKE '%maxlinelengthcmd%';");
+
+ // Migrate webauthn tfa
+ $stmt = $pdo->query("ALTER TABLE `tfa` MODIFY COLUMN `authmech` ENUM('yubi_otp', 'u2f', 'hotp', 'totp', 'webauthn')");
// Inject admin if not exists
$stmt = $pdo->query("SELECT NULL FROM `admin`");
@@ -1213,6 +1280,7 @@
$pdo->query("UPDATE `pushover` SET `attributes` = '{}' WHERE `attributes` = '' OR `attributes` IS NULL;");
$pdo->query("UPDATE `pushover` SET `attributes` = JSON_SET(`attributes`, '$.evaluate_x_prio', \"0\") WHERE JSON_VALUE(`attributes`, '$.evaluate_x_prio') IS NULL;");
$pdo->query("UPDATE `pushover` SET `attributes` = JSON_SET(`attributes`, '$.only_x_prio', \"0\") WHERE JSON_VALUE(`attributes`, '$.only_x_prio') IS NULL;");
+ $pdo->query("UPDATE `pushover` SET `attributes` = JSON_SET(`attributes`, '$.sound', \"pushover\") WHERE JSON_VALUE(`attributes`, '$.sound') IS NULL;");
// mailbox
$pdo->query("UPDATE `mailbox` SET `attributes` = '{}' WHERE `attributes` = '' OR `attributes` IS NULL;");
$pdo->query("UPDATE `mailbox` SET `attributes` = JSON_SET(`attributes`, '$.passwd_update', \"0\") WHERE JSON_VALUE(`attributes`, '$.passwd_update') IS NULL;");
@@ -1241,6 +1309,95 @@
// Fix domain_admins
$pdo->query("DELETE FROM `domain_admins` WHERE `domain` = 'ALL';");
+ // add default templates
+ $default_domain_template = array(
+ "template" => "Default",
+ "type" => "domain",
+ "attributes" => array(
+ "tags" => array(),
+ "max_num_aliases_for_domain" => 400,
+ "max_num_mboxes_for_domain" => 10,
+ "def_quota_for_mbox" => 3072 * 1048576,
+ "max_quota_for_mbox" => 10240 * 1048576,
+ "max_quota_for_domain" => 10240 * 1048576,
+ "rl_frame" => "s",
+ "rl_value" => "",
+ "active" => 1,
+ "gal" => 1,
+ "backupmx" => 0,
+ "relay_all_recipients" => 0,
+ "relay_unknown_only" => 0,
+ "dkim_selector" => "dkim",
+ "key_size" => 2048,
+ "max_quota_for_domain" => 10240 * 1048576,
+ )
+ );
+ $default_mailbox_template = array(
+ "template" => "Default",
+ "type" => "mailbox",
+ "attributes" => array(
+ "tags" => array(),
+ "quota" => 0,
+ "quarantine_notification" => strval($GLOBALS['MAILBOX_DEFAULT_ATTRIBUTES']['quarantine_notification']),
+ "quarantine_category" => strval($GLOBALS['MAILBOX_DEFAULT_ATTRIBUTES']['quarantine_category']),
+ "rl_frame" => "s",
+ "rl_value" => "",
+ "force_pw_update" => intval($GLOBALS['MAILBOX_DEFAULT_ATTRIBUTES']['force_pw_update']),
+ "sogo_access" => intval($GLOBALS['MAILBOX_DEFAULT_ATTRIBUTES']['sogo_access']),
+ "active" => 1,
+ "tls_enforce_in" => intval($GLOBALS['MAILBOX_DEFAULT_ATTRIBUTES']['tls_enforce_in']),
+ "tls_enforce_out" => intval($GLOBALS['MAILBOX_DEFAULT_ATTRIBUTES']['tls_enforce_out']),
+ "imap_access" => intval($GLOBALS['MAILBOX_DEFAULT_ATTRIBUTES']['imap_access']),
+ "pop3_access" => intval($GLOBALS['MAILBOX_DEFAULT_ATTRIBUTES']['pop3_access']),
+ "smtp_access" => intval($GLOBALS['MAILBOX_DEFAULT_ATTRIBUTES']['smtp_access']),
+ "sieve_access" => intval($GLOBALS['MAILBOX_DEFAULT_ATTRIBUTES']['sieve_access']),
+ "acl_spam_alias" => 1,
+ "acl_tls_policy" => 1,
+ "acl_spam_score" => 1,
+ "acl_spam_policy" => 1,
+ "acl_delimiter_action" => 1,
+ "acl_syncjobs" => 0,
+ "acl_eas_reset" => 1,
+ "acl_sogo_profile_reset" => 0,
+ "acl_pushover" => 1,
+ "acl_quarantine" => 1,
+ "acl_quarantine_attachments" => 1,
+ "acl_quarantine_notification" => 1,
+ "acl_quarantine_category" => 1,
+ "acl_app_passwds" => 1,
+ )
+ );
+ $stmt = $pdo->prepare("SELECT id FROM `templates` WHERE `type` = :type AND `template` = :template");
+ $stmt->execute(array(
+ ":type" => "domain",
+ ":template" => $default_domain_template["template"]
+ ));
+ $row = $stmt->fetch(PDO::FETCH_ASSOC);
+ if (empty($row)){
+ $stmt = $pdo->prepare("INSERT INTO `templates` (`type`, `template`, `attributes`)
+ VALUES (:type, :template, :attributes)");
+ $stmt->execute(array(
+ ":type" => "domain",
+ ":template" => $default_domain_template["template"],
+ ":attributes" => json_encode($default_domain_template["attributes"])
+ ));
+ }
+ $stmt = $pdo->prepare("SELECT id FROM `templates` WHERE `type` = :type AND `template` = :template");
+ $stmt->execute(array(
+ ":type" => "mailbox",
+ ":template" => $default_mailbox_template["template"]
+ ));
+ $row = $stmt->fetch(PDO::FETCH_ASSOC);
+ if (empty($row)){
+ $stmt = $pdo->prepare("INSERT INTO `templates` (`type`, `template`, `attributes`)
+ VALUES (:type, :template, :attributes)");
+ $stmt->execute(array(
+ ":type" => "mailbox",
+ ":template" => $default_mailbox_template["template"],
+ ":attributes" => json_encode($default_mailbox_template["attributes"])
+ ));
+ }
+
if (php_sapi_name() == "cli") {
echo "DB initialization completed" . PHP_EOL;
} else {