sqlgrey-users — Users of SQLgrey

Re: [Sqlgrey-users] Periodic crashes of sqlgrey

[Lionel B. <lio...@bo...>]

HaJo Schatz wrote the following on 17.11.2004 16:40 :

>Lionel,
>
>Pls don't dig deep into the long sender name issue, it seems I have
>screwed up while going through my maillogs. The "sender too long" is in
>fact from the old version of sqlgrey. I have now identified that 1.3.0
>crashed differently:
>  
>

OK.

>Nov 16 19:16:55 sun sqlgrey[18895]: Error: couldn't access domain_awl
>table: FATAL:  This connection has been terminated by the administrator.
>Nov 16 19:16:57 sun sqlgrey[18895]: fatal: Can't connect to DB: could
>not connect to server: Connection refused at /usr/bin/sqlgrey line 336.
>Nov 16 19:16:58 sun sqlgrey[18895]: warning: Database handle destroyed
>without explicit disconnect at /usr/bin/sqlgrey line 336.
>
>I'm not sure why the "administrator terminated the connection", but I'm
>trying to trace this back. Is it unavoidable that sqlgrey terminates if
>there's an error with the DB connection?
>

When disconnected SQLgrey tries to reconnect to the database but if it 
fails it will exit. 1.1.3 was more robust in this respect because it 
never retried immediately. But it had a bug which made it stop filtering 
for as much as 24hours before trying to reconnect to the database.
I consider this a bug in 1.2.0 and 1.3.x, what I'll code is a more 
tolerant behaviour in 1.2.1 to fix the problem (never exit) and redesign 
1.3 to sleep a little instead of trying to reconnect immediately (in 
order to allow a temporary connection problem to go away) and refuse to 
exit on DB errors too.

> Maybe a setting in the config
>file could determine if in such a case the mail is considered either
>pass or fail
>

Fail could be nasty !

> and a warning being issued to syslog or postmaster.
>

syslog is already ready. Postmaster could be handy.

> The
>connection could then be re-tried on the next mail-RX, no?
>
>Sorry for the trouble,
>
>  
>

Don't be sorry, this report is quite useful :-)

Best regards,

Lionel.

Re: [Sqlgrey-users] Suggestion: Comment field for net_whitelist

[David R. <dr...@gr...>]

Lionel Bouton wrote, On 11/17/2004 1:50 AM:
> 
> I'm in the process of obsoleting this table by creating a lookup file 
> which will allow both IP and fqdn whitelisting. Adding a comment will be 
> simple to do. You'll have something like :
> 132.4.5.6 # This mailserver never retries (it handles domain aaa)
> 196.54.3 # For a single message several servers in this class C can 
> retry (domain a.com, b.org and c.net)
> 
> I'm doing this because it's simpler for people to use files for 
> configuration and net_whitelist is only supported with PostgreSQL.

OK, that'll work.

> - only a primary key (which creates an index implicitly) for other 
> tables. An index on the timestamp could help the cleanup of these 
> tables, all other operations are speed up by the primary key index.

Ah I forgot about the implicit index created by the primary key.

> I'm not yet considering adding indexes to the connect table (I've a 
> problem with MySQL on this one and I've no performance problem with it 
> reported). But adding an index on the timestamps of from_awl and 
> domain_awl is on my TODO list. In the 1.3.x devel releases I've added on 
> the fly database schema conversion support, so when 1.4.0 is out you'll 
> have indexes on timestamps added automatically.

Awesome!

-Dave

Re: [Sqlgrey-users] Periodic crashes of sqlgrey

[Lionel B. <lio...@bo...>]

HaJo Schatz wrote the following on 17.11.2004 16:12 :

>I suspect the sender contains "illegal" characters such as the
>backslash. Did you consider this? I'm wondering what an <INSERT ...
>("C:\DocumentsAndSettings...")..> would do.
>  
>

This SQL can't hit the database with 1.2.0 and later: the '\' is 
properly quoted by code looking like :
$dbh->quote($sender_name)
Perl DBI makes it easy to protect yourself against SQL injections by 
providing the quote() method.

SQL injections work in the following way :
you run the concatenation "SELECT col FROM table WHERE key = " and a 
value provided by a user you can't trust. If the value is something like 
"iownyou; DELETE FROM another_table" you end up sending "DELETE FROM 
another_table" to your database. Not a good thing...

