E-mail gateway

The MasonSQL framework comes built-in with a messaging system for the users with the following forwarding modes:

• viewing of messages on the web portal
• transmission of messages via e-mail
• transmission of messages via SMS

Messages are generated from the application and then saved in the database. A built-in process sorts the messages every minute.

More details can be found at: HandlingEmailAndSmsMessages

We want now to allow the reception of e-mail messages to be filtered upon entry and then to be subsequently forwarded to the MasonSQL messaging system.

Messages originating from the e-mail server will trigger a script for each message. The script will then assess where to deliver the message in the MasonSQL messaging system; this script will then arrange for its forwarding according to the defined set of rules.

The mail server to use depends on the installation environment and not necessarily the script will be installed on the same web server or database server.

Documentation

The following document, HandlingEmailAndSmsMessages, is in partial completion. In particular, it does not describe the scheme of the messages and messages_users tables and how they would be used for message archival as well as the progress report managed by the following script: /opt/masonsql/bin/cronjobs.

Thus, the documentation regarding the management of the messages implemented so far needs to be completed, and subsequently integrating it with the documentation of the filter and gateway defined herewith.

Forwarding rules

The forwarding rules must allow identification of recipients based on the headers and the body of the message using regular expressions. The rules can define the recipients to single users, groups, or based on an SQL query that will retrieve the list of recipients.

Examples:

Filter	Recipients	Email	SMS	Subject	Short Message	Message
{{BODY|perl}} =~ m/Insufficient memory/i	@Admins carlos			'Insufficient memory'	"{{Subject}}"	"{{BODY}}"
{{From}} eq 'root@server.local' and {{BODY}} =~ m/avvis[oi]/i	@Users	x		"From {{From}}"	"From: {{From}}\n\n{{BODY}}"
{{Subject}} =~ m/error/i	SELECT login FROM anagrafiche WHERE email ~* {{To|psql}};	x	x	'ERROR'	"Error from $Subject"	{{EMAIL}}

• Filter: Evaluation code of a rule
  • {{BODY}} contains the body of the message
  • {{HEADERS}} contains the headers of the message
  • {{EMAIL}} contains the entire message, including the headers
  • {{Ccccc-Cccc}} contains the header named Ccccc-Cccc (example: Return-Path, Date, from, To, Subject, X-Mailer)
• Recipients:
  • Groups are indicated with the character "@" at the beginning of the name
  • Queries are identified if they start with "SELECT" and end with the character ";"
• Email: the message is also sent via email
• SMS: the message is also sent via SMS
• Subject: Message subject
• Short Message: Short message (used for SMS messaging)
• Message: Long message (used also as body when sending via e-mail)

Development environment

The script has to be developed in the Perl language and in the same environment of the current script /opt/masonsql/bin/cronjobs, which has to be used as a "skeleton" using the same code for the reading of parameters for the connection to the database.

The packages to be used need to be agreed upon with GuidoBrugnara, among which are the following:

• Email::MIME easy MIME message handling
• DTL::Fast Perl implementation of Django templating language

Email Filter Processing

• Database Detail
  • New table public.email_gateway_rules will be created to store filter rules.

    Column	Type	Modifiers
    id	sequence
    filter	character varying (240)
    recipients	character varying
    email	boolean
    sms	boolean
    subject	character varying
    short_message	character varying
    message	character varying
    rule_ord	integer

• Processing Detail - Initialize Process
  • Script Location: /opt/masonsql/bin/email_gateway.pl
  • Validate Inputs: Validate user has provided all required inputs to program.
  • Read Configuration: Read configuration file provided by user.
  • Set DB Handler: Read and set database handler.
  • Load Filter Rules: Read filter table and load all configured rules. All rules are loaded in same order preference given for each rule.
  • Load Users: Load all user login data. This will be used when to forward message to multiple recipients.
• Processing Detail - Filter Emails Process
  • Read Inputs: Read input either from list of files or from STDIN.
  • Process Inputs: Process each input provided by user in below sequence.
    • Load headers given in email context.
    • Substitute DTL tags in the filter and check (if true)
      • Substitute DTL tag in the other field (recipients, subject, short_message, message)
      • Prepare email message as per defined rule.
      • Forward Emails: Forward email to MasonSQL messaging system.single quote
        • Store message "messages" table with all required fields
        • Store multiple records for each login in "messages_users" table.
      • Process should stop validating as soon as first rule get qualified.
  • Send Emails: MasonSQL system has pre-built process to send mail for each recipient added in "messages_uses" table. This mail sent process will not be a part of email gateway process flow.
• Perl Modules - Perl packages used to parse email contents.
  • Email::MIME MIME message handling of RFC2822 message format and headers.
  • DTL::Fast - Perl implementation of Django templating language
    • Developmend of custom Tags
      • perl - escape text with Perl syntax and return a perl string delimited by single quotes
      • psql - escape text with Postgresql syntax and return a psql string delimited by [[https://www.postgresql.org/docs/9.4/static/sql-syntax-lexical.html][String Constants with C-style Escapes (4.1.2.2)]

Configuration Parameter:

Config Parameter	Description	Example
DBIconnect	Database connection parameter with dbname, user and password detail.	dbi:Pg:dbname=masonsql;user=[user_name];password=[password]

Usage:

---

The script can be called in several ways:

# MasonSQL scripts directory
cd /opt/masonsql/bin

# with extended parameters name
./email_gateway --config <config_file> --files <file1.eml,file2.eml,file3.eml>

# with abbreviated parameters
./email_gateway -c <config_file> -f <f1.eml,f2.eml,f3.eml>

# providing e-mail from standard input
cat f1.eml | ./email_gateway -c <config_file>

---

Comments