Contents 1 Design Concepts 1.1 Archive Considerations 1.1.1 Email Archival Formats 1.1.2 Database Replication 1.2 Archival Architectures 1.2.1 Simple Archival Architecture 1.2.2 Hierarchical Archive Architecture 1.2.2.1 Archive Agent 1.2.2.2 Aggregator 1.2.3 Small Site Email Filtering 1.2.4 Large Scale Email Filtering 1.3 MySQL Considerations 1.3.1 Commercial Licenses 1.3.2 Replication 1.3.3 Max Packet Size and Max Rows 1.3.4 Centralized Databases 1.3.5 Message Tracking 1.3.6 Shared Databases 2 Appendix 2.1 Boorex Engine Guide 2.1.1 Introduction 2.1.2 Use-case View 2.1.2.1 Define Boorex Engine 2.1.2.2 Use Boorex Engine 2.1.3 Structural View 2.1.3.1 Boorex Node Structure 2.2 LDAP Configuration Guide 2.2.1 Overview 2.2.2 LDAP Caching 2.2.3 Configuring LDAP Connections 2.2.4 MPP LDAP Schema 2.2.5 MPP/LDAP sample how-to 2.3 SNMP 2.3.1 Introduction 2.3.2 Requirements 2.3.3 Setup of NET-SNMP 2.3.4 Supported SNMP Variables 2.4 Obsolete Commands from MPPv3 2.5 Custom SQL Queries 2.5.1 Explanation 2.5.2 Use Case View 2.5.2.1 Define MySQL Engine 2.5.2.2 Define Queries 2.5.2.3 Specify Query Template 2.5.2.4 Specify Query Results 2.5.2.5 Use MySQL Engine and Queries 2.5.2.6 Use Query Results 2.5.2.7 Handle Multi•Value Variables 2.5.3 Structural View 2.5.3.1 MySQL Node Structure 2.5.3.2 Query Node Structure 2.5.3.3 Query Template 2.5.3.4 Query Result 2.5.3.5 Case Condition Format 2.5.4 Real-Life Solutions 2.5.4.1 MPP WBL 2.5.4.2 AmavisD WBL 2.5.4.3 MPP ACL 2.5.4.4 Combination of MPP and AmavisD ACL and WBL 2.5.5 Security Notes 2.5.6 Known Issues

Design Concepts

This section will provide some basic design concepts and hints for success with MPP.

(back to the top)

Archive Considerations

MPP provides a complete, standards based solution for email archives. MPP archives email from a number of sources including other instances MPP, Postfix always_bcc and MS Exchange 2003 and 2007 journal reports. MPP imports email from MIME encoded files and IMAP message stores.

(back to the top)

Email Archival Formats

Mailspect Archive stores email in a hybrid format (metadata) with metadata in a MySQL database and the actual emails are stored as files on the file system. MPP has macros available to scale storage of files on the file system. These macros allow MPP to create random numbers of directories, 16 or 256, which can be nested (/usr/local/MPP/archives%rnd256%/%rnd256%) to avoid file system limitations on the number of files that may be stored in a directory.

When the same email is archived for many users only a single copy is stored in a MySQL database, however, when files are used, a copied of the email is stored for each user.

NOTE: Mailspect will only support meta-data storage of email for archive.

(back to the top)

Database Replication

It is highly recommended to use database replication with multiple slave databases. MPP is cluster aware, as such the archive review application supports separate read and write channels. It is recommended for reads to be performed on slaves while mppd writes to a database master.

(back to the top)

Archival Architectures

MPP archival systems can be complex with multiple archival agents speaking to an aggregator, or they can be simple, with a single archival agent and a single database.

(back to the top)

Simple Archival Architecture

In this model a single instance of MPP is archiving to one message store.

(back to the top)

Hierarchical Archive Architecture

In a hierarchical setup there is a central MPP instance that writes to the message store and accepts read requests from remote instances of MPP that archive with the smtp:// method. MPP has special EMSTP extensions for this architecture that are described in the MTA Integration section above. It is highly recommended to consider this architecture for MPP archive applications. Even if you have a single server it is possible to have two instances of MPP running to support this architecture.

(back to the top)

Archive Agent

The archive agent is an instance of MPP running in conjunction with an email server or SMTP relay. If there is a single archival agent it can connect directly to the MySQL database.

(back to the top)

Aggregator

If there are multiple archive agents MPP can use ESMTP to transport archived email to an instance of MPP that functions as an aggregator of remote archival instances. The aggregator will hand traffic to a queued MPP process that stores the email into a database, file system or hybrid message store.

(back to the top)

Small Site Email Filtering

Mailspect Defense is perfect for virus and spam email filtering on the SMTP gateway or email server. It is highly recommended to use our Virtual Appliance as it is completely pre-configured.

(back to the top)

Large Scale Email Filtering

Mailspect Defense is especially well suited to large-scale email filtering applications. Here are a few design tips to scale Mailspect in large environments.

• Here is an article that describes an optimal scaling model for Mailspect Defense.

 In this model we leverage LDAP and MySQL for dynamic provisioning and settings changes and use hierarchical filtering layers separated by a load balancer.  
 We recommend Postfix to handle SMTP relaying and transports.  We recommend to store transports in LDAP or MySQL for dynamic provisioning. 

There are two levels of servers, both using a load balancer. The top layer handles the initial SMTP transaction, transports and relays. Each server has an identical configuration. All servers speak through a load balancer to a battery of scanning servers that serve as pre-queue Postfix policy servers and post-queue content filters. Each scanning server has an identical configuration and shares common configuration databases.

• Use shared SQL servers for all front-end’s. These SQL servers should store client-host blacklists, access-control lists, and per-user black lists, Greylist.

• Use hierarchical quarantine storage to reduce direct connections between mppd processing agents and message stores. This is achieved with remote mppd instances storing messages with smtp:// method and a central mppd listening on an smtp socket defined in Advanced > MTA Settings with Postfix configured and SMTP Extension set to messagestore. It is important to note that there need not be an instance of Postfix running on the central mppd as mppd provides its own smtp instance for this purpose.

(back to the top)

MySQL Considerations

MySQL Configuration Guide

Commercial Licenses

If you are using archive it is highly recommended to have a commercial license of MySQL server to have access to their management tools. Don’t reinvent the wheel with things like replication, backups, etc. as these are all solved problems in the MySQL world. Leverage the community.

(back to the top)

Replication

It is highly recommended to use MySQL replication for archive and large spam quarantine applications. Replication doesn’t help much for spam settings databases, but it will make a huge difference for archive and spam quarantine. MPP Manager is replication aware and as such supports separate read and write channels. In replication architecture mppd should write to the master and MPP Manager should be configured to read from slaves. Simultaneous reads and writes on the same database, especially if it is too big to fit in memory will clobber performance and replication solves this problem.

(back to the top)

Max Packet Size and Max Rows

Make sure to set the max rows to a large number if you are using archival. We have set this for you in the virtual appliance. Set the max packet size to the largest attachment size that you want to store.

(back to the top)

Centralized Databases