What you want to do is let the database know that nothing in the 
"iownyou; DELETE FROM another_table" string should be interpreted as the 
end of the value against which key is compared. $dbh->quote("iownyou; 
DELETE FROM another_table") makes sure it doesn't happen by properly 
escaping the ';' character and whatever other character the database 
would treat specially.

Re: [Sqlgrey-users] Periodic crashes of sqlgrey

[HaJo S. <ha...@ha...>]

Lionel,

Pls don't dig deep into the long sender name issue, it seems I have
screwed up while going through my maillogs. The "sender too long" is in
fact from the old version of sqlgrey. I have now identified that 1.3.0
crashed differently:

Nov 16 19:16:55 sun sqlgrey[18895]: Error: couldn't access domain_awl
table: FATAL:  This connection has been terminated by the administrator.
Nov 16 19:16:57 sun sqlgrey[18895]: fatal: Can't connect to DB: could
not connect to server: Connection refused at /usr/bin/sqlgrey line 336.
Nov 16 19:16:58 sun sqlgrey[18895]: warning: Database handle destroyed
without explicit disconnect at /usr/bin/sqlgrey line 336.

I'm not sure why the "administrator terminated the connection", but I'm
trying to trace this back. Is it unavoidable that sqlgrey terminates if
there's an error with the DB connection? Maybe a setting in the config
file could determine if in such a case the mail is considered either
pass or fail and a warning being issued to syslog or postmaster. The
connection could then be re-tried on the next mail-RX, no?

Sorry for the trouble,
HaJo

On Wed, 2004-11-17 at 23:12, HaJo Schatz wrote:
> On Wed, 2004-11-17 at 21:09, Lionel Bouton wrote:
> 
> > I must confess I didn't test this case after coding the protection, but 
> > I just did and it worked :
> > 
> > c:doc...@ma...
> > is put in the connect table as :
> > 'c:documentsandsettingsadministratordesktopgevaliagevaliafroms.tx' | 'mail.eppele.co.kr'
> > (notice the missing 't')
> 
> I suspect the sender contains "illegal" characters such as the
> backslash. Did you consider this? I'm wondering what an <INSERT ...
> ("C:\DocumentsAndSettings...")..> would do.
> 
> > 
> > My best guess is that you have an old (1.1.3 wasn't protected against this) sqlgrey release in your $PATH.
> Nack. I only have one version here which is (well, says through "sqlgrey
> --version") 1.3.0. I had the crash a few times with 1.1.3 upon which I
> upgraded to 1.3.0. There's a remote possibility that I didn't restart
> sqlgrey after upgrading, but I strongly doubt it, as I have a log entry
> (sqlgrey starting) which is younger than the mtime of the (new)
> /etc/sqlgrey/ directory.
> 
> Anyway, I'm sure that now the correct version is running. I'll observe
> it and report back if it crashes again.
> 
> HaJo
-- 
HaJo Schatz <ha...@ha...>
http://www.HaJo.Net

PGP-Key:  http://www.hajo.net/hajonet/keys/pgpkey_hajo.txt

Re: [Sqlgrey-users] Periodic crashes of sqlgrey

[HaJo S. <ha...@ha...>]

On Wed, 2004-11-17 at 21:09, Lionel Bouton wrote:

> I must confess I didn't test this case after coding the protection, but 
> I just did and it worked :
> 
> c:doc...@ma...
> is put in the connect table as :
> 'c:documentsandsettingsadministratordesktopgevaliagevaliafroms.tx' | 'mail.eppele.co.kr'
> (notice the missing 't')

I suspect the sender contains "illegal" characters such as the
backslash. Did you consider this? I'm wondering what an <INSERT ...
("C:\DocumentsAndSettings...")..> would do.

> 
> My best guess is that you have an old (1.1.3 wasn't protected against this) sqlgrey release in your $PATH.
Nack. I only have one version here which is (well, says through "sqlgrey
--version") 1.3.0. I had the crash a few times with 1.1.3 upon which I
upgraded to 1.3.0. There's a remote possibility that I didn't restart
sqlgrey after upgrading, but I strongly doubt it, as I have a log entry
(sqlgrey starting) which is younger than the mtime of the (new)
/etc/sqlgrey/ directory.

Anyway, I'm sure that now the correct version is running. I'll observe
it and report back if it crashes again.

HaJo
-- 
HaJo Schatz <ha...@ha...>
http://www.HaJo.Net

PGP-Key:  http://www.hajo.net/hajonet/keys/pgpkey_hajo.txt

Re: [Sqlgrey-users] Periodic crashes of sqlgrey

[Lionel B. <lio...@bo...>]

HaJo Schatz wrote the following on 17.11.2004 12:36 :

>[...]
>c:documentsandsettingsadministratordesktopgevaliagevaliafroms.txt-at-mail.epelle.co.kr -> [my e-mail address]
>Nov 16 00:29:38 sun sqlgrey[9064]: Warning: couldn't do query: INSERT
>INTO connect (sender_name, sender_domain, ip_addr, rcpt, first_seen)
>VALUES('c:documentsandsettingsadministratordesktopgevaliagevaliafroms.txt', 'mail.epelle.co.kr', '219.254.35.115', '[my e-mail address]', NOW()): , sleeping and reconnecting to DB
>Nov 16 00:29:48 sun sqlgrey[9064]: warning: Database handle destroyed
>without explicit disconnect at /usr/bin/sqlgrey line 178.
>Nov 16 00:29:48 sun sqlgrey[9064]: fatal: Error: db reconnection
>failed:  at /usr/bin/sqlgrey line 77.
>Nov 16 00:29:48 sun postfix/smtpd[5045]: warning: premature end-of-input
>on 127.0.0.1:2501 while reading input attribute name
>[...]
>  
>


If the INSERT can't be done due to some malformed value, this is 
expected. What isn't expected in 1.2.0 or 1.3.x is a malformed valued 
hitting the database.
What surprises me is that your sample shows a 65 characters sender_name 
which :
- is illegal per RFC (but this is a spammer OK);
- is taken into account by sqlgrey-1.2.0 and 1.3.x : there's code 
explicitly trucating sender_name to 64 chars before putting it in database.

I must confess I didn't test this case after coding the protection, but 
I just did and it worked :

c:doc...@ma...
is put in the connect table as :
'c:documentsandsettingsadministratordesktopgevaliagevaliafroms.tx' | 'mail.eppele.co.kr'
(notice the missing 't')

My best guess is that you have an old (1.1.3 wasn't protected against this) sqlgrey release in your $PATH.


>I'm getting these mails about twice a day, so my mail server is
>meanwhile more down than up...
>
>I believe sqlgrey would need some (more -- I understand some has been
>introduced since 1.2.0) sanity checks about values it wants to insert
>into the data base. I smell targeted exploits being possible here...
>  
>

In this particular case, only a DoS (killing sqlgrey at will) would have 
been possible, properly quoting values (which is done in 1.2.0) solves 
the '; DELETE ...' exploits.

Best regards,

Lionel.

[Sqlgrey-users] Periodic crashes of sqlgrey

[HaJo S. <ha...@ha...>]

Hi List,

Truly impressive what sqlgrey does to the amount of spam I receive
(not). However I've identified a bug in both 1.1.3 and 1.3.0 which
causes sqlgrey to crash (and hence the mail server to reject all
incoming mail!): 

Some spammers nowadays use very bogus sender names, such as file names &
paths to documents including backslashes, etc. If sqlgrey encounters one
of these, it crashes with this log (I've replaced the "@" with an "-at-"
so that the list mailer will not hide the address):

Nov 16 00:29:38 sun sqlgrey[9064]: new: 219.254.35.115:
c:documentsandsettingsadministratordesktopgevaliagevaliafroms.txt-at-mail.epelle.co.kr -> [my e-mail address]
Nov 16 00:29:38 sun sqlgrey[9064]: Warning: couldn't do query: INSERT
INTO connect (sender_name, sender_domain, ip_addr, rcpt, first_seen)
VALUES('c:documentsandsettingsadministratordesktopgevaliagevaliafroms.txt', 'mail.epelle.co.kr', '219.254.35.115', '[my e-mail address]', NOW()): , sleeping and reconnecting to DB
Nov 16 00:29:48 sun sqlgrey[9064]: warning: Database handle destroyed
without explicit disconnect at /usr/bin/sqlgrey line 178.
Nov 16 00:29:48 sun sqlgrey[9064]: fatal: Error: db reconnection
failed:  at /usr/bin/sqlgrey line 77.
Nov 16 00:29:48 sun postfix/smtpd[5045]: warning: premature end-of-input
on 127.0.0.1:2501 while reading input attribute name

I'm getting these mails about twice a day, so my mail server is
meanwhile more down than up...

I believe sqlgrey would need some (more -- I understand some has been
introduced since 1.2.0) sanity checks about values it wants to insert
into the data base. I smell targeted exploits being possible here...

HaJo
-- 
HaJo Schatz <ha...@ha...>
http://www.HaJo.Net

PGP-Key:  http://www.hajo.net/hajonet/keys/pgpkey_hajo.txt

Re: [Sqlgrey-users] Suggestion: Comment field for net_whitelist

[Lionel B. <lio...@bo...>]

David Rees wrote the following on 17.11.2004 09:25 :

> I would like to see a comment column for the net_whitelist table.  
> That would help me keep track of why I added a particular net to the 
> table.
>

I'm in the process of obsoleting this table by creating a lookup file 
which will allow both IP and fqdn whitelisting. Adding a comment will be 
simple to do. You'll have something like :
132.4.5.6 # This mailserver never retries (it handles domain aaa)
196.54.3 # For a single message several servers in this class C can 
retry (domain a.com, b.org and c.net)

I'm doing this because it's simpler for people to use files for 
configuration and net_whitelist is only supported with PostgreSQL.

> Another question/comment:  I didn't see any indicies on the tables 
> (I'm using PostgreSQL.  In general do the tables stay small enough 
> that full table scans don't affect performance, or is that just 
> something you haven't gotten around to looking at yet?  That's 
> something I could spend some time on...


There are currently 2 lacks :
- no index for the connect table, but this table shouldn't grow much as 
it is cleaned up automatically.
- only a primary key (which creates an index implicitly) for other 
tables. An index on the timestamp could help the cleanup of these 
tables, all other operations are speed up by the primary key index.

I'm not yet considering adding indexes to the connect table (I've a 
problem with MySQL on this one and I've no performance problem with it 
reported). But adding an index on the timestamps of from_awl and 
domain_awl is on my TODO list. In the 1.3.x devel releases I've added on 
the fly database schema conversion support, so when 1.4.0 is out you'll 
have indexes on timestamps added automatically.

If there are Gentoo users, I've just tested a sqlgrey-1.3.1.ebuild, one 
of my test systems is now a working Gentoo SQLgrey install. Feel free to 
ask for the ebuild here if needed.

Happy greylisting !

Lionel

[Sqlgrey-users] Suggestion: Comment field for net_whitelist

[David R. <dr...@gr...>]

I would like to see a comment column for the net_whitelist table.  That 
would help me keep track of why I added a particular net to the table.

Another question/comment:  I didn't see any indicies on the tables (I'm 
using PostgreSQL.  In general do the tables stay small enough that full 
table scans don't affect performance, or is that just something you 
haven't gotten around to looking at yet?  That's something I could spend 
some time on...

Cheers

Dave

[Sqlgrey-users] [BUGFIX] 1.2.0

[Lionel B. <lio...@bo...>]

1.2.0 is out on sourceforge.

[SEC BUGFIX]
I did some cleanups before coding new things and had a look at each SQL 
statements and found some places where SQLgrey trusted Postfix input too 
much.

I don't know if there really was an exploit possible (you must trick 
Postfix to accept invalid "MAIL FROM: " or "RCPT TO:" before SQLgrey 
gets the bogus values itself).

Anyway, if Postfix didn't check for something like this :
"MAIL FROM: <cr...@io...>; INSERT INTO domain_awl ....; DELETE 
FROM connect; ..."
now SQLgrey does (even if Postfix is actually checking now, we don't 
want to rely on the current behaviour).

[RELIABILITY BUGFIX]
There was a small bug where most of the time SQLgrey didn't reconnect to 
the DB when the connection was lost (it waited until the DB cleanups are 
triggered). Symptom : SQLgrey reverts to always accept (as it prefers to 
let mails in if it can't greylist dumping errors on each mail to syslog) 
until a DB cleanup comes (once every 24 hour in 1.1.3).

[NUMBERING]
I switched to a new numbering scheme :
- odd subversions are stable releases : 1.2.0.
- even subversions are devel releases : pending 1.3.0.

Best regards,

Lionel.

Re: [Sqlgrey-users] libnet-server-perl issues

[Lionel B. <lio...@bo...>]

Alexey Grigoriev wrote the following on 11/04/04 09:45 :

> Hello Lionel.
> I've found the problem with sqlgrey in Adamantix. I was running 
> libnet-server-perl package version < 0.87, however sqlgrey can't suid 
> to sqlgrey user and doesen't feed any logs to syslog if the library 
> version is less than mentioned.


Odd: 0.86 worked for me. It must be a bad interaction with something 
else. Anyway I added a warning on SQLgrey's homepage.

Re: [Sqlgrey-users] libnet-server-perl issues

[Lionel B. <lio...@bo...>]

Alexey Grigoriev wrote the following on 04.11.2004 09:45 :

> Hello Lionel.
> I've found the problem with sqlgrey in Adamantix. I was running 
> libnet-server-perl package version < 0.87, however sqlgrey can't suid 
> to sqlgrey user and doesen't feed any logs to syslog if the library 
> version is less than mentioned. It also hangs sometimes due to bugs in 
> earlier libnet-server-perl implementation. I think it would be useful 
> to inform users about this issue on the project  homepage.


Will do, I've some work to do on SQLgrey : enhancements, cleanups, ... 
Expect such things to be done this week-end.

>
> Now, when my huge mail server is stable for some days, I have time to 
> prepare debian/adamantix pacakges for sqlgrey ;)
>
> PS: I wrote you about a week before, but your MX didn't replied. What 
> was wrong?
>

The address you just used was invalid (cc: lionel-dev@_bouton.name), 
notice the underscore.
Maybe you have a bad entry in your adress book ?
There's always the option of a problem on my side : I modified the 
"bouton.name" NS entries twice to have direct control of the content of 
my zone, it seems there were problems with one third-party slave DNS 
which for an unknown reason didn't process NOTIFY requests. This is 
solved now.

SQLgrey hacking was on hold last weeks, due to my playing with djbdns 
and DSPAM. This is now almost over (nice products, there's just a 
learning curve to integrate them properly with Bind slaves for the first 
and Postfix virtual mailbox domains for the latter).

Best regards,

Lionel.

[Sqlgrey-users] libnet-server-perl issues

[Alexey G. <ra...@pe...>]

Hello Lionel.
I've found the problem with sqlgrey in Adamantix. I was running 
libnet-server-perl package version < 0.87, however sqlgrey can't suid to 
sqlgrey user and doesen't feed any logs to syslog if the library version 
is less than mentioned. It also hangs sometimes due to bugs in earlier 
libnet-server-perl implementation. I think it would be useful to inform 
users about this issue on the project  homepage.

Now, when my huge mail server is stable for some days, I have time to 
prepare debian/adamantix pacakges for sqlgrey ;)

PS: I wrote you about a week before, but your MX didn't replied. What 
was wrong?

[Sqlgrey-users] Re: suggestion some small fixes for sqlgrey

[Lionel B. <lio...@bo...>]

Detlef Plotzky wrote the following on 10/22/04 10:01 :

>On Fri, 22 Oct 2004 09:09:51 +0200, Lionel Bouton wrote:
>
>  
>
>>I see you changed char(64) into varchar(255) for sender_name. From my 
>>understanding of the RFCs the name shouldn't be more than 64 chars. Is 
>>there a reason for the change ? Did you see more than 64 chars in the wild ?
>>    
>>
>No, it was just more easy to change all text fields to the same type, because the
>waste of space is the same in case of postgresql as far as I know.
>  
>

Yes you are right. I just checked both MySQL and PostgreSQL docs and 
apparently the performance difference of the two types is marginal. I 
guess I'll go the VARCHAR way then.
I'll add a 'dbversion' table to the database to make SQLgrey aware of 
the layout and where possible change it on the fly...

>Yesterday I have had some difficulties to send you an email, because the MX of sourceforge.net
>seems to do a call back to myself and then becomes greylisted. It didn#t retry within 30min.
>  
>

30 minutes is quite aggressive :
- the RFC is vague on the delays, but the longuest first delay I've seen 
advised is one hour,
- some mailing-lists managers have huge delays (more like 6 hours).

I bumped the default to 24 hours and the ratio between rejected spams 
and accepted spams (after more than 30 min delay) is still really high 
(like 10 for 1).

>So I put its IP in the whitelist.
>After about one hour the sqlqrey on every incoming mail was logging
>  yyyy/mm/dd-hh-mm-ss CONNECT TCP Peer: "127.0.0.1:xxxx Local: "127.0.0.1:2501"
>  Error: couldn't access connect table:
>  request: ...action=dunno
>  
>

I knew coding a fallback to 'dunno' when the database access went down 
wasn't a bad idea :-) You may have been spammed, but at least you still 
received your legit e-mails.

>But manual connect 'su sqlgrey -c "psql sqlgrey"' and "select * from connect;" works anyway.
>  
>

Hum quite surprising, especially since sqlgrey tries to reconnect to the 
database on such errors. I wonder if the changes you made to the data 
types might be related to this. The "Error: couldn't access connect 
table:" is indeed a generic error message. In reality there was an error 
when trying to execute a SQL statement involving the connect table.

>So I have configured my postfix back without sqlgrey and shut down sqlgrey daemon.
>
>  
>

I've some other enhancement to do before 1.2, I'll test the code with 
VARCHAR based tables when coding them.

Best regards,

Lionel.

Re: [Sqlgrey-users] Enhancements planned

[Lionel B. <lio...@bo...>]

Klaus Alexander Seistrup wrote the following on 10/21/04 08:02 :

>Lionel Bouton wrote:
>
>  
>
>>If you have enhancements in mind, feel free to discuss them on the
>>mailing-list and put a RFE on sourceforge.
>>    
>>
>
>I find postgrey's postfix_whitelist_clients.local feature highly
>useful, especially that I can use regexps to specify allowed clients. 
>Should be very easy to implement, I think.
>
>Cheers,
>  
>

Ok. Here's what I have in mind for SQLgrey 1.3 to allow something like 
this :

1/ Group conf in a new /etc/sqlgrey directory

move current /etc/sqlgrey.conf into it.

Add a /etc/sqlgrey/clients_whitelist file that will be distributed with 
future sqlgrey release (not meant to be modified by users but 
submissions of known weird clients wil be encouraged).
Add an empty /etc/sqlgrey/clients_whitelist.local file meant to be set 
up by the local admin.

2/ Format of client_whitelist*

classic '#' comments, spaces ignored, empty lines ignored.

Line format :
# 1 regexp on fqdn
/regexp/ : match the fqdn if Postfix could look it up, the regexp will 
be a case insensitive perl regexp (see man perlre).
/domain.tld$/ : any client with a A record in domain.tld will be 
whitelisted.
/^a.fqdn.tld$/ : a.fqdn.tld will be whitelisted

# 2 IP/net match
aaa.bbb.ccc.ddd
aaa.bbb.ccc        : match the first bytes of the sender's IP address.
I don't think it's a good idea to match against whole class B networks...

3/ Runtime configuration

SQLgrey will monitor the sqlgrey_whitelist_clients.local file by 
stat'ing the file on each request and reloading if the mtime has 
changed. sqlgrey_whitelist_clients won't be checked (don't touch that).

4/ Integration into current SQLgrey

This whitelist will be top priority : the AWL checks will be done 
*after* the whitelist check.
The net_whitelist supported only with PostgreSQL will be deprecated, 
SQLgrey won't create the table anymore, if the table is empty it will 
drop it, if not it will print a big scarry warning advising to move to 
the new whitelist (but will still use it).

Note : regexp matching will be slow because I will need to match against 
each entry, this is an o(n) algorithm -> don't put too much regexps in 
whitelists.
           ip/net whitelist will be quick because it will be based on 2 
hashes (one for the IPs, one for the class C nets) : expect o(log(n)) perfs.

Suggestions welcomed,

Regards,

Lionel.

Re: [Sqlgrey-users] Enhancements planned

[Klaus A. S. <kse...@gm...>]

Lionel Bouton wrote:

> If you have enhancements in mind, feel free to discuss them on the
> mailing-list and put a RFE on sourceforge.

I find postgrey's postfix_whitelist_clients.local feature highly
useful, especially that I can use regexps to specify allowed clients. 
Should be very easy to implement, I think.

Cheers,

-- 
Klaus Alexander Seistrup
Magnetic Ink, Copenhagen, Denmark
http://www.magnetic-ink.dk/

[Sqlgrey-users] Enhancements planned

[Lionel B. <lio...@bo...>]

Hi sqlgrey-users,

I've just checked postgrey latest version and they did some enhancements 
that I think could be useful :

- add a X-Greylist header describing the treatment received by the 
message. postgrey seems to only flag messages that have been delayed, 
would you be interested by knowing why a message is allowed on first try 
(net whitelisted, address/IP in AWL, domain/IP in AWL) ?

- postgrey isn't using the client_IP by default now, but the class-C 
network instead. I'm not sure it really is useful, but I lack data to 
make up my mind on this. postgrey added a supplementary check that 
compares the IP address with the fqdn to guess if it is a residential 
connection (last numbers of the IP in the fqdn), in this case it apply 
more strict rules (reverting to the client_IP key instead of the 
class-C_net). This latter check might be useful. Something like a 
"residential-reconnect-delay" parameter meant to be higher than the 
default one for example.

- Better VERP cleanups : this will be included in the next SQLgrey 
release (really simple to add, most useful to support some mailing list 
managers),

- There's a reporting tool for postgrey too that reads the log and 
access the DB to report spam attempts (grouped by client_IP, listing 
each sender/recipient pair and allowing to flag client_IPs which are MX 
for the domain in the PTR, ...), this one could be quite a lot of work 
to code but a reporting tool can be quite handy.

If you have enhancements in mind, feel free to discuss them on the 
mailing-list and put a RFE on sourceforge.

Best regards,

Lionel.

[Sqlgrey-users] Re: Strange sqlgrey behaviour

[Lionel B. <lio...@bo...>]

Alexey Grigoriev wrote the following on 10/18/04 16:55 :

> Hello. I had no time to prepare debian packages this weekend but found 
> some strange feature in postgrey behaviour.
> My question is: When does sqlgrey clear his connection cache? At 
> startup? I've got a lof of records in a connect table. And they 
> disappear only after restarting sqlgrey.
>
I just read my sqlgrey logs and found no problems. But I understood one 
thing : by setting the cleanup to run roughly once a day, I allow 
entries to remain in the connect table for roughly 2 days (the maximum 
reconnect delay is set to 24 hours and the cleanup is done every day : 
the maximum time is the sum, 2 days).

So the number of entries in the connnect table may have go way up 
because of the new default settings.

You can check for stale entries by doing the following query :

PostgreSQL :
SELECT * from connect WHERE first_seen < now() - INTERVAL '2 DAY';

MySQL :
SELECT * from connect WHERE first_seen < now() - INTERVAL 2 DAY;

SQLite :
SELECT * from connect WHERE first_seen < {value} - 172800;

where value is the ouput of
perl -e 'print time . "\n"'

Alexey, if you have entries returned by the above query and your 
settings are the defaults, there's a bug in SQLgrey (or you didn't have 
incoming SMTP connections since ages, as sqlgrey can only clean if 
called by postfix now).

Hint :
If you want to have an estimation of the number of entries in the 
connect table vs different sqlgrey settings, you should :
a: take the mean number of entries in connect with your settings,
b: compute (max_reconnect_delay + cleanup_delay) / 2 (where 
cleanup_delay is now hard-coded to 24 hours), it will give you the 
average delay between a blocked spam and the cleanup of the connect entry,
c: compute the same value above for the settings you would want to try

a x (c/b) will give you the number of entries in connect you should expect.

Best regards,

Lionel.

[Sqlgrey-users] Re: Strange sqlgrey behaviour

[Lionel B. <lio...@bo...>]

Alexey Grigoriev wrote the following on 10/18/04 16:55 :

> Hello. I had no time to prepare debian packages this weekend but found 
> some strange feature in postgrey behaviour.
> My question is: When does sqlgrey clear his connection cache? At 
> startup? I've got a lof of records in a connect table. And they 
> disappear only after restarting sqlgrey.
>

It seems someting is actually wrong with the cleanup. This is not a 
major problem on low volume sites, as this can only affect SELECT from 
connect times and not the results.

sqlgrey should clean the connect table on 2 occasions :
- at startup,
- when it is handling a request and a cleanup wasn't triggered for 24 
hours (was changed in recent releases, previously the delay was set to 
15 minutes).

I witnessed an odd behaviour with a previous version weeks ago where 
sqlgrey seemed to forget to do cleanups. I didn't understand why and 
couldn't reproduce this behaviour since then.

The code involved is pretty simple on each request sqlgrey does the 
following :

    if (time() > ($self->{sqlgrey}{last_maint} +
                  $self->{sqlgrey}{maint_delay})
       ) {
        $self->log(4, "Maintenance delay expired: cleanup triggered");
        $self->cleanup();
        $self->{sqlgrey}{last_maint} = time();
    }

$self->{sqlgrey}{maint_delay} is set to 86400 at startup time and 
$self->{sqlgrey}{last_maint} is initially set to the current time.
A grep on "maint" in the code shows that these are the only places 
"last_maint" and "maint_delay" are used.

If you start sqlgrey with "-v" or "--verbose" the $self->log ligne will 
actually send the message to syslog (there's a filter on the loglevel) 
and you can verify that a cleanup is triggered.
Anyway, on each cleanup, sqlgrey logs the failed transmissions attempts 
like this :

Oct 11 23:44:32 xxxxx sqlgrey[ddddd]: Probable spam: 66.117.40.12: 
xxxxxx@spammer_domain.tld -> lio...@bo... at 2004-10-10 
15:17:19.439293

The log above doesn't match with the intent of the code : I had some 
connections between Oct 11 15:17:19 (24 hours after the connection 
according to this log) and the date of the log line ("Oct 11 23:44:32").
So there is a problem but I can't put a finger on it yet. I'll try 
debugging it on my MX by logging each time value involved in the piece 
of code above whenever they are used.
Meanwhile, unless you want to help debugging the problem, my advice is 
to set a cron job to restart sqlgrey regularly.

Note :
$self->cleanup() actually handles the AWL cleanups as well.

Please CC: the sqlgrey-users mailing-list, as others are most probably 
affected. I'll open a bug on sourceforge right away.

Best regards,

Lionel.

[Sqlgrey-users] Re: gld and lightgreydomain

[Lionel B. <lio...@bo...>]

Advanced Computers wrote the following on 10/12/04 20:12 :

>sorry for this intrustion into your mailbox, but I saw your posting in the postfix mailing list:
>
>"gld does have a lightgreydomain algorithm 
>which is a dumbed down version of SQLgrey's second-level 
>auto-whitelisting (apply greylisting on domains only, disregard user 
>component in e-mail addresses)."
>
>that's not quite accurate.
>  
>

So this intrusion is welcomed :-) Better being corrected than remain 
ignorant.

In fact I had some regrets after sending the mail you refer to because I 
may have not chosen the right words "dumbed down" was used to mean that 
*in my opinion* sqlgrey's 2-level AWL is better than lightgreydomain, 
but english is not my mother tongue and I most probably did not choose 
the right words, I realized soon after that it could be offensive to gld 
authors...

This is CCed to the postfix user list, because I think you deserve a 
public apology.

Next is your description of gld's algorithm and mine for SQLgrey, 
followed by my opinion on SQLgrey strenghts vs gld. I hope it could be 
the start of some beneficial exchanges between gld and SQLgrey 
developpers where people on the list might want to make suggestions 
based on their real-world experience.

>lightgreydomain doesn't just use the domain part.  it works as follows
>
>1a) is the domain triplet around?  Nope?
>1b) is the normal user triplet around?  Nope?
>2) is this IP whitelisted?  Nope?
>3) add the normal (user) triplet and give a 4xx
>< time passes>
>4) is the user triplet around? Yep?
>5) is it pass XX time?  yep?
>6) allow the email to pass and add the domain triplet.
>
>The full triplet (sender, recipient, ip) has to match and pass once before the domain triplet gets used.  All future emails between different sender (but same domain) coming from the same IP going to the same recipient domain get through without delay.  The user triplet will get expired with a 'gld -C'.  This feature is not really dumbed down.  It might not be as configurable as what you've coded into sqlgrey, but I'm unaware of any greylisting server having it before gld and [...]
>
SQLgrey doesn't use the same approach, it's quite similar tough :

