Fail2Ban and Brute-Force Password attacks on WordPress

I maintain a server hosting a fair number of WordPress blogs and I get inundated with brute-force password attempts.    In order to minimize the likelyhood of success of an attack, I have taken to limiting the number of login attempts I’ve customised some Fail2Ban rules to provide “overriding” lockout of accounts.

The code certainly has its limitations – for example it will – without warning –  temporarily lock out people who have forgotten their passwords, however for the most part it works pretty well.

One of the things I’ve noticed recently is that some attempts are persistent – they will continue to try log in even when null-routed, and for long periods of time.  I’ve thus written a second rule which looks through the fail2ban logs and bans – for an extended period – anyone which has been banned more then a few times.   This further reduces the likelyhood of a compromise, and also reduces the amount of “fail2ban spam” I receive, ie notifications of a ban being put in place.

Additionally, I’ve come up with a custom rule to ban IP’s sniffing around for a wordpress site where none exists.

The appropriate Fail2Ban rules are as follows –


# Fail2Ban configuration file
# Author: Tim Connors
# Tweeked by David Go


# Ignore specific client who often forgets password.
ignoreip = XXX.XXX.XXX.XXX

# Option:  failregex
# Notes.:  Regexp to catch Apache dictionary attacks on Wrodpress wp-login
# Values:  TEXT
failregex = :80 <HOST> -.*(GET|POST).*/wp-login.php.*(HTTP)
:443 <HOST> -.*(GET|POST).*/wp-login.php.*(HTTP)


# Fail2Ban configuration file
# Author:  David Go


# Option:  failregex
# Notes.:  Regexp to catch Apache dictionary attacks on Wrodpress wp-login
# Values:  TEXT
#failregex = <HOST>.*] "POST /wp-login.php

failregex = \[client <HOST>\] script \'/PATH/TO/VIRTUALHOSTSl/(.*)/wp-login.php\' not found or unable to stat


# Fail2Ban configuration file


# Make sure we never lock ourselves out.

failregex = fail2ban.actions: WARNING.* Ban <HOST

And, of-course, the appropriate lines in jail.conf

maxretry = 3
findtime = 180
bantime = 14400
enabled = true
port    = http,https
filter  = apache-wp-probe
logpath = /var/log/apache2/error.log
action  = iptables-multiport[name=wpprobe, port="80,443", protocol=tcp]

maxretry = 3
#findtime = 14400
bantime = 14400
enabled = true
port    = http,https
filter  = apache-wp-probe2
logpath = /var/log/apache2/error.log
action  = iptables-multiport[name=wpprobe2, port="80,443", protocol=tcp]

maxretry = 3
enabled = true
filter = persistentban
findtime = 3600
bantime = 86400
logpath = /var/log/fail2ban.log
action = iptables-multiport[name=multiban, port="80,443,21", protocol=tcp]

Review of the Domain DM-DV703USB 2 DIN video/mp3/cd car stereo

My Toyota VITZ 2004 came with a “Japanese Only” Stereo, which included a reverse camera. To get the reverse camera working, in addition to some wiring tweaks (See my post on “Original Toyota Reverse Light Camera on 2004 Vitz/Echo with an aftermarket stereo“).

As I can’t call myself an audiophile, and I can be tight-fisted (who wants to spend 1/10th of a run-around vehicles value on expensive stereo equipment – especially when it spends time in a “not-that-good” neighbourhood), I decided to purchase a budget stereo off Trademe.

I picked up a Domain 7″ DVD/CD/USB/SD receiver, model DM-DV703USB from Sound Tech for arround $200 (you can get a similar one from Jonvy), and tried to install it.

First the good news – It uses an ISO wiring harness, meaning I could just spend a few dollars converting the Toyota stereo cabling to work with this stereo, and it uses standard RCA inputs for video.   The wires are labled, so this is all quite straight forward.

When the unit starts up, it shows the stereo splash screen, complete with a picture of Auckland CBD (Sky Tower is clearly prominent).  From the lack of information about this stereo I thus presume its a Chinese Import which has been customised and labelled for a New Zealand Importer.

The screen looks quite readable, and there is a row of buttons down the bottom.   The front screen tilts down to reveal the CD and SD slots.  There is a mini USB port on the front.   One nice thing about this unit (which is not boasted about) is that it also has a full size rear USB port – which is what I plugged my memory stick into, as I was able to access this from underneath dash even when the radio was installed.

