sqlgrey-users Mailing List for SQLgrey Postfix greylisting service

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

I wrote a web-interface for sqlgrey.
It's here: http://www.vanheusden.com/sgwi/sqlgreywebinterface-0.1.tgz


Folkert van Heusden

- -- 
Try MultiTail! Multiple windows with logfiles, filtered with regular
expressions, colored output, etc. etc. www.vanheusden.com/multitail/
- ----------------------------------------------------------------------
Get your PGP/GPG key signed at www.biglumber.com!
- ----------------------------------------------------------------------
Phone: +31-6-41278122, PGP-key: 1F28D8AE, www.vanheusden.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iIMEARECAEMFAkMV5Xw8Gmh0dHA6Ly93d3cudmFuaGV1c2Rlbi5jb20vZGF0YS1z
aWduaW5nLXdpdGgtcGdwLXBvbGljeS5odG1sAAoJEDAZDowfKNiudcYAoJKjgoPQ
shM75uzmvrNz0jM3Q7D+AJ0XnclXv5M3aPfXro3/14QBIRy5Nw==
=Apvh
-----END PGP SIGNATURE-----

Ray Booysen wrote the following on 18.08.2005 21:56 :

>
> Hi Lionel
>
> This brings me to a point that I have been thinking about for a 
> while.  How large is this white list going to become over time?  
> Surely over time this file is going to get quite large?


Could be. But we have orders of magnitude of growth before it could mean 
a slowdown and it would only occur when updating them or starting 
SQLgrey, not when it is already running.

>   Another point is that shouldn't it form a table in the sql-grey 
> database?
>

The earliest versions of SQLgrey had a whitelist table, but it was 
dropped because it wasn't convenient enough (and it was based on a 
proprietary PostgreSQL data type).

The whitelists being in files and separated in official/local versions 
have several benefits:
- the official versions are easily synced with a repository, which means 
that each admin regularly launching update_sqlgrey_config benefits from 
what others have already found automatically,
- it's trivial to add entries in the local versions when needed (pending 
their addition in the official version),
- in the common cases, the whitelist entries are put in a hash in memory 
which is by far faster than querying the database, the regexp and 
wildcard entries are in a list (no way of using a hash here) which is 
slower but still far faster than SQL queries (they wouldn't be able to 
benefit from indices if put in database anyway).

In fact, if you find out that a handful of IPs/fqdns are trusted sources 
of trafic and that are related to a fair percentage of your SQL queries 
(run the log parsing tool to have the top AWL matches) you can add them 
to the local whitelists to lighten the load on the database.

To sum it up, by nature the database is the best tool for the 
greylisting/AWL work (all the related tables get regular INSERT, UPDATE 
and DELETE operations, which would be costly in processing with 
plainfiles and a pain to distribute amongst several hosts), the files 
are more suited to ponctual adjustements.

Lionel

Lionel Bouton wrote:

> Hi,
>
> This is a first: a software company asked for entries in the whitelist 
> of all greylisting implementation. Their (Ciphire Labs) software needs 
> a challenge-response over SMTP which must complete in less than 60 
> minutes. Greylisting usually is ok, but several implementations 
> recommend 2 hours for the reconnect delay which breaks their soft.
>
> To be on the safe side their 6 servers dedicated to this 
> challenge-response system are now in the whitelist on the public 
> configuration repository. The next update_sqlgrey_config run will 
> update your installations.
>
> It seems unlikely to me that spam will ever get sent from these 
> addresses but if you get problems with them, please report here.
>
> Lionel.

Hi Lionel

This brings me to a point that I have been thinking about for a while.  
How large is this white list going to become over time?  Surely over 
time this file is going to get quite large?  Another point is that 
shouldn't it form a table in the sql-grey database?

Regards
Ray

-- 
Ray Booysen
rj_...@rj...

Hi,