1/ check whitelists :
1a/ Is the IP whitelisted ?
1b/ Is the domain of the sender AWL for this IP ?
1c/ Is the sender AWL for this IP ?
2/ check pending connection attempts :
Is is past XX time and before YY time ? No -> delay or consider it as a 
new mail (past YY time case)
3/ if OK add to AWL :
3a/ Do we hit group-domain-level for the sender's domain ? No -> Add to 
mail-level AWL. Yes ->
3b/ Cleanup mail-level AWL for this domain and switch to domain-level AWL.

Triplets never enter the AWLs as is, they are stripped from the 
recipient address. I made this choice for 2 reasons :
- SMTP servers only use greylisting for domains they are MX for so the 
rcpt domain and even rcpt address weren't really useful information for 
the AWL in my opinion,
- even if they do chances that a spammer hit it with mails on several 
domains from the *same sender* are slim.

sqlgrey only AWL sender/IP couples, and then sender's domain/IP. I find 
this behavior better because when one of the usual contact is accepted 
for one rcpt, it doesn't need to be revalidated again -> less 
greylisting delays.


> I haven't seen any need to have more than one user triplet get authenticated.  If the spammer can come back with the right pair once, he can do it for all the rest (but is more likely to get bl.spamcop.net listed ;)
>  
>