If you have multiple mppd filtering servers use a centralized database for spam settings, white and black lists, client-host blacklists and access-control lists. All instances should share the same database for maximum efficiency.

(back to the top)

Message Tracking

Message tracking is a powerful feature for MPP and it should have its own dedicated database.

(back to the top)

Shared Databases

Do not share the same database for spam quarantine, archive and message tracking. It is ok to share the same DB for spam quarantine, acl, wbl, spam settings and client-host WBL.

(back to the top)

Appendix

This section provides some technical details for your information. Perl Compatible Regular Expression Basics

NOTE: PCRE is the original regular expression syntax supported by MPP; however, in version 4 we have significantly expanded our regular expression capabilities. This guide is provided for legacy compatibility. See the next section, Boorex Expressions, to learn about the expanded regular expression capabilities of MPP. Regular expressions are defined in file(s), one per line. Lines beginning with # are ignored at reading/parsing.

The format of regular expression is

/regular expression/{flags}

{flags} are optional and they can be combined.

(back to the top)

Available flags are

i	Search case insensitive.
m	Match on multiple lines, thus ^ and $ are interpreted as the start and end of the entire string, not of a single line.
s	A dot in an expression matches newlines too, which is normally not the case.

Examples for flags

/regular expression/	search case sensitive
/regular expression/i	search case insensitive
/regular expression/im	search case insensitive on multiples lines of the string, with ^ and $ matching begging and end of string
/regular expression/is	search case insensitive, having dot (.) matching newlines too

Important- When searching with regular expressions through headers or body, the headers or body are searched as single string, not line by line. Thus using of "s" or "m" flags is useful.

Regular expressions Meta characters

\	Quote the next meta character
^	Match the beginning of the line
.	Match any character (except newline)
$	Match the end of the line (or before newline at the end)
|	Alternation
()	Grouping
[]	Character class

(back to the top)

Regular expressions standard quantifiers

*	Match 0 or more times
"+"	Match 1 or more times
?	Match 1 or 0 times
{n}	Match exactly n times
{n,}	Match at least n times
{n,m}	Match at least n but not more than m times

(back to the top)

Examples for header regular expressions

/From:.*ebay.com.*/is - search case insensitive for From: header with anyone@ebay.com as sender

/From:.*ebay.com.*Return-Path:.*seb\@ns2\.shanghai\.fr.*|Return-Path:.*seb\@ns2\.shanghai\.fr.*From:.*ebay.com.*/is - search case insensitive for anyone@ebay.com and Return-Path: seb@ns2\.shanghai.fr considering that From: and Return-Path: might occur in different order.

NOTE: Using | enables internal regexp alternation. reg1 | reg 2 - search reg1 or reg2

Examples for attachment names regular expressions:

/(.*\.pif)|(.*\.scr)/i - search case insensitive for PIF or SCR extensions in attachment name

Examples for content regular expressions:

/secret/i - search for "secret" word case insensitive </pre>

(back to the top)

Boorex Engine Guide

MPP4 introduces an entirely new content filter engine with greatly expanded capabilities for construction of regular expressions and sharing of expressions amongst policy groups. This section contains brief description of the features from usage point of view. Entities referenced in this section are described in “Structural View” section. The Boorex Engine is not yet fully implemented in the MPP GUI thus more granular detail is provided for editing the mppd.conf.xml file directly. This is for advanced users only.

(back to the top)

Introduction

Boorex Engine incorporates a list of pattern expressions that are matched against input text with “first-match” principle and returns an ID of matched expression. It is based on Boost Regex library [1] and supports all expression formats that this library does. The engine can be used by other MPP functionality (Content Filter, Spam Traps, etc.) whenever pattern matching is required and supported.

(back to the top)

Use-case View

This section contains brief description of the features from usage point of view. Entities referenced in this section are described in “Structural View” section.

(back to the top)

Define Boorex Engine

Administrator defines an instance of Boorex Engine in mppd.conf.xml file under

/mppd/engines node with node name <boorex>. ID of the engine is defined with “id” attribute of <boorex> node. Multiple Boorex Engines with different ID's can be defined. Boorex Node Structure is described further in Structural Viewsection.

(back to the top)

Use Boorex Engine

Boorex Engine can be used by specifying its ID in any context that supports this. Engine that is not used is not actually loaded.

(back to the top)

Structural View

Boorex Node Structure

Boorex node defines configuration of Boorex Engine. The following example is intended to provide quick inside into its structure:

<mppd>
<engines>
 <boorex id=”engine_id”>
  <encoding>ASCII</encoding>
  <defaults id=”expression_id” options=”perl,match_perl”>global</defaults>
  <inline id=”expression_id2” options=”perl,match_perl”>\.gif$</options>
  \.pdf$
  <file id=”expression_id4” options=”perl,match_perl”> patterns.txt</file>
  <f id=”expression_id5 o=”perl,match_perl”>/usr/local/MPP/patternts2.txt</f>
 </boorex>
</engines>
</mppd>

All options and attributes are optional. If any is omitted then default value is used. Minimum engine specification looks can look like this:

<mppd>
 <engines>
  <boorex>\.gif$</boorex>
 </engines>
</mppd>

Options Available for Boorex Engine

Expression ID

Expression Options

Expression File

(back to the top)

LDAP Configuration Guide

Overview

MPP can store policy group membership, ACL information and other items in an LDAP directory, a specialized database optimized for reading, browsing and searching. Directories tend to contain descriptive, attribute-based information and support sophisticated filtering capabilities.

In LDAP, directory information is in a hierarchical tree-like structure of entries. Traditionally, this structure reflected the geographic and/or organizational boundaries. An entry is a collection of attributes that has a globally unique Distinguished Name (DN) that is used to refer to the entity. Each of the entry's attributes has a type and one or more values. The types are typically mnemonic strings, like "cn" for common name, or "mail" for email address. The syntax of values depends on the attribute type.

An entry has an object class (raeMPP) that has attributes raeMPPGroupNameRecipient and raeMPPGroupSender.

An entry is referenced by its distinguished name, which is constructed by taking the name of the entry itself (called the Relative Distinguished Name or RDN) and concatenating the names of its ancestor entries. For example, the entry for Barbara Jensen has an RDN of uid=babs and a DN of uid=babs,ou=People,dc=example,dc=com.

LDAP defines operations for interrogating and updating the directory. Operations are provided for adding and deleting an entry from the directory, changing an existing entry, and changing the name of an entry. Most of the time, LDAP is used to search for information in the directory. The LDAP search operation allows some portion of the directory to be searched for entries that match some criteria specified by a search filter. Information can be requested from each entry that matches the criteria.

(back to the top)

LDAP Caching

MPP can cache LDAP query results to reduce the burden on LDAP servers for repetitive queries. Below are guidelines for configuring the LDAP cache.

1. Consider a system has many users defined in LDAP but only X of them are frequently addressed. LDAP is not frequently changed and you want to speed up mail processing. In this case:cache size = X * 1.1 * 8 / 1024
2. Consider a Customer has X users defined in LDAP and all of them are frequently addressed. LDAP is not frequently changed and you would like to speed up mail processing. In this case:cache size = X * 8 / 1024