This is a first: a software company asked for entries in the whitelist 
of all greylisting implementation. Their (Ciphire Labs) software needs a 
challenge-response over SMTP which must complete in less than 60 
minutes. Greylisting usually is ok, but several implementations 
recommend 2 hours for the reconnect delay which breaks their soft.

To be on the safe side their 6 servers dedicated to this 
challenge-response system are now in the whitelist on the public 
configuration repository. The next update_sqlgrey_config run will update 
your installations.

It seems unlikely to me that spam will ever get sent from these 
addresses but if you get problems with them, please report here.

Lionel.

On Mon, 15 Aug 2005, Lionel Bouton wrote:

> Depends on my free time... There's a lot to do in the 1.7.x branch and I
> may very well start by splitting the code into more manageable modules
> which will delay new functionnalities but then speed the development.
>

Hi Lionel,

don't begin with splitting the code in different modules. This is nothing
for the 1.7-branch. I think for the new structure we should start a
2.0-branch, in parallel to the 1.X-branches, because it needs a lot of
time and testing to get the new structure back into production.

I already began with this restructuring to an OO-structure and put many
hours of my free time into it. But it is still far away from a prototype.

I think there is still a lot to do for the 1.X-branches.

Regards,
Michael Storz
-------------------------------------------------
Leibniz-Rechenzentrum   !   <mailto:St...@lr...>
Barer Str. 21           !   Fax: +49 89 2809460
80333 Muenchen, Germany !   Tel: +49 89 289-28840

Sascha Lucas wrote the following on 15.08.2005 10:27 :

>> Ok, adding only the first header would indeed being meaningful in the 
>> example above. I was thinking of the future evolutions of SQLgrey and 
>> didn't explained myself well.
>>
>> The above works only because we don't use AWLs based on recipients... 
>> *yet*. In the future, SQLgrey will have an intermediate connect_awl 
>> table (with src, sender and recipient) and a rcpt_awl (with only src, 
>> recipients) in addition
>
>
> Ok. Thats what I didn't know :-).
>
> Lionel: Thanks a lot for your long explanation. Can you tell me 
> roughly when SQLgrey will have rcpt_awl?
>

Depends on my free time... There's a lot to do in the 1.7.x branch and I 
may very well start by splitting the code into more manageable modules 
which will delay new functionnalities but then speed the development.

> BTW: I just want to say that I (and many mail-users) are happy with 
> sqlgrey.
>

Knowing that people benefit from my work is a good motivation for me :-)

Thanks!

Lionel.

Sascha Lucas wrote the following on 15.08.2005 10:28 :

>> They might already do : optout email addresses are already added to 
>> the headers.
>>
>> But this is irrelevant if the sender or one of the mail gateway along 
>> the path follows the security recommendation of RFC2822 : send a 
>> separate copy of the message to the "To: and CC:" recipient and 
>> another to the "Bcc:" recipients.
>> Anyway, I'll make the header configurable with some macros (already 
>> in my TODO), so that the local admin can make the decision.
>
>
> Does this mean that Bcc: a@b.c and Bcc: d@e.f can see each other? Well 
> I don't know.


As stated in the RFC2822, it is up to the implementation to decide. This 
said I'm not aware of any implementation showing each BCC to each other.

>
> But here in germany the people of data privacy (not me :-)), wouldn't 
> even be glad about RCPT-specific headers regardless of To:, CC: and Bcc:.
>
> BTW: does anybody know how the postfix-users mailinglist works? This 
> is the list, where I first saw multiple X-Greylist: headers. So I know 
> someone else is getting this mail. For now I had to look in the
> postfix-logfile to see who the others are, but with RCPT-specific 
> headers every one can see.
>
> If the header is customizable (in future versions), there should be a 
> big warning in the config file.
>

This is the plan. The defaults won't show any "RCPT TO" value to be on 
the safe side.

Lionel.