In SQLgrey's case, it might prove useful, statistically you have more 
chances to hit an AWL entry using only the sender's domain than both the 
sender's domain and the rcpt's domain.
The problem is that we can't easily get stats on algorithm performance. 
We have to guess what is the most reliable data (IP being already 
obvious) to put in AWL. Only the behaviour of the SMTP MTA is validated. 
In AWL we only look for information hinting that when the IP comes back 
it is really the same MTA using it. For me the rcpt data isn't a good 
hint, only the sender is (I may be wrong). In fact just writing this, I 
see that the "hello" data available to the greylisting server could be a 
really good hint. I think a future SQLgrey version might very well use 
the hello string too.

In conclusion gld doesn't use a "dumbed down" algorithm, but a different 
one that I believe to be less efficient (if your goal is to minimize 
mail delays). Now I think this could be the start of an interesting 
discussion which could help both gld and sqlgrey. In fact I already 
considered lightgreydomain (as I understood it before : only look for 
domains) to be more efficient when the mail server is under heavy load : 
it could very well prove to be interesting for really big sites where 
the usual algorithms put the databases down to their knees.
I may have overlooked some benefits of gld's algorithms or 
underestimated some of my design choices' drawbacks. So constructive 
criticism is welcomed :-)

>I came up with the idea and did ugly C code for it.  Salim cleaned it up and included it.  The docs could be better, but you can tell from the source what is happening.
>
>  
>