For more details on LDAP, please consult documentation at sites such as www.openldap.org and the appropriate RFCs.

(back to the top)

Configuring LDAP Connections

Connecting to an LDAP server requires

• Identification of the server and the client
• Authentication
• Caching

The LDAP connection or bind requires identification of the server through a network name or IP address and the TCP port. The LDAP client may need to be identified (source IP address). The bind to the server may fail due to timeouts.

Authentication is typically with a password called the bind password.

Queries may access static or dynamic data. In static mode all data are loaded at startup and at restart. In contrast, in the dynamic mode the LDAP server is queried for each sender/recipient in each email.

Each dynamic query result may be cached. Caching policy is very simple, based on a static cache size and expiration time. If the cache grows more than a maximum cache size, the oldest record is deleted. If the cached record is outdated (as determined by expiration time), this record is removed and LDAP lookup is performed again. Expiration time is checked only at the time of query.

(back to the top)

MPP LDAP Schema

MPP queries an LDAP directory for policy membership using the following sample attributes. You can direct mppd to look in any LDAP directory in any element name. We support email address, domain name or CIDR IP.

MPP has a custom schema as follows:

# raeMpp object class
#
# Depends upon RFC 1274 (uid/dc)
#(core.schema)

attributetype (1.3.6.1.4.1.21157.2.1.1
       NAME 'raeMppGroupnameRecipient'
       DESC 'Group name for e-mail`s that contains current e-mail in
Recipients field (used by RAE-INTERNET MPP)'
	EQUALITY caseIgnoreMatch
	SUBSTR caseIgnoreSubstringsMatch
	SYNTAX 1.3.6.1.4.1.1466.115.121.1.15
	SINGLE-VALUE )

attributetype ( 1.3.6.1.4.1.21157.2.1.2
	NAME 'raeMppGroupnameSender'
	DESC 'Group name for e-mail’s that contains current e-mail in Sender field (used by RAE-INTERNET MPP)'
	SUP raeMppGroupnameRecipient )

# raeMpp
# The raeMpp represents group names and e-mail address which are associated 
# with a person in some way.
objectclass	( 1.3.6.1.4.1.21157.2.2.1
	NAME 'raeMpp'
	DESC 'Object class for group names for RAE-INTERNET MPP'
	AUXILIARY
	MAY (raeMppGroupnameRecipient $ raeMppGroupnameSender $ mail ))

LDAP schema-related parameters

mail_attribute

sender_group_attribute

receiver_group_attribute

The following are the general steps

Define Requirements

Modify the existing schema

Import Data

(back to the top)

MPP/LDAP sample how-to

We assume that OpenLDAP and MPP 2.x are installed on the working machine.

1) Setting up OpenLDAP

NOTE: Crypted passwords and LDAP/SSL are not the object of this how-to. Assuming that configuration files for OpenLDAP are installed in /etc/openldap, please make the following changes (replace "example" and "com" with your own domain parts).

Copy rae-mpp.schema in to /etc/openldap/schema:

 BASE         dc=example, dc=com
 URI          ldap://ldap.example.com
              /etc/openldap/slapd.conf
_____________________________________________________________
 include          /etc/openldap/schema/core.schema
 include         /etc/openldap/schema/cosine.schema
 include         /etc/openldap/schema/nis.schema
 include         /etc/openldap/schema/inetorgperson.schema
 include          /etc/openldap/schema/rae-mpp.schema
_____________________________________________________________
 database      bdb
 suffix        "dc=example,dc=com"
 rootdn        "cn=Manager,dc=example,dc=com"
 rootpw        secret
 index         objectClass     eq

Now, you can start the slapd daemon

 /etc/init.d/slapd start or 
 /usr/lib/openldap/slapd or 
 /usr/lib/openldap/slapd -u USER -g GROUP 

You must have the directory option specified in slapd.conf and that directory owned by USER and GROUP, i.e. in /etc/slapd.conf

 directory       /var/lib/openldap-data
 chown -R ldap:ldap /var/lib/openldap-data
 /usr/lib/openldap/slapd -u ldap -g ldap

Create initial LDIF in your favorite editor and save as initial.ldif after edits are complete

 dn: dc=example,dc=com
 objectClass: dcObject
 objectClass: organization
 o: EXAMPLE
 dc: example
___________________________________________________________________________________________
 dn: cn=Manager,dc=example,dc=com
 objectClass: organizationalRole
 cn: Manager

Use:

 ldapadd -x -D "cn=Manager,dc=example,dc=com" -W -f initial.ldif
 Password asked is: "secret" as defined in by rootpw in /etc/openldap/slapd.conf 
 Add some entries in another LDIF file: users.ldif
____________________________________________________________________________________________
 dn: cn=User1 Stuff, dc=example, dc=com
 cn: User1 Stuff
 objectClass: person
 objectClass: raeMpp
 mail: user1@example.com
 raeMppGroupnameSender: group1
 raeMppGroupnameRecipient: group2
 sn: User1 FirstName LastName
___________________________________________________________________________________________
 dn: cn=User2 Stuff, dc=example, dc=com
 cn: User2 Stuff
 objectClass: person
 objectClass: raeMpp
 mail: user2@example.com
 raeMppGroupnameSender: group1
 raeMppGroupnameRecipient: group2
 sn: User2 FirstName LastName 
____________________________________________________________________________________________
 ldapadd -x -D "cn=Manager,dc=example,dc=com" -W -f users.ldif
 Password asked is: "secret" as defined in by rootpw in /etc/openldap/slapd.conf 

2) MPP configuration

From the users.ldif we will use the field "mail" to match an e-mail address

raeMPPGroupnameSender will point to the MPP group name used to scan the mail when

e-mail address is the sender address

raeMPPGroupnameRecipient will point to the MPP group name used to scan the mail when

e-mail address is the recipient address

In mppd.conf.xml we should have:

 <groups>
 ...
 <group id="group1">
  ... settings for this group here ...
 </group>

 <group id="group2">
  ... settings for this group here ...
 </group>

  </groups>
  .....
       <ldaps>
               <ldap_use>yes</ldap_use>
               <ldap_dynamic>yes</ldap_dynamic>
               <ldap id="sample">
                       <base_dn>dc=example,dc=com</base_dn>
                       <server>192.168.0.1</server>
                       <server_timeout>30000</server_timeout>
                       <port>389</port>
                       <bind_dn>cn=Manager,dc=example,dc=com</bind_dn>
                       <bind_pw>secret</bind_pw>
                       <custom_filter_dynamic> ((<mail_attribute>=$ADDRESS$)(<mail_attribute>=$DOMAIN$))</custom_filter>
                       <mail_attribute>mail</mail_attribute>
                       <sender_group_attribute>raeMppGroupnameSender</sender_group_attribute>
                         <receiver_group_attribute>raeMppGroupnameRecipient</receiver_group_attribute>
                       <use_cache>no</use_cache>
                       <cache_size>1024</cache_size>
                       <cache_ttl>30</cache_ttl>
                       <search_scope>2</search_scope>
                       <version>3</version>
               </ldap>
       </ldaps>

