sqlgrey ostrich
graphic courtesy of Hajo Schatz
postfix mouseperl camel

SQLgrey

Sourceforge project page

What is SQLgrey ?

SQLgrey is a postfix policy service implementing a grey-listing policy.

SQLgrey is written in Perl and uses DBI to access an SQL database.

Its goal is reducing the SPAM reaching user mailboxes

Software dependencies

SQLgrey needs:

Download

You can find the latest versions on the sourceforge project page: download.

RPMs can be built by running:

rpmbuild -ta sqlgrey-<version>.tar.bz2

Gentoo users can fetch a stable Gentoo ebuild here and a development ebuild here.

Current state

SQLgrey gained the following features since the beginning:

Performance

SQLgrey has successfully been used on mail systems handling more than a million mails per day (the database tuning done for these sites is in all releases after 1.5.0).

Development

Even subversion releases are considered stable, bugfix only releases, odd subversion releases are for developpers, betatesters and people living on the bleeding edge.

The latest stable release is SQLgrey-1.6.8
The latest development release is SQLgrey-1.7.6.

Development release highlights:

Documentation

Run sqlgrey with "--man" option and read the included HOWTO. The example sqlgrey.conf file is well commented too. If something isn't clear enough it's a documentation bug, please contact me (see the footer).

What is grey-listing ?

Grey-listing is a mechanism implemented at the MTA (mail transfer agent) level. The goal is to prevent most junk mails (spams and virus alike) to reach the users' mailboxes.

When a mailbox receives an email from an unknown contact (IP), it is first rejected with a "try again later"-message (SMTP 450 error). It gets delayed until the sender tries again (the sender is required by the SMTP protocol to retry the delivery).

Most of the MTAs do retry between 6 and 30 minutes after the first attempt, some (usually mailing-list managers) can wait for as much as 4 hours.

Note: to alleviate the fact that some ISPs use pools of servers, if reverse DNS is available and the name passes some sanity checks, SQLgrey by default won't check if a retry is done from the same IP but from the same class C network.

On the second try (after a configurable delay) the grey-listing mechanism accepts the email.

Nowadays most unsollicited mails are not sent out using RFC compliant MTAs; the spamming software will not try again later. Virus using their own SMTP engine are blocked too.

Is it effective ?

Yes, this really cuts the SPAM volume by a magnitude order in the end-users' mailboxes. grey-listing algorithms don't need much CPU power compared to rule-based filters like SpamAssassin or statistical filters like DSPAM. So adding grey-listing to your anti-SPAM toolkit brings you 2 benefits:

Spammers could adapt their SMTP engines in the future but there are two problems for them to overcome:

Nevertheless if you have a SPAM problem now, grey-listing is a great tool.

Origins

SQLgrey is a fork of the postgrey grey-listing service for Postfix.

At the time of the fork postgrey used a local BDB database for storing connections and didn't use auto-white-listing. postgrey is still maintained, if you're not interested in storing the data in a database (for easier inspection, scaling, distribution of greylisting policy servers, ...) you should check postgrey current state.

The goals were :

Drawbacks

Delay

The main drawback of grey-listing as it was done in early implementations was the delaying of legit emails.

This is nearly solved by the auto-white-listing mechanisms built into SQLgrey:

The delay shouldn't really be a drawback anymore: users will only experience delays (mostly in the 6 to 30 minutes range) when receiving the first email from a given sender. And on a large scale, new senders from a given domain are automatically accepted from the IPs previously used for this domain email traffic.

Non-delivery

Some rare MTAs (a known list is distributed with SQLgrey as clients_ip_whitelist and clients_fqdn_whitelist in order to solve the problem, these files can be updated with the 'update_sqlgrey_config' command) don't retry a failed email transfer in a standard way.

If new ones appear, the administrator will have to add them to /etc/sqlgrey/clients_ip_whitelist.local or /etc/sqlgrey/clients_fqdn_whitelist.local (which is preferred if there's a good reverse dns entry), they will be instantly recognised and used by SQLgrey.

In these rare cases, the sender will be noticed of the problem (by a non-delivery message) and will report this through another communication medium (phone, snail-mail, IM, ...). The administrator can look into SQLgrey logs (sent to syslog with MAIL facility, should end in /var/log/maillog or /var/log/mail.log depending on the syslog configuration) to have the trace of every connection attempts accepted/refused by SQLgrey.

This is detailed in the HOWTO file distributed with SQLgrey.

Software

Other grey-listing daemons

Written by Lionel Bouton using vi. Hosted by sourceforge.net SourceForge.net Logo