SQLgrey and whitelisting senders

Submitted by mhy on Tue, 11/03/2015 - 15:26

I implemented whitelisting of sender addresses and domains in SQLgrey. Now you can whitelist senders that should never be greylisted, and you can let outgoing email automatically update the whitelist.

On my mail server I use postfix, amavisd-new, clamav, and SQLgrey. SQLgrey implements greylisting for postfix using MySQL as repository. There is a web interface called sgwi that allows you to view and modify the SQLgrey database tables.

I have recently been looking at ASSP, a spam filtering SMTP proxy server. One neat feature of ASSP is the ability to whitelist senders so that they will not be greylisted. The neat thing is that the whitelist is automatically updated whenever you send an email to someone: the recipient is added to the whitelist so that the recipient's reply will not be greylisted.

This "whitelisting of sender addresses" feature is missing in SQLgrey, so I decided to implement it. (SQLgrey comes with optin/optout functionality, but this is for the recipient address.)

  1. First of all I modified SQLgrey 1.7.6 by adding two new database tables named "whitelist" and "whitelist_exclude":
    • "whitelist" contains sender email addresses or domains that are whitelisted, i.e. will not be greylisted.
    • "whitelist_exclude" contains sender email addresses or domains that should never be whitelisted, i.e. will always be greylisted.
    SQLgrey now creates these tables and checks them to see if greylisting should be applied to a particular email.
  2. Second, I modified sgwi so that I could view and modify the new database tables.
  3. Third, I wrote a postfix content filter by modifying dkimproxy, see the story about that. The proxy automatically adds email addresses to the whitelist when outgoing mail is sent.

The idea is that when I send an outgoing email, I use a special smtp port, e.g. 587:

client ---> (587) smtpd -----> (10026) dkimproxy-----> (10027) smtpd ----> next smtp server

Incoming mail will normally be received on port 25 (the standard smtp port), which doesn't do whitelisting, while I send my outgoing mail on port 587. To be sure the port won't be abused, I have configured it to require SASL authentication.

Here is how I configured the postfix master.cf file:

# ====================================================================
# DKIM signature and SQLgrey whitelisting
#
# Mail is sent to filter on localhost:10026, and received back on localhost:10027
# (advanced after-queue filter according to http://www.postfix.org/FILTER_README.html#advanced_filter)
#
587     inet  n       -       n       -       -       smtpd
    -o content_filter=scan:localhost:10026
    -o receive_override_options=no_address_mappings
    -o smtpd_enforce_tls=yes
    -o smtpd_delay_reject=yes
    -o smtpd_client_restrictions=
    -o smtpd_helo_restrictions=
    -o smtpd_sender_restrictions=
    -o smtpd_recipient_restrictions=permit_mynetworks,permit_sasl_authenticated,reject

scan      unix  -       -       n       -       4      smtp
    -o smtp_send_xforward_command=yes
    -o disable_mime_output_conversion=yes
    -o smtp_generic_maps=

localhost:10027 inet  n       -       n       -       5      smtpd
    -o content_filter=
    -o receive_override_options=no_unknown_recipient_checks,no_header_body_checks,no_milters
    -o smtpd_helo_restrictions=
    -o smtpd_client_restrictions=
    -o smtpd_sender_restrictions=
    -o smtpd_recipient_restrictions=permit_mynetworks,reject
    -o mynetworks=127.0.0.0/8
    -o smtpd_authorized_xforward_hosts=127.0.0.0/8
 

The lines above define the two smtpd listeners on ports 587 and 10027. The dkimproxy process, which is started separately, listens on 10026 and sends to 10027.

Attached are two files that contain the whitelisting patches I made to SQLgrey and sgwi.

Update 090526: The whitelist now keeps track of the last send and receive dates.
Update 100209: Updated the sgwi patch for sgwi 0.8. Note however that the patch for the copyright.inc.php file will fail until the sgwi version number has been updated correctly to 0.8 Image removed.
Update 100428: Added patch for SQLgrey 1.8.0-rc2. I cleaned up the patch a bit, removed some unnecessary changes.
Update 101112: Added patch for sgwi 1.1.1.
Update 110816: Added patch for sgwi 1.1.2.
Update 131223: Updated patch for sgwi 1.1.2.
Update 140220: Added patches for SQLgrey 1.8.0 for sgwi 1.1.6.
Update 151103: Added patch for sgwi 1.1.8.
Update 161110: Updated patch for sgwi 1.1.8.

Submitted by Anonymous (not verified) on Wed, 09/09/2009 - 02:01

Permalink