The mppd daemon can be started/restarted now. Actual ldap queries will print in the MPP log file with LOGGING set to DEBUG. Please refer to them when troubleshooting.

   <common> …
   </common>

   <groups>  …               
   </groups>
   <--------------------------------------------------------------------------
   Enter global parameters for LDAP use. 
   -------------------------------------------------------------------------->
   <ldaps>
       <ldap id="sample">    

       </ldap>
   </ldaps>
</mppd>

(back to the top)

SNMP

Introduction

SNMP is a network protocol used to manage networked systems. MPP 2.x supports SNMP for performance management. For example, network managers can use SNMP to monitor MPP. MPP works with the net SNMP agent which is standard on most Unix operating systems. MPP uses the agentx protocol to speak with the snmp daemon.

(back to the top)

Requirements

net-snmp version 5.2.1 or higher are recommended.

(back to the top)

Setup of NET-SNMP

This step is generally not required as most default net SNMP installations should work with MPP. If net SNMP is functional, skip to step 3.

The MPP SNMP module was completely redesigned in version 2.5 and now runs as an net SNMP agent. In order for MPP to work as described below you must use the updated MPP MIB included in the installation packages.

1) Configure and make net-snmp if the installed snmp daemon lacks SNMPv3 support.

a.Download & unpack latest stable net-snmp sources from http://www.net-snmp.org.

b.Before starting, read README.<OS> file located in net-snmp base directory.

c.Follow the instructions from INSTALL file. The build process will default to /usr/local as the install root.

d.The configure script will set dynamically loadable module support on (--enable-shared on) by default if the operating system supports dynamic libraries. After the software has been made, check for support in your agent by looking at the output of the "snmpd -H" command for the "dlmod" token.

e.If it’s listed, the compiled agent supports it.

2)Configure NET-SNMP installation

a.Follow the instructions in README.snmpv3 located in net-snmp base directory. Don’t forget to test your setup as described in the file.Create any Net-SNMP user with read/write privileges with: net-snmp-config --create-snmpv3-user -a "user_password" user_name

b.This command is generally located in /usr/lobal/bin or /usr/bin depending on your installation preferences.

3) Tell Net-SNMP daemon to run as master agent. Add the following line into snmpd.conf: master agentx

a.This file generally located in /usr/local/share/snmp or /usr/share/snmp or other depending on you installation preferences.

4)Tell Net-SNMP management application to use MPP-MIB.txt file by adding the following line into snmp.conf.: mibfile /usr/local/MPP/snmp/MPP-MIB.txt.

5)Configure MPP.

a.Make sure that lib/libnetsnmpengine.so is a link to lib/libnetsnmpengine_5_2_2.so.

b.Make sure that you have new MIB at snmp/MPP-MIB.txt. In mppd.conf.xml under common->snmp set:

<snmp_use_statistics>yes</snmp_use_statistics>

Other related option can have default values. You can consider to specify:

<snmp_cache_ttl>- Cache TTL in seconds

<snmp_track_senders> - Whether to track senders

<snmp_track_recipients> - Whether to track recipients

6)Start Net-SNMP daemon with the following command; /usr/local/sbin/snmpd or /usr/sbin/snmpd

7) Re-Start MPP.

8) Test SNMP commands:

snmpwalk -v 3 -u user_name -l authNoPriv -a MD5 -A user_password localhost MPP-MIB::mppSnmpMIB

snmptable -v 3 -u user_name -l authNoPriv -a MD5 -A user_password localhost

MPP-MIB::mppLibraryTable

snmpget -v 3 -u denis -l authNoPriv -a MD5 -A 12345678 localhost MPP-MIB::mtaReceivedMessages.0

defPrivPassphrase "Bet you can't see me"

(back to the top)

Supported SNMP Variables

The following list represents the currently supported MIB variables. For the most current list, please consult the MPP-MIB.txt that came with the software distribution or the support website.

version	        Version of mppd
Library_version	Version of each mppd library
System_name	        Hostname of System or defined system name
Mta_name	        MTA that MPP is configured for
Thread_count	
Location	        Configurable system location
Up_time	        Time since daemon started
-Memory_utilization	Amount of RAM mppd consumes. Not implemented but will be in future builds.
Message_count	        Counts all messages processed by mppd
Message_rate_second	Rate of messages processed by mppd.  Calculated as messages per second, 
                       updated every 30 seconds as an average
Message_rate_hour	Average rate of messages per hour, updated hourly
Message_volume_hour	Amount in Megabytes’s of email processed per hour
Message_volume_second	Amount in Kilobytes’s of email processed per second
Message_recievers	Counts how many messages each receiver in cc, to and bcc receives.  
                       Flushed daily, stored for up to 1 week
Message_senders	Counts how many emails for each sender
Virus_count	        Count total number of viruses
Virus_name	        Count number of each virus by name
Spam_count	        Count total number of emails classified as spam by any engine
Scan_errors	        Count total number of scan errors
Scan_error_count	Count number of scan errors by error type
Errors	                Count total number of other errors
Error_count	        Count errors by error name
Encrypted_msg_count	Count total number of encrypted messages as reported by scan engines
Max_mime_parts_exceeded	       Count total number of messages exceeding max_mime_parts
Max_recursion_level_exceeded	       Count total number of messages exceeding max_recursion_level
Max_mime_parts_exceeded_sender	       Track senders of each message exceeding max_mime_parts
Max_recursion_level_exceeded_sender	Track senders of each message exceeded max recursion level

These variables may be used with a SNMPv2 or SNMPv3 manager to monitor MPP performance. Key variables include virus_count, and spam_count.

(back to the top)

Obsolete Commands from MPPv3

<email_server_in_threads_min>

<email_server_in_threads_max>

<email_server_in_threads_relax_timeout>

This options are obsolete and removed. Now number of smtp thread is fixed and equal to a number of processing threads.

Processing threads are shared with Policy Server.

<email_server_in_timeout_connect>

This options is obsolete for postfix.

New options:

New Options	Attributes
<smtp_socket>	Specifies	Additional addresses to bind to. Multiple options can be specified - server will bind to all corresponding ports. Port can be specified as name according to /etc/services. Host address can be specified as IPv6 address or DNS name that resolves to IPv6 address.Unix sockets are not supported that time.
	Value	string (URI)
	Default	inet:10025@localhost
<smtp_socket_retry_period>	Specifies	If bind to port failed then it is retried with period specified with this option.
	Value	integer (milliseconds)
	Default	15000
<smtp_buffer_size_envelope_read>	Specifies	Size of received (read) buffer of a socket while reading SMTP envelope. Applied for all specified sockets.
	Value	integer (bytes)
	Default	1024
<smtp_buffer_size_data_read>	Specifies	Size of received (read) buffer of a socket while reading SMTP data. Applied for all specified sockets.
	Value	integer (bytes)
	Default	16384