Guess I should have spent more than 5 minutes reading the sources :-) 
IIRC my post was based on the understanding of the configuration file 
comments related to lightgreydomain :

#
# Shall we use lightgrey on domains ? (0=No,1=Yes) (default is 0)
#
# The lightgreydomain option, mask the left hand side of email addresses
# and thus we greylist only domains instead of emails.
#
# Do not activate this feature without understanding it ....
#

Guess a little more explanations wouldn't hurt :-)

>Wayne Smith
>  
>

Lionel Bouton

[Sqlgrey-users] Re: Greylist or sender verification?

[Lionel B. <li...@bo...>]

Lionel Bouton wrote the following on 10/07/04 10:41 :

>>
>> No, sender verification will connect to the DNS-listed MX of the 
>> domain part of the sender address -- which may be different from the 
>> "outgoing" mail server used to send from that domain[1] -- and 
>> attempt to verify if the sender address exists. Of course, some 
>> connecting MTAs don't keep a local_recipient_maps (or their MTA 
>> equivalent) on their MXes, so you'll get a positive sender 
>> verification every single time. So sender verification is also not 
>> the silver bullet.
>
>
>
> Note to self : toy with this in SQLgrey.


Should have searched for "sender verification" on postfix.org earlier... 
I'm not yet through the whole pile of posftix docs !