The sound was quite good – I’m no audiophile, but certainly nothing to complain about.  The stereo also seemed to work well, automatically picking up the station names as it found them.   I did not try playing any DVD’s.

I really wanted this unit to be good enough – and gave it my best – I even communicated with the manufacturer who eventually took the unit back – after convincing themselves the unit was not faulty.

Now the Problems.  Unfortunately they were – for me – dealbreakers.  I eventually returned the unit for a refund from the supplier.

There were some (tolerable) limitations to the stereo design – It relied very heavily on a resistive touch screen, so skipping tracks required taking ones eyes off the road for longer then is ideal.   Still, at $200, I could have lived with that.

The problem I could not live with were the software / touch screen bugs.  The software in this unit is clearly buggy.  The biggest problem is that intermittently the touch screen would just stop working.  Even restarting the vehicle or powering off the stereo would not fix it.    (I now believe removing the USB stick might have fixed the problem, I only worked this possibility out when I was removing the unit to send back to the seller).

See the video above to show me pushing at the touch screen and nothing happening, even after a reset and other attempts.

When the touch screen was not working, the remote also did not respond.

There were other intermittent software faults as well.   For example (and despite accusations it was not wired correctly – which don’t stand up to scrutiny as the video below shows), the reverse camera sometime stayed on even when not reversing.  (It could not have been a wiring issue as changing the inputs reset it to work, and there was music playing while the camera input was showing.  The reverse camera only had a video input, and when I was in reverse no sound was played, so this is clearly a software fault.

Conclusion – This would be a great deal if it worked properly.  There is nothing fundamentally wrong with the hardware, but the software just does not cut it for me – not by a long shot.   If, and its a big if, a firmware revision comes out fixing these bugs it might be worth looking at again, but I’m not holding my breath as the distributor never responded to my request for a firmware upgrade.

Original Toyota Reverse Light Camera on 2004 Vitz/Echo with an aftermarket stereo.

I recently got a 2004 Vitz with some cool factory upgrade.  (Most of the world has the Vitz under the “Echo” brand, the Vitz means its imported from Japan)  Coming from Japan, it came with a factory new stereo system which only worked in Japanese,  did not play MP3’s and committed Seppuku when unplugged from the battery.

(While this is a post about replacing the stereo, a quick note – Toyota New Zealand are absolute bastards, and I won’t do business with them.  They would not even tell me the part number or frequency of the remote the car uses – because they correctly knew I would pick up an aftermarket one for a fraction of the price.   I didn’t even ask them about the disk needed for the firmware for the radio – required to restore it to working (in Japanese) after the battery is unplugged – which I believe sells for over US$100 and can’t be easily found online.    I have resolved to ensure Toyota New Zealand never see a dime of my money – thus far they have lost out on a service and break fix for my other Toyota which my wife wanted to get done through a Toyota dealer).

Anyway, information on getting the factory reverse camera working with this vehicle is very hard to come by.  Using some educated guesses and a lot of Googling I managed to get my (new, cheap-and-cheerful Chinese import Radio/display) working.  Hopefully this information is useful to others.

The first thing to note is the camera is powered by the radio with a 6 volt supply.  While I don’t know the current draw, the wires are very thin, so I’d imagine its not much current.  Of-course, most stereos don’t supply 6 volts.   This is easily rectified with a LM7806 voltage regulator IC  and a couple of (possibly not needed) 100nf or similar caps.  I used 200nf ceramic ones.   The 7806 (rated to 1 amp dissipation) voltage regulator cost about $US3 from Jaycar with a small heat-sink.

Toyota 4 pin reversing camera plug
Toyota 4 pin reversing camera plug

The original connector to the stereo has 4 pins – 2 of them are ground (black and white, and connected together with a wire as per the image),  red being 6 volts, and yellow being the signal.

Bastardizing an RCA lead, I soldered the yellow wire to the centre pin and grounded the shielding wire against the chassis.   I fed this in to the reverse camera input of the stereo.

There is a 5 pin cable on the standard stereo with 3 wires, the green/white wire measuring 12 volts when the car is reversing.  I tapped this line both to power the camera through the LM7806 (ie indirectly to red wire) and the reverse signal input in the stereo – which means the output is automatically displayed when reversing.

lm7805-vitzThe wiring for the LM7806 is quite trivial.  Looking at it head on, the centre pin goes to ground/chassis, the left pin goes to the green wire (or another source of voltage > 8 volts DC) and the right pin is the regulated 6 volt DC which goes to the red pin of the reverse camera.   For good measure I threw a 200nf ceramic cap between the input voltage and ground, and another one between output voltage and ground.   (Some stuff on the Internet says this is not required, I did it because that’s what I was taught to do in another life when playing with its close cousin the LM7805 and TTL logic circuits, and I believe its best practice.  At least it can’t hurt – if you don’t somehow damage the 7805 while soldering them on !)   Unfortunately I lost the picture I took of this.

Although it took me a long time to do, it worked first try.

Alo Alo… Alo Vaikeli

I enjoy occasional Mugu baiting.  This was my first non-Internet attempt.

I recently had need to find a tenant for a rental property.   As the timing of this rental was off (it came available just before Christmas), there was not a lot of immediate interest in the property – however I did get one very keen applicant.

The year was 2013, and he introduced himself as Alo Vaikeli, providing a business card for Birkenhead Quality Fencing Limited, listing himself as a specialist and Director of the Company.  The phone numbers on this card were 022-1549264, and listed a company landline 09-482-2194.  (FWIW the card did not have a picture of him, otherwise I would have scanned it.)

Although badly dressed, this guy posed (badly) as a model tenant.  For a start he claimed to be a pastor at a church, and wanted to rent the back unit out for me as well, he also had 2 rental properties in South Auckland so he knew what it was like to be a landlord.  He had just sold his Birkenhead property to buy a large businesses and was planning on being a long term (very specifically 7 years in fact) tenant.   And here’s  the thing – he needed a place URGENTLY to move into because he had to move out of his house.

If Pigs could fly and his story was true it would have been any landlords wet dream – a long term, stable tenant with good community roots and money, wanting to move into a vacant property immediately.   The only concerns about taking on this tenant was that his English varied from quite good – when what he said made sense, to almost non-existant when questioning something which did not add up.  Oh , and that pigs don’t fly.

I decided to check him out, and play along to see where it went.  (Who knows, maybe GE pigs can fly and no one told me ?).    First stop, check out his company.   Well, good news.  His company does exist, and he was, indeed the sole director of it.   Bad news – it was struck off in 2008 –

As he wants the tenancy urgently, I advised him to bring references etc with him so he could fill out the paperwork and I could approve his application the following day.  He failed to do so.    He was definitely interested, and wanted to pay 2 weeks bond immediately + 1 week up front and move in (with the remainder coming later).   In fact, he wanted to move in on the Wednesday morning and pay the bond and sign the tenancy on Wednesday night….  I think he was upset when I would not let him move in until the bond and payment had been paid.

After chasing him to provide the application form if he wanted the property – he was keen-as – I received a form which was very cleverly filled in – Mr Big Business claimed to be earning $800 per week, but able to afford a $490 per week property – and yet, somehow, “forgot” to sign the application form… the bit which said I could do credit and reference checks and the like.    Strangely his form did not mension his Company, his address, his houses, the church where he was a pastor.

Also of interest, Birkenhead Quality Fencing Limited, despite having lots of staff has neither Internet nor a scanner – Its director had to go down to the Birkenhead Public Library to scan me the application form.  Also no email address.  Yet it lists on on Trademe – and touts for business there.

So, Of-course, I call Alo Vaikeli  to get more information – at which point it all falls apart for him.  The trigger was my casually worded comment “I need to do reference checks, after all, I don’t know that you are not a scammer” – which sent him into an angry tirade of accusing me of not trusting him, and he is as good as his word etc.   Unsurprisingly, he agreed with the BS I made up about reference checks required for insurance purposes – which he of-course would understand, owning mortgaged rental properties himself), but still would not sign the form, eventually, angrily backing out of the deal (more or less – I’m sure he would have been in like a shot if I had agreed to drop due diligence)