Hi Magnus.  This looks great and exactly what I am trying to implement.  Regarding the two files, are these replacment files for the originals?  If not, how do I implement your work?  My knowledge of linux is basic to intermediate so please forgive my ignorance.   Thanks. Simon

Submitted by Anonymous (not verified) on Thu, 10/08/2009 - 23:55

In reply to by Anonymous (not verified)

Permalink

Hello..Great tool...you can run patch -p1 -i <saved-patch-file-name> and replace your current sqlgrey file with new one!!!!

Submitted by Anonymous (not verified) on Sun, 01/31/2010 - 21:34

In reply to by Anonymous (not verified)

Permalink

will you update your patch for the current version?unfortunately i cant't patch my version, 'cause it's newer than yours. i get the following error: unk #1 succeeded at 48 (offset -2 lines).Hunk #2 succeeded at 81 (offset -10 lines).Hunk #3 succeeded at 414 (offset -4 lines).Hunk #4 succeeded at 961 (offset -47 lines).Hunk #5 succeeded at 1808 (offset -139 lines).Hunk #6 succeeded at 1831 (offset -138 lines).Hunk #7 succeeded at 1854 (offset -137 lines).Hunk #8 FAILED at 1876.Hunk #9 succeeded at 2085 with fuzz 1 (offset -228 lines).

Submitted by Anonymous (not verified) on Fri, 02/26/2010 - 20:56

Permalink

Hi,I'm using sqlgrey since 2008 with your patch and really appreciate your work so here are some comments from my side.The patch also works with version 1.8.0-rc2 of sqlgrey, only a minor change to the patchfile was necessary. I had to update the version number from 1.7.6 to 1.8.0-rc2 .208 +++ sqlgrey/sqlgrey.spec     2009-05-26 12:04:27.000000000 +0200209 @@ -1,6 +1,6 @@210  %define name sqlgrey211  %define ver  1.8.0-rc2212 -%define rel  1213 +%define rel  2Oh, and something else ... Since I used sqlgrey 1.7.6 with your first patch before, the one without the "Last sent to" and "Last received from" columns, I got database errors after starting sqlgrey 1.8.0-rc2 and receiving mail for the first time.I figured out that the columns last_sent_to and last_rcvd_from in the whitelist table were missing because the table already existed and the columns are only created if the table doesn't exist. So after adding them manually everything worked fine. I used the column definitions from the patchfile."last_sent_to datetime NOT NULL DEFAULT '0000-00-00 00:00:00'" "last_rcvd_from datetime NOT NULL DEFAULT '0000-00-00 00:00:00'" Kind Regards and thanks a lot for your workMatthias Marx

Hi Mattias,Thanks for the input! I wasn't aware that new work was being done on sqlgrey, but now I've upgraded and created an updated patch.Magnus

Submitted by Anonymous (not verified) on Wed, 07/21/2010 - 16:25

Permalink

Hi, can u say clear what tables I need to creat and what those structure?for whitelist_exlude, its one with name address(varchar255), but for whitelist cant get it

Submitted by mhy on Tue, 08/24/2010 - 00:40

In reply to by Anonymous (not verified)

Permalink

Hi, actually sqlgrey will create all necessary tables. But should you need it:whitelist table:CREATE TABLE whitelist (address varchar(255) NOT NULL,   last_sent_to datetime NOT NULL DEFAULT '0000-00-00 00:00:00',   last_rcvd_from datetime NOT NULL DEFAULT '0000-00-00 00:00:00',   PRIMARY KEY (address));whitelist_exclude table:CREATE TABLE whitelist_exclude (address varchar(255) NOT NULL,    PRIMARY KEY (address));

Submitted by Anonymous (not verified) on Fri, 05/15/2015 - 12:03

Permalink

Hi,Thanks a lot for your patch for sqlgrey-1.8.0-rc2 and sgwi-1.1.6 it's OKThere a new sgwi version : 1.1.7Are you going to patch this version too ;))?Thank you for your workregards

Submitted by mhy on Tue, 11/03/2015 - 00:35

In reply to by Anonymous (not verified)

Permalink

It's been a long time since I looked at this, but now I updated to sgwi 1.1.8 and added a patch for it.

Submitted by Anonymous (not verified) on Mon, 02/22/2016 - 08:33

Permalink

Hi if we want add "*@domaine.com" in whitelist, it's possible ? or not good synthax ? *@domaine.com@domaine.comdomain.com? thanks

Submitted by Anonymous (not verified) on Sun, 03/06/2016 - 17:10

Permalink

my $sth = $self->prepare_cached("SELECT * FROM $whitelist_exclude WHERE address = ? OR address = ? OR ? LIKE CONCAT('%.', address )");if (!defined $sth or !$sth->execute($email,$domain,$domain)) {