> They might already do : optout email addresses are already added to the 
> headers.
>
> But this is irrelevant if the sender or one of the mail gateway along the 
> path follows the security recommendation of RFC2822 : send a separate copy of 
> the message to the "To: and CC:" recipient and another to the "Bcc:" 
> recipients.
> Anyway, I'll make the header configurable with some macros (already in my 
> TODO), so that the local admin can make the decision.

Does this mean that Bcc: a@b.c and Bcc: d@e.f can see each other? Well I 
don't know.

But here in germany the people of data privacy (not me :-)), wouldn't even 
be glad about RCPT-specific headers regardless of To:, CC: and Bcc:.

BTW: does anybody know how the postfix-users mailinglist works? This is 
the list, where I first saw multiple X-Greylist: headers. So I know 
someone else is getting this mail. For now I had to look in the
postfix-logfile to see who the others are, but with RCPT-specific headers 
every one can see.

If the header is customizable (in future versions), there should be a big 
warning in the config file.

Thanks,

Sascha.

> Ok, adding only the first header would indeed being meaningful in the example 
> above. I was thinking of the future evolutions of SQLgrey and didn't 
> explained myself well.
>
> The above works only because we don't use AWLs based on recipients... *yet*. 
> In the future, SQLgrey will have an intermediate connect_awl table (with src, 
> sender and recipient) and a rcpt_awl (with only src, recipients) in addition

Ok. Thats what I didn't know :-).

Lionel: Thanks a lot for your long explanation. Can you tell me roughly 
when SQLgrey will have rcpt_awl?

BTW: I just want to say that I (and many mail-users) are happy with 
sqlgrey.

Thanks,

Sascha.

Michael Storz wrote the following on 13.08.2005 07:04 :

>On Fri, 12 Aug 2005, Lionel Bouton wrote:
>
>  
>
>>X-Greylist: delayed 01:21:26.853989 by SQLgrey-1.6.5 for ad...@ex...
>>X-Greylist: recipient auto-whitelisted by SQLgrey-1.6.5 for
>>for...@ex...
>>X-Greylist: greylisting inactive for cri...@ex... in
>>SQLgrey-1.6.5
>>
>>    
>>
>
>This will mean, that in some rare situations BCCed recipients will see
>each other :-(
>  
>

They might already do : optout email addresses are already added to the 
headers.

But this is irrelevant if the sender or one of the mail gateway along 
the path follows the security recommendation of RFC2822 : send a 
separate copy of the message to the "To: and CC:" recipient and another 
to the "Bcc:" recipients.
Anyway, I'll make the header configurable with some macros (already in 
my TODO), so that the local admin can make the decision.

Lionel.

On Fri, 12 Aug 2005, Lionel Bouton wrote:

>
> X-Greylist: delayed 01:21:26.853989 by SQLgrey-1.6.5 for ad...@ex...
> X-Greylist: recipient auto-whitelisted by SQLgrey-1.6.5 for
> for...@ex...
> X-Greylist: greylisting inactive for cri...@ex... in
> SQLgrey-1.6.5
>

