diff --git a/documentation/database/patches/patch-0003-add-host-mda.sql b/documentation/database/patches/patch-0003-add-host-mda.sql new file mode 100644 index 0000000..7642d19 --- /dev/null +++ b/documentation/database/patches/patch-0003-add-host-mda.sql @@ -0,0 +1,30 @@ +CREATE TABLE `{$prefix}host` ( + `host_ID` INT UNSIGNED NOT NULL AUTO_INCREMENT, + `host_ipv4` INT UNSIGNED NOT NULL COMMENT 'main IPv4 stored with INET_ATON()', + `host_hostname` VARCHAR(128) NOT NULL COMMENT 'hostname', + `host_description` TEXT, + PRIMARY KEY (`host_ID`) +) ENGINE = InnoDB + COMMENT = 'a generic host'; + +INSERT INTO `{$prefix}host` + (`host_ID`, `host_ipv4`, `host_hostname`, `host_description`) +VALUES (NULL, INET_ATON('127.0.0.1'), 'localhost', 'This is the default server.\r\n\r\nPlease set your absolute IP address and your hostname.'); + +CREATE TABLE `{$prefix}mta` ( + `mta_ID` INT(10) unsigned NOT NULL AUTO_INCREMENT, + `host_ID` INT(10) unsigned NOT NULL, + PRIMARY KEY (`mta_ID`), + KEY `host_ID` (`host_ID`), + CONSTRAINT `mta_ibfk_1` FOREIGN KEY (`host_ID`) REFERENCES `{$prefix}host` (`host_ID`) ON DELETE CASCADE +) ENGINE = InnoDB + COMMENT = 'known mail delivery agents e.g. Postfix instances'; + +INSERT INTO `{$prefix}mta` + ( `mta_ID`, `host_ID` ) +VALUES ( NULL, 1 ); + +ALTER TABLE `{$prefix}domain` + ADD `mta_ID` INT UNSIGNED NULL AFTER `domain_parent`, + ADD INDEX (`mta_ID`), + ADD CONSTRAINT `domain_ibfk_mta` FOREIGN KEY (`mta_ID`) REFERENCES `{$prefix}mta`(`mta_ID`) ON DELETE RESTRICT ON UPDATE RESTRICT; diff --git a/documentation/etc/postfix/mysql/mysql-domains.cf b/documentation/etc/postfix/mysql/mysql-domains.cf index c9715bf..0741f27 100644 --- a/documentation/etc/postfix/mysql/mysql-domains.cf +++ b/documentation/etc/postfix/mysql/mysql-domains.cf @@ -1,19 +1,25 @@ ##################################################################### # This Postfix configuration was provided from the KISS Hosting Panel # # Project homepage: # https://gitpull.it/project/profile/15/ # # Happy hacking! # # -- Valerio Bozzolan - Thu 10 Apr 2016 03:34:07 AM CEST ##################################################################### # # This configuration file is read by 'virtual_mailbox_domains'. # Read 'main.cf' for further information. +# +# IMPORTANT: make sure that in your `mta` table you have at least one row +# and that its ID specified here is the same. +# +# Note: The mta_ID is useful if in the future you want to handle +# multiple mailservers with a single shared authentication database. user = {$YOUR_POSTFIX_READONLY_MYSQL_USERNAME} password = {$YOUR_POSTFIX_READONLY_MYSQL_PASSWORD} dbname = {$YOUR_DATABASE_NAME} -query = SELECT `domain_name` AS virtual FROM `{$YOUR_DATABASE_PREFIX}domain` WHERE `domain_name` = '%s' AND `domain_active` = 1 +query = SELECT `domain_name` AS virtual FROM `{$YOUR_DATABASE_PREFIX}domain` WHERE `domain_name` = '%s' AND `domain_active` = 1 AND `mta_ID` = {$YOUR_DEFAULT_MTA_ID} hosts = 127.0.0.1