Attachment Logging
From ESM Wiki
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.