This will mean, that in some rare situations BCCed recipients will see
each other :-(

Michael Storz
-------------------------------------------------
Leibniz-Rechenzentrum   !   <mailto:St...@lr...>
Barer Str. 21           !   Fax: +49 89 2809460
80333 Muenchen, Germany !   Tel: +49 89 289-28840

Sascha Lucas wrote the following on 12.08.2005 17:13 :

>> There's a problem with that: nothing prevents two recipients for the 
>> same message from being treated differently (one can match a connect 
>> entry and the following matches the AWL entry just created:  two 
>> different headers). The problem is that we don't add the header on 
>> delivery but way before that. We have no way of adding a header per 
>> RCPT TO: this is a limitation of the policy protocol. We could have 
>> more meaningful headers with the "RCPT TO" listed for each header but 
>> I'm afraid it's the best we can do.
>
>
> I dont understand. If the 1st RCPT matches the connect entry and it is 
> an "reconnect ok", then you add the X-Greylist: delayed ... header. 
> The other RCPTs would result in a match of the from_awl table which 
> would result in a X-Greylist: from auto-whitelisted ... header. But 
> you know this instance=... allready gots it's header so sqlgrey don't 
> say action=PREPEND....


Ok, adding only the first header would indeed being meaningful in the 
example above. I was thinking of the future evolutions of SQLgrey and 
didn't explained myself well.

The above works only because we don't use AWLs based on recipients... 
*yet*. In the future, SQLgrey will have an intermediate connect_awl 
table (with src, sender and recipient) and a rcpt_awl (with only src, 
recipients) in addition to the current from_awl and domain_awl (this 
will solve some problems - false positive and negatives that could be 
avoided - seen in the wild and discussed earlier on this list). They 
will both introduce more complex cases that won't be resolvable by 
allowing only a single prepend to the same instance (simply because a 
single mail to different recipients would be accepted for totally 
different reasons in some cases).
Even if awls using "rcpt to"s wouldn't be used, maintaining a backlog of 
past instances is not that simple (nothing tells us that an instance is 
gone, so we will have to use heuristics to clean this backlog).
There is the case of optin/optout too: the prepended header for one 
"optout" address has nothing to do with the awl match of another.

Currently the multiple X-Greylist headers looks like a bug at first 
glance (I've already had reports on this). I believe adding the "rcpt 
to" matching each header to make them less confusing is the right thing 
to do. In a mail sent to 3 recipients, one of which happens to get a 
match in connect (another unrelated mail created the connect entry for 
example), another already being auto-whitelisted for this source (future 
rcpt_awl) and a last one which doesn't want to use greylisting at all 
(current optout mechanism):

X-Greylist: delayed 01:21:26.853989 by SQLgrey-1.6.5 for ad...@ex...
X-Greylist: recipient auto-whitelisted by SQLgrey-1.6.5 for 
for...@ex...
X-Greylist: greylisting inactive for cri...@ex... in 
SQLgrey-1.6.5

On a purely theoritical level, this doesn't really make sense to try to 
consolidate all headers in a single one. All decisions are currently 
completely independent from each other and only happen to be losely 
related because of some choices in the current AWLs design that *will* 
evolve.

Lionel.

> There's a problem with that: nothing prevents two recipients for the same 
> message from being treated differently (one can match a connect entry and the 
> following matches the AWL entry just created:  two different headers). The 
> problem is that we don't add the header on delivery but way before that. We 
> have no way of adding a header per RCPT TO: this is a limitation of the 
> policy protocol. We could have more meaningful headers with the "RCPT TO" 
> listed for each header but I'm afraid it's the best we can do.

I dont understand. If the 1st RCPT matches the connect entry and it is an 
"reconnect ok", then you add the X-Greylist: delayed ... header. The other 
RCPTs would result in a match of the from_awl table which would result in 
a X-Greylist: from auto-whitelisted ... header. But you know this 
instance=... allready gots it's header so sqlgrey don't say 
action=PREPEND....

Please tell me what point I misunderstand.

Thanks,

Sascha.

Sascha Lucas wrote the following on 12.08.2005 14:58 :

> Hi list,
>
> I have configured sqlgrey-1.6.5 to PREPEND the X-Greylist: header. 
> What I noticed is that the header was prepended multiple times (for 
> each RCPT TO) in the mail. The logfile shows also "sqlgrey: grey: from 
> awl match: updating" for each RCPT TO.
>
> My question is: can the instance= in the postfix policy protocol be 
> used, to prevent multiple action=PREPEND .... ? From postfix's 
> SMTPD_POLICY_READM the instance=... can be used to correlate different 
> requests regarding the same message delivery.


There's a problem with that: nothing prevents two recipients for the 
same message from being treated differently (one can match a connect 
entry and the following matches the AWL entry just created:  two 
different headers). The problem is that we don't add the header on 
delivery but way before that. We have no way of adding a header per RCPT 
TO: this is a limitation of the policy protocol. We could have more 
meaningful headers with the "RCPT TO" listed for each header but I'm 
afraid it's the best we can do.

Lionel.

Hi list,

I have configured sqlgrey-1.6.5 to PREPEND the X-Greylist: header. What I 
noticed is that the header was prepended multiple times (for each RCPT TO) 
in the mail. The logfile shows also "sqlgrey: grey: from awl match: 
updating" for each RCPT TO.

My question is: can the instance= in the postfix policy protocol be 
used, to prevent multiple action=PREPEND .... ? From postfix's 
SMTPD_POLICY_READM the instance=... can be used to correlate different 
requests regarding the same message delivery.

THX,

Sascha.

Beast wrote the following on 12.08.2005 09:29 :

>
> What if we can edit file instead of direct modification to database?
> Just a suggestion.
>
That was a possibility... but I wanted to allow mail admins to easily 
integrate optout/optin into their admin infrastructure. An ISP should be 
able to add a switch somewhere in its user interface to allow its user 
to decide themselves if they want the greylisting. This is far easier if 
optin/optout decision is stored in database.

Checking files for modification is time-consuming too (especially when 
they become large). SQLgrey only does this for the "local" whitelists 
which should:
- change rarely,
- remain quite small as their content is fed to the main whitelists upon 
notification on this list.

If you don't want to develop a web-based interface, editing database 
entries is rather straightforward if you install a generic web-based 
database admin tool like phpPgAdmin (PostgreSQL) or phpMyAdmin (MySQL) 
(I'm not sure for SQLite but I believe there is an admin package out 
there too).

Lionel.

Lionel Bouton wrote:
> Beast wrote the following on 11.08.2005 13:36 :
> 
>>
>> Some of my user did not want their email delayed, so I have to exclude 
>> them from sqlgrey.
>>
>> These step is required:
>> 1. edit sqlgrey.conf
>>
>> optmethod = optout
>>
>> 2. add "sp...@my..." in optout_email table.
>>
>> 3. restart sqlgrey.
>>
>> Is that correct?
>>
> 
> Yes it is.
> 

What if we can edit file instead of direct modification to database?
Just a suggestion.

-- 

--beast

Beast wrote the following on 11.08.2005 14:35 :

>
> AWL is based on domain or per email address?


Both. The domain AWL is used when enough entries for the same domain/src 
couple exist in the mail AWL.

>
> What happen when sender using more than one mail server (with same 
> return path address)?
>

 From the example sqlgrey.conf:

## Greylisting method:
# - full   : greylist by IP address
# - classc : greylist by class C network. eg:
#            2.3.4.6 connection accepted if 2.3.4.145 did connect earlier
# - smart  : greylist by class C network unless there is no reverse lookup
#            or it looks like a home-user address
# Default is smart
# greymethod = smart

So if the 2 servers are in the same class C network, the AWL entry is 
reused in 'classc' and may be in 'smart' depending on the reverse lookup.
If the 2 servers aren't in the same class C network, SQLgrey greylists 
and create a new AWL entry.

> In sqlgrey.conf:
>
> group_domain_level = 2
>
> 2 means 2 different email address or 2 different email (with same 
> address)?
>

2 different email addresses in the same domain.

Lionel.

Michael Storz wrote the following on 11.08.2005 14:12 :

>Hi Lionel,
>
>since your answers are coming so fast, you do not seem to be in vacation ;-)
>
>  
>

I'm not but I've not had much free time to work on SQLgrey either.

>What is the status of the sqlgrey development? On which parts are you
>working now?
>
>  
>

As you know I've began looking at your patches but didn't work much on 
it since our last mail exchanges.

>I got the book "Programming the Perl DBI" yesterday and now I try to
>understand all this database stuff in sqlgrey.
>
>And I am still trying to figure out, how sqlgrey could be broken up into
>objects, classes, modules ... It seems to be more work than I thought.
>  
>

I'll take vacations from August 22 to 29, after that I'll have my 
batteries fully charged and probably more free time. I expect 1.7.x 
development to resume in September.

Lionel

AWL is based on domain or per email address?

What happen when sender using more than one mail server (with same 
return path address)?

In sqlgrey.conf:

group_domain_level = 2

2 means 2 different email address or 2 different email (with same address)?

Tks.




-- 

--beast

Hi Lionel,

since your answers are coming so fast, you do not seem to be in vacation ;-)

