Attachment Logging

From ESM Wiki
Jump to: navigation, search

Contents

Introduction

This document provides an example implementation of email message attachment logging functionality. Implemented use-case is the following:

  • save message data on file system;
  • save message metadata (smtp sender and recipients, size, etc.) in MySQL database reusing QReview's structures;
  • save all attachments data on file system;
  • save each attachments metadata (mime type, file name, etc.) in MySQL database;

Database structure

QReview's structure is reused. Additional table `message_attachment` is added. Bellow is the list of used tables.

CREATE TABLE `message` (

        -- Record ID.
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    
        -- Local-part of sender email address.
    `sender_local` VARCHAR(255) DEFAULT NULL,
    
        -- Domain-part of sender email address. Specified as reverse string ('domain.com' should be stored as 'moc.miamod').
    `sender_domain` VARCHAR(255) DEFAULT NULL,
    
        -- Time when the message was stored. It is always set to NOW() during insertion or the record.
    `mpp_time_stored` DATETIME DEFAULT NULL,
    
        -- Result of gethostbyname() on storing machine
    `mpp_host_name` VARCHAR(255) DEFAULT NULL,
    
        -- Name of MPP policy group that this message was scanned by
    `mpp_group_name` VARCHAR(255) DEFAULT NULL,
    
    `mpp_store_type` ENUM ('Quarantine', 'Archive') NOT NULL,
    
    `mpp_scan_state_virus`         BOOL NOT NULL DEFAULT 0,
    `mpp_scan_state_spam`          BOOL NOT NULL DEFAULT 0,
    `mpp_scan_state_harass`        BOOL NOT NULL DEFAULT 0,
    `mpp_scan_state_unauthorized`  BOOL NOT NULL DEFAULT 0,
    `mpp_scan_state_error`         BOOL NOT NULL DEFAULT 0,
    `mpp_scan_state_max_recursion` BOOL NOT NULL DEFAULT 0,
    `mpp_scan_state_max_size`      BOOL NOT NULL DEFAULT 0,
    `mpp_scan_state_acl`           BOOL NOT NULL DEFAULT 0,
    `mpp_scan_state_encrypted`     BOOL NOT NULL DEFAULT 0,
    `mpp_scan_state_malformed`     BOOL NOT NULL DEFAULT 0,
    `mpp_scan_state_user_bl`       BOOL NOT NULL DEFAULT 0,
    
    `mpp_scan_state_spf` ENUM ('invalid', 'neutral', 'pass', 'fail', 'softfail', 'none', 'temperror', 'permerror') DEFAULT NULL,
                               
        -- Subject header value extracted from message content.
    `content_subject` TEXT CHARACTER SET utf8,
    
        -- Message-Id header value extracted from message content.
    `content_messageid` VARCHAR(1024) DEFAULT NULL,
    
    -- Message size
    `content_size` int(10) unsigned NOT NULL default '0',
    
        -- URL of file that contain message content. If NULL the content is in message_content table.
    `url_content` VARCHAR(255) DEFAULT NULL,
    
        -- Flag that indicates whether MPP has finished to write this record and its dependencies
    `transaction_committed` BOOL NOT NULL DEFAULT 0,
    
    PRIMARY KEY(`id`)
) CHARSET=latin1 TYPE=InnoDB;

--
-- Recipient for a message. Message can have multiple recipients.
--
CREATE TABLE `message_recipient` (

        -- Record ID.
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
        
        -- Reference to message record that recipient bellongs to.
    `message_id` INT UNSIGNED NOT NULL,
    
        -- Local-part of recipient email address.
    `recipient_local` VARCHAR(255) DEFAULT NULL,
    
        -- Domain-part of recipient email address. Specified as reverse string ('domain.com' should be stored as 'moc.miamod').
    `recipient_domain` VARCHAR(255) DEFAULT NULL,
    
    PRIMARY KEY (`id`),
    FOREIGN KEY (`message_id`) REFERENCES `message`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
) CHARSET=latin1 TYPE=INNODB;

--
-- Message attachment
--
CREATE TABLE `message_attachment` (

    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `message_id` INT UNSIGNED NOT NULL,
    
    `mime_type` VARCHAR(255) DEFAULT NULL,
    `file_name` VARCHAR(255) CHARACTER SET utf8 DEFAULT NULL,
    `size`		INT UNSIGNED NOT NULL DEFAULT 0,
    `url`		VARCHAR(1024) DEFAULT NULL,
    
    PRIMARY KEY (`id`),
    FOREIGN KEY (`message_id`) REFERENCES `message`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
) CHARSET=latin1 TYPE=INNODB;