FWIW, BQF Limited (actually struck off) currently is advertising on Trademe – The associated login ID is manse6 and contact persdon is Alo Vaikeli – I’m not entirely sure why Trademe have not pulled his listing as of today – but it can be found at

Also of interest might be the post from this unlucky person – who was obviously upset enough to pay money to let the world – and me know.

Anyway, the idea behind this post is that if anyone googles Alo Vaikeli, director of Birkenhead Quality Fencing Limited – there are now at least 2 posts warning them to steer clear.








Getting UDEV and Touchpad to play together

One of the downsides of the HP Folio 13 is that the built in touchpad sucks – at least under Linux.  While there are some tweeks that can be done to make it “less bad”, the experience is never better then “OK in a pinch, but only just”.

One of the particularly irritating things is that the sensitivity – quite frequently I brush the touchpad while typing, which moves the cursor.

I have finally found a workable solution – leave the touchpad disabled when a mouse is plugged in, and enable when the mouse is removed.   Although conceptually simple,  the devil proved to be in the details with getting UDEV to “play nice” with my mouse.  I eventually came up with a very simple solution (this is for Ubuntu 12.04, but no doubt will work with other distro’s and versions – provided the file is correctly placed)

I created a file /etc/udev/rules.d/95-mouse.rules with the following content:

ACTION=="add",KERNEL=="mouse[0-9]",SUBSYSTEM=="input" RUN+="/bin/sh -c '/usr/bin/logger TouchpadOff; export DISPLAY=:0;/usr/bin/synclient TouchPadOff=1'"
ACTION=="remove",KERNEL=="mouse[0-9]",SUBSYSTEM=="input" RUN+="/bin/sh -c '/usr/bin/logger TouchpadOn; export DISPLAY=:0;/usr/bin/synclient TouchPadOff=0'"

This worked a treat. It should be practical to remove the usr/bin/logger TouchpadXXX; part – this simply logs the action to Syslog.

Some lessons I learnt along the way:

  1. Beware of ==  vs =   – It took a while to work out that “invalid rule ‘xxxxxx’ was occuring because I used = rather then ==
  2. Ensure the filename has ends “.rules”  – Somewhere along the way I landed up renaming it to “-rules”, and it silently stopped functioning.
  3. While you can identify the mouse using Attributes ATTRS{bInterfaceClass}==”03″,ATTRS{bInterfaceSubClass}==”01″,ATTRS{bInterfaceProtocol}==”02″ you can’t remove a device based on attributes – you need to use environment variables which UDEV sets instead.  (This was not a good solution here as it made the solution mouse specific)

HP Folio WPA2 Wifi on Ubuntu 12.04

The HP Folio comes with a Broadcom BCM4313 802.11n Wifi module.    Getting this to work with WPA2 proved a struggle, and I had just-about given up and ordered a USB WIFI stick to get things working.  Luckily things started working with the BCM4313 card. In the end, I’m not sure “what did it” that fixed it, but it now works.   This documents my knowledge and outcome.

Broadcom Drivers

There are, it seems, 3 broadcom drivers.

  1. The NDISWrapper (b43/b43legacy) does not work with the BCM4313 (but is apparently a work in progress)
  2. The brcmsmac appears as wlan0 if that driver is used.
  3. The wl driver appears as eth1, and this is the driver which eventually worked. I think this is also the default for an Ubuntu 12.04 install.

The Kernel

I initially started off with a 3.2.0-31-generic kernel, and tried installing the “wireless-compat” drivers.  This seemed to break things.  A number of reboots (yes, reboots, the initrd kept on getting rebuilt) later I am not using a kernel, and suspect that this upgrade is part of the solution.


I suspect part of the solution was reinstalling the WL driver after getting rid of the wifi-compat packages and instlaling the linux kernel.  To reinstall the wl driver

apt-get install --reinstall bcmwl-kernel-source

I tried using wifi-radar, wicd and even wpa_gui, but in the end wpa_supplicant is all that was required.

It was (I believe) necessary to manually bring the interface up initially.  I used the command

ifconfig eth1 up


iwlist eth1 scan

showed the available Access Points

In order to create the config I ran wpa_passphrase SSIDNAME Passphrase > /etc/ssid.conf

Bringing up the network is a matter of typing

wpa_supplicant -Dwext -ieth1 -c /etc/ssid.conf &



On-Demand RAID for Laptop with SSD and USB Disk

Summary:  DRBD provides a nifty way of allowing a “RAID when available” setup, which offers more flexibility then MDADM.  When in “RAID” mode, DRBD performs about 10% slower then MDADM, but provides near SSD performance when DRBD is not mirroring.  Importantly you don’t need to have the USB drive connected all the time, so its great if you want to grab your laptop and have it mirror to your USB disk when you get back to base.

The Project