What is the status of the sqlgrey development? On which parts are you
working now?

I got the book "Programming the Perl DBI" yesterday and now I try to
understand all this database stuff in sqlgrey.

And I am still trying to figure out, how sqlgrey could be broken up into
objects, classes, modules ... It seems to be more work than I thought.

Michael Storz
-------------------------------------------------
Leibniz-Rechenzentrum   !   <mailto:St...@lr...>
Barer Str. 21           !   Fax: +49 89 2809460
80333 Muenchen, Germany !   Tel: +49 89 289-28840

On Thu, 11 Aug 2005, Beast wrote:

>
> Some of my user did not want their email delayed, so I have to exclude
> them from sqlgrey.
>
> These step is required:
> 1. edit sqlgrey.conf
>
> optmethod = optout
>
> 2. add "sp...@my..." in optout_email table.
>
> 3. restart sqlgrey.
>
> Is that correct?
>

Yes, this is correct. After a change of the config file, you have to
restart sqlgrey. After that you can add entries to the optin/out tables at
any time without restarting sqlgrey.

Michael Storz
-------------------------------------------------
Leibniz-Rechenzentrum   !   <mailto:St...@lr...>
Barer Str. 21           !   Fax: +49 89 2809460
80333 Muenchen, Germany !   Tel: +49 89 289-28840