<smtp_buffer_size_write>	Specifies	Size of send (write) buffer of a socket. Applied for all specified sockets.
	Value	integer (bytes)
	Default	128

(back to the top)

Custom SQL Queries

Explanation

MPP supports the ability to create custom SQL queries for logging select information or reading WBL information from any MySQL database. This functionality is quite powerful and the applications are quite extensive since you can define selects and inserts with conditional actions and variables derived from messages. The MySQL template language can be used to replace MPP’s default queries for WBL’s and ACL’s or to create SQL logging for your own application requirements.

MySQL Engine allows the definition of a connection to a MySQL database that is associated with one or more queries of arbitrary form with the connection. Queries can be used by other MPP parts (Users) to utilize database functionality. It is possible to specify that during execution of queries different variables (sender, recipient, client ip, etc) must be inserted into query text before sending it to the database. Basic means of processing results of queries and presenting them to Users are provided. MySQL Engine implements Generic Engine interface and can be used in any context that uses this interface.

(back to the top)

Use Case View

This section contains brief description of the features from usage point of view. Entities referenced in this section are described in “Structural View” section.

(back to the top)

Define MySQL Engine

An instance of MySQL Engine is defined in mppd.conf.xml file under <mppd><engines> node with node name <mysql>. ID of the engine is defined with “id” attribute of <mysql> node. Multiple MySQL Engines with different ID's can be defined but ID must be unique with <engines>. MySQL Node Structure is described further in “Structural View” section. There should be specified a connection and one or more queries. Queries will share specified connection.

(back to the top)

Define Queries

One or more queries that will be associated with MySQL Engine and will share its connection are defined under <mysql> node with a series of <query> nodes. ID of a query is defined with “id” attribute of <query> node and must be unique within the engine. Query consists from Query Template and a series of Query Results.

(back to the top)

Specify Query Template

Query Template is specified under <query> node using <template> node. During execution query text is made from Query Template by substituting different runtime variables.

(back to the top)

Specify Query Results

One or more Query Results are specified under <query> node using <result> node. ID of result is defined with “id” attribute of <result> node and must be unique within the query. Query Result is a string that is deduced from result table using a set of conditions, setters and rules for table rows traversal.

(back to the top)

Use MySQL Engine and Queries

MySQL Engine is used though its Queries. Query is a Generic Engine that is referenced by ID that is composed from ID of MySQL Engine and ID of a Query separated by dot (like “mysql_engine_id.query_id”). Query can be referenced in any context that accepts Generic Engines.

(back to the top)

Use Query Results

Query Results are used in two ways. First of all different contexts where queries are used may impose requirements on Query Results and use this results in specific manner. Second way is usage of results in templates, conditions and setters of other queries that goes next in processing chain. In that way result of a query is referenced using dot•separated composition of word “engines”, engine ID, query ID and result ID (like “engines.mysql_engine_id.query_id.result_id”).

(back to the top)

Handle Multi•Value Variables

Some variables within processing context may have multiple values. For example there may be multiple recipients for a message so recipient is considered as multi-value variable. To avoid introducing of loops operators MySQL Engine does implicit looping, i. e. it repeats corresponding action for each value of a variable. Consequently if there are two multi-value variables then action is repeated for each combination or the two and so on. Results of such queries become multi-value variables too.

(back to the top)

Structural View

MySQL Node Structure

MySQL Node defines a configuration of MySQL Engine and its queries. The following example is intended to provide a quick insight into its structure:

<engines>
 <mysql id=”mpp”>
   <connection>
      <host>example.com</host>
      <port>2222</port>
      <database>mpp</database>
      <unix_socket>/usr/local/mysql/var/mysql.sock</unix_socket>
      <defaults_file>/etc/my.cnf</defaults_file>
      <concurrency>4</concurrency>
      <timeout>
             <connect>60</connect>
      </timeout>
      <user>mpp_database_user</user>
      <password_id>password_id_from_passwords_file</password_id>
    </connection>
    <query id=”query_1”>
      ...
    </query>
    <query id=”query_2”>
      ...
    </query>
      ...
 </mysql>
</engines>

Query Nodes will be specified in next sections. All options and attributes are optional. If any is omitted then default value is used. Minimum survival specification that defines engine with ID “mysql” and single query with ID “query” can look like this:

<engines>
 <mysql>
   <connection>
      <host>example.com</host>
      <port>2222</port>
      <database>mpp</database>
   </connection>
   <query>
    ...
   </query>
 </mysql>
</engines>

Description of node values and attributes is provided below:

New Options	Attributes
<mysql>	Specifies	MySQL Engine
	Attributes	id
	Specified	id if the engine
	Value	string
	Default	“mysql” (i.e. name of the node)
	Value	breaks further on sub-nodes
<connection>	Specifies	Properties for connection to database
	Value	breaks further on sub-nodes
<host>	Specifies	remote host where database server is running
	Value	string (DNS name or IP of a host)
	Default	empty
<port>	Specifies	remote port that database server listens for
	Value	integer
	Default	3306
<database>	Specifies	remote database to query in
	Value	string
	Default	empty
<unix_socket>	Specifies	local socket to connect to local server
	Value	string (path to a socket)
	Default	empty
<defaults_file>	Specifies	path to file with defaults options for mysql client library
	Value	string (path to file)
	Default	empty
<concurrency>	Specifies	number of concurrently running connections
	Value	integer
	Default	equal to the number of processing threads
<timeout>	Specifies	timeouts for different operations
	Value	breaks further on sub-nodes
	Default
<connect>	Specifies	timeout for connecting to server
	Value	integer (seconds)
	Default	60
<query>

(back to the top)

Query Node Structure

Query Node specifies configuration of a query and its results within MySQL engine. Multiple <query> nodes may be specified under <mysql> node. The following example is intended to provide quick inside into its structure:

<mppd>
  <engines>
    <mysql id=”mpp”>
      ...
       <query id=”wbl”>
         <template>...</template>
         <result id=”result_1”>
           ...
         </result>
         <result id=”result_2”>
           ...
         </result>
       <query>
     </mysql>
  </engines>
</mppd>

Query Node contains query template and one or more results. Results specifies how result table will be mapped to string results that then exposed as results to query users.

(back to the top)

Description of node values and attributes is provided bellow:

New Options	Attributes
<query>	Specifies	single query
	Attributes	id
	Specified	id if the query
	Value	string
	Default	“query” (i. e. name of the node)
	Value	breaks further on sub-nodes
<template>	Specifies	query template
	Value	string (format is described further in Query Template section)
	Default	empty (no query is sent at all)
<result>	Specifies	how result table is converted to string result of the query
	Attributes	id
	Specifies	id of the result
	Value	string
	Default	"result"
	Value	breaks further on sub-nodes (see Query Result section)

(back to the top)

Query Template

Query Template is a string with macro substitutions. For example template “here comes $recipient of a message” during runtime will produce the following query string: “here comes recipient@example.com of a message”. In this example “here comes “ and “ of a message” are a plain text pieces and $recipient is macro substitution.

