USE `vokesscp`;


##############################################################################################################################################################################################
# Fail2Ban                                                                                                                                                                                   #
##############################################################################################################################################################################################


CREATE TABLE IF NOT EXISTS `vokesscp`.`fail2ban` (
    `id`                                    BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `ipAddress`                             VARCHAR(39) BINARY NOT NULL,
    `blockDate`                             DATETIME NOT NULL,
    `jail`                                  VARCHAR(255) NOT NULL,
    `protocol`                              VARCHAR(255) NOT NULL,
    `ports`                                 VARCHAR(255) NOT NULL,
    PRIMARY KEY `pidx_id` (`id`),
    INDEX `idx_ipAddress` (`ipAddress`)
) ENGINE=InnoDB COMMENT='Fail2Ban' CHARACTER SET=utf8 COLLATE=utf8_bin;


DELIMITER |
DROP TRIGGER IF EXISTS `vokesscp`.`AFTER_INSERT_fail2ban`
|
CREATE
    DEFINER = 'vsscp'@'localhost'
    TRIGGER `vokesscp`.`AFTER_INSERT_fail2ban` AFTER INSERT ON `vokesscp`.`fail2ban`
    FOR EACH ROW BEGIN
        DECLARE `requiredHitsForBlock` SMALLINT(5) UNSIGNED;
        DECLARE `requiredHitsForBlockInPeriod` SMALLINT(5) UNSIGNED;

        SELECT CAST(`value` AS UNSIGNED INTEGER) INTO `requiredHitsForBlock` FROM `vokesscp`.`configuration` WHERE `key` = 'fail2banRequiredHitsForBlock';
        SELECT CAST(`value` AS UNSIGNED INTEGER) INTO `requiredHitsForBlockInPeriod` FROM `vokesscp`.`configuration` WHERE `key` = 'fail2banRequiredHitsForBlockInPeriod';

	    IF (SELECT COUNT(`id`) FROM `vokesscp`.`fail2ban` WHERE `ipAddress` = NEW.`ipAddress` AND `blockDate` BETWEEN DATE_SUB(UTC_TIMESTAMP(), INTERVAL `requiredHitsForBlockInPeriod` DAY) AND UTC_TIMESTAMP()) >= `requiredHitsForBlock` AND (SELECT COUNT(`ipAddress`) FROM `vokesscp`.`iptables` WHERE `ipAddress` = NEW.`ipAddress`) = 0 THEN
            INSERT IGNORE INTO `vokesscp`.`iptables` (`ipAddress`, `blockDate`, `type`) VALUES (NEW.`ipAddress`, UTC_TIMESTAMP(), 'fail2ban');
	    END IF;
    END;
|
DELIMITER ;


GRANT INSERT, UPDATE, SELECT ON `vokesscp`.`fail2ban`                                    TO 'vsscp_fail2ba'@'localhost' IDENTIFIED BY 'P8B97mwqC94HeTcbuq7p';
FLUSH PRIVILEGES;

