Directory of company email addresses or data replication in an unstable network

Introduction

I had the opportunity to work as an administrator in a large company that has more than 10 branches in different cities, united by rather unstable and slow channels. As in many others, the basis for the exchange of information in the company was e-mail. It should be noted that each branch, as well as the head office has its own mail server, mail accounts are managed by the local admin. All mail servers are running FreeBSD + Postfix + SpamAssassin + amavisd-new + Courier-IMAP.

The main task is to maintain an up-to-date directory of email addresses of all users of the company for internal use, secondary is a list of available email addresses for the main mail server, so that it can check if there is such an address in the company before sending the letter to the desired branch.

Channels are unstable, often "fall", the speed of their work is generally unpredictable. Who cares how the task was solved I invite under the cat.

Anamnesis

Mail topology, if you can call it the relationship scheme of the company's mail servers, star. There is one transit server, accessible from the outside, through which mail is exchanged between branches of the company, as well as between users of the company and the outside world. On the server, all passing messages are scanned for viruses that also come in from outside for spam. There are no mailboxes on the server, this is a pure SMTP relay.

The remaining servers are located physically in the offices of the branches and one in the head office. They do not have access to the outside world, they receive incoming messages from the main server, outgoing messages are sent through it. The main function is the maintenance of end-user mailboxes.

All servers are running FreeBSD versions 7.x - 8.x, Postfix is ​​used as SMTP, Courier-IMAP is used as POP3 / IMAP.

Where does the data come from

Mail accounts of users are stored in MySQL database, they are managed by local administrators through a web interface written in PHP. On the branches, my unpretentious development is still 2005. in PHP 4, in the head office a beautiful application on Yii using AJAX features. It was also written by me in the framework of studying the framework.

Decision

Initially, the directory was stored on the server in the form of an HTML page, containing lists of all emails by file. Over time, this decision has ceased to satisfy me for the following reasons, in descending order of importance:

• Spammers People are simple and lazy, very many in the company when asked to provide someone’s address simply saved the page and sent it by mail. A list of email addresses replicated with amazing speed
• Reliability. The lists were generated once a day on each server using a cron script, then the main server, also cron collected them and formed the very HTML page. If there was no channel, the information was not updated, saving the previous copy in case of a repeated lack of communication the following night led to a space in the branch's location in the list. Of course, the scripts developed and grew wiser, but how it was all not beautiful.
• Convenience. On good, I’d put this item first, but I’m the admin and I’m not always worried about the convenience of the users until the Mosk comes to me. For me, the main thing is that everything works and requires a minimum of my intervention. But, be that as it may, the directory has grown, fields for internal and city telephone numbers have been added, as well as posts, divisions and office numbers. These fields were filled optionally, but it was thanks to them that the directory became also a telephone. It became more and more difficult to find a person in such a list. At branches with slow channels, its loading became longer, in general something was brewing.

The choice of transport

I thought for a long time how to do everything, went through the options from replication using built-in MySQL tools to writing my own services listening to network ports. The answer all this time stood by and smirked. Is it all about email? Well, here's your transport. We don’t need realtime with a fractional second error, but within a minute everything will work if there is a channel. In the absence of a channel, the data will queue up and wait, then it will be delivered. In general, we only send, the rest is not our concern.

So, it is decided that data exchange will occur via email messages.

')

Algorithm

Data exchange will be one-way, mail servers will, as the admin user database is edited, send data on changes to the central server, where our directory will be located.

Implementation

First we decide on the format of the commands. Commands will be transmitted in the body of the letter. The content of the letter will be text / plain, there will be no attachments, which means there will be no MIME multipart with coding everything in base64 or quoted-printable.



The subject of the letter will contain the unique code of the division (branch) in order to uniquely identify which data will be changed. Also, this is a small protection from “hooligans” if any of the algorithms of our system are known, without knowing the subdivision code (rather tricky) no changes can be made, a letter with an unknown code will be simply ignored. In the body of the letter can be an arbitrary number of lines of the form:



_cmd=insert:type=mbox:name= :eml=ivanov@company.ru:vis=1:gor=88-02-93:mer=38-32-93:sot=:of=214:post= :sdiv=

_cmd=update:type=mbox:eml=petrov@company.ru:name= :act=1:vis=1:post=:of=822:mer=27-71:gor=88-02-49:sot=:sdiv= .

_cmd=delete:type=mbox:eml=sidorov@company.ru



As can be seen from these lines to uniquely identify the record we will have an email address. Usually there will be one command (string) in the letter, because for efficiency, there is no point in accumulating commands. However, occasionally different scripts will use the ability to use one letter for a whole package of commands.



Accept commands will be a script written in perl. To send letters with commands to the input stream of the script, a mailbox accessible only from the internal network will be used. To implement on my system (postfix on FreeBSD), I simply created such a mail alias in / etc / mail / aliases:



userdir: "|/home/admin/directory/userdir.pl"



Here I’ll quote his userdir.pl listing that is very short, in order not to overload the already large article:

#! / usr / bin / perl -w



use Email :: Simple ;

use MIME :: Base64 ( ) ;

use DBI ;

...





# Hash of field names for converting system commands to SQL