Plain text pieces are just 'constant' text. There are a few special characters that must be escaped within it. First of all it is XML escapes:

char	escape
&	&
<	<
>	>

Second group of escapes is connected with template syntax itself and is the following:

char	escape
\	\\
{	\{
}	\}
$	\$

Macro substitution starts from unescaped '$' (dollar) character. Macro consists of macro name and depending on macro type – macro arguments. Macro name may consists of any of alphabetic letters (a-zA-Z), '.' (dot) and '_' (underscope).For macro that doesn't expect arguments (simple macro) there are two possible forms:

$macroName

${macroName}

In first form macro name is everything starting immediately after '$' and ending at first not allowed for macro name character (usually space). In second form macro name is quoted in curly braces '{}'. This form is useful if immediately after macro should go a plain text character that otherwise can be considered as part of macro name. Examples:

SELECT `id` FROM `contacts` WHERE `address`=$sender

INSERT IGNORE INTO `contacts` (`address`) VALUES (${sender}with_suffix)

For macro that expects one or more arguments there is the following form:

${macroName macroArgument1 macroArgument2}

Macro arguments are templates itself that may recursively consists of plain text and macros. Macro arguments are separated with spaces so it is required that plain text in arguments at highest level contain no space. Space inside recursive macros are allowed. If there is a need for space in argument then entire argument must be quoted with '{}'. For example:

${wrap { OR `users`.`address`='$#'} ${escape $sender.component}}

Here you may see 'wrap' macro with two arguments. First argument must contain space in plaint text around 'OR' so entire argument is quoted with '{}'. Quotation characters are not appeared in result string generated from macro. You may notice nonconforming macro name '$#'. This is special feature of 'wrap' macro that will be described further. Last macro name is 'sender.component'. Dot '.' is legal character for macro name and generally expresses access to smaller property of a bigger object.

(back to the top)

---

The following simple macros are available:

New Options	Attributes
$sender	Specifies	SMTP envelope sender of a message
	Dimensions	zero dimension variable
$sender.local	Specifies	local part of SMTP envelope sender
	Dimensions	zero dimension variable
$sender.domain	Specifies	domain part of SMTP envelope sender
	Dimensions	zero dimensional variable
$sender.component	Specifies	list of components of SMTP envelope sender. Components are full email itself and email domains starting from most specific to less specific. For example address sender@domain.example.com will have the following
	Components	sender@domain.example.com, domain.example.com, example.com and com.
	Dimensions	one dimensional variable
$recipient	Specifies	SMTP envelope recipient of a message
	Dimensions	one dimensional variable
$recipient.local	Specifies	local part of SMTP envelope recipient of a message
	Dimensions	one dimensional variable
$recipient.domain	Specifies	domain part of SMTP envelope recipient of a message
	Dimensions	one dimensional variable
$recipient.component	Specifies	list of components of SMTP envelope recipient. Components are full email itself and email domains starting from most specific to less specific. For example address recipient@domain.example.com will have the following components: recipient@domain.example.com, domain.example.com, example.com and com.
	Dimensions	two dimensional variable
$ip	Specifies	IP address of client host as string. If IP is not available this is empty string.
	Dimensions	zero dimensional variable
$ip.dec	Specifies	IP address of client host as integer. If IP is not available this is zero.
	Dimensions	zero dimensional variable
$ip.hex	Specifies	IP address of client host as hexadecimal integer. If IP is not available this is zero.
	Dimensions	zero dimensional variable
$host	Specifies	DNS name of client host. If DNS name is not available this is empty string.
	Dimensions	zero dimensional variable
$group	Specifies	name of MPP group that executes a query.
	Dimensions	zero dimensional variable

(back to the top)

---

Next macros are functions that preprocess their arguments before inserting them into result string. Dimensions of a function is a union of dimensions of its arguments.

${escape argument}

Evaluates its argument and does MySQL specific escaping of character that are special to MySQL syntax. It is strongly recommended that all strings coming from outside world ($sender, $recipient, ...) are escaped and quoted when used in query templates. Otherwise it may appear runtime error or even security threat if those strings contain special characters or commands. For example one of previously appeared statements should be refined as following:

SELECT `id` FROM `contacts` WHERE `address`='${escape $sender}'

INSERT IGNORE INTO `contacts` (`address`) VALUES ('${escape $sender}with_suffix')

NOTE: that it is also added MySQL quotation around '${escape $sender}' to prevent interpreting of strings as command.

${wrap template argument2}

This is a special tool for concatenating multi-value variable into single string. It creates 'written' lists. The tool effectively 'wraps' last dimension of its last argument. That is why it is called 'wrap'. Lets start explanation of this tool from an example. Imagine that there is a message with two recipients: rcpt@example.com and other@domain.net. Then the following template:

SELECT `id` FROM `contacts` WHERE `address`='${escape $recipient}'

Will lead to generation and execution of two separate queries:

SELECT `id` FROM `contacts` WHERE `address`='rcpt@example.com'

SELECT `id` FROM `contacts` WHERE `address`='other@domain.net'

Result of the query will have same dimension as $recipient variable and thus will be multi-value too. From the other hand another template:

SELECT `id` FROM `contacts` WHERE ${wrap `address`='$#'{ OR } ${escape $recipient}}

Will lead to single query with list of recipients separated with ' OR ':

SELECT `id` FROM `contacts` WHERE address`='rcpt@example.com' OR address`='other@domain.net'

Result of last query will have zero dimension even that its template depends on $recipient.

Wrap macro imposes special syntax and semantics on its first argument (which is called wrap template):

Argument consists from main part and optional suffix. Main part generates list entries and suffix generate list separator. Important property of separator (suffix) that it will not be put after last item in list. Suffix is placed at the end of main part and separated from it with '{}' quotation. In the example above main part is “`address`='$#'” and separator (suffix) is “ OR “. If main part's plain text must contain spaces the entire argument including suffix must be quoted with '{}' as follow: ${wrap {plain text $# with spaces {suffix}} argument2}.

Special macro $# is substituted with value of second argument each time list entry is generated during iteration through last dimension of the argument. This macro is called placeholder. Placeholder must not appear inside other macro so it is illegal to write '${escape $#}'.

Main purpose of wrap macro is to generate lists of email components. Lets again imagine that there is a message with two recipients: rcpt@example.com and other@domain.net. The following template will produce two queries (one for each recipient) that will contain lists of recipient email components:

SELECT `id` FROM `contacts`

WHERE ${wrap `address`='$#'{ OR } ${escape $recipient.component}}

Will produce:

SELECT `id' FROM `contacts`

WHERE `address`='rcpt@example.com' OR `address`='example.com' OR `address`='com'

SELECT `id' FROM `contacts`

WHERE `address`='other@domain.net' OR `address`='domain.net' OR `address`='net'

Consequently query result will be one dimensional with dimension same as $recipient variable.

Next macros can be used only when evaluating conditions of result cases and generating result strings after query is generated and executed. It is a runtime error when this macros are used for query generation.

${field name}

Returns a field value for field with name 'name' for current row. Should be used with SELECT queries. Operates on field aliases if specified. For example if query template is:

SELECT `id` FROM `contacts` WHERE `address`='${escape $recipient}'

Then expression ${field id} will evaluate to selected value of `id` field. Example with alias:

SELECT COUNT(*) AS n FROM `contacts` WHERE `address`='${escape $recipient}'

In this case ${field n} will evaluate to calculated value of COUNT(*).

Field macro should not be used for fields that may contain binary data with '\0' (zero) characters. Otherwise unexpected results may be produced.

$insert_id

Returns value of auto-increment column for last INSERT query.

For more examples on query templates see Real-Life Solutions section.

(back to the top)

Query Result

Query Result defines how result table is mapped to a result string that is exposed to engine users. Multiple results can be defined for each query. The following example is intended to provide quick inside into structure of Query Result node:

<mppd>
  <engines>
    <mysql id=”mpp”>
      ...
       <query id=”wbl”>
         ...
         <result id="result">
           <if_empty_table>
               <result>none</result>
           </if_empty_table>
           <if_filled_table>
               <row_to_case_relation>all-to-one</row_to_case_relation>
               <case>
                  <condition>${field wb} $EQ W $OR ${field wb} $EQ Y</condition>
                  <result>spam_whitelist</result>
               </case>
               <case>
                  <condition>${field wb} $EQ B $OR ${field wb} $EQ N</condition>
                  <result>blacklist</result>
               </case>
               <result>none</result>
           </if_filled_table>
         </result>
       </query>
       ...
    </mysql>
  </engines>
</mppd>

Each result has an id assigned to it that will be used to access the result by users. Result is branched for empty result table (or for not performed query due to unspecified query template or other reason) <if_empty_table> and for filled result table <if_filled_table>. Each branch contains zero or more <case> and mandatory default <result> setter. Case consists from <condition> and <result> setter that will be used if corresponding condition is met. Additional option <row_to_case_relation> that have effect in <if_filled_table> branch. It tunes the way rows and cases are iterated and matched.

(back to the top)

Processing flow is the following.

After template is generated and query is executed result table is obtained. If table is empty (query was not actually performed due to unspecified query template or other reason) then <if_empty_table> branch is executed. Otherwise <if_filled_table> branch is executed.

Branch <if_empty_table> is executed in the following way. First each <case> condition is matched until match is found. If it is found corresponding <result> template is evaluated and final result is set equal to obtained value. If matched condition is not found or there are no <case> nodes at all then default <result> template is evaluated and final result is set. Because the branch is for empty table macro ${field ...} is not allowed in <condition>'s and <results>'s of the branch.

Execution of <if_filled_table> branch is a bit complexer. Because in general case there may be multiple rows in result table and multiple <case>'s specified final matching result of rows against <case> <condition>'s may depend on algorithm of traversal through both sets. Option <row_to_case_relation> provides a user with ability to select predefined algorithm suitable for his needs. The option may have the following values: all-to-one, one-to-all and first-to-all. Algorithm all-to-one matches all rows to one first (one) case, then to second (one) case and so on until match is found. Algorithm one-to-all matches first (one) row to all cases, then second (one) row to all case and so on until match is found. Algorithm first-to-all matches only first row to all cases until match is found (other rows are not matched at all and thus ignored). If match '<case> <condition> is found then corresponding <case> <result> is evaluated and is taken as final result. Otherwise default <result>. Macro ${field ...} can be used in <case> <condition>'s and <result>'s to access any field of current row by name. This macro can also be used in default <result> but its meaning depends on <row_to_case_relation>: for all-to-one and one-to-all it is applied to last row but for first-to-all it is applied to first row.

Option <condition> is a string that has a format similar to Query Templates but with a few additional features needed for making comparisons and boolean expressions. The format is described further in Case Condition Format section.

Option <result> is a string that has the same format as Query Template. One deference is in the <if_filled_table> branch macro ${field ...} is available. In both contexts ${insert_id} is available but it should be used for INSERT's which do not return results so actually <if_empty_table> branch will always be executed.

(back to the top)

Case Condition Format

Case Condition string is a boolean expression string that consists from arguments, comparisons and boolean expressions. Arguments have the same format as Query Templates. Then arguments are compared with comparison operators. Then if there are more then one comparison needed comparisons may form boolean expression. For example here is a simple comparison: ${field condition} $EQ 0. Field condition is checked for equality with zero. And here is two comparisons that forms boolean expression: ${field wb} $EQ W $OR ${field wb} $EQ Y. Field wb is compared for equality with 'W' or 'Y' strings.

The following comparison operator are available:

Operator	Meaning	Equivalent in C/C++
$EQ	equal	==
$NE	not equal	!=
$GT	greater	>
$LT	lower	<
$GE	greater or equal	>=
$LE	lower or equal	<=

The following boolean operators are available:

Operator	Meaning	Equivalent in C/C++
$NOT	unary logical negate	!
$AND	binary logical AND	&&
$OR	binary logical OR	||

Arguments should not contain spaces at top most plain text. However if they must they should be quoted with {...}: ${field some} $EQ {here comes text with spaces}. Empty strings are expressed as {}: $ip $EQ {}

Comparison operators have greater precedence over boolean operator. Boolean operators have precedence over each other as they appear in table above. To explicitly specify in what precedence boolean operator should be applied operator may be bracketed with {...}. For example: $NOT {${field wb} $EQ W $OR ${field wb} $EQ Y}. Here $NOT applied after $OR because $OR is bracketed. Otherwise $NOT would be applied to first comparison and the $OR would be applied.

When comparison is performed first of all string values for corresponding arguments are generated. If both arguments can be converted to integers the they are converted and compared as integers (signed 64 bit). If not they converted to reals (signed 64 bit). If not they compared as string.

(back to the top)

Real-Life Solutions

MPP WBL

