I recently created a set-up for iPayroll Ltd to push Postfix log files in a summarized form into a PostgreSQL database, and they generously agreed – and paid me for my time –  to share this howto.

Although there was a fair amount of information on a basic setup, all the information I found online fell short of what I wanted to do, which was create as relatively simple table including the from address, to address and status of the message at various stages, without the need to manually patch together what happened based on the message ID.  Using http://185iq.blogspot.co.nz/2010/05/postfix-rsyslog.html as a starting point, this is the solution I came up with.

Note that the solution I came up with will only work with PostgreSQL as it uses a stored procedure, although I am sure it can be modified to work with MySQL.

The installation of PostgreSQL, Postfix are beyond the scope of this document which assumes that Postgres and Postfix is working correctly.

Setting up the Database

To set up the appropriate tables in a (pre-existing) database connect to that database with administrator privileges.

First create the table with the following commands –

CREATE TABLE deliverystatus (
  id serial,
  ReportedTime timestamp NOT NULL,
  ClientIP varchar NOT NULL,
  MailFrom varchar NOT NULL,
  MailTo varchar NOT NULL,
  MessageID varchar(12) NOT NULL,
  Status varchar NOT NULL,
  PRIMARY KEY (id)
) ;

create index on deliverystatus (MessageID);

depending on your usage case you may want to create some additional indexes on this table to speed up searching. MailFrom, Mailto and ReportedTime are all reasonable candidates for indexes. Further indexes can be created with a command with the syntax “create index on deliverystatus (Fieldname)”.

Next set up permissions required for a new user to log to this database. The stored procedure I use requires select permissions. I’ve used “BadPassword” as the password here, change it to something random –

create role logwriter with login encrypted password 'BadPassword';
grant select on table deliverystatus to logwriter;
grant insert on table deliverystatus to logwriter;
grant delete on table deliverystatus to logwriter;
grant all on sequence deliverystatus_id_seq to logwriter;

Now the secret sauce – create the stored procedure with the following code

create function updatedeliverystatus (
                MessageIDVal varchar, 
                ReportedTimeVal timestamp, 
                ClientIPVal varchar, 
                MailFromVal varchar, 
                MailToVal varchar, 
                StatusVal varchar
                ) RETURNS VOID AS $$
DECLARE
        emailrecord deliverystatus%ROWTYPE;
BEGIN

        IF MessageIDVal != '' then

                SELECT * into emailrecord from deliverystatus WHERE MessageID=MessageIDVal and Mai
lFrom != '' limit 1;

                IF found then
                   MailFromVal:=emailrecord.MailFrom;
                END IF; 

                IF StatusVal not like '%removed' THEN
                        DELETE from deliverystatus where messageid=MessageIDVal and MailTo='';
                        INSERT into deliverystatus 
                                (ReportedTime,ClientIP,MailFrom,MailTo,MessageID,status)
                                values
                                (ReportedTimeVal,ClientIPVal,MailFromVal,MailToVal,MessageIDVal,StatusVal);

                END IF;
        END IF;

END
$$
LANGUAGE plpgsql

 

Setting up rsyslogd

To replace syslogd with rsyslogd in CentOS from the command line –

yum install rsyslog rsyslog-pgsql
/sbin/chkconfig syslogd off
/sbin/chkconfig rsyslogd on

(Or in Debian/Ubuntu it would seem to be apt-get install rsyslog rsyslog-pgsql, refusing to set up the database when requested and commenting out the contents of /etc/rsyslog.d/pgsql.conf)

Add the lines below near the top of /etc/rsyslog.conf to enable logging. Note that this provides additional logging, all standard logging is left in place, meaning that you you can still refer to the raw logs if the need arises. (Not all the intricacies of the delivery are captured in the database – just the stuff I most commonly want to know about as an administrator asked to check if an email was sent or received)

Remember to change the password in the mail.info line !!!

$ModLoad ompgsql

$template logmail,"select updatedeliverystatus('%msg:R,ERE,1,BLANK:(([A-Z]|[0-9]){11}):--end%','%timereported:::date-rfc3339%','%msg:R,ERE,3,BLANK:(from |client=)([a-z]+|[A-Z]+|\.|\-|[0-9]+)+\[([0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3})\]--end%','%msg:R,ERE,1,BLANK:from=, size--end%','%msg:R,ERE,1,BLANK:to=<(([a-zA-Z0-9@]|[.!#$%&'*+-/=?`{|}~_])+)>--end%','%msg:R,ERE,0,FIELD:relay=(.*)--end%')",STDSQL

$WorkDirectory          /var/spool/rsyslogtmp
$ActionQueueType        LinkedList
$ActionQueueFileName    dbq
$ActionResumeRetryCount -1

# Log all the mail messages in one place.
mail.info           :ompgsql:127.0.0.1,mail,logwriter,BadPassword;logmail

You may also need to create the directory /var/spool/rsyslogtmp and restart rsyslogd.

mkdir /var/spool/rsyslogtmp
/etc/init.d/rsyslogd restart

All going well you should now have a new database table “deliverylog” with useful information. Fields I envisage will be used most often are ReportedTime, MailFrom, MailTo, MessageID, Status.

Hopefully this should “just work”.  If you want to tweek it or need to debug as I did, you may find the following information useful

• http://www.rsyslog.com/regex/  to debug the regex used to pull the appropriate bits out of the database.
• Run  /sbin/rsyslogd -n 1  to check the validity of your configuration file.
• If adapting this methodology to a MySQL database you probably need to change the last word in the $template line in rsyslogd.conf from STDSQL to SQL for correct escaping of special characters in the log file (and if you get it working please send me a like to how you did it or send me the stored procedure so I can point others in the correct direction !)
• Telling PostgreSQL to log errors (or all statements) and looking at what SQL the stored procedure is doing.  Your configuration might be slightly different, but I edited /var/lib/pgsql/ver.no/data/postgresql.conf – I made the following change – “log_min_error_statement = error”.   Earlier on I changed log_min_duration_statement to “0″ to log all queries.  Logs were written to /var/lib/postgresql/ver.no/data/pg_log/that_days.log.