my % fnames = (

name => 'name' ,

gor => 'gtel' ,

...

) ;

...



The message as is is received on the input stream of our script

# Write it entirely to a variable

while ( <> )

{

$ message . = $ _ ;

}



# Parsim variable, selecting the data we need

my $ email = new Email :: Simple ( $ message ) ;



my $ from = $ email -> header ( "From" ) ;

my $ to = $ email -> header ( "To" ) ;

my $ subj = $ email -> header ( "Subject" ) ;

...



# On the subject of the letter we determine the unit ID

my $ sth = $ dbh -> prepare ( "select id from divs where code =?" ) ;

if ( $ sth -> execute ( $ subj ) )

{

if ( defined ( $ div_code = $ sth -> fetchrow ( ) ) ) { $ div_id = $ div_code ; }

}

$ sth -> finish ( ) ;

...



if ( $ div_id ne 'empty' )

{

$ cmd = ~ s / n // ;

# $ cmd = ~ tr / // g;



my @lines = split ( /; / , $ cmd ) ;

...



foreach $ line ( @lines )

{

# Here the command is immediately parsed.

chomp ( $ line ) ;

$ line = ~ s / n // ;



if ( $ line = ~ / ^ _ cmd / )

{

my @fields = split ( /: / , $ line ) ;

my $ debug_str = '' ;



foreach $ field ( @fields )

{

my @prms = split ( / = / , $ field ) ;

if ( $ # prms == 1 ) { $ params { $ prms [ 0 ] } = $ prms [ 1 ] ; }

$ debug_str . = "$ prms [0] = $ params {$ prms [0]};" ;

}

}



Next, the received commands are already being processed.

if ( $ params { '_cmd' } eq 'insert' ) ########################## INSERT cmd

{

my $ qry = '' ;

my $ qry_valid = 0 ;



if ( $ params { 'type' } eq 'mbox' ) ######## Mailbox

{

$ qry = "insert into persons (" ;

my $ dfields = '' ;

my $ fields_cnt = 0 ;



while ( ( $ key , $ value ) = each ( % params ) )

{

if ( defined ( $ fnames { $ key } ) && $ params { $ key } ne '' )

{

...

}

}

...

}

elsif ( $ params { 'type' } eq 'alias' )

...

}

...

# Next, other commands are processed in the same way.

# and write data to the database

}

}

Now it only remains to make the formation of the team when editing mail accounts and sending it to the receiver described above. Since I wrote the web interface myself, it was not difficult to do this, for example, the component for the Yii Framework, which is used in the afterSave model:

/ *

To set properties throught config file main.php you have to add this lines to file



'components' => array (

...

'Userdir' => array (

'class' => 'Userdir',

'divId' => '0001',

'userdirAddr' => 'admin@company.ru',

),

...

),



* /



class Userdir extends CApplicationComponent

{

private $ _divId ;

private $ _userdirAddr ;

private $ _isLoaded = false ;

private $ _transNames = array (

'name' => 'name' ,

'username' => 'eml' ,

'post' => 'post' ,

'phone_gor' => 'gor' ,

...

) ;

private $ _cmdLine = '' ;



// Working with properties



public function getDivId ( )

{

return $ this -> _divId ;

}



public function setDivId ( $ value )

{

$ this -> _divId = $ value ;

}



public function getUserdirAddr ( )

{

return $ this -> _userdirAddr ;

}



public function setUserdirAddr ( $ value )

{

$ this -> _userdirAddr = $ value ;

}



// Public methods



public function load ( $ attrs , $ cmd , $ type = 'mbox' )

{

$ this -> _cmdLine = '_cmd =' . $ cmd . ': type =' . $ type ;



foreach ( $ attrs as $ key => $ value )

{

if ( isset ( $ this -> _transNames [ $ key ] ) )

{

$ encodedValue = iconv ( 'UTF-8' , 'cp1251' , $ value ) ;

$ this -> _cmdLine . = ':' . $ this -> _transNames [ $ key ] . '=' . $ encodedValue ;

}

}



$ this -> _cmdLine . = "n" ;

$ this -> _isLoaded = true ;

}



public function send ( $ params = null )

{

if ( $ this -> _isLoaded )

{

return mail ( $ this -> _userdirAddr , $ this -> _divId , $ this -> _cmdLine ) ;

}



return true ;

}

}

In afterSave we write something like:

// Userdir replication



$ userdir = Yii :: app ( ) -> Userdir ;

$ userdir -> setDivId ( $ div_id = Domain :: model ( ) -> find ( "domain = ' $ this-> domain '" ) -> userdir_id ) ;



$ _userdirModes = array (

'phones' => 'update' ,

'update' => 'update' ,

'create' => 'insert' ,

'delete' => 'delete' ,

'active' => 'update' ,

) ;



if ( isset ( $ _userdirModes [ $ this -> scenario ] ) )

{

/ * Edit * /

$ userdir -> load ( $ this -> attributes , $ _userdirModes [ $ this -> scenario ] ) ;

}



if ( ! $ retCode = $ userdir -> send ( ) ) return $ retCode ;

And now everything is automatically updated here.



The system has been implemented and is successfully operating for the third year. I wrote a little messy, tried to cut, if anyone is interested - I will answer any questions.