This XML snapshot illustrates how MPP-styled WBL's can be supported.

 <mppd>
   <common>
     ...
     <passwords>/usr/loca/MPP/secret.txt</passwords>
     ...
   </common>
 <groups>
     <group id=”default”>
        ...
        <wbl_engines>mpp.wbl</wbl_engines>
        ...
     </group>
     ...
   </groups>
   <engines>
     ...
        <mysql id="mpp">
           <connection>
             <host>localhost.localdomain</host>
               <port>50270</port>
               <database>mpp</database>
               <unix_socket>/usr/local/mysql-5.0.27/var/mysql.sock</unix_socket>
               <defaults_file>/etc/my-5.0.27.cnf</defaults_file>
               <user>root</user>
           <password_id>root_password_id</password>
             </connection>
             <query id="wbl">
               <template>
                   SELECT `wbl_lists`.`condition` AS `condition` FROM `users`,`wbl_lists`,`contacts`
                   WHERE `wbl_lists`.`user_id`=`users`.`id` AND `wbl_lists`.`contact_id`=`contacts`.`id`
                   AND (
                           (       (`users`.`address`='*'
                                       ${wrap { OR `users`.`address`='$#'} ${escape $sender.component}})
                               AND (`contacts`.`address`='*'
                                       ${wrap { OR `contacts`.`address`='$#'} ${escape $recipient.component}})
                               AND (`wbl_lists`.`direction`=0)
                           )
                           OR
                           (           (`users`.`address`='*'
                                       ${wrap { OR `users`.`address`='$#'} ${escape $recipient.component}})
                               AND (`contacts`.`address`='*'
                                       ${wrap { OR `contacts`.`address`='$#'} ${escape $sender.component}})
                               AND (`wbl_lists`.`direction`=1)
                           )
                           OR
                           (           (`users`.`address`='*'
                                       ${wrap { OR `users`.`address`='$#'} ${escape $sender.component}})
                               AND (`contacts`.`address`='*'
                                        ${wrap { OR `contacts`.`address`='$#'} ${escape $recipient.component}})
                               AND (`wbl_lists`.`direction`=2)
                           )
                           OR
                           (           (`users`.`address`='*'
                                       ${wrap { OR `users`.`address`='$#'} ${escape $recipient.component}})
                               AND (`contacts`.`address`='*'
                                       ${wrap { OR `contacts`.`address`='$#'} ${escape $sender.component}})
                               AND (`wbl_lists`.`direction`=2)
                           )
                       )
               </template>
               <result id="result">
                   <if_empty_table>
                       <result>none</result>
                   </if_empty_table>
                   <if_filled_table>
                       <row_to_case_relation>all-to-one</row_to_case_relation>
                       <case>
                           <condition>${field condition} $EQ 0</condition>
                           <result>spam_whitelist</result>
                       </case>
                       <case>
                           <condition>${field condition} $EQ 1</condition>
                           <result>blacklist</result>
                       </case>
                       <result>none</result>
                  </if_filled_table>
               </result>
           </query>
       </mysql>
        ...
        </engines>
 </mppd>

(back to the top)

AmavisD WBL

Next XML snapshot illustrates how AmavisD-styled WBL's can be supported.

 <mppd>
   <common>
     ...
        <passwords>/usr/local/MPP/secret.txt</passwords>
        ...
      </common>
      <groups>
        <group id=”default”>
            ...
            <wbl_engines>amavisd.wbl</wbl_engines>
            ...
        </group>
        ...
      </groups>
      <engines>
        ...
       <mysql id="amavisd">
           <connection>
               <host>localhost.localdomain</host>
               <port>50270</port>
               <database>amavisd</database>
               <unix_socket>/usr/local/mysql-5.0.27/var/mysql.sock</unix_socket>
               <defaults_file>/etc/my-5.0.27.cnf</defaults_file>
               <user>root</user>
               <password_id>root_password_id</password>
           </connection>
           <query id="wbl">
               <template>
                   SELECT `wblist`.`wb` AS `wb` FROM `wblist`,`users`,`mailaddr`
                   WHERE `wblist`.`rid`=`users`.`id` AND `wblist`.`sid`=`mailaddr`.`id`
                   AND (`users`.`email`='${escape $recipient}'
                   OR `users`.`email`='${escape @$recipient.domain}')
                   AND (`mailaddr`.`email`='${escape $sender}'
                   OR `mailaddr`.`email`='${escape @$sender.domain}')
               </template>
               <result id="result">
                   <if_empty_table>
                       <result>none</result>
                   </if_empty_table>
                   <if_filled_table>
                       <row_to_case_relation>all-to-one</row_to_case_relation>
                       <case>
                           <condition>${field wb} $EQ W $OR ${field wb} $EQ Y</condition>
                           <result>spam_whitelist</result>
                       </case>
                       <case>
                           <condition>${field wb} $EQ B $OR ${field wb} $EQ N</condition>
                           <result>blacklist</result>
                       </case>
                       <result>none</result>
                  </if_filled_table>
               </result>
           </query>
       </mysql>
      ...
    </engines>
 </mppd>

(back to the top)

MPP ACL

Next XML snapshot illustrates how MPP-styped ACL's can be supported

<mppd>
   <common>
        ...
        <passwords>/usr/local/MPP/secret.txt</passwords>
        ...
   </common>
   <groups>
      <group id=”default”>
        ...
        <wbl_engines>mpp.acl_sender, mpp.acl_recipient</wbl_engines>
        ...
      </group>
    ...
   </groups>
   <engines>
      ...
       <mysql id="mpp">
           <connection>
               <host>localhost.localdomain</host>
               <port>50270</port>
               <database>mpp</database>
               <unix_socket>/usr/local/mysql-5.0.27/var/mysql.sock</unix_socket>
               <defaults_file>/etc/my-5.0.27.cnf</defaults_file>
               <user>root</user>
         <password_id>root_password_id</password>
           </connection>
           <query id="acl_sender">
               <template>
           SELECT `id` FROM `accesslists`
           WHERE `mail`='${escape $sender}' AND `direction`=1
               </template>
               <result id="result">
                   <if_empty_table>
                       <result>acl_sender</result>
                   </if_empty_table>
                   <if_filled_table>

<result>none</result>

                  </if_filled_table>
              </result>
           </query>
           <query id="acl_recipient">
               <template>
           SELECT `id` FROM `accesslists`
           WHERE `mail`='${escape $recipient}' AND `direction`=0
               </template>
               <result id="result">
                   <if_empty_table>
                       <result>acl_recipient</result>
                   </if_empty_table>
                   <if_filled_table >
                   <result>none</result>                      
                   </if_filled_table>
               </result>
           </query>
       </mysql>
     ...
   </engines>
 </mppd>

(back to the top)

Combination of MPP and AmavisD ACL and WBL

Previous examples should be 'merged' and <wbl_engines> should be replaced with the following:

<wbl_engines>mpp.acl_sender, mpp.acl_recipient, mpp.wbl, amavisd.wbl</wbl_engines>.

(back to the top)

Security Notes

It is strongly recommended that all strings coming from outside world ($sender, $recipient, ...) are escaped and quoted when used in query templates. Otherwise it may appear runtime error or even security threat if those strings contain special characters or commands.

(back to the top)

Known Issues

1. When using two or more engines in chain execution of second and next engines is unconditional. Even if results of first engine enough to make some decision next engines may be executed though their result will be discarded. This is not a big problem for SELECTs because it will lead only to spare queries. But it can be the problem for INSERTs/UPDATEs because it may lead to undesired changes in data. The solution is to implement preconditions so that if a precondition is matched then query will not be generated and executed and default results will be returned.
2. Desired features: preconditions, global variables, policy dependencies, modifiable message variables, private queries.
3. If field accessed with ${field ...} macro may be NULL according to structure of corresponding table and is a string field that theoretically can have 'NULL' value then this two situations remain indistinguishable. The macro returns 'NULL' value for both cases to comparison ${field ...} $EQ NULL will be true for both cases.
4. Macro ${field ...} should not be used for binary data fields that may contain '\0' (zero) characters. Otherwise results are not specified.
5. Because during comparisons integer and double comparisons are tried unconditionally before string comparison it may lead to unexpected result if there should not be only string comparison. However this seem to be unlikely.

(back to the top)