On Thu, 11 Aug 2005, Beast wrote:

> Lionel Bouton wrote:
> > Beast wrote the following on 11.08.2005 08:45 :
> >
> >> [...]
> >> It is because of mail from using "%". I have tried sending mail with
> >> this kind of address (from any host) and sqlgrey dies, probably
> >> because of unquote '%'.
> >>
> >> MAIL FROM: <LNGNOTES/LNGJAPAN%LNG...@ln...>
> >>
> >>
> >
> > This problem was fixed with the 1.6.2 release. Just install latest
> > stable SQLgrey (1.6.5) and the problem will go away.
>
> Do I need to recreate mysql table after upgrading?
> Tks.
>
You don't have to, sqlgrey handles necessary upgrades of the tables
automatically.

Michael Storz
-------------------------------------------------
Leibniz-Rechenzentrum   !   <mailto:St...@lr...>
Barer Str. 21           !   Fax: +49 89 2809460
80333 Muenchen, Germany !   Tel: +49 89 289-28840

Beast wrote the following on 11.08.2005 13:36 :

>
> Some of my user did not want their email delayed, so I have to exclude 
> them from sqlgrey.
>
> These step is required:
> 1. edit sqlgrey.conf
>
> optmethod = optout
>
> 2. add "sp...@my..." in optout_email table.
>
> 3. restart sqlgrey.
>
> Is that correct?
>

Yes it is.

Beast wrote the following on 11.08.2005 13:40 :

> Lionel Bouton wrote:
>
>> [...]
>> This problem was fixed with the 1.6.2 release. Just install latest 
>> stable SQLgrey (1.6.5) and the problem will go away.
>
>
> Do I need to recreate mysql table after upgrading?
> Tks.
>
No. SQLgrey upgrades never need database administration: tables are 
automatically converted to new formats and new tables are created by 
SQLgrey itself when needed.

Lionel.