ESM config

ESM config defines processing logic. Full listing is provided bellow and detailed step-by-step explanation is provided in subsequent sections. To use this listing put it into a /usr/local/MPP/esm.local/attachment_logging.xml file.

<?xml version="1.0"?>
<mppd>
    <common>
        <archive_engines>
            file.save_message_data
            mysql.save_message
            mysql.save_message_smtp_recipient
            
            file.save_attachment_data
            mysql.save_attachment
            
            mysql.save_message_commit
        </archive_engines>
    </common>

    <engines>
    
        <mysql>
            <connection xpath="/mppd/common/attachment_logging_mysql_connection"/>
            
            <query id="save_message">
                <template>
                    INSERT INTO `message` (   `sender_local`, `sender_domain`, `mpp_time_stored`, `mpp_host_name`, `mpp_group_name`
                                           ,  `mpp_store_type`, `content_subject`, `content_messageid`
                                           ,  `content_size`, `url_content`)
                    VALUES (  '${mysql_escape $sender.local}', REVERSE('${mysql_escape $sender.domain}'), NOW(), '${mysql_escape $gethostname}', '${mysql_escape $group}'
                            , 'Archive', '${mysql_escape $headers.subject}', '${mysql_escape $headers.message_id}'
                            , ${size $data}, '${mysql_escape $engines.file.save_message_data.full.path}')
                
                </template>
                <result id="id">
                    <set>$mysql_insert_id</set>
                </result>
            </query>
            
            <query id="save_message_smtp_recipient">
                <template>
                    INSERT INTO `message_recipient` (`message_id`, `recipient_local`, `recipient_domain`)
                    VALUES ($engines.mysql.save_message.id, '${mysql_escape $recipient.local}', REVERSE('${mysql_escape $recipient.domain}'))
                </template>
            </query>
            
            <query id="save_attachment">
                <template>
                    INSERT INTO `message_attachment` (`message_id`, `mime_type`, `file_name`, `size`, `url`)
                    VALUES ( $engines.mysql.save_message.id, '${mysql_escape $attachment.file_name}', '${mysql_escape $attachment.mime_type}'
                           , ${size $attachment}, '${mysql_escape file://$engines.file.save_attachment_data.full.path}')
                </template>
            </query>
            
            <query id="save_message_commit">
                <template>
                    UPDATE `message` SET `transaction_committed`=1
                    WHERE `id`=$engines.mysql.save_message.id
                </template>
            </query>

        </mysql>
        
        <file>
            <base>
                <path>/var/MPP/archive</path>
            </base>
            <tmp>
                <path>/var/MPP/archive/tmp/mppd_$pid$time6$counter16</path>
            </tmp>
        
            <save id="save_message_data">
                <relative>
                    <path>messages/${year}_${month}_${day}/${hour}_${minute}/$time6$counter16</path>
                </relative>
                <data>$data</data>
            </save>

            <save id="save_attachment_data">
                <relative>
                    <path>attachments/${year}_${month}_${day}/${hour}_${minute}/$time6$counter16</path>
                </relative>
                <data>$attachment</data>
            </save>

        </file>
    </engines>
</mppd>

Main config

Main config contains other not-ESM options and references ESM config. Full listing is provided bellow. To use this listing put it into /usr/local/MPP/mppd.conf.xml file.

<?xml version="1.0"?>
<mppd>
    <common>
        <passwords>/usr/local/MPP/passwords.txt</passwords>
        <email_server_in_protocol>lmtp</email_server_in_protocol>
        
        <attachment_logging_mysql_connection>
            <database>archive</database>
            <defaults_file>/etc/my.cnf</defaults_file>
            <user>mppd</user>
            <password_id>mysql_mppd_password</password_id>
        </attachment_logging_mysql_connection>
        
        <archive_engines file="esm.local/attachment_logging.xml" xpath="/mppd/common/archive_engines"/>
        
    </common>
    
    <engines file="esm.local/attachment_logging.xml" xpath="/mppd/engines"/>
</mppd>

Main config is suitable for running mppd with postfix.

Password file

Passwords file contains key-value pair of password id/value. It must be located at /usr/local/MPP/passwords.txt and must contain an entry with "mysql_mppd_password" id. This entry defines a password for mppd user in MySQL database. For example:

mysql_mppd_password      here_goes_password

MySQL connection settings

MySQL connections setting are defined under <attachment_logging_mysql_connection> option. Option <password_id> references a password from passwords file.

ESM config reference

ESM config is referenced with two options <archive_engines> and <engines>. MPP loads data from referenced files and options.

Personal tools