[Sqlgrey-users] Re: Greylist or sender verification?

[Lionel B. <lio...@bo...>]

Andrew Boring wrote the following on 10/07/04 06:16 :

>
> On Oct 6, 2004, at 11:49 PM, Francis Vidal wrote:
>
>> From what I gather, greylisting limits the bogus SMTP servers as it
>> requires the server to resend the mail while sender verification is
>> for limiting bogus sender addresses.
>
>
> Greylisting was designed to combat "spam runs", or a quick one-time 
> mass send of spam before the spammer switches to a different mail host 
> for the next run. However, spammers can (and perhaps eventually will) 
> retry too...so it's not a silver bullet.
>
> Your end-to-end (ie, user-to-user) mail performance will suffer, since 
> some really crappy legit MTAs won't retry very quickly. When I set up 
> greylisting at my last corporate gig, several of my users complained 
> that "mail didn't come through immediately" like it used to, so they 
> would call their contact at the other end to resend it, and several 
> hours later my end users would get large qtys of duplicate emails...


First implementations did that. But now auto-whitelisting is implemented 
: only the very first messages between 2 individuals are delayed.
SQLgrey add a second level auto-whitelisting to learn which domains are 
handled by mail servers and let them pass : when it sees several (actual 
number configurable) e-mail adresses from the same domain and same IP, 
the domain is whitelisted. gld does have a lightgreydomain algorithm 
which is a dumbed down version of SQLgrey's second-level 
auto-whitelisting (apply greylisting on domains only, disregard user 
component in e-mail addresses).

