git subrepo commit (merge) mailcow/src/mailcow-dockerized
subrepo: subdir: "mailcow/src/mailcow-dockerized"
merged: "c7b1dc37"
upstream: origin: "https://github.com/mailcow/mailcow-dockerized.git"
branch: "master"
commit: "a366494c"
git-subrepo: version: "0.4.6"
origin: "???"
commit: "???"
Change-Id: Id574ecd4e02e3c4fbf8a1efd49be11c0b6d19a3f
diff --git a/mailcow/src/mailcow-dockerized/data/web/inc/functions.address_rewriting.inc.php b/mailcow/src/mailcow-dockerized/data/web/inc/functions.address_rewriting.inc.php
index 8193c05..140ae47 100644
--- a/mailcow/src/mailcow-dockerized/data/web/inc/functions.address_rewriting.inc.php
+++ b/mailcow/src/mailcow-dockerized/data/web/inc/functions.address_rewriting.inc.php
@@ -49,7 +49,9 @@
}
elseif (filter_var($local_dest, FILTER_VALIDATE_EMAIL)) {
$mailbox = mailbox('get', 'mailbox_details', $local_dest);
- if ($mailbox === false && array_key_exists($local_dest, array_merge($direct_aliases, $shared_aliases)) === false) {
+ $shared_aliases = mailbox('get', 'shared_aliases');
+ $direct_aliases = mailbox('get', 'direct_aliases');
+ if ($mailbox === false && in_array($local_dest, array_merge($direct_aliases, $shared_aliases)) === false) {
$_SESSION['return'][] = array(
'type' => 'danger',
'log' => array(__FUNCTION__, $_action, $_data, $_attr),
diff --git a/mailcow/src/mailcow-dockerized/data/web/inc/functions.customize.inc.php b/mailcow/src/mailcow-dockerized/data/web/inc/functions.customize.inc.php
index 16c5c03..0da8c35 100644
--- a/mailcow/src/mailcow-dockerized/data/web/inc/functions.customize.inc.php
+++ b/mailcow/src/mailcow-dockerized/data/web/inc/functions.customize.inc.php
@@ -24,9 +24,10 @@
}
switch ($_item) {
case 'main_logo':
- if (in_array($_data['main_logo']['type'], array('image/gif', 'image/jpeg', 'image/pjpeg', 'image/x-png', 'image/png', 'image/svg+xml'))) {
+ case 'main_logo_dark':
+ if (in_array($_data[$_item]['type'], array('image/gif', 'image/jpeg', 'image/pjpeg', 'image/x-png', 'image/png', 'image/svg+xml'))) {
try {
- if (file_exists($_data['main_logo']['tmp_name']) !== true) {
+ if (file_exists($_data[$_item]['tmp_name']) !== true) {
$_SESSION['return'][] = array(
'type' => 'danger',
'log' => array(__FUNCTION__, $_action, $_item, $_data),
@@ -34,7 +35,7 @@
);
return false;
}
- $image = new Imagick($_data['main_logo']['tmp_name']);
+ $image = new Imagick($_data[$_item]['tmp_name']);
if ($image->valid() !== true) {
$_SESSION['return'][] = array(
'type' => 'danger',
@@ -63,7 +64,7 @@
return false;
}
try {
- $redis->Set('MAIN_LOGO', 'data:' . $_data['main_logo']['type'] . ';base64,' . base64_encode(file_get_contents($_data['main_logo']['tmp_name'])));
+ $redis->Set(strtoupper($_item), 'data:' . $_data[$_item]['type'] . ';base64,' . base64_encode(file_get_contents($_data[$_item]['tmp_name'])));
}
catch (RedisException $e) {
$_SESSION['return'][] = array(
@@ -160,6 +161,25 @@
'msg' => 'ui_texts'
);
break;
+ case 'ip_check':
+ $ip_check = ($_data['ip_check_opt_in'] == "1") ? 1 : 0;
+ try {
+ $redis->set('IP_CHECK', $ip_check);
+ }
+ catch (RedisException $e) {
+ $_SESSION['return'][] = array(
+ 'type' => 'danger',
+ 'log' => array(__FUNCTION__, $_action, $_item, $_data),
+ 'msg' => array('redis_error', $e)
+ );
+ return false;
+ }
+ $_SESSION['return'][] = array(
+ 'type' => 'success',
+ 'log' => array(__FUNCTION__, $_action, $_item, $_data),
+ 'msg' => 'ip_check_opt_in_modified'
+ );
+ break;
}
break;
case 'delete':
@@ -182,8 +202,9 @@
}
switch ($_item) {
case 'main_logo':
+ case 'main_logo_dark':
try {
- if ($redis->del('MAIN_LOGO')) {
+ if ($redis->del(strtoupper($_item))) {
$_SESSION['return'][] = array(
'type' => 'success',
'log' => array(__FUNCTION__, $_action, $_item, $_data),
@@ -220,8 +241,9 @@
return ($app_links) ? $app_links : false;
break;
case 'main_logo':
+ case 'main_logo_dark':
try {
- return $redis->get('MAIN_LOGO');
+ return $redis->get(strtoupper($_item));
}
catch (RedisException $e) {
$_SESSION['return'][] = array(
@@ -258,9 +280,14 @@
}
break;
case 'main_logo_specs':
+ case 'main_logo_dark_specs':
try {
$image = new Imagick();
- $img_data = explode('base64,', customize('get', 'main_logo'));
+ if($_item == 'main_logo_specs') {
+ $img_data = explode('base64,', customize('get', 'main_logo'));
+ } else {
+ $img_data = explode('base64,', customize('get', 'main_logo_dark'));
+ }
if ($img_data[1]) {
$image->readImageBlob(base64_decode($img_data[1]));
return $image->identifyImage();
@@ -276,6 +303,20 @@
return false;
}
break;
+ case 'ip_check':
+ try {
+ $ip_check = ($ip_check = $redis->get('IP_CHECK')) ? $ip_check : 0;
+ return $ip_check;
+ }
+ catch (RedisException $e) {
+ $_SESSION['return'][] = array(
+ 'type' => 'danger',
+ 'log' => array(__FUNCTION__, $_action, $_item, $_data),
+ 'msg' => array('redis_error', $e)
+ );
+ return false;
+ }
+ break;
}
break;
}
diff --git a/mailcow/src/mailcow-dockerized/data/web/inc/functions.docker.inc.php b/mailcow/src/mailcow-dockerized/data/web/inc/functions.docker.inc.php
index 78efac0..5b5b7ac 100644
--- a/mailcow/src/mailcow-dockerized/data/web/inc/functions.docker.inc.php
+++ b/mailcow/src/mailcow-dockerized/data/web/inc/functions.docker.inc.php
@@ -192,5 +192,16 @@
}
return false;
break;
+ case 'broadcast':
+ $request = array(
+ "api_call" => "container_post",
+ "container_name" => $service_name,
+ "post_action" => $attr1,
+ "request" => $attr2
+ );
+
+ $redis->publish("MC_CHANNEL", json_encode($request));
+ return true;
+ break;
}
}
diff --git a/mailcow/src/mailcow-dockerized/data/web/inc/functions.domain_admin.inc.php b/mailcow/src/mailcow-dockerized/data/web/inc/functions.domain_admin.inc.php
index 804c0f8..bb88ea3 100644
--- a/mailcow/src/mailcow-dockerized/data/web/inc/functions.domain_admin.inc.php
+++ b/mailcow/src/mailcow-dockerized/data/web/inc/functions.domain_admin.inc.php
@@ -1,407 +1,468 @@
-<?php
-function domain_admin($_action, $_data = null) {
- global $pdo;
- global $lang;
- $_data_log = $_data;
- !isset($_data_log['password']) ?: $_data_log['password'] = '*';
- !isset($_data_log['password2']) ?: $_data_log['password2'] = '*';
- !isset($_data_log['user_old_pass']) ?: $_data_log['user_old_pass'] = '*';
- !isset($_data_log['user_new_pass']) ?: $_data_log['user_new_pass'] = '*';
- !isset($_data_log['user_new_pass2']) ?: $_data_log['user_new_pass2'] = '*';
- switch ($_action) {
- case 'add':
- $username = strtolower(trim($_data['username']));
- $password = $_data['password'];
- $password2 = $_data['password2'];
- $domains = (array)$_data['domains'];
- $active = intval($_data['active']);
- if ($_SESSION['mailcow_cc_role'] != "admin") {
- $_SESSION['return'][] = array(
- 'type' => 'danger',
- 'log' => array(__FUNCTION__, $_action, $_data_log),
- 'msg' => 'access_denied'
- );
- return false;
- }
- if (empty($domains)) {
- $_SESSION['return'][] = array(
- 'type' => 'danger',
- 'log' => array(__FUNCTION__, $_action, $_data_log),
- 'msg' => 'domain_invalid'
- );
- return false;
- }
- if (!ctype_alnum(str_replace(array('_', '.', '-'), '', $username)) || empty ($username) || $username == 'API') {
- $_SESSION['return'][] = array(
- 'type' => 'danger',
- 'log' => array(__FUNCTION__, $_action, $_data_log),
- 'msg' => array('username_invalid', $username)
- );
- return false;
- }
-
- $stmt = $pdo->prepare("SELECT `username` FROM `mailbox`
- WHERE `username` = :username");
- $stmt->execute(array(':username' => $username));
- $num_results[] = count($stmt->fetchAll(PDO::FETCH_ASSOC));
-
- $stmt = $pdo->prepare("SELECT `username` FROM `admin`
- WHERE `username` = :username");
- $stmt->execute(array(':username' => $username));
- $num_results[] = count($stmt->fetchAll(PDO::FETCH_ASSOC));
-
- $stmt = $pdo->prepare("SELECT `username` FROM `domain_admins`
- WHERE `username` = :username");
- $stmt->execute(array(':username' => $username));
- $num_results[] = count($stmt->fetchAll(PDO::FETCH_ASSOC));
-
- foreach ($num_results as $num_results_each) {
- if ($num_results_each != 0) {
- $_SESSION['return'][] = array(
- 'type' => 'danger',
- 'log' => array(__FUNCTION__, $_action, $_data_log),
- 'msg' => array('object_exists', htmlspecialchars($username))
- );
- return false;
- }
- }
- if (password_check($password, $password2) !== true) {
- continue;
- }
- $password_hashed = hash_password($password);
- $valid_domains = 0;
- foreach ($domains as $domain) {
- if (!is_valid_domain_name($domain) || mailbox('get', 'domain_details', $domain) === false) {
- $_SESSION['return'][] = array(
- 'type' => 'danger',
- 'log' => array(__FUNCTION__, $_action, $_data_log),
- 'msg' => array('domain_invalid', htmlspecialchars($domain))
- );
- continue;
- }
- $valid_domains++;
- $stmt = $pdo->prepare("INSERT INTO `domain_admins` (`username`, `domain`, `created`, `active`)
- VALUES (:username, :domain, :created, :active)");
- $stmt->execute(array(
- ':username' => $username,
- ':domain' => $domain,
- ':created' => date('Y-m-d H:i:s'),
- ':active' => $active
- ));
- }
- if ($valid_domains != 0) {
- $stmt = $pdo->prepare("INSERT INTO `admin` (`username`, `password`, `superadmin`, `active`)
- VALUES (:username, :password_hashed, '0', :active)");
- $stmt->execute(array(
- ':username' => $username,
- ':password_hashed' => $password_hashed,
- ':active' => $active
- ));
- }
- $stmt = $pdo->prepare("INSERT INTO `da_acl` (`username`) VALUES (:username)");
- $stmt->execute(array(
- ':username' => $username
- ));
- $_SESSION['return'][] = array(
- 'type' => 'success',
- 'log' => array(__FUNCTION__, $_action, $_data_log),
- 'msg' => array('domain_admin_added', htmlspecialchars($username))
- );
- break;
- case 'edit':
- if ($_SESSION['mailcow_cc_role'] != "admin" && $_SESSION['mailcow_cc_role'] != "domainadmin") {
- $_SESSION['return'][] = array(
- 'type' => 'danger',
- 'log' => array(__FUNCTION__, $_action, $_data_log),
- 'msg' => 'access_denied'
- );
- return false;
- }
- // Administrator
- if ($_SESSION['mailcow_cc_role'] == "admin") {
- if (!is_array($_data['username'])) {
- $usernames = array();
- $usernames[] = $_data['username'];
- }
- else {
- $usernames = $_data['username'];
- }
- foreach ($usernames as $username) {
- $is_now = domain_admin('details', $username);
- $domains = (isset($_data['domains'])) ? (array)$_data['domains'] : null;
- if (!empty($is_now)) {
- $active = (isset($_data['active'])) ? intval($_data['active']) : $is_now['active'];
- $domains = (!empty($domains)) ? $domains : $is_now['selected_domains'];
- $username_new = (!empty($_data['username_new'])) ? $_data['username_new'] : $is_now['username'];
- }
- else {
- $_SESSION['return'][] = array(
- 'type' => 'danger',
- 'log' => array(__FUNCTION__, $_action, $_data_log),
- 'msg' => 'access_denied'
- );
- continue;
- }
- $password = $_data['password'];
- $password2 = $_data['password2'];
- if (!empty($domains)) {
- foreach ($domains as $domain) {
- if (!is_valid_domain_name($domain) || mailbox('get', 'domain_details', $domain) === false) {
- $_SESSION['return'][] = array(
- 'type' => 'danger',
- 'log' => array(__FUNCTION__, $_action, $_data_log),
- 'msg' => array('domain_invalid', htmlspecialchars($domain))
- );
- continue 2;
- }
- }
- }
- if (!ctype_alnum(str_replace(array('_', '.', '-'), '', $username_new))) {
- $_SESSION['return'][] = array(
- 'type' => 'danger',
- 'log' => array(__FUNCTION__, $_action, $_data_log),
- 'msg' => array('username_invalid', $username_new)
- );
- continue;
- }
- if ($username_new != $username) {
- if (!empty(domain_admin('details', $username_new)['username'])) {
- $_SESSION['return'][] = array(
- 'type' => 'danger',
- 'log' => array(__FUNCTION__, $_action, $_data_log),
- 'msg' => array('username_invalid', $username_new)
- );
- continue;
- }
- }
- $stmt = $pdo->prepare("DELETE FROM `domain_admins` WHERE `username` = :username");
- $stmt->execute(array(
- ':username' => $username,
- ));
- $stmt = $pdo->prepare("UPDATE `da_acl` SET `username` = :username_new WHERE `username` = :username");
- $stmt->execute(array(
- ':username_new' => $username_new,
- ':username' => $username
- ));
- if (!empty($domains)) {
- foreach ($domains as $domain) {
- $stmt = $pdo->prepare("INSERT INTO `domain_admins` (`username`, `domain`, `created`, `active`)
- VALUES (:username_new, :domain, :created, :active)");
- $stmt->execute(array(
- ':username_new' => $username_new,
- ':domain' => $domain,
- ':created' => date('Y-m-d H:i:s'),
- ':active' => $active
- ));
- }
- }
- if (!empty($password)) {
- if (password_check($password, $password2) !== true) {
- return false;
- }
- $password_hashed = hash_password($password);
- $stmt = $pdo->prepare("UPDATE `admin` SET `username` = :username_new, `active` = :active, `password` = :password_hashed WHERE `username` = :username");
- $stmt->execute(array(
- ':password_hashed' => $password_hashed,
- ':username_new' => $username_new,
- ':username' => $username,
- ':active' => $active
- ));
- if (isset($_data['disable_tfa'])) {
- $stmt = $pdo->prepare("UPDATE `tfa` SET `active` = '0' WHERE `username` = :username");
- $stmt->execute(array(':username' => $username));
- }
- else {
- $stmt = $pdo->prepare("UPDATE `tfa` SET `username` = :username_new WHERE `username` = :username");
- $stmt->execute(array(':username_new' => $username_new, ':username' => $username));
- }
- }
- else {
- $stmt = $pdo->prepare("UPDATE `admin` SET `username` = :username_new, `active` = :active WHERE `username` = :username");
- $stmt->execute(array(
- ':username_new' => $username_new,
- ':username' => $username,
- ':active' => $active
- ));
- if (isset($_data['disable_tfa'])) {
- $stmt = $pdo->prepare("UPDATE `tfa` SET `active` = '0' WHERE `username` = :username");
- $stmt->execute(array(':username' => $username));
- }
- else {
- $stmt = $pdo->prepare("UPDATE `tfa` SET `username` = :username_new WHERE `username` = :username");
- $stmt->execute(array(':username_new' => $username_new, ':username' => $username));
- }
- }
- $_SESSION['return'][] = array(
- 'type' => 'success',
- 'log' => array(__FUNCTION__, $_action, $_data_log),
- 'msg' => array('domain_admin_modified', htmlspecialchars($username))
- );
- }
- return true;
- }
- // Domain administrator
- // Can only edit itself
- elseif ($_SESSION['mailcow_cc_role'] == "domainadmin") {
- $username = $_SESSION['mailcow_cc_username'];
- $password_old = $_data['user_old_pass'];
- $password_new = $_data['user_new_pass'];
- $password_new2 = $_data['user_new_pass2'];
-
- $stmt = $pdo->prepare("SELECT `password` FROM `admin`
- WHERE `username` = :user");
- $stmt->execute(array(':user' => $username));
- $row = $stmt->fetch(PDO::FETCH_ASSOC);
- if (!verify_hash($row['password'], $password_old)) {
- $_SESSION['return'][] = array(
- 'type' => 'danger',
- 'log' => array(__FUNCTION__, $_action, $_data_log),
- 'msg' => 'access_denied'
- );
- return false;
- }
- if (password_check($password_new, $password_new2) !== true) {
- return false;
- }
- $password_hashed = hash_password($password_new);
- $stmt = $pdo->prepare("UPDATE `admin` SET `password` = :password_hashed WHERE `username` = :username");
- $stmt->execute(array(
- ':password_hashed' => $password_hashed,
- ':username' => $username
- ));
- $_SESSION['return'][] = array(
- 'type' => 'success',
- 'log' => array(__FUNCTION__, $_action, $_data_log),
- 'msg' => array('domain_admin_modified', htmlspecialchars($username))
- );
- }
- break;
- case 'delete':
- if ($_SESSION['mailcow_cc_role'] != "admin") {
- $_SESSION['return'][] = array(
- 'type' => 'danger',
- 'log' => array(__FUNCTION__, $_action, $_data_log),
- 'msg' => 'access_denied'
- );
- return false;
- }
- $usernames = (array)$_data['username'];
- foreach ($usernames as $username) {
- if (empty(domain_admin('details', $username))) {
- $_SESSION['return'][] = array(
- 'type' => 'danger',
- 'log' => array(__FUNCTION__, $_action, $_data_log),
- 'msg' => array('username_invalid', $username)
- );
- continue;
- }
- $stmt = $pdo->prepare("DELETE FROM `domain_admins` WHERE `username` = :username");
- $stmt->execute(array(
- ':username' => $username,
- ));
- $stmt = $pdo->prepare("DELETE FROM `admin` WHERE `username` = :username");
- $stmt->execute(array(
- ':username' => $username,
- ));
- $stmt = $pdo->prepare("DELETE FROM `da_acl` WHERE `username` = :username");
- $stmt->execute(array(
- ':username' => $username,
- ));
- $stmt = $pdo->prepare("DELETE FROM `tfa` WHERE `username` = :username");
- $stmt->execute(array(
- ':username' => $username,
- ));
- $stmt = $pdo->prepare("DELETE FROM `fido2` WHERE `username` = :username");
- $stmt->execute(array(
- ':username' => $username,
- ));
- $_SESSION['return'][] = array(
- 'type' => 'success',
- 'log' => array(__FUNCTION__, $_action, $_data_log),
- 'msg' => array('domain_admin_removed', htmlspecialchars($username))
- );
- }
- break;
- case 'get':
- $domainadmins = array();
- if ($_SESSION['mailcow_cc_role'] != "admin") {
- $_SESSION['return'][] = array(
- 'type' => 'danger',
- 'log' => array(__FUNCTION__, $_action, $_data_log),
- 'msg' => 'access_denied'
- );
- return false;
- }
- $stmt = $pdo->query("SELECT DISTINCT
- `username`
- FROM `domain_admins`
- WHERE `username` IN (
- SELECT `username` FROM `admin`
- WHERE `superadmin`!='1'
- )");
- $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
- while ($row = array_shift($rows)) {
- $domainadmins[] = $row['username'];
- }
- return $domainadmins;
- break;
- case 'details':
- $domainadmindata = array();
- if ($_SESSION['mailcow_cc_role'] == "domainadmin" && $_data != $_SESSION['mailcow_cc_username']) {
- return false;
- }
- elseif ($_SESSION['mailcow_cc_role'] != "admin" || !isset($_data)) {
- return false;
- }
- if (!ctype_alnum(str_replace(array('_', '.', '-'), '', $_data))) {
- return false;
- }
- $stmt = $pdo->prepare("SELECT
- `tfa`.`active` AS `tfa_active`,
- `domain_admins`.`username`,
- `domain_admins`.`created`,
- `domain_admins`.`active` AS `active`
- FROM `domain_admins`
- LEFT OUTER JOIN `tfa` ON `tfa`.`username`=`domain_admins`.`username`
- WHERE `domain_admins`.`username`= :domain_admin");
- $stmt->execute(array(
- ':domain_admin' => $_data
- ));
- $row = $stmt->fetch(PDO::FETCH_ASSOC);
- if (empty($row)) {
- return false;
- }
- $domainadmindata['username'] = $row['username'];
- $domainadmindata['tfa_active'] = (is_null($row['tfa_active'])) ? 0 : $row['tfa_active'];
- $domainadmindata['tfa_active_int'] = (is_null($row['tfa_active'])) ? 0 : $row['tfa_active'];
- $domainadmindata['active'] = $row['active'];
- $domainadmindata['active_int'] = $row['active'];
- $domainadmindata['created'] = $row['created'];
- // GET SELECTED
- $stmt = $pdo->prepare("SELECT `domain` FROM `domain`
- WHERE `domain` IN (
- SELECT `domain` FROM `domain_admins`
- WHERE `username`= :domain_admin)");
- $stmt->execute(array(':domain_admin' => $_data));
- $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
- while($row = array_shift($rows)) {
- $domainadmindata['selected_domains'][] = $row['domain'];
- }
- // GET UNSELECTED
- $stmt = $pdo->prepare("SELECT `domain` FROM `domain`
- WHERE `domain` NOT IN (
- SELECT `domain` FROM `domain_admins`
- WHERE `username`= :domain_admin)");
- $stmt->execute(array(':domain_admin' => $_data));
- $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
- while($row = array_shift($rows)) {
- $domainadmindata['unselected_domains'][] = $row['domain'];
- }
- if (!isset($domainadmindata['unselected_domains'])) {
- $domainadmindata['unselected_domains'] = "";
- }
-
- return $domainadmindata;
- break;
- }
-}
+<?php
+function domain_admin($_action, $_data = null) {
+ global $pdo;
+ global $lang;
+ $_data_log = $_data;
+ !isset($_data_log['password']) ?: $_data_log['password'] = '*';
+ !isset($_data_log['password2']) ?: $_data_log['password2'] = '*';
+ !isset($_data_log['user_old_pass']) ?: $_data_log['user_old_pass'] = '*';
+ !isset($_data_log['user_new_pass']) ?: $_data_log['user_new_pass'] = '*';
+ !isset($_data_log['user_new_pass2']) ?: $_data_log['user_new_pass2'] = '*';
+ switch ($_action) {
+ case 'add':
+ $username = strtolower(trim($_data['username']));
+ $password = $_data['password'];
+ $password2 = $_data['password2'];
+ $domains = (array)$_data['domains'];
+ $active = intval($_data['active']);
+ if ($_SESSION['mailcow_cc_role'] != "admin") {
+ $_SESSION['return'][] = array(
+ 'type' => 'danger',
+ 'log' => array(__FUNCTION__, $_action, $_data_log),
+ 'msg' => 'access_denied'
+ );
+ return false;
+ }
+ if (empty($domains)) {
+ $_SESSION['return'][] = array(
+ 'type' => 'danger',
+ 'log' => array(__FUNCTION__, $_action, $_data_log),
+ 'msg' => 'domain_invalid'
+ );
+ return false;
+ }
+ if (!ctype_alnum(str_replace(array('_', '.', '-'), '', $username)) || empty ($username) || $username == 'API') {
+ $_SESSION['return'][] = array(
+ 'type' => 'danger',
+ 'log' => array(__FUNCTION__, $_action, $_data_log),
+ 'msg' => array('username_invalid', $username)
+ );
+ return false;
+ }
+
+ $stmt = $pdo->prepare("SELECT `username` FROM `mailbox`
+ WHERE `username` = :username");
+ $stmt->execute(array(':username' => $username));
+ $num_results[] = count($stmt->fetchAll(PDO::FETCH_ASSOC));
+
+ $stmt = $pdo->prepare("SELECT `username` FROM `admin`
+ WHERE `username` = :username");
+ $stmt->execute(array(':username' => $username));
+ $num_results[] = count($stmt->fetchAll(PDO::FETCH_ASSOC));
+
+ $stmt = $pdo->prepare("SELECT `username` FROM `domain_admins`
+ WHERE `username` = :username");
+ $stmt->execute(array(':username' => $username));
+ $num_results[] = count($stmt->fetchAll(PDO::FETCH_ASSOC));
+
+ foreach ($num_results as $num_results_each) {
+ if ($num_results_each != 0) {
+ $_SESSION['return'][] = array(
+ 'type' => 'danger',
+ 'log' => array(__FUNCTION__, $_action, $_data_log),
+ 'msg' => array('object_exists', htmlspecialchars($username))
+ );
+ return false;
+ }
+ }
+ if (password_check($password, $password2) !== true) {
+ continue;
+ }
+ $password_hashed = hash_password($password);
+ $valid_domains = 0;
+ foreach ($domains as $domain) {
+ if (!is_valid_domain_name($domain) || mailbox('get', 'domain_details', $domain) === false) {
+ $_SESSION['return'][] = array(
+ 'type' => 'danger',
+ 'log' => array(__FUNCTION__, $_action, $_data_log),
+ 'msg' => array('domain_invalid', htmlspecialchars($domain))
+ );
+ continue;
+ }
+ $valid_domains++;
+ $stmt = $pdo->prepare("INSERT INTO `domain_admins` (`username`, `domain`, `created`, `active`)
+ VALUES (:username, :domain, :created, :active)");
+ $stmt->execute(array(
+ ':username' => $username,
+ ':domain' => $domain,
+ ':created' => date('Y-m-d H:i:s'),
+ ':active' => $active
+ ));
+ }
+ if ($valid_domains != 0) {
+ $stmt = $pdo->prepare("INSERT INTO `admin` (`username`, `password`, `superadmin`, `active`)
+ VALUES (:username, :password_hashed, '0', :active)");
+ $stmt->execute(array(
+ ':username' => $username,
+ ':password_hashed' => $password_hashed,
+ ':active' => $active
+ ));
+ }
+ $stmt = $pdo->prepare("INSERT INTO `da_acl` (`username`) VALUES (:username)");
+ $stmt->execute(array(
+ ':username' => $username
+ ));
+ $_SESSION['return'][] = array(
+ 'type' => 'success',
+ 'log' => array(__FUNCTION__, $_action, $_data_log),
+ 'msg' => array('domain_admin_added', htmlspecialchars($username))
+ );
+ break;
+ case 'edit':
+ if ($_SESSION['mailcow_cc_role'] != "admin" && $_SESSION['mailcow_cc_role'] != "domainadmin") {
+ $_SESSION['return'][] = array(
+ 'type' => 'danger',
+ 'log' => array(__FUNCTION__, $_action, $_data_log),
+ 'msg' => 'access_denied'
+ );
+ return false;
+ }
+ // Administrator
+ if ($_SESSION['mailcow_cc_role'] == "admin") {
+ if (!is_array($_data['username'])) {
+ $usernames = array();
+ $usernames[] = $_data['username'];
+ }
+ else {
+ $usernames = $_data['username'];
+ }
+ foreach ($usernames as $username) {
+ $is_now = domain_admin('details', $username);
+ $domains = (isset($_data['domains'])) ? (array)$_data['domains'] : null;
+ if (!empty($is_now)) {
+ $active = (isset($_data['active'])) ? intval($_data['active']) : $is_now['active'];
+ $domains = (!empty($domains)) ? $domains : $is_now['selected_domains'];
+ $username_new = (!empty($_data['username_new'])) ? $_data['username_new'] : $is_now['username'];
+ }
+ else {
+ $_SESSION['return'][] = array(
+ 'type' => 'danger',
+ 'log' => array(__FUNCTION__, $_action, $_data_log),
+ 'msg' => 'access_denied'
+ );
+ continue;
+ }
+ $password = $_data['password'];
+ $password2 = $_data['password2'];
+ if (!empty($domains)) {
+ foreach ($domains as $domain) {
+ if (!is_valid_domain_name($domain) || mailbox('get', 'domain_details', $domain) === false) {
+ $_SESSION['return'][] = array(
+ 'type' => 'danger',
+ 'log' => array(__FUNCTION__, $_action, $_data_log),
+ 'msg' => array('domain_invalid', htmlspecialchars($domain))
+ );
+ continue 2;
+ }
+ }
+ }
+ if (!ctype_alnum(str_replace(array('_', '.', '-'), '', $username_new))) {
+ $_SESSION['return'][] = array(
+ 'type' => 'danger',
+ 'log' => array(__FUNCTION__, $_action, $_data_log),
+ 'msg' => array('username_invalid', $username_new)
+ );
+ continue;
+ }
+ if ($username_new != $username) {
+ if (!empty(domain_admin('details', $username_new)['username'])) {
+ $_SESSION['return'][] = array(
+ 'type' => 'danger',
+ 'log' => array(__FUNCTION__, $_action, $_data_log),
+ 'msg' => array('username_invalid', $username_new)
+ );
+ continue;
+ }
+ }
+ $stmt = $pdo->prepare("DELETE FROM `domain_admins` WHERE `username` = :username");
+ $stmt->execute(array(
+ ':username' => $username,
+ ));
+ $stmt = $pdo->prepare("UPDATE `da_acl` SET `username` = :username_new WHERE `username` = :username");
+ $stmt->execute(array(
+ ':username_new' => $username_new,
+ ':username' => $username
+ ));
+ if (!empty($domains)) {
+ foreach ($domains as $domain) {
+ $stmt = $pdo->prepare("INSERT INTO `domain_admins` (`username`, `domain`, `created`, `active`)
+ VALUES (:username_new, :domain, :created, :active)");
+ $stmt->execute(array(
+ ':username_new' => $username_new,
+ ':domain' => $domain,
+ ':created' => date('Y-m-d H:i:s'),
+ ':active' => $active
+ ));
+ }
+ }
+ if (!empty($password)) {
+ if (password_check($password, $password2) !== true) {
+ return false;
+ }
+ $password_hashed = hash_password($password);
+ $stmt = $pdo->prepare("UPDATE `admin` SET `username` = :username_new, `active` = :active, `password` = :password_hashed WHERE `username` = :username");
+ $stmt->execute(array(
+ ':password_hashed' => $password_hashed,
+ ':username_new' => $username_new,
+ ':username' => $username,
+ ':active' => $active
+ ));
+ if (isset($_data['disable_tfa'])) {
+ $stmt = $pdo->prepare("UPDATE `tfa` SET `active` = '0' WHERE `username` = :username");
+ $stmt->execute(array(':username' => $username));
+ }
+ else {
+ $stmt = $pdo->prepare("UPDATE `tfa` SET `username` = :username_new WHERE `username` = :username");
+ $stmt->execute(array(':username_new' => $username_new, ':username' => $username));
+ }
+ }
+ else {
+ $stmt = $pdo->prepare("UPDATE `admin` SET `username` = :username_new, `active` = :active WHERE `username` = :username");
+ $stmt->execute(array(
+ ':username_new' => $username_new,
+ ':username' => $username,
+ ':active' => $active
+ ));
+ if (isset($_data['disable_tfa'])) {
+ $stmt = $pdo->prepare("UPDATE `tfa` SET `active` = '0' WHERE `username` = :username");
+ $stmt->execute(array(':username' => $username));
+ }
+ else {
+ $stmt = $pdo->prepare("UPDATE `tfa` SET `username` = :username_new WHERE `username` = :username");
+ $stmt->execute(array(':username_new' => $username_new, ':username' => $username));
+ }
+ }
+ $_SESSION['return'][] = array(
+ 'type' => 'success',
+ 'log' => array(__FUNCTION__, $_action, $_data_log),
+ 'msg' => array('domain_admin_modified', htmlspecialchars($username))
+ );
+ }
+ return true;
+ }
+ // Domain administrator
+ // Can only edit itself
+ elseif ($_SESSION['mailcow_cc_role'] == "domainadmin") {
+ $username = $_SESSION['mailcow_cc_username'];
+ $password_old = $_data['user_old_pass'];
+ $password_new = $_data['user_new_pass'];
+ $password_new2 = $_data['user_new_pass2'];
+
+ $stmt = $pdo->prepare("SELECT `password` FROM `admin`
+ WHERE `username` = :user");
+ $stmt->execute(array(':user' => $username));
+ $row = $stmt->fetch(PDO::FETCH_ASSOC);
+ if (!verify_hash($row['password'], $password_old)) {
+ $_SESSION['return'][] = array(
+ 'type' => 'danger',
+ 'log' => array(__FUNCTION__, $_action, $_data_log),
+ 'msg' => 'access_denied'
+ );
+ return false;
+ }
+ if (password_check($password_new, $password_new2) !== true) {
+ return false;
+ }
+ $password_hashed = hash_password($password_new);
+ $stmt = $pdo->prepare("UPDATE `admin` SET `password` = :password_hashed WHERE `username` = :username");
+ $stmt->execute(array(
+ ':password_hashed' => $password_hashed,
+ ':username' => $username
+ ));
+ $_SESSION['return'][] = array(
+ 'type' => 'success',
+ 'log' => array(__FUNCTION__, $_action, $_data_log),
+ 'msg' => array('domain_admin_modified', htmlspecialchars($username))
+ );
+ }
+ break;
+ case 'delete':
+ if ($_SESSION['mailcow_cc_role'] != "admin") {
+ $_SESSION['return'][] = array(
+ 'type' => 'danger',
+ 'log' => array(__FUNCTION__, $_action, $_data_log),
+ 'msg' => 'access_denied'
+ );
+ return false;
+ }
+ $usernames = (array)$_data['username'];
+ foreach ($usernames as $username) {
+ if (empty(domain_admin('details', $username))) {
+ $_SESSION['return'][] = array(
+ 'type' => 'danger',
+ 'log' => array(__FUNCTION__, $_action, $_data_log),
+ 'msg' => array('username_invalid', $username)
+ );
+ continue;
+ }
+ $stmt = $pdo->prepare("DELETE FROM `domain_admins` WHERE `username` = :username");
+ $stmt->execute(array(
+ ':username' => $username,
+ ));
+ $stmt = $pdo->prepare("DELETE FROM `admin` WHERE `username` = :username");
+ $stmt->execute(array(
+ ':username' => $username,
+ ));
+ $stmt = $pdo->prepare("DELETE FROM `da_acl` WHERE `username` = :username");
+ $stmt->execute(array(
+ ':username' => $username,
+ ));
+ $stmt = $pdo->prepare("DELETE FROM `tfa` WHERE `username` = :username");
+ $stmt->execute(array(
+ ':username' => $username,
+ ));
+ $stmt = $pdo->prepare("DELETE FROM `fido2` WHERE `username` = :username");
+ $stmt->execute(array(
+ ':username' => $username,
+ ));
+ $_SESSION['return'][] = array(
+ 'type' => 'success',
+ 'log' => array(__FUNCTION__, $_action, $_data_log),
+ 'msg' => array('domain_admin_removed', htmlspecialchars($username))
+ );
+ }
+ break;
+ case 'get':
+ $domainadmins = array();
+ if ($_SESSION['mailcow_cc_role'] != "admin") {
+ $_SESSION['return'][] = array(
+ 'type' => 'danger',
+ 'log' => array(__FUNCTION__, $_action, $_data_log),
+ 'msg' => 'access_denied'
+ );
+ return false;
+ }
+ $stmt = $pdo->query("SELECT DISTINCT
+ `username`
+ FROM `domain_admins`
+ WHERE `username` IN (
+ SELECT `username` FROM `admin`
+ WHERE `superadmin`!='1'
+ )");
+ $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
+ while ($row = array_shift($rows)) {
+ $domainadmins[] = $row['username'];
+ }
+ return $domainadmins;
+ break;
+ case 'details':
+ $domainadmindata = array();
+ if ($_SESSION['mailcow_cc_role'] == "domainadmin" && $_data != $_SESSION['mailcow_cc_username']) {
+ return false;
+ }
+ elseif ($_SESSION['mailcow_cc_role'] != "admin" || !isset($_data)) {
+ return false;
+ }
+ if (!ctype_alnum(str_replace(array('_', '.', '-'), '', $_data))) {
+ return false;
+ }
+ $stmt = $pdo->prepare("SELECT
+ `tfa`.`active` AS `tfa_active`,
+ `domain_admins`.`username`,
+ `domain_admins`.`created`,
+ `domain_admins`.`active` AS `active`
+ FROM `domain_admins`
+ LEFT OUTER JOIN `tfa` ON `tfa`.`username`=`domain_admins`.`username`
+ WHERE `domain_admins`.`username`= :domain_admin");
+ $stmt->execute(array(
+ ':domain_admin' => $_data
+ ));
+ $row = $stmt->fetch(PDO::FETCH_ASSOC);
+ if (empty($row)) {
+ return false;
+ }
+ $domainadmindata['username'] = $row['username'];
+ $domainadmindata['tfa_active'] = (is_null($row['tfa_active'])) ? 0 : $row['tfa_active'];
+ $domainadmindata['tfa_active_int'] = (is_null($row['tfa_active'])) ? 0 : $row['tfa_active'];
+ $domainadmindata['active'] = $row['active'];
+ $domainadmindata['active_int'] = $row['active'];
+ $domainadmindata['created'] = $row['created'];
+ // GET SELECTED
+ $stmt = $pdo->prepare("SELECT `domain` FROM `domain`
+ WHERE `domain` IN (
+ SELECT `domain` FROM `domain_admins`
+ WHERE `username`= :domain_admin)");
+ $stmt->execute(array(':domain_admin' => $_data));
+ $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
+ while($row = array_shift($rows)) {
+ $domainadmindata['selected_domains'][] = $row['domain'];
+ }
+ // GET UNSELECTED
+ $stmt = $pdo->prepare("SELECT `domain` FROM `domain`
+ WHERE `domain` NOT IN (
+ SELECT `domain` FROM `domain_admins`
+ WHERE `username`= :domain_admin)");
+ $stmt->execute(array(':domain_admin' => $_data));
+ $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
+ while($row = array_shift($rows)) {
+ $domainadmindata['unselected_domains'][] = $row['domain'];
+ }
+ if (!isset($domainadmindata['unselected_domains'])) {
+ $domainadmindata['unselected_domains'] = "";
+ }
+
+ return $domainadmindata;
+ break;
+ }
+}
+function domain_admin_sso($_action, $_data) {
+ global $pdo;
+
+ switch ($_action) {
+ case 'check':
+ $token = $_data;
+
+ $stmt = $pdo->prepare("SELECT `t1`.`username` FROM `da_sso` AS `t1` JOIN `admin` AS `t2` ON `t1`.`username` = `t2`.`username` WHERE `t1`.`token` = :token AND `t1`.`created` > DATE_SUB(NOW(), INTERVAL '30' SECOND) AND `t2`.`active` = 1 AND `t2`.`superadmin` = 0;");
+ $stmt->execute(array(
+ ':token' => preg_replace('/[^a-zA-Z0-9-]/', '', $token)
+ ));
+ $return = $stmt->fetch(PDO::FETCH_ASSOC);
+ return empty($return['username']) ? false : $return['username'];
+ case 'issue':
+ if ($_SESSION['mailcow_cc_role'] != "admin") {
+ $_SESSION['return'][] = array(
+ 'type' => 'danger',
+ 'log' => array(__FUNCTION__, $_action, $_data),
+ 'msg' => 'access_denied'
+ );
+ return false;
+ }
+
+ $username = $_data['username'];
+
+ $stmt = $pdo->prepare("SELECT `username` FROM `domain_admins`
+ WHERE `username` = :username");
+ $stmt->execute(array(':username' => $username));
+ $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
+
+ if ($num_results < 1) {
+ $_SESSION['return'][] = array(
+ 'type' => 'danger',
+ 'log' => array(__FUNCTION__, $_action, $_data),
+ 'msg' => array('object_doesnt_exist', htmlspecialchars($username))
+ );
+ return false;
+ }
+
+ $token = implode('-', array(
+ strtoupper(bin2hex(random_bytes(3))),
+ strtoupper(bin2hex(random_bytes(3))),
+ strtoupper(bin2hex(random_bytes(3))),
+ strtoupper(bin2hex(random_bytes(3))),
+ strtoupper(bin2hex(random_bytes(3)))
+ ));
+
+ $stmt = $pdo->prepare("INSERT INTO `da_sso` (`username`, `token`)
+ VALUES (:username, :token)");
+ $stmt->execute(array(
+ ':username' => $username,
+ ':token' => $token
+ ));
+
+ // perform cleanup
+ $pdo->query("DELETE FROM `da_sso` WHERE created < DATE_SUB(NOW(), INTERVAL '30' SECOND);");
+
+ return ['token' => $token];
+ break;
+ }
+}
diff --git a/mailcow/src/mailcow-dockerized/data/web/inc/functions.fail2ban.inc.php b/mailcow/src/mailcow-dockerized/data/web/inc/functions.fail2ban.inc.php
index 2a7f11e..2c4aa41 100644
--- a/mailcow/src/mailcow-dockerized/data/web/inc/functions.fail2ban.inc.php
+++ b/mailcow/src/mailcow-dockerized/data/web/inc/functions.fail2ban.inc.php
@@ -239,7 +239,9 @@
$is_now = fail2ban('get');
if (!empty($is_now)) {
$ban_time = intval((isset($_data['ban_time'])) ? $_data['ban_time'] : $is_now['ban_time']);
+ $ban_time_increment = (isset($_data['ban_time_increment']) && $_data['ban_time_increment'] == "1") ? 1 : 0;
$max_attempts = intval((isset($_data['max_attempts'])) ? $_data['max_attempts'] : $is_now['max_attempts']);
+ $max_ban_time = intval((isset($_data['max_ban_time'])) ? $_data['max_ban_time'] : $is_now['max_ban_time']);
$retry_window = intval((isset($_data['retry_window'])) ? $_data['retry_window'] : $is_now['retry_window']);
$netban_ipv4 = intval((isset($_data['netban_ipv4'])) ? $_data['netban_ipv4'] : $is_now['netban_ipv4']);
$netban_ipv6 = intval((isset($_data['netban_ipv6'])) ? $_data['netban_ipv6'] : $is_now['netban_ipv6']);
@@ -256,6 +258,8 @@
}
$f2b_options = array();
$f2b_options['ban_time'] = ($ban_time < 60) ? 60 : $ban_time;
+ $f2b_options['ban_time_increment'] = ($ban_time_increment == 1) ? true : false;
+ $f2b_options['max_ban_time'] = ($max_ban_time < 60) ? 60 : $max_ban_time;
$f2b_options['netban_ipv4'] = ($netban_ipv4 < 8) ? 8 : $netban_ipv4;
$f2b_options['netban_ipv6'] = ($netban_ipv6 < 8) ? 8 : $netban_ipv6;
$f2b_options['netban_ipv4'] = ($netban_ipv4 > 32) ? 32 : $netban_ipv4;
diff --git a/mailcow/src/mailcow-dockerized/data/web/inc/functions.inc.php b/mailcow/src/mailcow-dockerized/data/web/inc/functions.inc.php
index 3bab56b..6418945 100644
--- a/mailcow/src/mailcow-dockerized/data/web/inc/functions.inc.php
+++ b/mailcow/src/mailcow-dockerized/data/web/inc/functions.inc.php
@@ -526,8 +526,9 @@
':remote' => get_remote_ip()
));
}
- catch (Exception $e) {
- // Do nothing
+ catch (PDOException $e) {
+ # handle the exception here, as the exception handler function results in a white page
+ error_log($e->getMessage(), 0);
}
}
}
@@ -1015,20 +1016,58 @@
}
return round(pow(1024, $base - floor($base)), $precision) . $suffixes[floor($base)];
}
-function update_sogo_static_view() {
+function update_sogo_static_view($mailbox = null) {
if (getenv('SKIP_SOGO') == "y") {
return true;
}
global $pdo;
global $lang;
- $stmt = $pdo->query("SELECT 'OK' FROM INFORMATION_SCHEMA.TABLES
- WHERE TABLE_NAME = 'sogo_view'");
- $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
- if ($num_results != 0) {
- $stmt = $pdo->query("REPLACE INTO _sogo_static_view (`c_uid`, `domain`, `c_name`, `c_password`, `c_cn`, `mail`, `aliases`, `ad_aliases`, `ext_acl`, `kind`, `multiple_bookings`)
- SELECT `c_uid`, `domain`, `c_name`, `c_password`, `c_cn`, `mail`, `aliases`, `ad_aliases`, `ext_acl`, `kind`, `multiple_bookings` from sogo_view");
- $stmt = $pdo->query("DELETE FROM _sogo_static_view WHERE `c_uid` NOT IN (SELECT `username` FROM `mailbox` WHERE `active` = '1');");
+
+ $mailbox_exists = false;
+ if ($mailbox !== null) {
+ // Check if the mailbox exists
+ $stmt = $pdo->prepare("SELECT username FROM mailbox WHERE username = :mailbox AND active = '1'");
+ $stmt->execute(array(':mailbox' => $mailbox));
+ $row = $stmt->fetch(PDO::FETCH_ASSOC);
+ if ($row){
+ $mailbox_exists = true;
+ }
}
+
+ $query = "REPLACE INTO _sogo_static_view (`c_uid`, `domain`, `c_name`, `c_password`, `c_cn`, `mail`, `aliases`, `ad_aliases`, `ext_acl`, `kind`, `multiple_bookings`)
+ SELECT
+ mailbox.username,
+ mailbox.domain,
+ mailbox.username,
+ IF(JSON_UNQUOTE(JSON_VALUE(attributes, '$.force_pw_update')) = '0',
+ IF(JSON_UNQUOTE(JSON_VALUE(attributes, '$.sogo_access')) = 1, password, '{SSHA256}A123A123A321A321A321B321B321B123B123B321B432F123E321123123321321'),
+ '{SSHA256}A123A123A321A321A321B321B321B123B123B321B432F123E321123123321321'),
+ mailbox.name,
+ mailbox.username,
+ IFNULL(GROUP_CONCAT(ga.aliases ORDER BY ga.aliases SEPARATOR ' '), ''),
+ IFNULL(gda.ad_alias, ''),
+ IFNULL(external_acl.send_as_acl, ''),
+ mailbox.kind,
+ mailbox.multiple_bookings
+ FROM
+ mailbox
+ LEFT OUTER JOIN grouped_mail_aliases ga ON ga.username REGEXP CONCAT('(^|,)', mailbox.username, '($|,)')
+ LEFT OUTER JOIN grouped_domain_alias_address gda ON gda.username = mailbox.username
+ LEFT OUTER JOIN grouped_sender_acl_external external_acl ON external_acl.username = mailbox.username
+ WHERE
+ mailbox.active = '1'";
+
+ if ($mailbox_exists) {
+ $query .= " AND mailbox.username = :mailbox";
+ $stmt = $pdo->prepare($query);
+ $stmt->execute(array(':mailbox' => $mailbox));
+ } else {
+ $query .= " GROUP BY mailbox.username";
+ $stmt = $pdo->query($query);
+ }
+
+ $stmt = $pdo->query("DELETE FROM _sogo_static_view WHERE `c_uid` NOT IN (SELECT `username` FROM `mailbox` WHERE `active` = '1');");
+
flush_memcached();
}
function edit_user_account($_data) {
@@ -1739,7 +1778,7 @@
$_SESSION['return'][] = array(
'type' => 'danger',
'log' => array(__FUNCTION__, $username, '*'),
- 'msg' => array('webauthn_verification_failed', 'authenticator not found')
+ 'msg' => array('webauthn_authenticator_failed')
);
return false;
}
@@ -1748,11 +1787,20 @@
$_SESSION['return'][] = array(
'type' => 'danger',
'log' => array(__FUNCTION__, $username, '*'),
- 'msg' => array('webauthn_verification_failed', 'publicKey not found')
+ 'msg' => array('webauthn_publickey_failed')
);
return false;
}
+ if ($process_webauthn['username'] != $_SESSION['pending_mailcow_cc_username']){
+ $_SESSION['return'][] = array(
+ 'type' => 'danger',
+ 'log' => array(__FUNCTION__, $username, '*'),
+ 'msg' => array('webauthn_username_failed')
+ );
+ return false;
+ }
+
try {
$WebAuthn->processGet($clientDataJSON, $authenticatorData, $signature, $process_webauthn['publicKey'], $challenge, null, $GLOBALS['WEBAUTHN_UV_FLAG_LOGIN'], $GLOBALS['WEBAUTHN_USER_PRESENT_FLAG']);
}
@@ -1784,21 +1832,12 @@
$_SESSION['return'][] = array(
'type' => 'danger',
'log' => array(__FUNCTION__, $username, '*'),
- 'msg' => array('webauthn_verification_failed', 'could not determine user role')
+ 'msg' => array('webauthn_role_failed')
);
return false;
}
}
- if ($process_webauthn['username'] != $_SESSION['pending_mailcow_cc_username']){
- $_SESSION['return'][] = array(
- 'type' => 'danger',
- 'log' => array(__FUNCTION__, $username, '*'),
- 'msg' => array('webauthn_verification_failed', 'user who requests does not match with sql entry')
- );
- return false;
- }
-
$_SESSION["mailcow_cc_username"] = $process_webauthn['username'];
$_SESSION['tfa_id'] = $process_webauthn['id'];
$_SESSION['authReq'] = null;
@@ -2093,6 +2132,120 @@
break;
}
}
+function cors($action, $data = null) {
+ global $redis;
+
+ switch ($action) {
+ case "edit":
+ if ($_SESSION['mailcow_cc_role'] != "admin") {
+ $_SESSION['return'][] = array(
+ 'type' => 'danger',
+ 'log' => array(__FUNCTION__, $action, $data),
+ 'msg' => 'access_denied'
+ );
+ return false;
+ }
+
+ $allowed_origins = isset($data['allowed_origins']) ? $data['allowed_origins'] : array($_SERVER['SERVER_NAME']);
+ $allowed_origins = !is_array($allowed_origins) ? array_filter(array_map('trim', explode("\n", $allowed_origins))) : $allowed_origins;
+ foreach ($allowed_origins as $origin) {
+ if (!filter_var($origin, FILTER_VALIDATE_DOMAIN, FILTER_FLAG_HOSTNAME) && $origin != '*') {
+ $_SESSION['return'][] = array(
+ 'type' => 'danger',
+ 'log' => array(__FUNCTION__, $action, $data),
+ 'msg' => 'cors_invalid_origin'
+ );
+ return false;
+ }
+ }
+
+ $allowed_methods = isset($data['allowed_methods']) ? $data['allowed_methods'] : array('GET', 'POST', 'PUT', 'DELETE');
+ $allowed_methods = !is_array($allowed_methods) ? array_map('trim', preg_split( "/( |,|;|\n)/", $allowed_methods)) : $allowed_methods;
+ $available_methods = array('GET', 'POST', 'PUT', 'DELETE');
+ foreach ($allowed_methods as $method) {
+ if (!in_array($method, $available_methods)) {
+ $_SESSION['return'][] = array(
+ 'type' => 'danger',
+ 'log' => array(__FUNCTION__, $action, $data),
+ 'msg' => 'cors_invalid_method'
+ );
+ return false;
+ }
+ }
+
+ try {
+ $redis->hMSet('CORS_SETTINGS', array(
+ 'allowed_origins' => implode(', ', $allowed_origins),
+ 'allowed_methods' => implode(', ', $allowed_methods)
+ ));
+ } catch (RedisException $e) {
+ $_SESSION['return'][] = array(
+ 'type' => 'danger',
+ 'log' => array(__FUNCTION__, $action, $data),
+ 'msg' => array('redis_error', $e)
+ );
+ return false;
+ }
+
+ $_SESSION['return'][] = array(
+ 'type' => 'success',
+ 'log' => array(__FUNCTION__, $action, $data),
+ 'msg' => 'cors_headers_edited'
+ );
+ return true;
+ break;
+ case "get":
+ try {
+ $cors_settings = $redis->hMGet('CORS_SETTINGS', array('allowed_origins', 'allowed_methods'));
+ } catch (RedisException $e) {
+ $_SESSION['return'][] = array(
+ 'type' => 'danger',
+ 'log' => array(__FUNCTION__, $action, $data),
+ 'msg' => array('redis_error', $e)
+ );
+ }
+
+ $cors_settings = !$cors_settings ? array('allowed_origins' => $_SERVER['SERVER_NAME'], 'allowed_methods' => 'GET, POST, PUT, DELETE') : $cors_settings;
+ $cors_settings['allowed_origins'] = empty($cors_settings['allowed_origins']) ? $_SERVER['SERVER_NAME'] : $cors_settings['allowed_origins'];
+ $cors_settings['allowed_methods'] = empty($cors_settings['allowed_methods']) ? 'GET, POST, PUT, DELETE, OPTION' : $cors_settings['allowed_methods'];
+
+ return $cors_settings;
+ break;
+ case "set_headers":
+ $cors_settings = cors('get');
+ // check if requested origin is in allowed origins
+ $allowed_origins = explode(', ', $cors_settings['allowed_origins']);
+ $cors_settings['allowed_origins'] = $allowed_origins[0];
+ if (in_array('*', $allowed_origins)){
+ $cors_settings['allowed_origins'] = '*';
+ } else if (in_array($_SERVER['HTTP_ORIGIN'], $allowed_origins)) {
+ $cors_settings['allowed_origins'] = $_SERVER['HTTP_ORIGIN'];
+ }
+ // always allow OPTIONS for preflight request
+ $cors_settings["allowed_methods"] = empty($cors_settings["allowed_methods"]) ? 'OPTIONS' : $cors_settings["allowed_methods"] . ', ' . 'OPTIONS';
+
+ header('Access-Control-Allow-Origin: ' . $cors_settings['allowed_origins']);
+ header('Access-Control-Allow-Methods: '. $cors_settings['allowed_methods']);
+ header('Access-Control-Allow-Headers: Accept, Content-Type, X-Api-Key, Origin');
+
+ // Access-Control settings requested, this is just a preflight request
+ if ($_SERVER['REQUEST_METHOD'] == 'OPTIONS' &&
+ isset($_SERVER['HTTP_ACCESS_CONTROL_REQUEST_METHOD']) &&
+ isset($_SERVER['HTTP_ACCESS_CONTROL_REQUEST_HEADERS'])) {
+
+ $allowed_methods = explode(', ', $cors_settings["allowed_methods"]);
+ if (in_array($_SERVER['HTTP_ACCESS_CONTROL_REQUEST_METHOD'], $allowed_methods, true))
+ // method allowed send 200 OK
+ http_response_code(200);
+ else
+ // method not allowed send 405 METHOD NOT ALLOWED
+ http_response_code(405);
+
+ exit;
+ }
+ break;
+ }
+}
function get_logs($application, $lines = false) {
if ($lines === false) {
diff --git a/mailcow/src/mailcow-dockerized/data/web/inc/functions.mailbox.inc.php b/mailcow/src/mailcow-dockerized/data/web/inc/functions.mailbox.inc.php
index d67fa3e..68cb50f 100644
--- a/mailcow/src/mailcow-dockerized/data/web/inc/functions.mailbox.inc.php
+++ b/mailcow/src/mailcow-dockerized/data/web/inc/functions.mailbox.inc.php
@@ -1250,9 +1250,27 @@
));
}
else {
- $stmt = $pdo->prepare("INSERT INTO `user_acl` (`username`) VALUES (:username)");
+ $stmt = $pdo->prepare("INSERT INTO `user_acl`
+ (`username`, `spam_alias`, `tls_policy`, `spam_score`, `spam_policy`, `delimiter_action`, `syncjobs`, `eas_reset`, `sogo_profile_reset`,
+ `pushover`, `quarantine`, `quarantine_attachments`, `quarantine_notification`, `quarantine_category`, `app_passwds`)
+ VALUES (:username, :spam_alias, :tls_policy, :spam_score, :spam_policy, :delimiter_action, :syncjobs, :eas_reset, :sogo_profile_reset,
+ :pushover, :quarantine, :quarantine_attachments, :quarantine_notification, :quarantine_category, :app_passwds) ");
$stmt->execute(array(
- ':username' => $username
+ ':username' => $username,
+ ':spam_alias' => 0,
+ ':tls_policy' => 0,
+ ':spam_score' => 0,
+ ':spam_policy' => 0,
+ ':delimiter_action' => 0,
+ ':syncjobs' => 0,
+ ':eas_reset' => 0,
+ ':sogo_profile_reset' => 0,
+ ':pushover' => 0,
+ ':quarantine' => 0,
+ ':quarantine_attachments' => 0,
+ ':quarantine_notification' => 0,
+ ':quarantine_category' => 0,
+ ':app_passwds' => 0
));
}
@@ -1264,11 +1282,13 @@
));
}
+ update_sogo_static_view($username);
$_SESSION['return'][] = array(
'type' => 'success',
'log' => array(__FUNCTION__, $_action, $_type, $_data_log, $_attr),
'msg' => array('mailbox_added', htmlspecialchars($username))
);
+ return true;
break;
case 'resource':
$domain = idn_to_ascii(strtolower(trim($_data['domain'])), 0, INTL_IDNA_VARIANT_UTS46);
@@ -1531,20 +1551,20 @@
$attr['acl_app_passwds'] = (in_array('app_passwds', $_data['acl'])) ? 1 : 0;
} else {
$_data['acl'] = (array)$_data['acl'];
- $attr['acl_spam_alias'] = 1;
- $attr['acl_tls_policy'] = 1;
- $attr['acl_spam_score'] = 1;
- $attr['acl_spam_policy'] = 1;
- $attr['acl_delimiter_action'] = 1;
+ $attr['acl_spam_alias'] = 0;
+ $attr['acl_tls_policy'] = 0;
+ $attr['acl_spam_score'] = 0;
+ $attr['acl_spam_policy'] = 0;
+ $attr['acl_delimiter_action'] = 0;
$attr['acl_syncjobs'] = 0;
- $attr['acl_eas_reset'] = 1;
+ $attr['acl_eas_reset'] = 0;
$attr['acl_sogo_profile_reset'] = 0;
- $attr['acl_pushover'] = 1;
- $attr['acl_quarantine'] = 1;
- $attr['acl_quarantine_attachments'] = 1;
- $attr['acl_quarantine_notification'] = 1;
- $attr['acl_quarantine_category'] = 1;
- $attr['acl_app_passwds'] = 1;
+ $attr['acl_pushover'] = 0;
+ $attr['acl_quarantine'] = 0;
+ $attr['acl_quarantine_attachments'] = 0;
+ $attr['acl_quarantine_notification'] = 0;
+ $attr['acl_quarantine_category'] = 0;
+ $attr['acl_app_passwds'] = 0;
}
@@ -2879,67 +2899,68 @@
$_SESSION['return'][] = array(
'type' => 'danger',
'log' => array(__FUNCTION__, $_action, $_type, $_data_log, $_attr),
- 'msg' => 'access_denied'
+ 'msg' => 'extended_sender_acl_denied'
);
- return false;
}
- $extra_acls = array_map('trim', preg_split( "/( |,|;|\n)/", $_data['extended_sender_acl']));
- foreach ($extra_acls as $i => &$extra_acl) {
- if (empty($extra_acl)) {
- continue;
- }
- if (substr($extra_acl, 0, 1) === "@") {
- $extra_acl = ltrim($extra_acl, '@');
- }
- if (!filter_var($extra_acl, FILTER_VALIDATE_EMAIL) && !is_valid_domain_name($extra_acl)) {
- $_SESSION['return'][] = array(
- 'type' => 'danger',
- 'log' => array(__FUNCTION__, $_action, $_type, $_data_log, $_attr),
- 'msg' => array('extra_acl_invalid', htmlspecialchars($extra_acl))
- );
- unset($extra_acls[$i]);
- continue;
- }
- $domains = array_merge(mailbox('get', 'domains'), mailbox('get', 'alias_domains'));
- if (filter_var($extra_acl, FILTER_VALIDATE_EMAIL)) {
- $extra_acl_domain = idn_to_ascii(substr(strstr($extra_acl, '@'), 1), 0, INTL_IDNA_VARIANT_UTS46);
- if (in_array($extra_acl_domain, $domains)) {
+ else {
+ $extra_acls = array_map('trim', preg_split( "/( |,|;|\n)/", $_data['extended_sender_acl']));
+ foreach ($extra_acls as $i => &$extra_acl) {
+ if (empty($extra_acl)) {
+ continue;
+ }
+ if (substr($extra_acl, 0, 1) === "@") {
+ $extra_acl = ltrim($extra_acl, '@');
+ }
+ if (!filter_var($extra_acl, FILTER_VALIDATE_EMAIL) && !is_valid_domain_name($extra_acl)) {
$_SESSION['return'][] = array(
'type' => 'danger',
'log' => array(__FUNCTION__, $_action, $_type, $_data_log, $_attr),
- 'msg' => array('extra_acl_invalid_domain', $extra_acl_domain)
+ 'msg' => array('extra_acl_invalid', htmlspecialchars($extra_acl))
);
unset($extra_acls[$i]);
continue;
}
- }
- else {
- if (in_array($extra_acl, $domains)) {
- $_SESSION['return'][] = array(
- 'type' => 'danger',
- 'log' => array(__FUNCTION__, $_action, $_type, $_data_log, $_attr),
- 'msg' => array('extra_acl_invalid_domain', $extra_acl_domain)
- );
- unset($extra_acls[$i]);
- continue;
+ $domains = array_merge(mailbox('get', 'domains'), mailbox('get', 'alias_domains'));
+ if (filter_var($extra_acl, FILTER_VALIDATE_EMAIL)) {
+ $extra_acl_domain = idn_to_ascii(substr(strstr($extra_acl, '@'), 1), 0, INTL_IDNA_VARIANT_UTS46);
+ if (in_array($extra_acl_domain, $domains)) {
+ $_SESSION['return'][] = array(
+ 'type' => 'danger',
+ 'log' => array(__FUNCTION__, $_action, $_type, $_data_log, $_attr),
+ 'msg' => array('extra_acl_invalid_domain', $extra_acl_domain)
+ );
+ unset($extra_acls[$i]);
+ continue;
+ }
}
- $extra_acl = '@' . $extra_acl;
+ else {
+ if (in_array($extra_acl, $domains)) {
+ $_SESSION['return'][] = array(
+ 'type' => 'danger',
+ 'log' => array(__FUNCTION__, $_action, $_type, $_data_log, $_attr),
+ 'msg' => array('extra_acl_invalid_domain', $extra_acl_domain)
+ );
+ unset($extra_acls[$i]);
+ continue;
+ }
+ $extra_acl = '@' . $extra_acl;
+ }
}
- }
- $extra_acls = array_filter($extra_acls);
- $extra_acls = array_values($extra_acls);
- $extra_acls = array_unique($extra_acls);
- $stmt = $pdo->prepare("DELETE FROM `sender_acl` WHERE `external` = 1 AND `logged_in_as` = :username");
- $stmt->execute(array(
- ':username' => $username
- ));
- foreach ($extra_acls as $sender_acl_external) {
- $stmt = $pdo->prepare("INSERT INTO `sender_acl` (`send_as`, `logged_in_as`, `external`)
- VALUES (:sender_acl, :username, 1)");
+ $extra_acls = array_filter($extra_acls);
+ $extra_acls = array_values($extra_acls);
+ $extra_acls = array_unique($extra_acls);
+ $stmt = $pdo->prepare("DELETE FROM `sender_acl` WHERE `external` = 1 AND `logged_in_as` = :username");
$stmt->execute(array(
- ':sender_acl' => $sender_acl_external,
':username' => $username
));
+ foreach ($extra_acls as $sender_acl_external) {
+ $stmt = $pdo->prepare("INSERT INTO `sender_acl` (`send_as`, `logged_in_as`, `external`)
+ VALUES (:sender_acl, :username, 1)");
+ $stmt->execute(array(
+ ':sender_acl' => $sender_acl_external,
+ ':username' => $username
+ ));
+ }
}
}
if (isset($_data['sender_acl'])) {
@@ -3129,7 +3150,10 @@
'log' => array(__FUNCTION__, $_action, $_type, $_data_log, $_attr),
'msg' => array('mailbox_modified', $username)
);
+
+ update_sogo_static_view($username);
}
+ return true;
break;
case 'mailbox_templates':
if ($_SESSION['mailcow_cc_role'] != "admin") {
@@ -3314,6 +3338,45 @@
);
}
break;
+ case 'domain_wide_footer':
+ $domain = idn_to_ascii(strtolower(trim($_data['domain'])), 0, INTL_IDNA_VARIANT_UTS46);
+ if (!is_valid_domain_name($domain)) {
+ $_SESSION['return'][] = array(
+ 'type' => 'danger',
+ 'log' => array(__FUNCTION__, $_action, $_type, $_data_log, $_attr),
+ 'msg' => 'domain_invalid'
+ );
+ return false;
+ }
+ if (!hasDomainAccess($_SESSION['mailcow_cc_username'], $_SESSION['mailcow_cc_role'], $domain)) {
+ $_SESSION['return'][] = array(
+ 'type' => 'danger',
+ 'log' => array(__FUNCTION__, $_action, $_type, $_data_log, $_attr),
+ 'msg' => 'access_denied'
+ );
+ return false;
+ }
+
+ $footers = array();
+ $footers['html'] = isset($_data['footer_html']) ? $_data['footer_html'] : '';
+ $footers['plain'] = isset($_data['footer_plain']) ? $_data['footer_plain'] : '';
+ try {
+ $redis->hSet('DOMAIN_WIDE_FOOTER', $domain, json_encode($footers));
+ }
+ catch (RedisException $e) {
+ $_SESSION['return'][] = array(
+ 'type' => 'danger',
+ 'log' => array(__FUNCTION__, $_action, $_type, $_data_log, $_attr),
+ 'msg' => array('redis_error', $e)
+ );
+ return false;
+ }
+ $_SESSION['return'][] = array(
+ 'type' => 'success',
+ 'log' => array(__FUNCTION__, $_action, $_type, $_data_log, $_attr),
+ 'msg' => array('domain_footer_modified', htmlspecialchars($domain))
+ );
+ break;
}
break;
case 'get':
@@ -3959,6 +4022,39 @@
}
return $aliasdomaindata;
break;
+ case 'shared_aliases':
+ $shared_aliases = array();
+ $stmt = $pdo->query("SELECT `address` FROM `alias`
+ WHERE `goto` REGEXP ','
+ AND `address` NOT LIKE '@%'
+ AND `goto` != `address`");
+ $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
+ while($row = array_shift($rows)) {
+ $domain = explode("@", $row['address'])[1];
+ if (hasDomainAccess($_SESSION['mailcow_cc_username'], $_SESSION['mailcow_cc_role'], $domain)) {
+ $shared_aliases[] = $row['address'];
+ }
+ }
+
+ return $shared_aliases;
+ break;
+ case 'direct_aliases':
+ $direct_aliases = array();
+ $stmt = $pdo->query("SELECT `address` FROM `alias`
+ WHERE `goto` NOT LIKE '%,%'
+ AND `address` NOT LIKE '@%'
+ AND `goto` != `address`");
+ $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
+
+ while($row = array_shift($rows)) {
+ $domain = explode("@", $row['address'])[1];
+ if (hasDomainAccess($_SESSION['mailcow_cc_username'], $_SESSION['mailcow_cc_role'], $domain)) {
+ $direct_aliases[] = $row['address'];
+ }
+ }
+
+ return $direct_aliases;
+ break;
case 'domains':
$domains = array();
if ($_SESSION['mailcow_cc_role'] != "admin" && $_SESSION['mailcow_cc_role'] != "domainadmin") {
@@ -4393,6 +4489,40 @@
}
return $resourcedata;
break;
+ case 'domain_wide_footer':
+ $domain = idn_to_ascii(strtolower(trim($_data)), 0, INTL_IDNA_VARIANT_UTS46);
+ if (!is_valid_domain_name($domain)) {
+ $_SESSION['return'][] = array(
+ 'type' => 'danger',
+ 'log' => array(__FUNCTION__, $_action, $_type, $_data_log, $_attr),
+ 'msg' => 'domain_invalid'
+ );
+ return false;
+ }
+ if (!hasDomainAccess($_SESSION['mailcow_cc_username'], $_SESSION['mailcow_cc_role'], $_data)) {
+ $_SESSION['return'][] = array(
+ 'type' => 'danger',
+ 'log' => array(__FUNCTION__, $_action, $_type, $_data_log, $_attr),
+ 'msg' => 'access_denied'
+ );
+ return false;
+ }
+
+ try {
+ $footers = $redis->hGet('DOMAIN_WIDE_FOOTER', $domain);
+ $footers = json_decode($footers, true);
+ }
+ catch (RedisException $e) {
+ $_SESSION['return'][] = array(
+ 'type' => 'danger',
+ 'log' => array(__FUNCTION__, $_action, $_type, $_data_log, $_attr),
+ 'msg' => array('redis_error', $e)
+ );
+ return false;
+ }
+
+ return $footers;
+ break;
}
break;
case 'delete':
@@ -4891,13 +5021,19 @@
if (!empty($mailbox_details['domain']) && !empty($mailbox_details['local_part'])) {
$maildir = $mailbox_details['domain'] . '/' . $mailbox_details['local_part'];
$exec_fields = array('cmd' => 'maildir', 'task' => 'cleanup', 'maildir' => $maildir);
- $maildir_gc = json_decode(docker('post', 'dovecot-mailcow', 'exec', $exec_fields), true);
- if ($maildir_gc['type'] != 'success') {
- $_SESSION['return'][] = array(
- 'type' => 'warning',
- 'log' => array(__FUNCTION__, $_action, $_type, $_data_log, $_attr),
- 'msg' => 'Could not move maildir to garbage collector: ' . $maildir_gc['msg']
- );
+
+ if (getenv("CLUSTERMODE") == "replication") {
+ // broadcast to each dovecot container
+ docker('broadcast', 'dovecot-mailcow', 'exec', $exec_fields);
+ } else {
+ $maildir_gc = json_decode(docker('post', 'dovecot-mailcow', 'exec', $exec_fields), true);
+ if ($maildir_gc['type'] != 'success') {
+ $_SESSION['return'][] = array(
+ 'type' => 'warning',
+ 'log' => array(__FUNCTION__, $_action, $_type, $_data_log, $_attr),
+ 'msg' => 'Could not move maildir to garbage collector: ' . $maildir_gc['msg']
+ );
+ }
}
}
else {
@@ -4950,9 +5086,10 @@
$stmt->execute(array(
':username' => $username
));
- $stmt = $pdo->prepare("DELETE FROM `sender_acl` WHERE `logged_in_as` = :username");
+ $stmt = $pdo->prepare("DELETE FROM `sender_acl` WHERE `logged_in_as` = :logged_in_as OR `send_as` = :send_as");
$stmt->execute(array(
- ':username' => $username
+ ':logged_in_as' => $username,
+ ':send_as' => $username
));
// fk, better safe than sorry
$stmt = $pdo->prepare("DELETE FROM `user_acl` WHERE `username` = :username");
@@ -5052,12 +5189,15 @@
);
continue;
}
+
+ update_sogo_static_view($username);
$_SESSION['return'][] = array(
'type' => 'success',
'log' => array(__FUNCTION__, $_action, $_type, $_data_log, $_attr),
'msg' => array('mailbox_removed', htmlspecialchars($username))
);
}
+ return true;
break;
case 'mailbox_templates':
if ($_SESSION['mailcow_cc_role'] != "admin") {
@@ -5170,15 +5310,6 @@
$tags = $_data['tags'];
if (!is_array($tags)) $tags = array();
-
- if ($_SESSION['mailcow_cc_role'] != "admin") {
- $_SESSION['return'][] = array(
- 'type' => 'danger',
- 'log' => array(__FUNCTION__, $_action, $_type, $_data_log, $_attr),
- 'msg' => 'access_denied'
- );
- return false;
- }
$wasModified = false;
foreach ($domains as $domain) {
@@ -5190,7 +5321,15 @@
);
continue;
}
-
+ if (!hasDomainAccess($_SESSION['mailcow_cc_username'], $_SESSION['mailcow_cc_role'], $domain)) {
+ $_SESSION['return'][] = array(
+ 'type' => 'danger',
+ 'log' => array(__FUNCTION__, $_action, $_type, $_data_log, $_attr),
+ 'msg' => 'access_denied'
+ );
+ return false;
+ }
+
foreach($tags as $tag){
// delete tag
$wasModified = true;
@@ -5264,7 +5403,7 @@
}
break;
}
- if ($_action != 'get' && in_array($_type, array('domain', 'alias', 'alias_domain', 'mailbox', 'resource'))) {
+ if ($_action != 'get' && in_array($_type, array('domain', 'alias', 'alias_domain', 'resource')) && getenv('SKIP_SOGO') != "y") {
update_sogo_static_view();
}
}
diff --git a/mailcow/src/mailcow-dockerized/data/web/inc/header.inc.php b/mailcow/src/mailcow-dockerized/data/web/inc/header.inc.php
index f62819a..9afc288 100644
--- a/mailcow/src/mailcow-dockerized/data/web/inc/header.inc.php
+++ b/mailcow/src/mailcow-dockerized/data/web/inc/header.inc.php
@@ -40,6 +40,7 @@
'ui_texts' => $UI_TEXTS,
'css_path' => '/cache/'.basename($CSSPath),
'logo' => customize('get', 'main_logo'),
+ 'logo_dark' => customize('get', 'main_logo_dark'),
'available_languages' => $AVAILABLE_LANGUAGES,
'lang' => $lang,
'skip_sogo' => (getenv('SKIP_SOGO') == 'y'),
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 e781f94..e286ab5 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
@@ -1,1465 +1,1478 @@
-<?php
-function init_db_schema() {
- try {
- global $pdo;
-
- $db_version = "23122022_1445";
-
- $stmt = $pdo->query("SHOW TABLES LIKE 'versions'");
- $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
- if ($num_results != 0) {
- $stmt = $pdo->query("SELECT `version` FROM `versions` WHERE `application` = 'db_schema'");
- if ($stmt->fetch(PDO::FETCH_ASSOC)['version'] == $db_version) {
- return true;
- }
- if (!preg_match('/y|yes/i', getenv('MASTER'))) {
- $_SESSION['return'][] = array(
- 'type' => 'warning',
- 'log' => array(__FUNCTION__),
- 'msg' => 'Database not initialized: not running db_init on slave.'
- );
- return true;
- }
- }
-
- $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';"
- );
-
- $tables = array(
- "versions" => array(
- "cols" => array(
- "application" => "VARCHAR(255) NOT NULL",
- "version" => "VARCHAR(100) NOT NULL",
- "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
- ),
- "keys" => array(
- "primary" => array(
- "" => array("application")
- )
- ),
- "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
- ),
- "admin" => array(
- "cols" => array(
- "username" => "VARCHAR(255) NOT NULL",
- "password" => "VARCHAR(255) NOT NULL",
- "superadmin" => "TINYINT(1) NOT NULL DEFAULT '0'",
- "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
- "modified" => "DATETIME ON UPDATE NOW(0)",
- "active" => "TINYINT(1) NOT NULL DEFAULT '1'"
- ),
- "keys" => array(
- "primary" => array(
- "" => array("username")
- )
- ),
- "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
- ),
- "fido2" => array(
- "cols" => array(
- "username" => "VARCHAR(255) NOT NULL",
- "friendlyName" => "VARCHAR(255)",
- "rpId" => "VARCHAR(255) NOT NULL",
- "credentialPublicKey" => "TEXT NOT NULL",
- "certificateChain" => "TEXT",
- // Can be null for format "none"
- "certificate" => "TEXT",
- "certificateIssuer" => "VARCHAR(255)",
- "certificateSubject" => "VARCHAR(255)",
- "signatureCounter" => "INT",
- "AAGUID" => "BLOB",
- "credentialId" => "BLOB NOT NULL",
- "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
- "modified" => "DATETIME ON UPDATE NOW(0)",
- "active" => "TINYINT(1) NOT NULL DEFAULT '1'"
- ),
- "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
- ),
- "_sogo_static_view" => array(
- "cols" => array(
- "c_uid" => "VARCHAR(255) NOT NULL",
- "domain" => "VARCHAR(255) NOT NULL",
- "c_name" => "VARCHAR(255) NOT NULL",
- "c_password" => "VARCHAR(255) NOT NULL DEFAULT ''",
- "c_cn" => "VARCHAR(255)",
- "mail" => "VARCHAR(255) NOT NULL",
- // TODO -> use TEXT and check if SOGo login breaks on empty aliases
- "aliases" => "TEXT NOT NULL",
- "ad_aliases" => "VARCHAR(6144) NOT NULL DEFAULT ''",
- "ext_acl" => "VARCHAR(6144) NOT NULL DEFAULT ''",
- "kind" => "VARCHAR(100) NOT NULL DEFAULT ''",
- "multiple_bookings" => "INT NOT NULL DEFAULT -1"
- ),
- "keys" => array(
- "primary" => array(
- "" => array("c_uid")
- ),
- "key" => array(
- "domain" => array("domain")
- )
- ),
- "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
- ),
- "relayhosts" => array(
- "cols" => array(
- "id" => "INT NOT NULL AUTO_INCREMENT",
- "hostname" => "VARCHAR(255) NOT NULL",
- "username" => "VARCHAR(255) NOT NULL",
- "password" => "VARCHAR(255) NOT NULL",
- "active" => "TINYINT(1) NOT NULL DEFAULT '1'"
- ),
- "keys" => array(
- "primary" => array(
- "" => array("id")
- ),
- "key" => array(
- "hostname" => array("hostname")
- )
- ),
- "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
- ),
- "transports" => array(
- "cols" => array(
- "id" => "INT NOT NULL AUTO_INCREMENT",
- "destination" => "VARCHAR(255) NOT NULL",
- "nexthop" => "VARCHAR(255) NOT NULL",
- "username" => "VARCHAR(255) NOT NULL DEFAULT ''",
- "password" => "VARCHAR(255) NOT NULL DEFAULT ''",
- "is_mx_based" => "TINYINT(1) NOT NULL DEFAULT '0'",
- "active" => "TINYINT(1) NOT NULL DEFAULT '1'"
- ),
- "keys" => array(
- "primary" => array(
- "" => array("id")
- ),
- "key" => array(
- "destination" => array("destination"),
- "nexthop" => array("nexthop"),
- )
- ),
- "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
- ),
- "alias" => array(
- "cols" => array(
- "id" => "INT NOT NULL AUTO_INCREMENT",
- "address" => "VARCHAR(255) NOT NULL",
- "goto" => "TEXT NOT NULL",
- "domain" => "VARCHAR(255) NOT NULL",
- "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
- "modified" => "DATETIME ON UPDATE CURRENT_TIMESTAMP",
- "private_comment" => "TEXT",
- "public_comment" => "TEXT",
- "sogo_visible" => "TINYINT(1) NOT NULL DEFAULT '1'",
- "active" => "TINYINT(1) NOT NULL DEFAULT '1'"
- ),
- "keys" => array(
- "primary" => array(
- "" => array("id")
- ),
- "unique" => array(
- "address" => array("address")
- ),
- "key" => array(
- "domain" => array("domain")
- )
- ),
- "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
- ),
- "api" => array(
- "cols" => array(
- "api_key" => "VARCHAR(255) NOT NULL",
- "allow_from" => "VARCHAR(512) NOT NULL",
- "skip_ip_check" => "TINYINT(1) NOT NULL DEFAULT '0'",
- "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
- "modified" => "DATETIME ON UPDATE NOW(0)",
- "access" => "ENUM('ro', 'rw') NOT NULL DEFAULT 'rw'",
- "active" => "TINYINT(1) NOT NULL DEFAULT '1'"
- ),
- "keys" => array(
- "primary" => array(
- "" => array("api_key")
- ),
- ),
- "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
- ),
- "sender_acl" => array(
- "cols" => array(
- "id" => "INT NOT NULL AUTO_INCREMENT",
- "logged_in_as" => "VARCHAR(255) NOT NULL",
- "send_as" => "VARCHAR(255) NOT NULL",
- "external" => "TINYINT(1) NOT NULL DEFAULT '0'"
- ),
- "keys" => array(
- "primary" => array(
- "" => array("id")
- )
- ),
- "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(
- "domain" => "VARCHAR(255) NOT NULL",
- "description" => "VARCHAR(255)",
- "aliases" => "INT(10) NOT NULL DEFAULT '0'",
- "mailboxes" => "INT(10) NOT NULL DEFAULT '0'",
- "defquota" => "BIGINT(20) NOT NULL DEFAULT '3072'",
- "maxquota" => "BIGINT(20) NOT NULL DEFAULT '102400'",
- "quota" => "BIGINT(20) NOT NULL DEFAULT '102400'",
- "relayhost" => "VARCHAR(255) NOT NULL DEFAULT '0'",
- "backupmx" => "TINYINT(1) NOT NULL DEFAULT '0'",
- "gal" => "TINYINT(1) NOT NULL DEFAULT '1'",
- "relay_all_recipients" => "TINYINT(1) NOT NULL DEFAULT '0'",
- "relay_unknown_only" => "TINYINT(1) NOT NULL DEFAULT '0'",
- "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
- "modified" => "DATETIME ON UPDATE CURRENT_TIMESTAMP",
- "active" => "TINYINT(1) NOT NULL DEFAULT '1'"
- ),
- "keys" => array(
- "primary" => array(
- "" => array("domain")
- )
- ),
- "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",
- "dest" => "VARCHAR(255) NOT NULL",
- "policy" => "ENUM('none', 'may', 'encrypt', 'dane', 'dane-only', 'fingerprint', 'verify', 'secure') NOT NULL",
- "parameters" => "VARCHAR(255) DEFAULT ''",
- "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
- "modified" => "DATETIME ON UPDATE CURRENT_TIMESTAMP",
- "active" => "TINYINT(1) NOT NULL DEFAULT '1'"
- ),
- "keys" => array(
- "primary" => array(
- "" => array("id")
- ),
- "unique" => array(
- "dest" => array("dest")
- ),
- ),
- "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
- ),
- "quarantine" => array(
- "cols" => array(
- "id" => "INT NOT NULL AUTO_INCREMENT",
- "qid" => "VARCHAR(30) NOT NULL",
- "subject" => "VARCHAR(500)",
- "score" => "FLOAT(8,2)",
- "ip" => "VARCHAR(50)",
- "action" => "CHAR(20) NOT NULL DEFAULT 'unknown'",
- "symbols" => "JSON",
- "fuzzy_hashes" => "JSON",
- "sender" => "VARCHAR(255) NOT NULL DEFAULT 'unknown'",
- "rcpt" => "VARCHAR(255)",
- "msg" => "LONGTEXT",
- "domain" => "VARCHAR(255)",
- "notified" => "TINYINT(1) NOT NULL DEFAULT '0'",
- "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
- "user" => "VARCHAR(255) NOT NULL DEFAULT 'unknown'",
- ),
- "keys" => array(
- "primary" => array(
- "" => array("id")
- )
- ),
- "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
- ),
- "mailbox" => array(
- "cols" => array(
- "username" => "VARCHAR(255) NOT NULL",
- "password" => "VARCHAR(255) NOT NULL",
- "name" => "VARCHAR(255)",
- "description" => "VARCHAR(255)",
- // mailbox_path_prefix is followed by domain/local_part/
- "mailbox_path_prefix" => "VARCHAR(150) DEFAULT '/var/vmail/'",
- "quota" => "BIGINT(20) NOT NULL DEFAULT '102400'",
- "local_part" => "VARCHAR(255) NOT NULL",
- "domain" => "VARCHAR(255) NOT NULL",
- "attributes" => "JSON",
- "kind" => "VARCHAR(100) NOT NULL DEFAULT ''",
- "multiple_bookings" => "INT NOT NULL DEFAULT -1",
- "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
- "modified" => "DATETIME ON UPDATE CURRENT_TIMESTAMP",
- "active" => "TINYINT(1) NOT NULL DEFAULT '1'"
- ),
- "keys" => array(
- "primary" => array(
- "" => array("username")
- ),
- "key" => array(
- "domain" => array("domain"),
- "kind" => array("kind")
- )
- ),
- "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",
- "username" => "VARCHAR(255) NOT NULL",
- "script_desc" => "VARCHAR(255) NOT NULL",
- "script_name" => "ENUM('active','inactive')",
- "script_data" => "TEXT NOT NULL",
- "filter_type" => "ENUM('postfilter','prefilter')",
- "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
- "modified" => "DATETIME ON UPDATE CURRENT_TIMESTAMP"
- ),
- "keys" => array(
- "primary" => array(
- "" => array("id")
- ),
- "key" => array(
- "username" => array("username"),
- "script_desc" => array("script_desc")
- ),
- "fkey" => array(
- "fk_username_sieve_global_before" => array(
- "col" => "username",
- "ref" => "mailbox.username",
- "delete" => "CASCADE",
- "update" => "NO ACTION"
- )
- )
- ),
- "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
- ),
- "app_passwd" => array(
- "cols" => array(
- "id" => "INT NOT NULL AUTO_INCREMENT",
- "name" => "VARCHAR(255) NOT NULL",
- "mailbox" => "VARCHAR(255) NOT NULL",
- "domain" => "VARCHAR(255) NOT NULL",
- "password" => "VARCHAR(255) NOT NULL",
- "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
- "modified" => "DATETIME ON UPDATE CURRENT_TIMESTAMP",
- "imap_access" => "TINYINT(1) NOT NULL DEFAULT '1'",
- "smtp_access" => "TINYINT(1) NOT NULL DEFAULT '1'",
- "dav_access" => "TINYINT(1) NOT NULL DEFAULT '1'",
- "eas_access" => "TINYINT(1) NOT NULL DEFAULT '1'",
- "pop3_access" => "TINYINT(1) NOT NULL DEFAULT '1'",
- "sieve_access" => "TINYINT(1) NOT NULL DEFAULT '1'",
- "active" => "TINYINT(1) NOT NULL DEFAULT '1'"
- ),
- "keys" => array(
- "primary" => array(
- "" => array("id")
- ),
- "key" => array(
- "mailbox" => array("mailbox"),
- "password" => array("password"),
- "domain" => array("domain"),
- ),
- "fkey" => array(
- "fk_username_app_passwd" => array(
- "col" => "mailbox",
- "ref" => "mailbox.username",
- "delete" => "CASCADE",
- "update" => "NO ACTION"
- )
- )
- ),
- "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
- ),
- "user_acl" => array(
- "cols" => array(
- "username" => "VARCHAR(255) NOT NULL",
- "spam_alias" => "TINYINT(1) NOT NULL DEFAULT '1'",
- "tls_policy" => "TINYINT(1) NOT NULL DEFAULT '1'",
- "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 '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'",
- // quarantine is for quarantine actions, todo: rename
- "quarantine" => "TINYINT(1) NOT NULL DEFAULT '1'",
- "quarantine_attachments" => "TINYINT(1) NOT NULL DEFAULT '1'",
- "quarantine_notification" => "TINYINT(1) NOT NULL DEFAULT '1'",
- "quarantine_category" => "TINYINT(1) NOT NULL DEFAULT '1'",
- "app_passwds" => "TINYINT(1) NOT NULL DEFAULT '1'",
- ),
- "keys" => array(
- "primary" => array(
- "" => array("username")
- ),
- "fkey" => array(
- "fk_username" => array(
- "col" => "username",
- "ref" => "mailbox.username",
- "delete" => "CASCADE",
- "update" => "NO ACTION"
- )
- )
- ),
- "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
- ),
- "alias_domain" => array(
- "cols" => array(
- "alias_domain" => "VARCHAR(255) NOT NULL",
- "target_domain" => "VARCHAR(255) NOT NULL",
- "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
- "modified" => "DATETIME ON UPDATE CURRENT_TIMESTAMP",
- "active" => "TINYINT(1) NOT NULL DEFAULT '1'"
- ),
- "keys" => array(
- "primary" => array(
- "" => array("alias_domain")
- ),
- "key" => array(
- "active" => array("active"),
- "target_domain" => array("target_domain")
- )
- ),
- "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
- ),
- "spamalias" => array(
- "cols" => array(
- "address" => "VARCHAR(255) NOT NULL",
- "goto" => "TEXT NOT NULL",
- "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
- "modified" => "DATETIME ON UPDATE CURRENT_TIMESTAMP",
- "validity" => "INT(11)"
- ),
- "keys" => array(
- "primary" => array(
- "" => array("address")
- ),
- ),
- "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
- ),
- "filterconf" => array(
- "cols" => array(
- "object" => "VARCHAR(255) NOT NULL DEFAULT ''",
- "option" => "VARCHAR(50) NOT NULL DEFAULT ''",
- "value" => "VARCHAR(100) NOT NULL DEFAULT ''",
- "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
- "modified" => "DATETIME ON UPDATE CURRENT_TIMESTAMP",
- "prefid" => "INT(11) NOT NULL AUTO_INCREMENT"
- ),
- "keys" => array(
- "primary" => array(
- "" => array("prefid")
- ),
- "key" => array(
- "object" => array("object")
- )
- ),
- "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
- ),
- "settingsmap" => array(
- "cols" => array(
- "id" => "INT NOT NULL AUTO_INCREMENT",
- "desc" => "VARCHAR(255) NOT NULL",
- "content" => "LONGTEXT NOT NULL",
- "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
- "modified" => "DATETIME ON UPDATE CURRENT_TIMESTAMP",
- "active" => "TINYINT(1) NOT NULL DEFAULT '0'"
- ),
- "keys" => array(
- "primary" => array(
- "" => array("id")
- )
- ),
- "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
- ),
- "logs" => array(
- "cols" => array(
- "id" => "INT NOT NULL AUTO_INCREMENT",
- "task" => "CHAR(32) NOT NULL DEFAULT '000000'",
- "type" => "VARCHAR(32) DEFAULT ''",
- "msg" => "TEXT",
- "call" => "TEXT",
- "user" => "VARCHAR(64) NOT NULL",
- "role" => "VARCHAR(32) NOT NULL",
- "remote" => "VARCHAR(39) NOT NULL",
- "time" => "INT(11) NOT NULL"
- ),
- "keys" => array(
- "primary" => array(
- "" => array("id")
- )
- ),
- "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
- ),
- "sasl_log" => array(
- "cols" => array(
- "service" => "VARCHAR(32) NOT NULL DEFAULT ''",
- "app_password" => "INT",
- "username" => "VARCHAR(255) NOT NULL",
- "real_rip" => "VARCHAR(64) NOT NULL",
- "datetime" => "DATETIME(0) NOT NULL DEFAULT NOW(0)"
- ),
- "keys" => array(
- "primary" => array(
- "" => array("service", "real_rip", "username")
- ),
- "key" => array(
- "username" => array("username"),
- "service" => array("service"),
- "datetime" => array("datetime"),
- "real_rip" => array("real_rip")
- )
- ),
- "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
- ),
- "quota2" => array(
- "cols" => array(
- "username" => "VARCHAR(255) NOT NULL",
- "bytes" => "BIGINT(20) NOT NULL DEFAULT '0'",
- "messages" => "BIGINT(20) NOT NULL DEFAULT '0'"
- ),
- "keys" => array(
- "primary" => array(
- "" => array("username")
- )
- ),
- "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
- ),
- "quota2replica" => array(
- "cols" => array(
- "username" => "VARCHAR(255) NOT NULL",
- "bytes" => "BIGINT(20) NOT NULL DEFAULT '0'",
- "messages" => "BIGINT(20) NOT NULL DEFAULT '0'"
- ),
- "keys" => array(
- "primary" => array(
- "" => array("username")
- )
- ),
- "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
- ),
- "domain_admins" => array(
- "cols" => array(
- "id" => "INT NOT NULL AUTO_INCREMENT",
- "username" => "VARCHAR(255) NOT NULL",
- "domain" => "VARCHAR(255) NOT NULL",
- "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
- "active" => "TINYINT(1) NOT NULL DEFAULT '1'"
- ),
- "keys" => array(
- "primary" => array(
- "" => array("id")
- ),
- "key" => array(
- "username" => array("username")
- )
- ),
- "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
- ),
- "da_acl" => array(
- "cols" => array(
- "username" => "VARCHAR(255) NOT NULL",
- "syncjobs" => "TINYINT(1) NOT NULL DEFAULT '1'",
- "quarantine" => "TINYINT(1) NOT NULL DEFAULT '1'",
- "login_as" => "TINYINT(1) NOT NULL DEFAULT '1'",
- "sogo_access" => "TINYINT(1) NOT NULL DEFAULT '1'",
- "app_passwds" => "TINYINT(1) NOT NULL DEFAULT '1'",
- "bcc_maps" => "TINYINT(1) NOT NULL DEFAULT '1'",
- "pushover" => "TINYINT(1) NOT NULL DEFAULT '0'",
- "filters" => "TINYINT(1) NOT NULL DEFAULT '1'",
- "ratelimit" => "TINYINT(1) NOT NULL DEFAULT '1'",
- "spam_policy" => "TINYINT(1) NOT NULL DEFAULT '1'",
- "extend_sender_acl" => "TINYINT(1) NOT NULL DEFAULT '0'",
- "unlimited_quota" => "TINYINT(1) NOT NULL DEFAULT '0'",
- "protocol_access" => "TINYINT(1) NOT NULL DEFAULT '1'",
- "smtp_ip_access" => "TINYINT(1) NOT NULL DEFAULT '1'",
- "alias_domains" => "TINYINT(1) NOT NULL DEFAULT '0'",
- "mailbox_relayhost" => "TINYINT(1) NOT NULL DEFAULT '1'",
- "domain_relayhost" => "TINYINT(1) NOT NULL DEFAULT '1'",
- "domain_desc" => "TINYINT(1) NOT NULL DEFAULT '0'"
- ),
- "keys" => array(
- "primary" => array(
- "" => array("username")
- )
- ),
- "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
- ),
- "imapsync" => array(
- "cols" => array(
- "id" => "INT NOT NULL AUTO_INCREMENT",
- "user2" => "VARCHAR(255) NOT NULL",
- "host1" => "VARCHAR(255) NOT NULL",
- "authmech1" => "ENUM('PLAIN','LOGIN','CRAM-MD5') DEFAULT 'PLAIN'",
- "regextrans2" => "VARCHAR(255) DEFAULT ''",
- "authmd51" => "TINYINT(1) NOT NULL DEFAULT 0",
- "domain2" => "VARCHAR(255) NOT NULL DEFAULT ''",
- "subfolder2" => "VARCHAR(255) NOT NULL DEFAULT ''",
- "user1" => "VARCHAR(255) NOT NULL",
- "password1" => "VARCHAR(255) NOT NULL",
- "exclude" => "VARCHAR(500) NOT NULL DEFAULT ''",
- "maxage" => "SMALLINT NOT NULL DEFAULT '0'",
- "mins_interval" => "SMALLINT UNSIGNED NOT NULL DEFAULT '0'",
- "maxbytespersecond" => "VARCHAR(50) NOT NULL DEFAULT '0'",
- "port1" => "SMALLINT UNSIGNED NOT NULL",
- "enc1" => "ENUM('TLS','SSL','PLAIN') DEFAULT 'TLS'",
- "delete2duplicates" => "TINYINT(1) NOT NULL DEFAULT '1'",
- "delete1" => "TINYINT(1) NOT NULL DEFAULT '0'",
- "delete2" => "TINYINT(1) NOT NULL DEFAULT '0'",
- "automap" => "TINYINT(1) NOT NULL DEFAULT '0'",
- "skipcrossduplicates" => "TINYINT(1) NOT NULL DEFAULT '0'",
- "custom_params" => "VARCHAR(512) NOT NULL DEFAULT ''",
- "timeout1" => "SMALLINT NOT NULL DEFAULT '600'",
- "timeout2" => "SMALLINT NOT NULL DEFAULT '600'",
- "subscribeall" => "TINYINT(1) NOT NULL DEFAULT '1'",
- "is_running" => "TINYINT(1) NOT NULL DEFAULT '0'",
- "returned_text" => "LONGTEXT",
- "last_run" => "TIMESTAMP NULL DEFAULT NULL",
- "success" => "TINYINT(1) UNSIGNED DEFAULT NULL",
- "exit_status" => "VARCHAR(50) DEFAULT NULL",
- "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
- "modified" => "DATETIME ON UPDATE CURRENT_TIMESTAMP",
- "active" => "TINYINT(1) NOT NULL DEFAULT '0'"
- ),
- "keys" => array(
- "primary" => array(
- "" => array("id")
- )
- ),
- "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
- ),
- "bcc_maps" => array(
- "cols" => array(
- "id" => "INT NOT NULL AUTO_INCREMENT",
- "local_dest" => "VARCHAR(255) NOT NULL",
- "bcc_dest" => "VARCHAR(255) NOT NULL",
- "domain" => "VARCHAR(255) NOT NULL",
- "type" => "ENUM('sender','rcpt')",
- "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
- "modified" => "DATETIME ON UPDATE CURRENT_TIMESTAMP",
- "active" => "TINYINT(1) NOT NULL DEFAULT '0'"
- ),
- "keys" => array(
- "primary" => array(
- "" => array("id")
- ),
- "key" => array(
- "local_dest" => array("local_dest"),
- )
- ),
- "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
- ),
- "recipient_maps" => array(
- "cols" => array(
- "id" => "INT NOT NULL AUTO_INCREMENT",
- "old_dest" => "VARCHAR(255) NOT NULL",
- "new_dest" => "VARCHAR(255) NOT NULL",
- "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
- "modified" => "DATETIME ON UPDATE CURRENT_TIMESTAMP",
- "active" => "TINYINT(1) NOT NULL DEFAULT '0'"
- ),
- "keys" => array(
- "primary" => array(
- "" => array("id")
- ),
- "key" => array(
- "local_dest" => array("old_dest"),
- )
- ),
- "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
- ),
- "tfa" => array(
- "cols" => array(
- "id" => "INT NOT NULL AUTO_INCREMENT",
- "key_id" => "VARCHAR(255) NOT NULL",
- "username" => "VARCHAR(255) NOT NULL",
- "authmech" => "ENUM('yubi_otp', 'u2f', 'hotp', 'totp', 'webauthn')",
- "secret" => "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'"
- ),
- "keys" => array(
- "primary" => array(
- "" => array("id")
- )
- ),
- "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
- ),
- "forwarding_hosts" => array(
- "cols" => array(
- "host" => "VARCHAR(255) NOT NULL",
- "source" => "VARCHAR(255) NOT NULL",
- "filter_spam" => "TINYINT(1) NOT NULL DEFAULT '0'"
- ),
- "keys" => array(
- "primary" => array(
- "" => array("host")
- ),
- ),
- "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
- ),
- "sogo_acl" => array(
- "cols" => array(
- "id" => "INT NOT NULL AUTO_INCREMENT",
- "c_folder_id" => "INT NOT NULL",
- "c_object" => "VARCHAR(255) NOT NULL",
- "c_uid" => "VARCHAR(255) NOT NULL",
- "c_role" => "VARCHAR(80) NOT NULL"
- ),
- "keys" => array(
- "primary" => array(
- "" => array("id")
- ),
- "key" => array(
- "sogo_acl_c_folder_id_idx" => array("c_folder_id"),
- "sogo_acl_c_uid_idx" => array("c_uid")
- )
- ),
- "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
- ),
- "sogo_alarms_folder" => array(
- "cols" => array(
- "id" => "INT NOT NULL AUTO_INCREMENT",
- "c_path" => "VARCHAR(255) NOT NULL",
- "c_name" => "VARCHAR(255) NOT NULL",
- "c_uid" => "VARCHAR(255) NOT NULL",
- "c_recurrence_id" => "INT(11) DEFAULT NULL",
- "c_alarm_number" => "INT(11) NOT NULL",
- "c_alarm_date" => "INT(11) NOT NULL"
- ),
- "keys" => array(
- "primary" => array(
- "" => array("id")
- )
- ),
- "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
- ),
- "sogo_cache_folder" => array(
- "cols" => array(
- "c_uid" => "VARCHAR(255) NOT NULL",
- "c_path" => "VARCHAR(255) NOT NULL",
- "c_parent_path" => "VARCHAR(255) DEFAULT NULL",
- "c_type" => "TINYINT(3) unsigned NOT NULL",
- "c_creationdate" => "INT(11) NOT NULL",
- "c_lastmodified" => "INT(11) NOT NULL",
- "c_version" => "INT(11) NOT NULL DEFAULT '0'",
- "c_deleted" => "TINYINT(4) NOT NULL DEFAULT '0'",
- "c_content" => "LONGTEXT"
- ),
- "keys" => array(
- "primary" => array(
- "" => array("c_uid", "c_path")
- ),
- ),
- "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
- ),
- "sogo_folder_info" => array(
- "cols" => array(
- "c_folder_id" => "BIGINT(20) unsigned NOT NULL AUTO_INCREMENT",
- "c_path" => "VARCHAR(255) NOT NULL",
- "c_path1" => "VARCHAR(255) NOT NULL",
- "c_path2" => "VARCHAR(255) DEFAULT NULL",
- "c_path3" => "VARCHAR(255) DEFAULT NULL",
- "c_path4" => "VARCHAR(255) DEFAULT NULL",
- "c_foldername" => "VARCHAR(255) NOT NULL",
- "c_location" => "VARCHAR(2048) DEFAULT NULL",
- "c_quick_location" => "VARCHAR(2048) DEFAULT NULL",
- "c_acl_location" => "VARCHAR(2048) DEFAULT NULL",
- "c_folder_type" => "VARCHAR(255) NOT NULL"
- ),
- "keys" => array(
- "primary" => array(
- "" => array("c_path")
- ),
- "unique" => array(
- "c_folder_id" => array("c_folder_id")
- )
- ),
- "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
- ),
- "sogo_quick_appointment" => array(
- "cols" => array(
- "c_folder_id" => "INT NOT NULL",
- "c_name" => "VARCHAR(255) NOT NULL",
- "c_uid" => "VARCHAR(1000) NOT NULL",
- "c_startdate" => "INT",
- "c_enddate" => "INT",
- "c_cycleenddate" => "INT",
- "c_title" => "VARCHAR(1000) NOT NULL",
- "c_participants" => "TEXT",
- "c_isallday" => "INT",
- "c_iscycle" => "INT",
- "c_cycleinfo" => "TEXT",
- "c_classification" => "INT NOT NULL",
- "c_isopaque" => "INT NOT NULL",
- "c_status" => "INT NOT NULL",
- "c_priority" => "INT",
- "c_location" => "VARCHAR(255)",
- "c_orgmail" => "VARCHAR(255)",
- "c_partmails" => "TEXT",
- "c_partstates" => "TEXT",
- "c_category" => "VARCHAR(255)",
- "c_sequence" => "INT",
- "c_component" => "VARCHAR(10) NOT NULL",
- "c_nextalarm" => "INT",
- "c_description" => "TEXT"
- ),
- "keys" => array(
- "primary" => array(
- "" => array("c_folder_id", "c_name")
- )
- ),
- "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
- ),
- "sogo_quick_contact" => array(
- "cols" => array(
- "c_folder_id" => "INT NOT NULL",
- "c_name" => "VARCHAR(255) NOT NULL",
- "c_givenname" => "VARCHAR(255)",
- "c_cn" => "VARCHAR(255)",
- "c_sn" => "VARCHAR(255)",
- "c_screenname" => "VARCHAR(255)",
- "c_l" => "VARCHAR(255)",
- "c_mail" => "TEXT",
- "c_o" => "VARCHAR(500)",
- "c_ou" => "VARCHAR(255)",
- "c_telephonenumber" => "VARCHAR(255)",
- "c_categories" => "VARCHAR(255)",
- "c_component" => "VARCHAR(10) NOT NULL",
- "c_hascertificate" => "INT4 DEFAULT 0"
- ),
- "keys" => array(
- "primary" => array(
- "" => array("c_folder_id", "c_name")
- )
- ),
- "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
- ),
- "sogo_sessions_folder" => array(
- "cols" => array(
- "c_id" => "VARCHAR(255) NOT NULL",
- "c_value" => "VARCHAR(4096) NOT NULL",
- "c_creationdate" => "INT(11) NOT NULL",
- "c_lastseen" => "INT(11) NOT NULL"
- ),
- "keys" => array(
- "primary" => array(
- "" => array("c_id")
- )
- ),
- "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
- ),
- "sogo_store" => array(
- "cols" => array(
- "c_folder_id" => "INT NOT NULL",
- "c_name" => "VARCHAR(255) NOT NULL",
- "c_content" => "MEDIUMTEXT NOT NULL",
- "c_creationdate" => "INT NOT NULL",
- "c_lastmodified" => "INT NOT NULL",
- "c_version" => "INT NOT NULL",
- "c_deleted" => "INT"
- ),
- "keys" => array(
- "primary" => array(
- "" => array("c_folder_id", "c_name")
- )
- ),
- "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
- ),
- "pushover" => array(
- "cols" => array(
- "username" => "VARCHAR(255) NOT NULL",
- "key" => "VARCHAR(255) NOT NULL",
- "token" => "VARCHAR(255) NOT NULL",
- "attributes" => "JSON",
- "title" => "TEXT",
- "text" => "TEXT",
- "senders" => "TEXT",
- "senders_regex" => "TEXT",
- "active" => "TINYINT(1) NOT NULL DEFAULT '1'"
- ),
- "keys" => array(
- "primary" => array(
- "" => array("username")
- )
- ),
- "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
- ),
- "sogo_user_profile" => array(
- "cols" => array(
- "c_uid" => "VARCHAR(255) NOT NULL",
- "c_defaults" => "LONGTEXT",
- "c_settings" => "LONGTEXT"
- ),
- "keys" => array(
- "primary" => array(
- "" => array("c_uid")
- )
- ),
- "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
- ),
- "oauth_clients" => array(
- "cols" => array(
- "id" => "INT NOT NULL AUTO_INCREMENT",
- "client_id" => "VARCHAR(80) NOT NULL",
- "client_secret" => "VARCHAR(80)",
- "redirect_uri" => "VARCHAR(2000)",
- "grant_types" => "VARCHAR(80)",
- "scope" => "VARCHAR(4000)",
- "user_id" => "VARCHAR(80)"
- ),
- "keys" => array(
- "primary" => array(
- "" => array("client_id")
- ),
- "unique" => array(
- "id" => array("id")
- )
- ),
- "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
- ),
- "oauth_access_tokens" => array(
- "cols" => array(
- "access_token" => "VARCHAR(40) NOT NULL",
- "client_id" => "VARCHAR(80) NOT NULL",
- "user_id" => "VARCHAR(80)",
- "expires" => "TIMESTAMP NOT NULL",
- "scope" => "VARCHAR(4000)"
- ),
- "keys" => array(
- "primary" => array(
- "" => array("access_token")
- )
- ),
- "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
- ),
- "oauth_authorization_codes" => array(
- "cols" => array(
- "authorization_code" => "VARCHAR(40) NOT NULL",
- "client_id" => "VARCHAR(80) NOT NULL",
- "user_id" => "VARCHAR(80)",
- "redirect_uri" => "VARCHAR(2000)",
- "expires" => "TIMESTAMP NOT NULL",
- "scope" => "VARCHAR(4000)",
- "id_token" => "VARCHAR(1000)"
- ),
- "keys" => array(
- "primary" => array(
- "" => array("authorization_code")
- )
- ),
- "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
- ),
- "oauth_refresh_tokens" => array(
- "cols" => array(
- "refresh_token" => "VARCHAR(40) NOT NULL",
- "client_id" => "VARCHAR(80) NOT NULL",
- "user_id" => "VARCHAR(80)",
- "expires" => "TIMESTAMP NOT NULL",
- "scope" => "VARCHAR(4000)"
- ),
- "keys" => array(
- "primary" => array(
- "" => array("refresh_token")
- )
- ),
- "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
- )
- );
-
- foreach ($tables as $table => $properties) {
- // Migrate to quarantine
- if ($table == 'quarantine') {
- $stmt = $pdo->query("SHOW TABLES LIKE 'quarantaine'");
- $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
- if ($num_results != 0) {
- $stmt = $pdo->query("SHOW TABLES LIKE 'quarantine'");
- $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
- if ($num_results == 0) {
- $pdo->query("RENAME TABLE `quarantaine` TO `quarantine`");
- }
- }
- }
-
- // Migrate tls_enforce_* options
- if ($table == 'mailbox') {
- $stmt = $pdo->query("SHOW TABLES LIKE 'mailbox'");
- $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
- if ($num_results != 0) {
- $stmt = $pdo->query("SHOW COLUMNS FROM `mailbox` LIKE '%tls_enforce%'");
- $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
- if ($num_results != 0) {
- $stmt = $pdo->query("SELECT `username`, `tls_enforce_in`, `tls_enforce_out` FROM `mailbox`");
- $tls_options_rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
- while ($row = array_shift($tls_options_rows)) {
- $tls_options[$row['username']] = array('tls_enforce_in' => $row['tls_enforce_in'], 'tls_enforce_out' => $row['tls_enforce_out']);
- }
- }
- }
- }
-
- $stmt = $pdo->query("SHOW TABLES LIKE '" . $table . "'");
- $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
- if ($num_results != 0) {
- $stmt = $pdo->prepare("SELECT CONCAT('ALTER TABLE ', `table_schema`, '.', `table_name`, ' DROP FOREIGN KEY ', `constraint_name`, ';') AS `FKEY_DROP` FROM `information_schema`.`table_constraints`
- WHERE `constraint_type` = 'FOREIGN KEY' AND `table_name` = :table;");
- $stmt->execute(array(':table' => $table));
- $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
- while ($row = array_shift($rows)) {
- $pdo->query($row['FKEY_DROP']);
- }
- foreach($properties['cols'] as $column => $type) {
- $stmt = $pdo->query("SHOW COLUMNS FROM `" . $table . "` LIKE '" . $column . "'");
- $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
- if ($num_results == 0) {
- if (strpos($type, 'AUTO_INCREMENT') !== false) {
- $type = $type . ' PRIMARY KEY ';
- // Adding an AUTO_INCREMENT key, need to drop primary keys first, if exists
- $stmt = $pdo->query("SHOW KEYS FROM `" . $table . "` WHERE Key_name = 'PRIMARY'");
- $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
- if ($num_results != 0) {
- $pdo->query("ALTER TABLE `" . $table . "` DROP PRIMARY KEY");
- }
- }
- $pdo->query("ALTER TABLE `" . $table . "` ADD `" . $column . "` " . $type);
- }
- else {
- $pdo->query("ALTER TABLE `" . $table . "` MODIFY COLUMN `" . $column . "` " . $type);
- }
- }
- foreach($properties['keys'] as $key_type => $key_content) {
- if (strtolower($key_type) == 'primary') {
- foreach ($key_content as $key_values) {
- $fields = "`" . implode("`, `", $key_values) . "`";
- $stmt = $pdo->query("SHOW KEYS FROM `" . $table . "` WHERE Key_name = 'PRIMARY'");
- $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
- $is_drop = ($num_results != 0) ? "DROP PRIMARY KEY, " : "";
- $pdo->query("ALTER TABLE `" . $table . "` " . $is_drop . "ADD PRIMARY KEY (" . $fields . ")");
- }
- }
- if (strtolower($key_type) == 'key') {
- foreach ($key_content as $key_name => $key_values) {
- $fields = "`" . implode("`, `", $key_values) . "`";
- $stmt = $pdo->query("SHOW KEYS FROM `" . $table . "` WHERE Key_name = '" . $key_name . "'");
- $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
- $is_drop = ($num_results != 0) ? "DROP INDEX `" . $key_name . "`, " : "";
- $pdo->query("ALTER TABLE `" . $table . "` " . $is_drop . "ADD KEY `" . $key_name . "` (" . $fields . ")");
- }
- }
- if (strtolower($key_type) == 'unique') {
- foreach ($key_content as $key_name => $key_values) {
- $fields = "`" . implode("`, `", $key_values) . "`";
- $stmt = $pdo->query("SHOW KEYS FROM `" . $table . "` WHERE Key_name = '" . $key_name . "'");
- $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
- $is_drop = ($num_results != 0) ? "DROP INDEX `" . $key_name . "`, " : "";
- $pdo->query("ALTER TABLE `" . $table . "` " . $is_drop . "ADD UNIQUE KEY `" . $key_name . "` (" . $fields . ")");
- }
- }
- if (strtolower($key_type) == 'fkey') {
- foreach ($key_content as $key_name => $key_values) {
- $fields = "`" . implode("`, `", $key_values) . "`";
- $stmt = $pdo->query("SHOW KEYS FROM `" . $table . "` WHERE Key_name = '" . $key_name . "'");
- $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
- if ($num_results != 0) {
- $pdo->query("ALTER TABLE `" . $table . "` DROP INDEX `" . $key_name . "`");
- }
- @list($table_ref, $field_ref) = explode('.', $key_values['ref']);
- $pdo->query("ALTER TABLE `" . $table . "` ADD FOREIGN KEY `" . $key_name . "` (" . $key_values['col'] . ") REFERENCES `" . $table_ref . "` (`" . $field_ref . "`)
- ON DELETE " . $key_values['delete'] . " ON UPDATE " . $key_values['update']);
- }
- }
- }
- // Drop all vanished columns
- $stmt = $pdo->query("SHOW COLUMNS FROM `" . $table . "`");
- $cols_in_table = $stmt->fetchAll(PDO::FETCH_ASSOC);
- while ($row = array_shift($cols_in_table)) {
- if (!array_key_exists($row['Field'], $properties['cols'])) {
- $pdo->query("ALTER TABLE `" . $table . "` DROP COLUMN `" . $row['Field'] . "`;");
- }
- }
-
- // Step 1: Get all non-primary keys, that currently exist and those that should exist
- $stmt = $pdo->query("SHOW KEYS FROM `" . $table . "` WHERE `Key_name` != 'PRIMARY'");
- $keys_in_table = $stmt->fetchAll(PDO::FETCH_ASSOC);
- $keys_to_exist = array();
- if (isset($properties['keys']['unique']) && is_array($properties['keys']['unique'])) {
- foreach ($properties['keys']['unique'] as $key_name => $key_values) {
- $keys_to_exist[] = $key_name;
- }
- }
- if (isset($properties['keys']['key']) && is_array($properties['keys']['key'])) {
- foreach ($properties['keys']['key'] as $key_name => $key_values) {
- $keys_to_exist[] = $key_name;
- }
- }
- // Index for foreign key must exist
- if (isset($properties['keys']['fkey']) && is_array($properties['keys']['fkey'])) {
- foreach ($properties['keys']['fkey'] as $key_name => $key_values) {
- $keys_to_exist[] = $key_name;
- }
- }
- // Step 2: Drop all vanished indexes
- while ($row = array_shift($keys_in_table)) {
- if (!in_array($row['Key_name'], $keys_to_exist)) {
- $pdo->query("ALTER TABLE `" . $table . "` DROP INDEX `" . $row['Key_name'] . "`");
- }
- }
- // Step 3: Drop all vanished primary keys
- if (!isset($properties['keys']['primary'])) {
- $stmt = $pdo->query("SHOW KEYS FROM `" . $table . "` WHERE Key_name = 'PRIMARY'");
- $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
- if ($num_results != 0) {
- $pdo->query("ALTER TABLE `" . $table . "` DROP PRIMARY KEY");
- }
- }
- }
- else {
- // Create table if it is missing
- $sql = "CREATE TABLE IF NOT EXISTS `" . $table . "` (";
- foreach($properties['cols'] as $column => $type) {
- $sql .= "`" . $column . "` " . $type . ",";
- }
- foreach($properties['keys'] as $key_type => $key_content) {
- if (strtolower($key_type) == 'primary') {
- foreach ($key_content as $key_values) {
- $fields = "`" . implode("`, `", $key_values) . "`";
- $sql .= "PRIMARY KEY (" . $fields . ")" . ",";
- }
- }
- elseif (strtolower($key_type) == 'key') {
- foreach ($key_content as $key_name => $key_values) {
- $fields = "`" . implode("`, `", $key_values) . "`";
- $sql .= "KEY `" . $key_name . "` (" . $fields . ")" . ",";
- }
- }
- elseif (strtolower($key_type) == 'unique') {
- foreach ($key_content as $key_name => $key_values) {
- $fields = "`" . implode("`, `", $key_values) . "`";
- $sql .= "UNIQUE KEY `" . $key_name . "` (" . $fields . ")" . ",";
- }
- }
- elseif (strtolower($key_type) == 'fkey') {
- foreach ($key_content as $key_name => $key_values) {
- @list($table_ref, $field_ref) = explode('.', $key_values['ref']);
- $sql .= "FOREIGN KEY `" . $key_name . "` (" . $key_values['col'] . ") REFERENCES `" . $table_ref . "` (`" . $field_ref . "`)
- ON DELETE " . $key_values['delete'] . " ON UPDATE " . $key_values['update'] . ",";
- }
- }
- }
- $sql = rtrim($sql, ",");
- $sql .= ") " . $properties['attr'];
- $pdo->query($sql);
- }
- // Reset table attributes
- $pdo->query("ALTER TABLE `" . $table . "` " . $properties['attr'] . ";");
-
- }
-
- // Recreate SQL views
- foreach ($views as $view => $create) {
- $pdo->query("DROP VIEW IF EXISTS `" . $view . "`;");
- $pdo->query($create);
- }
-
- // 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`");
- $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
- if ($num_results == 0) {
- $pdo->query("INSERT INTO `admin` (`username`, `password`, `superadmin`, `created`, `modified`, `active`)
- VALUES ('admin', '{SSHA256}K8eVJ6YsZbQCfuJvSUbaQRLr0HPLz5rC9IAp0PAFl0tmNDBkMDc0NDAyOTAxN2Rk', 1, NOW(), NOW(), 1)");
- $pdo->query("INSERT INTO `domain_admins` (`username`, `domain`, `created`, `active`)
- SELECT `username`, 'ALL', NOW(), 1 FROM `admin`
- WHERE superadmin='1' AND `username` NOT IN (SELECT `username` FROM `domain_admins`);");
- $pdo->query("DELETE FROM `admin` WHERE `username` NOT IN (SELECT `username` FROM `domain_admins`);");
- }
- // Insert new DB schema version
- $pdo->query("REPLACE INTO `versions` (`application`, `version`) VALUES ('db_schema', '" . $db_version . "');");
-
- // Fix dangling domain admins
- $pdo->query("DELETE FROM `admin` WHERE `superadmin` = 0 AND `username` NOT IN (SELECT `username`FROM `domain_admins`);");
- $pdo->query("DELETE FROM `da_acl` WHERE `username` NOT IN (SELECT `username`FROM `domain_admins`);");
-
- // Migrate attributes
- // pushover
- $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;");
- $pdo->query("UPDATE `mailbox` SET `attributes` = JSON_SET(`attributes`, '$.relayhost', \"0\") WHERE JSON_VALUE(`attributes`, '$.relayhost') IS NULL;");
- $pdo->query("UPDATE `mailbox` SET `attributes` = JSON_SET(`attributes`, '$.force_pw_update', \"0\") WHERE JSON_VALUE(`attributes`, '$.force_pw_update') IS NULL;");
- $pdo->query("UPDATE `mailbox` SET `attributes` = JSON_SET(`attributes`, '$.sieve_access', \"1\") WHERE JSON_VALUE(`attributes`, '$.sieve_access') IS NULL;");
- $pdo->query("UPDATE `mailbox` SET `attributes` = JSON_SET(`attributes`, '$.sogo_access', \"1\") WHERE JSON_VALUE(`attributes`, '$.sogo_access') IS NULL;");
- $pdo->query("UPDATE `mailbox` SET `attributes` = JSON_SET(`attributes`, '$.imap_access', \"1\") WHERE JSON_VALUE(`attributes`, '$.imap_access') IS NULL;");
- $pdo->query("UPDATE `mailbox` SET `attributes` = JSON_SET(`attributes`, '$.pop3_access', \"1\") WHERE JSON_VALUE(`attributes`, '$.pop3_access') IS NULL;");
- $pdo->query("UPDATE `mailbox` SET `attributes` = JSON_SET(`attributes`, '$.smtp_access', \"1\") WHERE JSON_VALUE(`attributes`, '$.smtp_access') IS NULL;");
- $pdo->query("UPDATE `mailbox` SET `attributes` = JSON_SET(`attributes`, '$.mailbox_format', \"maildir:\") WHERE JSON_VALUE(`attributes`, '$.mailbox_format') IS NULL;");
- $pdo->query("UPDATE `mailbox` SET `attributes` = JSON_SET(`attributes`, '$.quarantine_notification', \"never\") WHERE JSON_VALUE(`attributes`, '$.quarantine_notification') IS NULL;");
- $pdo->query("UPDATE `mailbox` SET `attributes` = JSON_SET(`attributes`, '$.quarantine_category', \"reject\") WHERE JSON_VALUE(`attributes`, '$.quarantine_category') IS NULL;");
- foreach($tls_options as $tls_user => $tls_options) {
- $stmt = $pdo->prepare("UPDATE `mailbox` SET `attributes` = JSON_SET(`attributes`, '$.tls_enforce_in', :tls_enforce_in),
- `attributes` = JSON_SET(`attributes`, '$.tls_enforce_out', :tls_enforce_out)
- WHERE `username` = :username");
- $stmt->execute(array(':tls_enforce_in' => $tls_options['tls_enforce_in'], ':tls_enforce_out' => $tls_options['tls_enforce_out'], ':username' => $tls_user));
- }
- // Set tls_enforce_* if still missing (due to deleted attrs for example)
- $pdo->query("UPDATE `mailbox` SET `attributes` = JSON_SET(`attributes`, '$.tls_enforce_out', \"1\") WHERE JSON_VALUE(`attributes`, '$.tls_enforce_out') IS NULL;");
- $pdo->query("UPDATE `mailbox` SET `attributes` = JSON_SET(`attributes`, '$.tls_enforce_in', \"1\") WHERE JSON_VALUE(`attributes`, '$.tls_enforce_in') IS NULL;");
- // Fix ACL
- $pdo->query("INSERT INTO `user_acl` (`username`) SELECT `username` FROM `mailbox` WHERE `kind` = '' AND NOT EXISTS (SELECT `username` FROM `user_acl`);");
- $pdo->query("INSERT INTO `da_acl` (`username`) SELECT DISTINCT `username` FROM `domain_admins` WHERE `username` != 'admin' AND NOT EXISTS (SELECT `username` FROM `da_acl`);");
- // 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 {
- $_SESSION['return'][] = array(
- 'type' => 'success',
- 'log' => array(__FUNCTION__),
- 'msg' => 'db_init_complete'
- );
- }
- }
- catch (PDOException $e) {
- if (php_sapi_name() == "cli") {
- echo "DB initialization failed: " . print_r($e, true) . PHP_EOL;
- } else {
- $_SESSION['return'][] = array(
- 'type' => 'danger',
- 'log' => array(__FUNCTION__),
- 'msg' => array('mysql_error', $e)
- );
- }
- }
-}
-if (php_sapi_name() == "cli") {
- include '/web/inc/vars.inc.php';
- include '/web/inc/functions.docker.inc.php';
- // $now = new DateTime();
- // $mins = $now->getOffset() / 60;
- // $sgn = ($mins < 0 ? -1 : 1);
- // $mins = abs($mins);
- // $hrs = floor($mins / 60);
- // $mins -= $hrs * 60;
- // $offset = sprintf('%+d:%02d', $hrs*$sgn, $mins);
- $dsn = $database_type . ":unix_socket=" . $database_sock . ";dbname=" . $database_name;
- $opt = [
- PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
- PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
- PDO::ATTR_EMULATE_PREPARES => false,
- //PDO::MYSQL_ATTR_INIT_COMMAND => "SET time_zone = '" . $offset . "', group_concat_max_len = 3423543543;",
- ];
- $pdo = new PDO($dsn, $database_user, $database_pass, $opt);
- $stmt = $pdo->query("SELECT COUNT('OK') AS OK_C FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'sogo_view' OR TABLE_NAME = '_sogo_static_view';");
- $res = $stmt->fetch(PDO::FETCH_ASSOC);
- if (intval($res['OK_C']) === 2) {
- // Be more precise when replacing into _sogo_static_view, col orders may change
- try {
- $stmt = $pdo->query("REPLACE INTO _sogo_static_view (`c_uid`, `domain`, `c_name`, `c_password`, `c_cn`, `mail`, `aliases`, `ad_aliases`, `ext_acl`, `kind`, `multiple_bookings`)
- SELECT `c_uid`, `domain`, `c_name`, `c_password`, `c_cn`, `mail`, `aliases`, `ad_aliases`, `ext_acl`, `kind`, `multiple_bookings` from sogo_view");
- $stmt = $pdo->query("DELETE FROM _sogo_static_view WHERE `c_uid` NOT IN (SELECT `username` FROM `mailbox` WHERE `active` = '1');");
- echo "Fixed _sogo_static_view" . PHP_EOL;
- }
- catch ( Exception $e ) {
- // Dunno
- }
- }
- try {
- $m = new Memcached();
- $m->addServer('memcached', 11211);
- $m->flush();
- echo "Cleaned up memcached". PHP_EOL;
- }
- catch ( Exception $e ) {
- // Dunno
- }
- init_db_schema();
-}
+<?php
+function init_db_schema() {
+ try {
+ global $pdo;
+
+ $db_version = "14022023_1000";
+
+ $stmt = $pdo->query("SHOW TABLES LIKE 'versions'");
+ $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
+ if ($num_results != 0) {
+ $stmt = $pdo->query("SELECT `version` FROM `versions` WHERE `application` = 'db_schema'");
+ if ($stmt->fetch(PDO::FETCH_ASSOC)['version'] == $db_version) {
+ return true;
+ }
+ if (!preg_match('/y|yes/i', getenv('MASTER'))) {
+ $_SESSION['return'][] = array(
+ 'type' => 'warning',
+ 'log' => array(__FUNCTION__),
+ 'msg' => 'Database not initialized: not running db_init on slave.'
+ );
+ return true;
+ }
+ }
+
+ $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';"
+ );
+
+ $tables = array(
+ "versions" => array(
+ "cols" => array(
+ "application" => "VARCHAR(255) NOT NULL",
+ "version" => "VARCHAR(100) NOT NULL",
+ "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("application")
+ )
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
+ "admin" => array(
+ "cols" => array(
+ "username" => "VARCHAR(255) NOT NULL",
+ "password" => "VARCHAR(255) NOT NULL",
+ "superadmin" => "TINYINT(1) NOT NULL DEFAULT '0'",
+ "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
+ "modified" => "DATETIME ON UPDATE NOW(0)",
+ "active" => "TINYINT(1) NOT NULL DEFAULT '1'"
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("username")
+ )
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
+ "fido2" => array(
+ "cols" => array(
+ "username" => "VARCHAR(255) NOT NULL",
+ "friendlyName" => "VARCHAR(255)",
+ "rpId" => "VARCHAR(255) NOT NULL",
+ "credentialPublicKey" => "TEXT NOT NULL",
+ "certificateChain" => "TEXT",
+ // Can be null for format "none"
+ "certificate" => "TEXT",
+ "certificateIssuer" => "VARCHAR(255)",
+ "certificateSubject" => "VARCHAR(255)",
+ "signatureCounter" => "INT",
+ "AAGUID" => "BLOB",
+ "credentialId" => "BLOB NOT NULL",
+ "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
+ "modified" => "DATETIME ON UPDATE NOW(0)",
+ "active" => "TINYINT(1) NOT NULL DEFAULT '1'"
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
+ "_sogo_static_view" => array(
+ "cols" => array(
+ "c_uid" => "VARCHAR(255) NOT NULL",
+ "domain" => "VARCHAR(255) NOT NULL",
+ "c_name" => "VARCHAR(255) NOT NULL",
+ "c_password" => "VARCHAR(255) NOT NULL DEFAULT ''",
+ "c_cn" => "VARCHAR(255)",
+ "mail" => "VARCHAR(255) NOT NULL",
+ // TODO -> use TEXT and check if SOGo login breaks on empty aliases
+ "aliases" => "TEXT NOT NULL",
+ "ad_aliases" => "VARCHAR(6144) NOT NULL DEFAULT ''",
+ "ext_acl" => "VARCHAR(6144) NOT NULL DEFAULT ''",
+ "kind" => "VARCHAR(100) NOT NULL DEFAULT ''",
+ "multiple_bookings" => "INT NOT NULL DEFAULT -1"
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("c_uid")
+ ),
+ "key" => array(
+ "domain" => array("domain")
+ )
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
+ "relayhosts" => array(
+ "cols" => array(
+ "id" => "INT NOT NULL AUTO_INCREMENT",
+ "hostname" => "VARCHAR(255) NOT NULL",
+ "username" => "VARCHAR(255) NOT NULL",
+ "password" => "VARCHAR(255) NOT NULL",
+ "active" => "TINYINT(1) NOT NULL DEFAULT '1'"
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("id")
+ ),
+ "key" => array(
+ "hostname" => array("hostname")
+ )
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
+ "transports" => array(
+ "cols" => array(
+ "id" => "INT NOT NULL AUTO_INCREMENT",
+ "destination" => "VARCHAR(255) NOT NULL",
+ "nexthop" => "VARCHAR(255) NOT NULL",
+ "username" => "VARCHAR(255) NOT NULL DEFAULT ''",
+ "password" => "VARCHAR(255) NOT NULL DEFAULT ''",
+ "is_mx_based" => "TINYINT(1) NOT NULL DEFAULT '0'",
+ "active" => "TINYINT(1) NOT NULL DEFAULT '1'"
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("id")
+ ),
+ "key" => array(
+ "destination" => array("destination"),
+ "nexthop" => array("nexthop"),
+ )
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
+ "alias" => array(
+ "cols" => array(
+ "id" => "INT NOT NULL AUTO_INCREMENT",
+ "address" => "VARCHAR(255) NOT NULL",
+ "goto" => "TEXT NOT NULL",
+ "domain" => "VARCHAR(255) NOT NULL",
+ "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
+ "modified" => "DATETIME ON UPDATE CURRENT_TIMESTAMP",
+ "private_comment" => "TEXT",
+ "public_comment" => "TEXT",
+ "sogo_visible" => "TINYINT(1) NOT NULL DEFAULT '1'",
+ "active" => "TINYINT(1) NOT NULL DEFAULT '1'"
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("id")
+ ),
+ "unique" => array(
+ "address" => array("address")
+ ),
+ "key" => array(
+ "domain" => array("domain")
+ )
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
+ "api" => array(
+ "cols" => array(
+ "api_key" => "VARCHAR(255) NOT NULL",
+ "allow_from" => "VARCHAR(512) NOT NULL",
+ "skip_ip_check" => "TINYINT(1) NOT NULL DEFAULT '0'",
+ "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
+ "modified" => "DATETIME ON UPDATE NOW(0)",
+ "access" => "ENUM('ro', 'rw') NOT NULL DEFAULT 'rw'",
+ "active" => "TINYINT(1) NOT NULL DEFAULT '1'"
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("api_key")
+ ),
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
+ "sender_acl" => array(
+ "cols" => array(
+ "id" => "INT NOT NULL AUTO_INCREMENT",
+ "logged_in_as" => "VARCHAR(255) NOT NULL",
+ "send_as" => "VARCHAR(255) NOT NULL",
+ "external" => "TINYINT(1) NOT NULL DEFAULT '0'"
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("id")
+ )
+ ),
+ "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(
+ "domain" => "VARCHAR(255) NOT NULL",
+ "description" => "VARCHAR(255)",
+ "aliases" => "INT(10) NOT NULL DEFAULT '0'",
+ "mailboxes" => "INT(10) NOT NULL DEFAULT '0'",
+ "defquota" => "BIGINT(20) NOT NULL DEFAULT '3072'",
+ "maxquota" => "BIGINT(20) NOT NULL DEFAULT '102400'",
+ "quota" => "BIGINT(20) NOT NULL DEFAULT '102400'",
+ "relayhost" => "VARCHAR(255) NOT NULL DEFAULT '0'",
+ "backupmx" => "TINYINT(1) NOT NULL DEFAULT '0'",
+ "gal" => "TINYINT(1) NOT NULL DEFAULT '1'",
+ "relay_all_recipients" => "TINYINT(1) NOT NULL DEFAULT '0'",
+ "relay_unknown_only" => "TINYINT(1) NOT NULL DEFAULT '0'",
+ "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
+ "modified" => "DATETIME ON UPDATE CURRENT_TIMESTAMP",
+ "active" => "TINYINT(1) NOT NULL DEFAULT '1'"
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("domain")
+ )
+ ),
+ "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",
+ "dest" => "VARCHAR(255) NOT NULL",
+ "policy" => "ENUM('none', 'may', 'encrypt', 'dane', 'dane-only', 'fingerprint', 'verify', 'secure') NOT NULL",
+ "parameters" => "VARCHAR(255) DEFAULT ''",
+ "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
+ "modified" => "DATETIME ON UPDATE CURRENT_TIMESTAMP",
+ "active" => "TINYINT(1) NOT NULL DEFAULT '1'"
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("id")
+ ),
+ "unique" => array(
+ "dest" => array("dest")
+ ),
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
+ "quarantine" => array(
+ "cols" => array(
+ "id" => "INT NOT NULL AUTO_INCREMENT",
+ "qid" => "VARCHAR(30) NOT NULL",
+ "subject" => "VARCHAR(500)",
+ "score" => "FLOAT(8,2)",
+ "ip" => "VARCHAR(50)",
+ "action" => "CHAR(20) NOT NULL DEFAULT 'unknown'",
+ "symbols" => "JSON",
+ "fuzzy_hashes" => "JSON",
+ "sender" => "VARCHAR(255) NOT NULL DEFAULT 'unknown'",
+ "rcpt" => "VARCHAR(255)",
+ "msg" => "LONGTEXT",
+ "domain" => "VARCHAR(255)",
+ "notified" => "TINYINT(1) NOT NULL DEFAULT '0'",
+ "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
+ "user" => "VARCHAR(255) NOT NULL DEFAULT 'unknown'",
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("id")
+ )
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
+ "mailbox" => array(
+ "cols" => array(
+ "username" => "VARCHAR(255) NOT NULL",
+ "password" => "VARCHAR(255) NOT NULL",
+ "name" => "VARCHAR(255)",
+ "description" => "VARCHAR(255)",
+ // mailbox_path_prefix is followed by domain/local_part/
+ "mailbox_path_prefix" => "VARCHAR(150) DEFAULT '/var/vmail/'",
+ "quota" => "BIGINT(20) NOT NULL DEFAULT '102400'",
+ "local_part" => "VARCHAR(255) NOT NULL",
+ "domain" => "VARCHAR(255) NOT NULL",
+ "attributes" => "JSON",
+ "kind" => "VARCHAR(100) NOT NULL DEFAULT ''",
+ "multiple_bookings" => "INT NOT NULL DEFAULT -1",
+ "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
+ "modified" => "DATETIME ON UPDATE CURRENT_TIMESTAMP",
+ "active" => "TINYINT(1) NOT NULL DEFAULT '1'"
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("username")
+ ),
+ "key" => array(
+ "domain" => array("domain"),
+ "kind" => array("kind")
+ )
+ ),
+ "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",
+ "username" => "VARCHAR(255) NOT NULL",
+ "script_desc" => "VARCHAR(255) NOT NULL",
+ "script_name" => "ENUM('active','inactive')",
+ "script_data" => "TEXT NOT NULL",
+ "filter_type" => "ENUM('postfilter','prefilter')",
+ "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
+ "modified" => "DATETIME ON UPDATE CURRENT_TIMESTAMP"
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("id")
+ ),
+ "key" => array(
+ "username" => array("username"),
+ "script_desc" => array("script_desc")
+ ),
+ "fkey" => array(
+ "fk_username_sieve_global_before" => array(
+ "col" => "username",
+ "ref" => "mailbox.username",
+ "delete" => "CASCADE",
+ "update" => "NO ACTION"
+ )
+ )
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
+ "app_passwd" => array(
+ "cols" => array(
+ "id" => "INT NOT NULL AUTO_INCREMENT",
+ "name" => "VARCHAR(255) NOT NULL",
+ "mailbox" => "VARCHAR(255) NOT NULL",
+ "domain" => "VARCHAR(255) NOT NULL",
+ "password" => "VARCHAR(255) NOT NULL",
+ "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
+ "modified" => "DATETIME ON UPDATE CURRENT_TIMESTAMP",
+ "imap_access" => "TINYINT(1) NOT NULL DEFAULT '1'",
+ "smtp_access" => "TINYINT(1) NOT NULL DEFAULT '1'",
+ "dav_access" => "TINYINT(1) NOT NULL DEFAULT '1'",
+ "eas_access" => "TINYINT(1) NOT NULL DEFAULT '1'",
+ "pop3_access" => "TINYINT(1) NOT NULL DEFAULT '1'",
+ "sieve_access" => "TINYINT(1) NOT NULL DEFAULT '1'",
+ "active" => "TINYINT(1) NOT NULL DEFAULT '1'"
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("id")
+ ),
+ "key" => array(
+ "mailbox" => array("mailbox"),
+ "password" => array("password"),
+ "domain" => array("domain"),
+ ),
+ "fkey" => array(
+ "fk_username_app_passwd" => array(
+ "col" => "mailbox",
+ "ref" => "mailbox.username",
+ "delete" => "CASCADE",
+ "update" => "NO ACTION"
+ )
+ )
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
+ "user_acl" => array(
+ "cols" => array(
+ "username" => "VARCHAR(255) NOT NULL",
+ "spam_alias" => "TINYINT(1) NOT NULL DEFAULT '1'",
+ "tls_policy" => "TINYINT(1) NOT NULL DEFAULT '1'",
+ "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 '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'",
+ // quarantine is for quarantine actions, todo: rename
+ "quarantine" => "TINYINT(1) NOT NULL DEFAULT '1'",
+ "quarantine_attachments" => "TINYINT(1) NOT NULL DEFAULT '1'",
+ "quarantine_notification" => "TINYINT(1) NOT NULL DEFAULT '1'",
+ "quarantine_category" => "TINYINT(1) NOT NULL DEFAULT '1'",
+ "app_passwds" => "TINYINT(1) NOT NULL DEFAULT '1'",
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("username")
+ ),
+ "fkey" => array(
+ "fk_username" => array(
+ "col" => "username",
+ "ref" => "mailbox.username",
+ "delete" => "CASCADE",
+ "update" => "NO ACTION"
+ )
+ )
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
+ "alias_domain" => array(
+ "cols" => array(
+ "alias_domain" => "VARCHAR(255) NOT NULL",
+ "target_domain" => "VARCHAR(255) NOT NULL",
+ "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
+ "modified" => "DATETIME ON UPDATE CURRENT_TIMESTAMP",
+ "active" => "TINYINT(1) NOT NULL DEFAULT '1'"
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("alias_domain")
+ ),
+ "key" => array(
+ "active" => array("active"),
+ "target_domain" => array("target_domain")
+ )
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
+ "spamalias" => array(
+ "cols" => array(
+ "address" => "VARCHAR(255) NOT NULL",
+ "goto" => "TEXT NOT NULL",
+ "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
+ "modified" => "DATETIME ON UPDATE CURRENT_TIMESTAMP",
+ "validity" => "INT(11)"
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("address")
+ ),
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
+ "filterconf" => array(
+ "cols" => array(
+ "object" => "VARCHAR(255) NOT NULL DEFAULT ''",
+ "option" => "VARCHAR(50) NOT NULL DEFAULT ''",
+ "value" => "VARCHAR(100) NOT NULL DEFAULT ''",
+ "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
+ "modified" => "DATETIME ON UPDATE CURRENT_TIMESTAMP",
+ "prefid" => "INT(11) NOT NULL AUTO_INCREMENT"
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("prefid")
+ ),
+ "key" => array(
+ "object" => array("object")
+ )
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
+ "settingsmap" => array(
+ "cols" => array(
+ "id" => "INT NOT NULL AUTO_INCREMENT",
+ "desc" => "VARCHAR(255) NOT NULL",
+ "content" => "LONGTEXT NOT NULL",
+ "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
+ "modified" => "DATETIME ON UPDATE CURRENT_TIMESTAMP",
+ "active" => "TINYINT(1) NOT NULL DEFAULT '0'"
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("id")
+ )
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
+ "logs" => array(
+ "cols" => array(
+ "id" => "INT NOT NULL AUTO_INCREMENT",
+ "task" => "CHAR(32) NOT NULL DEFAULT '000000'",
+ "type" => "VARCHAR(32) DEFAULT ''",
+ "msg" => "TEXT",
+ "call" => "TEXT",
+ "user" => "VARCHAR(64) NOT NULL",
+ "role" => "VARCHAR(32) NOT NULL",
+ "remote" => "VARCHAR(39) NOT NULL",
+ "time" => "INT(11) NOT NULL"
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("id")
+ )
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
+ "sasl_log" => array(
+ "cols" => array(
+ "service" => "VARCHAR(32) NOT NULL DEFAULT ''",
+ "app_password" => "INT",
+ "username" => "VARCHAR(255) NOT NULL",
+ "real_rip" => "VARCHAR(64) NOT NULL",
+ "datetime" => "DATETIME(0) NOT NULL DEFAULT NOW(0)"
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("service", "real_rip", "username")
+ ),
+ "key" => array(
+ "username" => array("username"),
+ "service" => array("service"),
+ "datetime" => array("datetime"),
+ "real_rip" => array("real_rip")
+ )
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
+ "quota2" => array(
+ "cols" => array(
+ "username" => "VARCHAR(255) NOT NULL",
+ "bytes" => "BIGINT(20) NOT NULL DEFAULT '0'",
+ "messages" => "BIGINT(20) NOT NULL DEFAULT '0'"
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("username")
+ )
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
+ "quota2replica" => array(
+ "cols" => array(
+ "username" => "VARCHAR(255) NOT NULL",
+ "bytes" => "BIGINT(20) NOT NULL DEFAULT '0'",
+ "messages" => "BIGINT(20) NOT NULL DEFAULT '0'"
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("username")
+ )
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
+ "domain_admins" => array(
+ "cols" => array(
+ "id" => "INT NOT NULL AUTO_INCREMENT",
+ "username" => "VARCHAR(255) NOT NULL",
+ "domain" => "VARCHAR(255) NOT NULL",
+ "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
+ "active" => "TINYINT(1) NOT NULL DEFAULT '1'"
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("id")
+ ),
+ "key" => array(
+ "username" => array("username")
+ )
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
+ "da_acl" => array(
+ "cols" => array(
+ "username" => "VARCHAR(255) NOT NULL",
+ "syncjobs" => "TINYINT(1) NOT NULL DEFAULT '1'",
+ "quarantine" => "TINYINT(1) NOT NULL DEFAULT '1'",
+ "login_as" => "TINYINT(1) NOT NULL DEFAULT '1'",
+ "sogo_access" => "TINYINT(1) NOT NULL DEFAULT '1'",
+ "app_passwds" => "TINYINT(1) NOT NULL DEFAULT '1'",
+ "bcc_maps" => "TINYINT(1) NOT NULL DEFAULT '1'",
+ "pushover" => "TINYINT(1) NOT NULL DEFAULT '0'",
+ "filters" => "TINYINT(1) NOT NULL DEFAULT '1'",
+ "ratelimit" => "TINYINT(1) NOT NULL DEFAULT '1'",
+ "spam_policy" => "TINYINT(1) NOT NULL DEFAULT '1'",
+ "extend_sender_acl" => "TINYINT(1) NOT NULL DEFAULT '0'",
+ "unlimited_quota" => "TINYINT(1) NOT NULL DEFAULT '0'",
+ "protocol_access" => "TINYINT(1) NOT NULL DEFAULT '1'",
+ "smtp_ip_access" => "TINYINT(1) NOT NULL DEFAULT '1'",
+ "alias_domains" => "TINYINT(1) NOT NULL DEFAULT '0'",
+ "mailbox_relayhost" => "TINYINT(1) NOT NULL DEFAULT '1'",
+ "domain_relayhost" => "TINYINT(1) NOT NULL DEFAULT '1'",
+ "domain_desc" => "TINYINT(1) NOT NULL DEFAULT '0'"
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("username")
+ )
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
+ "da_sso" => array(
+ "cols" => array(
+ "username" => "VARCHAR(255) NOT NULL",
+ "token" => "VARCHAR(255) NOT NULL",
+ "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("token", "created")
+ ),
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
+ "imapsync" => array(
+ "cols" => array(
+ "id" => "INT NOT NULL AUTO_INCREMENT",
+ "user2" => "VARCHAR(255) NOT NULL",
+ "host1" => "VARCHAR(255) NOT NULL",
+ "authmech1" => "ENUM('PLAIN','LOGIN','CRAM-MD5') DEFAULT 'PLAIN'",
+ "regextrans2" => "VARCHAR(255) DEFAULT ''",
+ "authmd51" => "TINYINT(1) NOT NULL DEFAULT 0",
+ "domain2" => "VARCHAR(255) NOT NULL DEFAULT ''",
+ "subfolder2" => "VARCHAR(255) NOT NULL DEFAULT ''",
+ "user1" => "VARCHAR(255) NOT NULL",
+ "password1" => "VARCHAR(255) NOT NULL",
+ "exclude" => "VARCHAR(500) NOT NULL DEFAULT ''",
+ "maxage" => "SMALLINT NOT NULL DEFAULT '0'",
+ "mins_interval" => "SMALLINT UNSIGNED NOT NULL DEFAULT '0'",
+ "maxbytespersecond" => "VARCHAR(50) NOT NULL DEFAULT '0'",
+ "port1" => "SMALLINT UNSIGNED NOT NULL",
+ "enc1" => "ENUM('TLS','SSL','PLAIN') DEFAULT 'TLS'",
+ "delete2duplicates" => "TINYINT(1) NOT NULL DEFAULT '1'",
+ "delete1" => "TINYINT(1) NOT NULL DEFAULT '0'",
+ "delete2" => "TINYINT(1) NOT NULL DEFAULT '0'",
+ "automap" => "TINYINT(1) NOT NULL DEFAULT '0'",
+ "skipcrossduplicates" => "TINYINT(1) NOT NULL DEFAULT '0'",
+ "custom_params" => "VARCHAR(512) NOT NULL DEFAULT ''",
+ "timeout1" => "SMALLINT NOT NULL DEFAULT '600'",
+ "timeout2" => "SMALLINT NOT NULL DEFAULT '600'",
+ "subscribeall" => "TINYINT(1) NOT NULL DEFAULT '1'",
+ "is_running" => "TINYINT(1) NOT NULL DEFAULT '0'",
+ "returned_text" => "LONGTEXT",
+ "last_run" => "TIMESTAMP NULL DEFAULT NULL",
+ "success" => "TINYINT(1) UNSIGNED DEFAULT NULL",
+ "exit_status" => "VARCHAR(50) DEFAULT NULL",
+ "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
+ "modified" => "DATETIME ON UPDATE CURRENT_TIMESTAMP",
+ "active" => "TINYINT(1) NOT NULL DEFAULT '0'"
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("id")
+ )
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
+ "bcc_maps" => array(
+ "cols" => array(
+ "id" => "INT NOT NULL AUTO_INCREMENT",
+ "local_dest" => "VARCHAR(255) NOT NULL",
+ "bcc_dest" => "VARCHAR(255) NOT NULL",
+ "domain" => "VARCHAR(255) NOT NULL",
+ "type" => "ENUM('sender','rcpt')",
+ "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
+ "modified" => "DATETIME ON UPDATE CURRENT_TIMESTAMP",
+ "active" => "TINYINT(1) NOT NULL DEFAULT '0'"
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("id")
+ ),
+ "key" => array(
+ "local_dest" => array("local_dest"),
+ )
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
+ "recipient_maps" => array(
+ "cols" => array(
+ "id" => "INT NOT NULL AUTO_INCREMENT",
+ "old_dest" => "VARCHAR(255) NOT NULL",
+ "new_dest" => "VARCHAR(255) NOT NULL",
+ "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
+ "modified" => "DATETIME ON UPDATE CURRENT_TIMESTAMP",
+ "active" => "TINYINT(1) NOT NULL DEFAULT '0'"
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("id")
+ ),
+ "key" => array(
+ "local_dest" => array("old_dest"),
+ )
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
+ "tfa" => array(
+ "cols" => array(
+ "id" => "INT NOT NULL AUTO_INCREMENT",
+ "key_id" => "VARCHAR(255) NOT NULL",
+ "username" => "VARCHAR(255) NOT NULL",
+ "authmech" => "ENUM('yubi_otp', 'u2f', 'hotp', 'totp', 'webauthn')",
+ "secret" => "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'"
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("id")
+ )
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
+ "forwarding_hosts" => array(
+ "cols" => array(
+ "host" => "VARCHAR(255) NOT NULL",
+ "source" => "VARCHAR(255) NOT NULL",
+ "filter_spam" => "TINYINT(1) NOT NULL DEFAULT '0'"
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("host")
+ ),
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
+ "sogo_acl" => array(
+ "cols" => array(
+ "id" => "INT NOT NULL AUTO_INCREMENT",
+ "c_folder_id" => "INT NOT NULL",
+ "c_object" => "VARCHAR(255) NOT NULL",
+ "c_uid" => "VARCHAR(255) NOT NULL",
+ "c_role" => "VARCHAR(80) NOT NULL"
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("id")
+ ),
+ "key" => array(
+ "sogo_acl_c_folder_id_idx" => array("c_folder_id"),
+ "sogo_acl_c_uid_idx" => array("c_uid")
+ )
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
+ "sogo_alarms_folder" => array(
+ "cols" => array(
+ "id" => "INT NOT NULL AUTO_INCREMENT",
+ "c_path" => "VARCHAR(255) NOT NULL",
+ "c_name" => "VARCHAR(255) NOT NULL",
+ "c_uid" => "VARCHAR(255) NOT NULL",
+ "c_recurrence_id" => "INT(11) DEFAULT NULL",
+ "c_alarm_number" => "INT(11) NOT NULL",
+ "c_alarm_date" => "INT(11) NOT NULL"
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("id")
+ )
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
+ "sogo_cache_folder" => array(
+ "cols" => array(
+ "c_uid" => "VARCHAR(255) NOT NULL",
+ "c_path" => "VARCHAR(255) NOT NULL",
+ "c_parent_path" => "VARCHAR(255) DEFAULT NULL",
+ "c_type" => "TINYINT(3) unsigned NOT NULL",
+ "c_creationdate" => "INT(11) NOT NULL",
+ "c_lastmodified" => "INT(11) NOT NULL",
+ "c_version" => "INT(11) NOT NULL DEFAULT '0'",
+ "c_deleted" => "TINYINT(4) NOT NULL DEFAULT '0'",
+ "c_content" => "LONGTEXT"
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("c_uid", "c_path")
+ ),
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
+ "sogo_folder_info" => array(
+ "cols" => array(
+ "c_folder_id" => "BIGINT(20) unsigned NOT NULL AUTO_INCREMENT",
+ "c_path" => "VARCHAR(255) NOT NULL",
+ "c_path1" => "VARCHAR(255) NOT NULL",
+ "c_path2" => "VARCHAR(255) DEFAULT NULL",
+ "c_path3" => "VARCHAR(255) DEFAULT NULL",
+ "c_path4" => "VARCHAR(255) DEFAULT NULL",
+ "c_foldername" => "VARCHAR(255) NOT NULL",
+ "c_location" => "VARCHAR(2048) DEFAULT NULL",
+ "c_quick_location" => "VARCHAR(2048) DEFAULT NULL",
+ "c_acl_location" => "VARCHAR(2048) DEFAULT NULL",
+ "c_folder_type" => "VARCHAR(255) NOT NULL"
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("c_path")
+ ),
+ "unique" => array(
+ "c_folder_id" => array("c_folder_id")
+ )
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
+ "sogo_quick_appointment" => array(
+ "cols" => array(
+ "c_folder_id" => "INT NOT NULL",
+ "c_name" => "VARCHAR(255) NOT NULL",
+ "c_uid" => "VARCHAR(1000) NOT NULL",
+ "c_startdate" => "INT",
+ "c_enddate" => "INT",
+ "c_cycleenddate" => "INT",
+ "c_title" => "VARCHAR(1000) NOT NULL",
+ "c_participants" => "TEXT",
+ "c_isallday" => "INT",
+ "c_iscycle" => "INT",
+ "c_cycleinfo" => "TEXT",
+ "c_classification" => "INT NOT NULL",
+ "c_isopaque" => "INT NOT NULL",
+ "c_status" => "INT NOT NULL",
+ "c_priority" => "INT",
+ "c_location" => "VARCHAR(255)",
+ "c_orgmail" => "VARCHAR(255)",
+ "c_partmails" => "TEXT",
+ "c_partstates" => "TEXT",
+ "c_category" => "VARCHAR(255)",
+ "c_sequence" => "INT",
+ "c_component" => "VARCHAR(10) NOT NULL",
+ "c_nextalarm" => "INT",
+ "c_description" => "TEXT"
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("c_folder_id", "c_name")
+ )
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
+ "sogo_quick_contact" => array(
+ "cols" => array(
+ "c_folder_id" => "INT NOT NULL",
+ "c_name" => "VARCHAR(255) NOT NULL",
+ "c_givenname" => "VARCHAR(255)",
+ "c_cn" => "VARCHAR(255)",
+ "c_sn" => "VARCHAR(255)",
+ "c_screenname" => "VARCHAR(255)",
+ "c_l" => "VARCHAR(255)",
+ "c_mail" => "TEXT",
+ "c_o" => "VARCHAR(500)",
+ "c_ou" => "VARCHAR(255)",
+ "c_telephonenumber" => "VARCHAR(255)",
+ "c_categories" => "VARCHAR(255)",
+ "c_component" => "VARCHAR(10) NOT NULL",
+ "c_hascertificate" => "INT4 DEFAULT 0"
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("c_folder_id", "c_name")
+ )
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
+ "sogo_sessions_folder" => array(
+ "cols" => array(
+ "c_id" => "VARCHAR(255) NOT NULL",
+ "c_value" => "VARCHAR(4096) NOT NULL",
+ "c_creationdate" => "INT(11) NOT NULL",
+ "c_lastseen" => "INT(11) NOT NULL"
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("c_id")
+ )
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
+ "sogo_store" => array(
+ "cols" => array(
+ "c_folder_id" => "INT NOT NULL",
+ "c_name" => "VARCHAR(255) NOT NULL",
+ "c_content" => "MEDIUMTEXT NOT NULL",
+ "c_creationdate" => "INT NOT NULL",
+ "c_lastmodified" => "INT NOT NULL",
+ "c_version" => "INT NOT NULL",
+ "c_deleted" => "INT"
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("c_folder_id", "c_name")
+ )
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
+ "pushover" => array(
+ "cols" => array(
+ "username" => "VARCHAR(255) NOT NULL",
+ "key" => "VARCHAR(255) NOT NULL",
+ "token" => "VARCHAR(255) NOT NULL",
+ "attributes" => "JSON",
+ "title" => "TEXT",
+ "text" => "TEXT",
+ "senders" => "TEXT",
+ "senders_regex" => "TEXT",
+ "active" => "TINYINT(1) NOT NULL DEFAULT '1'"
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("username")
+ )
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
+ "sogo_user_profile" => array(
+ "cols" => array(
+ "c_uid" => "VARCHAR(255) NOT NULL",
+ "c_defaults" => "LONGTEXT",
+ "c_settings" => "LONGTEXT"
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("c_uid")
+ )
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
+ "oauth_clients" => array(
+ "cols" => array(
+ "id" => "INT NOT NULL AUTO_INCREMENT",
+ "client_id" => "VARCHAR(80) NOT NULL",
+ "client_secret" => "VARCHAR(80)",
+ "redirect_uri" => "VARCHAR(2000)",
+ "grant_types" => "VARCHAR(80)",
+ "scope" => "VARCHAR(4000)",
+ "user_id" => "VARCHAR(80)"
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("client_id")
+ ),
+ "unique" => array(
+ "id" => array("id")
+ )
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
+ "oauth_access_tokens" => array(
+ "cols" => array(
+ "access_token" => "VARCHAR(40) NOT NULL",
+ "client_id" => "VARCHAR(80) NOT NULL",
+ "user_id" => "VARCHAR(80)",
+ "expires" => "TIMESTAMP NOT NULL",
+ "scope" => "VARCHAR(4000)"
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("access_token")
+ )
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
+ "oauth_authorization_codes" => array(
+ "cols" => array(
+ "authorization_code" => "VARCHAR(40) NOT NULL",
+ "client_id" => "VARCHAR(80) NOT NULL",
+ "user_id" => "VARCHAR(80)",
+ "redirect_uri" => "VARCHAR(2000)",
+ "expires" => "TIMESTAMP NOT NULL",
+ "scope" => "VARCHAR(4000)",
+ "id_token" => "VARCHAR(1000)"
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("authorization_code")
+ )
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ ),
+ "oauth_refresh_tokens" => array(
+ "cols" => array(
+ "refresh_token" => "VARCHAR(40) NOT NULL",
+ "client_id" => "VARCHAR(80) NOT NULL",
+ "user_id" => "VARCHAR(80)",
+ "expires" => "TIMESTAMP NOT NULL",
+ "scope" => "VARCHAR(4000)"
+ ),
+ "keys" => array(
+ "primary" => array(
+ "" => array("refresh_token")
+ )
+ ),
+ "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
+ )
+ );
+
+ foreach ($tables as $table => $properties) {
+ // Migrate to quarantine
+ if ($table == 'quarantine') {
+ $stmt = $pdo->query("SHOW TABLES LIKE 'quarantaine'");
+ $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
+ if ($num_results != 0) {
+ $stmt = $pdo->query("SHOW TABLES LIKE 'quarantine'");
+ $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
+ if ($num_results == 0) {
+ $pdo->query("RENAME TABLE `quarantaine` TO `quarantine`");
+ }
+ }
+ }
+
+ // Migrate tls_enforce_* options
+ if ($table == 'mailbox') {
+ $stmt = $pdo->query("SHOW TABLES LIKE 'mailbox'");
+ $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
+ if ($num_results != 0) {
+ $stmt = $pdo->query("SHOW COLUMNS FROM `mailbox` LIKE '%tls_enforce%'");
+ $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
+ if ($num_results != 0) {
+ $stmt = $pdo->query("SELECT `username`, `tls_enforce_in`, `tls_enforce_out` FROM `mailbox`");
+ $tls_options_rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
+ while ($row = array_shift($tls_options_rows)) {
+ $tls_options[$row['username']] = array('tls_enforce_in' => $row['tls_enforce_in'], 'tls_enforce_out' => $row['tls_enforce_out']);
+ }
+ }
+ }
+ }
+
+ $stmt = $pdo->query("SHOW TABLES LIKE '" . $table . "'");
+ $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
+ if ($num_results != 0) {
+ $stmt = $pdo->prepare("SELECT CONCAT('ALTER TABLE `', `table_schema`, '`.', `table_name`, ' DROP FOREIGN KEY ', `constraint_name`, ';') AS `FKEY_DROP` FROM `information_schema`.`table_constraints`
+ WHERE `constraint_type` = 'FOREIGN KEY' AND `table_name` = :table;");
+ $stmt->execute(array(':table' => $table));
+ $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
+ while ($row = array_shift($rows)) {
+ $pdo->query($row['FKEY_DROP']);
+ }
+ foreach($properties['cols'] as $column => $type) {
+ $stmt = $pdo->query("SHOW COLUMNS FROM `" . $table . "` LIKE '" . $column . "'");
+ $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
+ if ($num_results == 0) {
+ if (strpos($type, 'AUTO_INCREMENT') !== false) {
+ $type = $type . ' PRIMARY KEY ';
+ // Adding an AUTO_INCREMENT key, need to drop primary keys first, if exists
+ $stmt = $pdo->query("SHOW KEYS FROM `" . $table . "` WHERE Key_name = 'PRIMARY'");
+ $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
+ if ($num_results != 0) {
+ $pdo->query("ALTER TABLE `" . $table . "` DROP PRIMARY KEY");
+ }
+ }
+ $pdo->query("ALTER TABLE `" . $table . "` ADD `" . $column . "` " . $type);
+ }
+ else {
+ $pdo->query("ALTER TABLE `" . $table . "` MODIFY COLUMN `" . $column . "` " . $type);
+ }
+ }
+ foreach($properties['keys'] as $key_type => $key_content) {
+ if (strtolower($key_type) == 'primary') {
+ foreach ($key_content as $key_values) {
+ $fields = "`" . implode("`, `", $key_values) . "`";
+ $stmt = $pdo->query("SHOW KEYS FROM `" . $table . "` WHERE Key_name = 'PRIMARY'");
+ $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
+ $is_drop = ($num_results != 0) ? "DROP PRIMARY KEY, " : "";
+ $pdo->query("ALTER TABLE `" . $table . "` " . $is_drop . "ADD PRIMARY KEY (" . $fields . ")");
+ }
+ }
+ if (strtolower($key_type) == 'key') {
+ foreach ($key_content as $key_name => $key_values) {
+ $fields = "`" . implode("`, `", $key_values) . "`";
+ $stmt = $pdo->query("SHOW KEYS FROM `" . $table . "` WHERE Key_name = '" . $key_name . "'");
+ $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
+ $is_drop = ($num_results != 0) ? "DROP INDEX `" . $key_name . "`, " : "";
+ $pdo->query("ALTER TABLE `" . $table . "` " . $is_drop . "ADD KEY `" . $key_name . "` (" . $fields . ")");
+ }
+ }
+ if (strtolower($key_type) == 'unique') {
+ foreach ($key_content as $key_name => $key_values) {
+ $fields = "`" . implode("`, `", $key_values) . "`";
+ $stmt = $pdo->query("SHOW KEYS FROM `" . $table . "` WHERE Key_name = '" . $key_name . "'");
+ $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
+ $is_drop = ($num_results != 0) ? "DROP INDEX `" . $key_name . "`, " : "";
+ $pdo->query("ALTER TABLE `" . $table . "` " . $is_drop . "ADD UNIQUE KEY `" . $key_name . "` (" . $fields . ")");
+ }
+ }
+ if (strtolower($key_type) == 'fkey') {
+ foreach ($key_content as $key_name => $key_values) {
+ $fields = "`" . implode("`, `", $key_values) . "`";
+ $stmt = $pdo->query("SHOW KEYS FROM `" . $table . "` WHERE Key_name = '" . $key_name . "'");
+ $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
+ if ($num_results != 0) {
+ $pdo->query("ALTER TABLE `" . $table . "` DROP INDEX `" . $key_name . "`");
+ }
+ @list($table_ref, $field_ref) = explode('.', $key_values['ref']);
+ $pdo->query("ALTER TABLE `" . $table . "` ADD FOREIGN KEY `" . $key_name . "` (" . $key_values['col'] . ") REFERENCES `" . $table_ref . "` (`" . $field_ref . "`)
+ ON DELETE " . $key_values['delete'] . " ON UPDATE " . $key_values['update']);
+ }
+ }
+ }
+ // Drop all vanished columns
+ $stmt = $pdo->query("SHOW COLUMNS FROM `" . $table . "`");
+ $cols_in_table = $stmt->fetchAll(PDO::FETCH_ASSOC);
+ while ($row = array_shift($cols_in_table)) {
+ if (!array_key_exists($row['Field'], $properties['cols'])) {
+ $pdo->query("ALTER TABLE `" . $table . "` DROP COLUMN `" . $row['Field'] . "`;");
+ }
+ }
+
+ // Step 1: Get all non-primary keys, that currently exist and those that should exist
+ $stmt = $pdo->query("SHOW KEYS FROM `" . $table . "` WHERE `Key_name` != 'PRIMARY'");
+ $keys_in_table = $stmt->fetchAll(PDO::FETCH_ASSOC);
+ $keys_to_exist = array();
+ if (isset($properties['keys']['unique']) && is_array($properties['keys']['unique'])) {
+ foreach ($properties['keys']['unique'] as $key_name => $key_values) {
+ $keys_to_exist[] = $key_name;
+ }
+ }
+ if (isset($properties['keys']['key']) && is_array($properties['keys']['key'])) {
+ foreach ($properties['keys']['key'] as $key_name => $key_values) {
+ $keys_to_exist[] = $key_name;
+ }
+ }
+ // Index for foreign key must exist
+ if (isset($properties['keys']['fkey']) && is_array($properties['keys']['fkey'])) {
+ foreach ($properties['keys']['fkey'] as $key_name => $key_values) {
+ $keys_to_exist[] = $key_name;
+ }
+ }
+ // Step 2: Drop all vanished indexes
+ while ($row = array_shift($keys_in_table)) {
+ if (!in_array($row['Key_name'], $keys_to_exist)) {
+ $pdo->query("ALTER TABLE `" . $table . "` DROP INDEX `" . $row['Key_name'] . "`");
+ }
+ }
+ // Step 3: Drop all vanished primary keys
+ if (!isset($properties['keys']['primary'])) {
+ $stmt = $pdo->query("SHOW KEYS FROM `" . $table . "` WHERE Key_name = 'PRIMARY'");
+ $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
+ if ($num_results != 0) {
+ $pdo->query("ALTER TABLE `" . $table . "` DROP PRIMARY KEY");
+ }
+ }
+ }
+ else {
+ // Create table if it is missing
+ $sql = "CREATE TABLE IF NOT EXISTS `" . $table . "` (";
+ foreach($properties['cols'] as $column => $type) {
+ $sql .= "`" . $column . "` " . $type . ",";
+ }
+ foreach($properties['keys'] as $key_type => $key_content) {
+ if (strtolower($key_type) == 'primary') {
+ foreach ($key_content as $key_values) {
+ $fields = "`" . implode("`, `", $key_values) . "`";
+ $sql .= "PRIMARY KEY (" . $fields . ")" . ",";
+ }
+ }
+ elseif (strtolower($key_type) == 'key') {
+ foreach ($key_content as $key_name => $key_values) {
+ $fields = "`" . implode("`, `", $key_values) . "`";
+ $sql .= "KEY `" . $key_name . "` (" . $fields . ")" . ",";
+ }
+ }
+ elseif (strtolower($key_type) == 'unique') {
+ foreach ($key_content as $key_name => $key_values) {
+ $fields = "`" . implode("`, `", $key_values) . "`";
+ $sql .= "UNIQUE KEY `" . $key_name . "` (" . $fields . ")" . ",";
+ }
+ }
+ elseif (strtolower($key_type) == 'fkey') {
+ foreach ($key_content as $key_name => $key_values) {
+ @list($table_ref, $field_ref) = explode('.', $key_values['ref']);
+ $sql .= "FOREIGN KEY `" . $key_name . "` (" . $key_values['col'] . ") REFERENCES `" . $table_ref . "` (`" . $field_ref . "`)
+ ON DELETE " . $key_values['delete'] . " ON UPDATE " . $key_values['update'] . ",";
+ }
+ }
+ }
+ $sql = rtrim($sql, ",");
+ $sql .= ") " . $properties['attr'];
+ $pdo->query($sql);
+ }
+ // Reset table attributes
+ $pdo->query("ALTER TABLE `" . $table . "` " . $properties['attr'] . ";");
+
+ }
+
+ // Recreate SQL views
+ foreach ($views as $view => $create) {
+ $pdo->query("DROP VIEW IF EXISTS `" . $view . "`;");
+ $pdo->query($create);
+ }
+
+ // 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`");
+ $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
+ if ($num_results == 0) {
+ $pdo->query("INSERT INTO `admin` (`username`, `password`, `superadmin`, `created`, `modified`, `active`)
+ VALUES ('admin', '{SSHA256}K8eVJ6YsZbQCfuJvSUbaQRLr0HPLz5rC9IAp0PAFl0tmNDBkMDc0NDAyOTAxN2Rk', 1, NOW(), NOW(), 1)");
+ $pdo->query("INSERT INTO `domain_admins` (`username`, `domain`, `created`, `active`)
+ SELECT `username`, 'ALL', NOW(), 1 FROM `admin`
+ WHERE superadmin='1' AND `username` NOT IN (SELECT `username` FROM `domain_admins`);");
+ $pdo->query("DELETE FROM `admin` WHERE `username` NOT IN (SELECT `username` FROM `domain_admins`);");
+ }
+ // Insert new DB schema version
+ $pdo->query("REPLACE INTO `versions` (`application`, `version`) VALUES ('db_schema', '" . $db_version . "');");
+
+ // Fix dangling domain admins
+ $pdo->query("DELETE FROM `admin` WHERE `superadmin` = 0 AND `username` NOT IN (SELECT `username`FROM `domain_admins`);");
+ $pdo->query("DELETE FROM `da_acl` WHERE `username` NOT IN (SELECT `username`FROM `domain_admins`);");
+
+ // Migrate attributes
+ // pushover
+ $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;");
+ $pdo->query("UPDATE `mailbox` SET `attributes` = JSON_SET(`attributes`, '$.relayhost', \"0\") WHERE JSON_VALUE(`attributes`, '$.relayhost') IS NULL;");
+ $pdo->query("UPDATE `mailbox` SET `attributes` = JSON_SET(`attributes`, '$.force_pw_update', \"0\") WHERE JSON_VALUE(`attributes`, '$.force_pw_update') IS NULL;");
+ $pdo->query("UPDATE `mailbox` SET `attributes` = JSON_SET(`attributes`, '$.sieve_access', \"1\") WHERE JSON_VALUE(`attributes`, '$.sieve_access') IS NULL;");
+ $pdo->query("UPDATE `mailbox` SET `attributes` = JSON_SET(`attributes`, '$.sogo_access', \"1\") WHERE JSON_VALUE(`attributes`, '$.sogo_access') IS NULL;");
+ $pdo->query("UPDATE `mailbox` SET `attributes` = JSON_SET(`attributes`, '$.imap_access', \"1\") WHERE JSON_VALUE(`attributes`, '$.imap_access') IS NULL;");
+ $pdo->query("UPDATE `mailbox` SET `attributes` = JSON_SET(`attributes`, '$.pop3_access', \"1\") WHERE JSON_VALUE(`attributes`, '$.pop3_access') IS NULL;");
+ $pdo->query("UPDATE `mailbox` SET `attributes` = JSON_SET(`attributes`, '$.smtp_access', \"1\") WHERE JSON_VALUE(`attributes`, '$.smtp_access') IS NULL;");
+ $pdo->query("UPDATE `mailbox` SET `attributes` = JSON_SET(`attributes`, '$.mailbox_format', \"maildir:\") WHERE JSON_VALUE(`attributes`, '$.mailbox_format') IS NULL;");
+ $pdo->query("UPDATE `mailbox` SET `attributes` = JSON_SET(`attributes`, '$.quarantine_notification', \"never\") WHERE JSON_VALUE(`attributes`, '$.quarantine_notification') IS NULL;");
+ $pdo->query("UPDATE `mailbox` SET `attributes` = JSON_SET(`attributes`, '$.quarantine_category', \"reject\") WHERE JSON_VALUE(`attributes`, '$.quarantine_category') IS NULL;");
+ foreach($tls_options as $tls_user => $tls_options) {
+ $stmt = $pdo->prepare("UPDATE `mailbox` SET `attributes` = JSON_SET(`attributes`, '$.tls_enforce_in', :tls_enforce_in),
+ `attributes` = JSON_SET(`attributes`, '$.tls_enforce_out', :tls_enforce_out)
+ WHERE `username` = :username");
+ $stmt->execute(array(':tls_enforce_in' => $tls_options['tls_enforce_in'], ':tls_enforce_out' => $tls_options['tls_enforce_out'], ':username' => $tls_user));
+ }
+ // Set tls_enforce_* if still missing (due to deleted attrs for example)
+ $pdo->query("UPDATE `mailbox` SET `attributes` = JSON_SET(`attributes`, '$.tls_enforce_out', \"1\") WHERE JSON_VALUE(`attributes`, '$.tls_enforce_out') IS NULL;");
+ $pdo->query("UPDATE `mailbox` SET `attributes` = JSON_SET(`attributes`, '$.tls_enforce_in', \"1\") WHERE JSON_VALUE(`attributes`, '$.tls_enforce_in') IS NULL;");
+ // Fix ACL
+ $pdo->query("INSERT INTO `user_acl` (`username`) SELECT `username` FROM `mailbox` WHERE `kind` = '' AND NOT EXISTS (SELECT `username` FROM `user_acl`);");
+ $pdo->query("INSERT INTO `da_acl` (`username`) SELECT DISTINCT `username` FROM `domain_admins` WHERE `username` != 'admin' AND NOT EXISTS (SELECT `username` FROM `da_acl`);");
+ // 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 {
+ $_SESSION['return'][] = array(
+ 'type' => 'success',
+ 'log' => array(__FUNCTION__),
+ 'msg' => 'db_init_complete'
+ );
+ }
+ }
+ catch (PDOException $e) {
+ if (php_sapi_name() == "cli") {
+ echo "DB initialization failed: " . print_r($e, true) . PHP_EOL;
+ } else {
+ $_SESSION['return'][] = array(
+ 'type' => 'danger',
+ 'log' => array(__FUNCTION__),
+ 'msg' => array('mysql_error', $e)
+ );
+ }
+ }
+}
+if (php_sapi_name() == "cli") {
+ include '/web/inc/vars.inc.php';
+ include '/web/inc/functions.docker.inc.php';
+ // $now = new DateTime();
+ // $mins = $now->getOffset() / 60;
+ // $sgn = ($mins < 0 ? -1 : 1);
+ // $mins = abs($mins);
+ // $hrs = floor($mins / 60);
+ // $mins -= $hrs * 60;
+ // $offset = sprintf('%+d:%02d', $hrs*$sgn, $mins);
+ $dsn = $database_type . ":unix_socket=" . $database_sock . ";dbname=" . $database_name;
+ $opt = [
+ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
+ PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
+ PDO::ATTR_EMULATE_PREPARES => false,
+ //PDO::MYSQL_ATTR_INIT_COMMAND => "SET time_zone = '" . $offset . "', group_concat_max_len = 3423543543;",
+ ];
+ $pdo = new PDO($dsn, $database_user, $database_pass, $opt);
+ $stmt = $pdo->query("SELECT COUNT('OK') AS OK_C FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'sogo_view' OR TABLE_NAME = '_sogo_static_view';");
+ $res = $stmt->fetch(PDO::FETCH_ASSOC);
+ if (intval($res['OK_C']) === 2) {
+ // Be more precise when replacing into _sogo_static_view, col orders may change
+ try {
+ $stmt = $pdo->query("REPLACE INTO _sogo_static_view (`c_uid`, `domain`, `c_name`, `c_password`, `c_cn`, `mail`, `aliases`, `ad_aliases`, `ext_acl`, `kind`, `multiple_bookings`)
+ SELECT `c_uid`, `domain`, `c_name`, `c_password`, `c_cn`, `mail`, `aliases`, `ad_aliases`, `ext_acl`, `kind`, `multiple_bookings` from sogo_view");
+ $stmt = $pdo->query("DELETE FROM _sogo_static_view WHERE `c_uid` NOT IN (SELECT `username` FROM `mailbox` WHERE `active` = '1');");
+ echo "Fixed _sogo_static_view" . PHP_EOL;
+ }
+ catch ( Exception $e ) {
+ // Dunno
+ }
+ }
+ try {
+ $m = new Memcached();
+ $m->addServer('memcached', 11211);
+ $m->flush();
+ echo "Cleaned up memcached". PHP_EOL;
+ }
+ catch ( Exception $e ) {
+ // Dunno
+ }
+ init_db_schema();
+}
diff --git a/mailcow/src/mailcow-dockerized/data/web/inc/sessions.inc.php b/mailcow/src/mailcow-dockerized/data/web/inc/sessions.inc.php
index 5c7ec71..1a33e76 100644
--- a/mailcow/src/mailcow-dockerized/data/web/inc/sessions.inc.php
+++ b/mailcow/src/mailcow-dockerized/data/web/inc/sessions.inc.php
@@ -1,140 +1,140 @@
-<?php
-// Start session
-if (session_status() !== PHP_SESSION_ACTIVE) {
- ini_set("session.cookie_httponly", 1);
- ini_set('session.gc_maxlifetime', $SESSION_LIFETIME);
-}
-
-if (isset($_SERVER['HTTP_X_FORWARDED_PROTO']) &&
- strtolower($_SERVER['HTTP_X_FORWARDED_PROTO']) == "https") {
- if (session_status() !== PHP_SESSION_ACTIVE) {
- ini_set("session.cookie_secure", 1);
- }
- $IS_HTTPS = true;
-}
-elseif (isset($_SERVER['HTTPS'])) {
- if (session_status() !== PHP_SESSION_ACTIVE) {
- ini_set("session.cookie_secure", 1);
- }
- $IS_HTTPS = true;
-}
-else {
- $IS_HTTPS = false;
-}
-
-if (session_status() !== PHP_SESSION_ACTIVE) {
- session_start();
-}
-
-if (!isset($_SESSION['CSRF']['TOKEN'])) {
- $_SESSION['CSRF']['TOKEN'] = bin2hex(random_bytes(32));
-}
-
-// Set session UA
-if (!isset($_SESSION['SESS_REMOTE_UA'])) {
- $_SESSION['SESS_REMOTE_UA'] = $_SERVER['HTTP_USER_AGENT'];
-}
-
-// Keep session active
-if (isset($_SESSION['LAST_ACTIVITY']) && (time() - $_SESSION['LAST_ACTIVITY'] > $SESSION_LIFETIME)) {
- session_unset();
- session_destroy();
-}
-$_SESSION['LAST_ACTIVITY'] = time();
-
-// API
-if (!empty($_SERVER['HTTP_X_API_KEY'])) {
- $stmt = $pdo->prepare("SELECT * FROM `api` WHERE `api_key` = :api_key AND `active` = '1';");
- $stmt->execute(array(
- ':api_key' => preg_replace('/[^a-zA-Z0-9-]/', '', $_SERVER['HTTP_X_API_KEY'])
- ));
- $api_return = $stmt->fetch(PDO::FETCH_ASSOC);
- if (!empty($api_return['api_key'])) {
- $skip_ip_check = ($api_return['skip_ip_check'] == 1);
- $remote = get_remote_ip(false);
- $allow_from = array_map('trim', preg_split( "/( |,|;|\n)/", $api_return['allow_from']));
- if ($skip_ip_check === true || ip_acl($remote, $allow_from)) {
- $_SESSION['mailcow_cc_username'] = 'API';
- $_SESSION['mailcow_cc_role'] = 'admin';
- $_SESSION['mailcow_cc_api'] = true;
- if ($api_return['access'] == 'rw') {
- $_SESSION['mailcow_cc_api_access'] = 'rw';
- }
- else {
- $_SESSION['mailcow_cc_api_access'] = 'ro';
- }
- }
- else {
- $redis->publish("F2B_CHANNEL", "mailcow UI: Invalid password for API_USER by " . $_SERVER['REMOTE_ADDR']);
- error_log("mailcow UI: Invalid password for " . $user . " by " . $_SERVER['REMOTE_ADDR']);
- http_response_code(401);
- echo json_encode(array(
- 'type' => 'error',
- 'msg' => 'api access denied for ip ' . $_SERVER['REMOTE_ADDR']
- ));
- unset($_POST);
- exit();
- }
- }
- else {
- $redis->publish("F2B_CHANNEL", "mailcow UI: Invalid password for API_USER by " . $_SERVER['REMOTE_ADDR']);
- error_log("mailcow UI: Invalid password for " . $user . " by " . $_SERVER['REMOTE_ADDR']);
- http_response_code(401);
- echo json_encode(array(
- 'type' => 'error',
- 'msg' => 'authentication failed'
- ));
- unset($_POST);
- exit();
- }
-}
-
-// Handle logouts
-if (isset($_POST["logout"])) {
- if (isset($_SESSION["dual-login"])) {
- $_SESSION["mailcow_cc_username"] = $_SESSION["dual-login"]["username"];
- $_SESSION["mailcow_cc_role"] = $_SESSION["dual-login"]["role"];
- unset($_SESSION["dual-login"]);
- header("Location: /mailbox");
- exit();
- }
- else {
- session_regenerate_id(true);
- session_unset();
- session_destroy();
- session_write_close();
- header("Location: /");
- }
-}
-
-// Check session
-function session_check() {
- if (isset($_SESSION['mailcow_cc_api']) && $_SESSION['mailcow_cc_api'] === true) {
- return true;
- }
- if (!isset($_SESSION['SESS_REMOTE_UA']) || ($_SESSION['SESS_REMOTE_UA'] != $_SERVER['HTTP_USER_AGENT'])) {
- $_SESSION['return'][] = array(
- 'type' => 'warning',
- 'msg' => 'session_ua'
- );
- return false;
- }
- if (!empty($_POST)) {
- if ($_SESSION['CSRF']['TOKEN'] != $_POST['csrf_token']) {
- $_SESSION['return'][] = array(
- 'type' => 'warning',
- 'msg' => 'session_token'
- );
- return false;
- }
- unset($_POST['csrf_token']);
- $_SESSION['CSRF']['TOKEN'] = bin2hex(random_bytes(32));
- $_SESSION['CSRF']['TIME'] = time();
- }
- return true;
-}
-
-if (isset($_SESSION['mailcow_cc_role']) && session_check() === false) {
- $_POST = array();
- $_FILES = array();
-}
+<?php
+// Start session
+if (session_status() !== PHP_SESSION_ACTIVE) {
+ ini_set("session.cookie_httponly", 1);
+ ini_set('session.gc_maxlifetime', $SESSION_LIFETIME);
+}
+
+if (isset($_SERVER['HTTP_X_FORWARDED_PROTO']) &&
+ strtolower($_SERVER['HTTP_X_FORWARDED_PROTO']) == "https") {
+ if (session_status() !== PHP_SESSION_ACTIVE) {
+ ini_set("session.cookie_secure", 1);
+ }
+ $IS_HTTPS = true;
+}
+elseif (isset($_SERVER['HTTPS'])) {
+ if (session_status() !== PHP_SESSION_ACTIVE) {
+ ini_set("session.cookie_secure", 1);
+ }
+ $IS_HTTPS = true;
+}
+else {
+ $IS_HTTPS = false;
+}
+
+if (session_status() !== PHP_SESSION_ACTIVE) {
+ session_start();
+}
+
+if (!isset($_SESSION['CSRF']['TOKEN'])) {
+ $_SESSION['CSRF']['TOKEN'] = bin2hex(random_bytes(32));
+}
+
+// Set session UA
+if (!isset($_SESSION['SESS_REMOTE_UA'])) {
+ $_SESSION['SESS_REMOTE_UA'] = $_SERVER['HTTP_USER_AGENT'];
+}
+
+// Keep session active
+if (isset($_SESSION['LAST_ACTIVITY']) && (time() - $_SESSION['LAST_ACTIVITY'] > $SESSION_LIFETIME)) {
+ session_unset();
+ session_destroy();
+}
+$_SESSION['LAST_ACTIVITY'] = time();
+
+// API
+if (!empty($_SERVER['HTTP_X_API_KEY'])) {
+ $stmt = $pdo->prepare("SELECT * FROM `api` WHERE `api_key` = :api_key AND `active` = '1';");
+ $stmt->execute(array(
+ ':api_key' => preg_replace('/[^a-zA-Z0-9-]/', '', $_SERVER['HTTP_X_API_KEY'])
+ ));
+ $api_return = $stmt->fetch(PDO::FETCH_ASSOC);
+ if (!empty($api_return['api_key'])) {
+ $skip_ip_check = ($api_return['skip_ip_check'] == 1);
+ $remote = get_remote_ip(false);
+ $allow_from = array_map('trim', preg_split( "/( |,|;|\n)/", $api_return['allow_from']));
+ if ($skip_ip_check === true || ip_acl($remote, $allow_from)) {
+ $_SESSION['mailcow_cc_username'] = 'API';
+ $_SESSION['mailcow_cc_role'] = 'admin';
+ $_SESSION['mailcow_cc_api'] = true;
+ if ($api_return['access'] == 'rw') {
+ $_SESSION['mailcow_cc_api_access'] = 'rw';
+ }
+ else {
+ $_SESSION['mailcow_cc_api_access'] = 'ro';
+ }
+ }
+ else {
+ $redis->publish("F2B_CHANNEL", "mailcow UI: Invalid password for API_USER by " . $_SERVER['REMOTE_ADDR']);
+ error_log("mailcow UI: Invalid password for " . $user . " by " . $_SERVER['REMOTE_ADDR']);
+ http_response_code(401);
+ echo json_encode(array(
+ 'type' => 'error',
+ 'msg' => 'api access denied for ip ' . $_SERVER['REMOTE_ADDR']
+ ));
+ unset($_POST);
+ exit();
+ }
+ }
+ else {
+ $redis->publish("F2B_CHANNEL", "mailcow UI: Invalid password for API_USER by " . $_SERVER['REMOTE_ADDR']);
+ error_log("mailcow UI: Invalid password for " . $user . " by " . $_SERVER['REMOTE_ADDR']);
+ http_response_code(401);
+ echo json_encode(array(
+ 'type' => 'error',
+ 'msg' => 'authentication failed'
+ ));
+ unset($_POST);
+ exit();
+ }
+}
+
+// Handle logouts
+if (isset($_POST["logout"])) {
+ if (isset($_SESSION["dual-login"])) {
+ $_SESSION["mailcow_cc_username"] = $_SESSION["dual-login"]["username"];
+ $_SESSION["mailcow_cc_role"] = $_SESSION["dual-login"]["role"];
+ unset($_SESSION["dual-login"]);
+ header("Location: /mailbox");
+ exit();
+ }
+ else {
+ session_regenerate_id(true);
+ session_unset();
+ session_destroy();
+ session_write_close();
+ header("Location: /");
+ }
+}
+
+// Check session
+function session_check() {
+ if (isset($_SESSION['mailcow_cc_api']) && $_SESSION['mailcow_cc_api'] === true) {
+ return true;
+ }
+ if (!isset($_SESSION['SESS_REMOTE_UA']) || ($_SESSION['SESS_REMOTE_UA'] != $_SERVER['HTTP_USER_AGENT'])) {
+ $_SESSION['return'][] = array(
+ 'type' => 'warning',
+ 'msg' => 'session_ua'
+ );
+ return false;
+ }
+ if (!empty($_POST)) {
+ if ($_SESSION['CSRF']['TOKEN'] != $_POST['csrf_token']) {
+ $_SESSION['return'][] = array(
+ 'type' => 'warning',
+ 'msg' => 'session_token'
+ );
+ return false;
+ }
+ unset($_POST['csrf_token']);
+ $_SESSION['CSRF']['TOKEN'] = bin2hex(random_bytes(32));
+ $_SESSION['CSRF']['TIME'] = time();
+ }
+ return true;
+}
+
+if (isset($_SESSION['mailcow_cc_role']) && session_check() === false) {
+ $_POST = array();
+ $_FILES = array();
+}
diff --git a/mailcow/src/mailcow-dockerized/data/web/inc/triggers.inc.php b/mailcow/src/mailcow-dockerized/data/web/inc/triggers.inc.php
index aec043e..6922429 100644
--- a/mailcow/src/mailcow-dockerized/data/web/inc/triggers.inc.php
+++ b/mailcow/src/mailcow-dockerized/data/web/inc/triggers.inc.php
@@ -1,4 +1,15 @@
<?php
+// SSO Domain Admin
+if (!empty($_GET['sso_token'])) {
+ $username = domain_admin_sso('check', $_GET['sso_token']);
+
+ if ($username !== false) {
+ $_SESSION['mailcow_cc_username'] = $username;
+ $_SESSION['mailcow_cc_role'] = 'domainadmin';
+ header('Location: /mailbox');
+ }
+}
+
if (isset($_POST["verify_tfa_login"])) {
if (verify_tfa_login($_SESSION['pending_mailcow_cc_username'], $_POST)) {
$_SESSION['mailcow_cc_username'] = $_SESSION['pending_mailcow_cc_username'];
@@ -6,7 +17,7 @@
unset($_SESSION['pending_mailcow_cc_username']);
unset($_SESSION['pending_mailcow_cc_role']);
unset($_SESSION['pending_tfa_methods']);
-
+
header("Location: /user");
} else {
unset($_SESSION['pending_mailcow_cc_username']);
@@ -34,7 +45,7 @@
if (isset($_POST["login_user"]) && isset($_POST["pass_user"])) {
$login_user = strtolower(trim($_POST["login_user"]));
$as = check_login($login_user, $_POST["pass_user"]);
-
+
if ($as == "admin") {
$_SESSION['mailcow_cc_username'] = $login_user;
$_SESSION['mailcow_cc_role'] = "admin";
@@ -52,7 +63,7 @@
unset($_SESSION['index_query_string']);
if (in_array('mobileconfig', $http_parameters)) {
if (in_array('only_email', $http_parameters)) {
- header("Location: /mobileconfig.php?email_only");
+ header("Location: /mobileconfig.php?only_email");
die();
}
header("Location: /mobileconfig.php");
@@ -110,9 +121,13 @@
if ($_FILES['main_logo']['error'] == 0) {
customize('add', 'main_logo', $_FILES);
}
+ if ($_FILES['main_logo_dark']['error'] == 0) {
+ customize('add', 'main_logo_dark', $_FILES);
+ }
}
if (isset($_POST["reset_main_logo"])) {
customize('delete', 'main_logo');
+ customize('delete', 'main_logo_dark');
}
// Some actions will not be available via API
if (isset($_POST["license_validate_now"])) {
diff --git a/mailcow/src/mailcow-dockerized/data/web/inc/vars.inc.php b/mailcow/src/mailcow-dockerized/data/web/inc/vars.inc.php
index 4f09d5f..3f72693 100644
--- a/mailcow/src/mailcow-dockerized/data/web/inc/vars.inc.php
+++ b/mailcow/src/mailcow-dockerized/data/web/inc/vars.inc.php
@@ -90,6 +90,7 @@
'es-es' => 'Español (Spanish)',
'fi-fi' => 'Suomi (Finish)',
'fr-fr' => 'Français (French)',
+ 'gr-gr' => 'Ελληνικά (Greek)',
'hu-hu' => 'Magyar (Hungarian)',
'it-it' => 'Italiano (Italian)',
'ko-kr' => '한국어 (Korean)',
@@ -99,6 +100,7 @@
'pt-pt' => 'Português (Portuguese)',
'ro-ro' => 'Română (Romanian)',
'ru-ru' => 'Pусский (Russian)',
+ 'si-si' => 'Slovenščina (Slovenian)',
'sk-sk' => 'Slovenčina (Slovak)',
'sv-se' => 'Svenska (Swedish)',
'tr-tr' => 'Türkçe (Turkish)',
@@ -124,7 +126,7 @@
);
// Rows until pagination begins
-$PAGINATION_SIZE = 20;
+$PAGINATION_SIZE = 25;
// Default number of rows/lines to display (log table)
$LOG_LINES = 1000;