I have an HP Folio Laptop with 128 gig Samsung MZPA128 SSD drive built in, and a 2.5″ spinning disk connected to my PC over USB3 when at home – running Ubuntu.  My research on SSD drives leads me to believe that they are about as unreliable as hard drives, but with the added disadvantage that when they fail, they normally fail catastrophically, unlike hard drives where you can often  recover most data.   While RAID is not a valid method of backing up, I want the “best of both worlds” in terms of performance and resiliency.

Tests and Benchmarks

The baseline tests for the SSD and HDD were performed using raw LVMs formatted with EXT4 (defaults).

Performance results for  SSD: bonnie++ -s 8000 -u 1000:1000

folio         8000M   798  99 190836  20 95514  10  4230  99 256948  14  6882 170
folio         8000M   812  99 189668  18 95897  10  4213  99 257911  13 13106 262
folio         8000M   789  99 177182  18 95188  10  4149  99 253408  15  6537 163

Performance results for HDD: bonnie++ -s 8000 -u 1000:1000

folio         8000M   751  97 65353   9 25092   5  3675  97 78205   7 148.6   5
folio         8000M   771  96 66600   9 25452   5  4131  96 75136   7 149.3   5


1 configuration stuff-up and complete reinstall later I have a raid array for testing built with the following command: mdadm –create /dev/md0 –level=mirror –write-behind=1024 –raid-devices=2 /dev/sda6 -W /dev/sdc1 -b /ssdgen/mdadm.bitmap

folio         8000M   805  98 33984   5 33185   5  3967  97 220468  12  2724  60
folio         8000M   783  99 34145   4 33485   5  4009  97 235770  12  2539  60

DRBD Protocol A

folio         8000M   761  98 31097   5 30644   6  3799  97 190543  14  2118  75
folio         8000M   798  99 31910   5 31631   6  4078  99 238925  12  2037  73

DRBD Protocol A, Unplugged

folio         8000M   811  99 110606  14 81152   8  4206 100 258376  13  2791 109

Setup to Support DRBD configuration.

I configured DRBD on top of LVM to allow the greatest flexibility with resizing file systems.  The configuration details I used were as follows:

root@folio:/home/davidgo# pvs
  PV         VG     Fmt  Attr PSize   PFree  
  /dev/sda5  intssd lvm2 a-   119.00g      0 
  /dev/sdc1  usbhdd lvm2 a-   298.09g 193.98g
root@folio:/home/davidgo# vgs
  VG     #PV #LV #SN Attr   VSize   VFree  
  intssd   1   3   0 wz--n- 119.00g      0 
  usbhdd   1   1   0 wz--n- 298.09g 193.98g
root@folio:/home/davidgo# lvs
  LV       VG     Attr   LSize   Origin Snap%  Move Log Copy%  Convert
  root     intssd -wi-ao  13.04g                                      
  ssd_drbd intssd -wi-ao 104.11g                                      
  swap     intssd -wi-ao   1.86g                                      
  hdd_drbd usbhdd -wi-ao 104.11g

The DRBD Configuration is

        usage-count no;

                # Rate in Megabytes
                rate 15M;

                max-buffers 250;

resource drbd0
        protocol A;
            become-primary-on folio;

        on  folio
            device /dev/drbd0;
            disk /dev/mapper/intssd-ssd_drbd;
            meta-disk "internal";

        on drbd2
            device /dev/drbd1;
            disk /dev/mapper/usbhdd-hdd_drbd;
            meta-disk "internal";

resource drbd1 
        protocol A;

        on  folio
                device /dev/drbd1;
                disk /dev/mapper/usbhdd-hdd_drbd;
                meta-disk "internal";

        on drbd2 
                device /dev/drbd0;
                disk /dev/mapper/intssd-ssd_drbd;
                meta-disk "internal";

In order to get “Automatic RAID when available working”, I usedle a custom script and UDEV. The UDEV command will need to be modified to match your device, but mine looks as follows:


# Generated by inspecting output of
# udevadm info -a -p $(udevadm info -q path -n /dev/sdd1)
# We can use "parents" info as well, not only the sdd block

KERNEL=="sd?1", ATTRS{product}=="SK7301", ATTRS{serial}=="000020110813",RUN+="/usr/local/bin/reconnectdrbd"


#! /bin/bash
/sbin/lvchange -a n /dev/usbhdd/hdd_drbd
/sbin/vgexport -a
sleep 2
/sbin/vgimport -a
/sbin/lvchange -a y /dev/usbhdd/hdd_drbd
/sbin/drbdadm attach drbd1