>
>> The sender verification would
>> verify both SMTP server AND sender but I don't know how it will affect
>> the performance of the server.
>
>
> No, sender verification will connect to the DNS-listed MX of the 
> domain part of the sender address -- which may be different from the 
> "outgoing" mail server used to send from that domain[1] -- and attempt 
> to verify if the sender address exists. Of course, some connecting 
> MTAs don't keep a local_recipient_maps (or their MTA equivalent) on 
> their MXes, so you'll get a positive sender verification every single 
> time. So sender verification is also not the silver bullet.


Note to self : toy with this in SQLgrey. I was thinking about SPF 
support in SQLgrey could be good to, but is there a plan to add SPF 
support directly in Postfix ?

Best regards,

Lionel.

[Sqlgrey-users] Re: Greylist or sender verification?

[Lionel B. <lio...@bo...>]

Lionel Bouton wrote the following on 10/07/04 10:41 :

>>
>> No, sender verification will connect to the DNS-listed MX of the 
>> domain part of the sender address -- which may be different from the 
>> "outgoing" mail server used to send from that domain[1] -- and 
>> attempt to verify if the sender address exists. Of course, some 
>> connecting MTAs don't keep a local_recipient_maps (or their MTA 
>> equivalent) on their MXes, so you'll get a positive sender 
>> verification every single time. So sender verification is also not 
>> the silver bullet.
>
>
>
> Note to self : toy with this in SQLgrey.


Should have searched for "sender verification" on postfix.org earlier...
I'm not yet through the whole pile of posftix docs !