Remember to “chmod 755 /usr/local/reconnectdrbd”

If you have problems getting automatic recovery working (as I did), try running it manually after connecting the drive. If that works, check the udev rule is working and the script is being called (I found adding a line “/bin/date >> /tmp/debugme.log” and inspecting this file helped prove the udev rule was not matching).

This script almost certainly contains bits which are unnecessary, but its “good enough” for what I want to do.   (The same can probably be said of the DRBD configuration !!!)

Linux and Samsung CLX-3185FN

I recently acquired a Samsung CLX-3185FN colour laser MFC. Although the device claims to work with Linux, it is a bit of a mixed bag.  Below are my views and outcomes of lessons learned thus far.

Background and Review

FWIW I use Ubuntu 12.04 64-bit, and am only interested in connecting the device across a network.    I am blown away by the speed and quality of the printer for black-and white laser printing relative to my Brother Fax2820 [laser printer] and multi-page scanning relative to my HP Officejet 6500 [ scanner, theoretical occasional color printing which never worked because cartridges dried up ].

For black-and-white scanning to email this device outperforms the OKI MB470 I use at work, and leaves the HP Officejet 6500 for dust.  Using the defaults it produces very clear pdfs of small size.  I find the scanning interface unnecessarily clunky, for example defaulting to USB scanning when no USB device is connected, with no apparent way to change the default behaviour or set up a “speed scan” button – at least with the firmware installed.

Colour scanning (using the flat bed) is very slow relative to the HP Officejet, but produces (subjectively) impressive images.

I don’t like the exorbitant prices of replacement toners and the “DRM” implemented in the printer and cartridges to extort this – a set of cartridges (let alone drum) will set me back more then the printer.   I am aware of ways to hack this device to use much cheaper toner – something I will be experimenting with soon.

Unsolved Issue –  Printing Graphics

Even using the latest driver from Samsung, printing images in draft mode worked fine, however printing normal or high quality images did not work for the most part (but regular documents worked OK).

I did manage to get a couple of pictures to work, and thought I had solved the problem, but have been unable to reproduce the feat, and Samsung offered exactly no help (see below).   I have, however come up with a work-around – Despite what the manual says, it is possible to print colour jpg files from a USB key plugged into the front of the device.  Its not fast or particularly convenient, but it works.

For reference, the presenting problem is/was that the image was duplicated twice on the page, stretched to full length, with white gaps.  The higher the quality of the image sent to the printer, the bigger the gaps.

Both GIMP and GTHUMB exhibited the same problem.

I have not had a chance to pursue it (I have a great HL5340D black and white printer, and am using the Samsung for scanning – and the very occasional picture for my son), but it looks like there may be an unofficial forum which can help –

Solved Issues


Initially I could not get the printer to work with XSANE, no matter what drivers I tried to use from Samsung or what I configured, however the solution turned out to be trivial to fix – The solution was simply a matter of adding the line “tcp” to /etc/sane.d/xerox_mfp.conf  – This works a charm – pity the manual is devoid of any hint of this solution.  It almost goes without saying that you would replace with the IP address of your scanner.


I installed other Samsung Linux software – Smartpanel and PSU.  Neither of these pieces of software provided any meaningful functionality and I would not bother with them again.

Also of note was Samsungs complete cop-out when it comes to supporting Linux. In response to a reasonably detailed email asking for help with the issues I was having with colour printing, their response (grammatical errors and all) was:

Thank you for your email, Unfortunately we can’t suppor Linux as it’s all open source

WE provide the drivers for the unit you have but can’t provide any further support on the operating system

All I can suggest is that you try and install the unit on a PC and see if you’re stull getting the same problem IF you are then return the unit back to the store as there may be a fault with it

DB logging With Postfix + Postgresql

Summarised database logging using Postfix and Postgresql

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 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,
) ;

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 $$
        emailrecord deliverystatus%ROWTYPE;

        IF MessageIDVal != '' then

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

                IF found then
                END IF; 

                IF StatusVal not like '%removed' THEN
                        DELETE from deliverystatus where messageid=MessageIDVal and MailTo='';
                        INSERT into deliverystatus 

                END IF;
        END IF;

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 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=&lt;(([a-zA-Z0-9@]|[.!#$%&amp;'*+-/=?`{|}~_])+)&gt;--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.           :ompgsql:,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

  •  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/ – 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/