sqlgrey-users

[Sqlgrey-users] lost connection event

[Bastien L. <ba...@yo...>]

Hi, using SQLGrey Version: 1.6.8-1  since a 10 days on  our production setup, receiving around  600 000 message per day.
2 servers hosting sqlgrey and 1 backend postgreSQL DB for sqlgrey.
It does work really great, thanks for this great tool ;)


Only little problem, I wanna solve is that really rarely (most of time, 1 or 2 times a day, sometimes 0), SQLGrey does encounter a concatenation problem and does not succeed to enter some data in DB.
This does trigger a lost connection and reconnection event, therefore sending me an email to tell me that connection to database was lost and a second one one second later to tell me that connection is back.

I identified 2 mails which triggered this kind of problem . Any idea of what was so special with theses 2 emails?
Or a way to tell SQLGrey to wait for 3 or 4 lost connections before sending alert email? (false positive are a bit annoying)

Mar 15 15:09:02 PHMCSMTP02 sqlgrey: grey: get_reconnect_delay error: no connect in database for web...@de..., 78.40.49, adm...@de...
Mar 15 15:09:02 PHMCSMTP02 sqlgrey: warning: Use of uninitialized value $delay in concatenation (.) or string at /usr/sbin/sqlgrey line 2053.
Mar 15 15:09:02 PHMCSMTP02 sqlgrey: grey: reconnect ok: 78.40.49(78.40.49.241), web...@de... -> adm...@de... ()
Mar 15 15:09:02 PHMCSMTP02 sqlgrey: grey: from awl: 78.40.49, web...@de... added
Mar 15 15:09:02 PHMCSMTP02 postfix/pickup[15438]: DE3F311C9FB: uid=111 from=<sqlgrey>
Mar 15 15:09:02 PHMCSMTP02 sqlgrey: warning: Use of uninitialized value $DBI::errstr in concatenation (.) or string at /usr/sbin/sqlgrey line 237.
Mar 15 15:09:02 PHMCSMTP02 sqlgrey: dbaccess: warning: couldn't do query:#012INSERT INTO from_awl (sender_name, sender_domain, src, first_seen, last_seen) VALUES('webmaster','de
cade.fr','78.40.49','error: nothing in connect',NOW()):#012, reconnecting to DB


Mar 10 20:56:48 PHMCSMTP02 sqlgrey: grey: reconnect ok: 88.191.81(88.191.81.9), bo...@ph... -> vg...@ka... (00:08:25.241069)
Mar 10 20:56:48 PHMCSMTP02 sqlgrey: grey: from awl: 88.191.81, bo...@ph... added
Mar 10 20:56:48 PHMCSMTP02 postfix/pickup[18078]: 94CF911C9FC: uid=111 from=<sqlgrey>
Mar 10 20:56:48 PHMCSMTP02 sqlgrey: warning: Use of uninitialized value $DBI::errstr in concatenation (.) or string at /usr/sbin/sqlgrey line 237.
Mar 10 20:56:48 PHMCSMTP02 sqlgrey: dbaccess: warning: couldn't do query:#012INSERT INTO from_awl (sender_name, sender_domain, src, first_seen, last_seen) VALUES('bounce','phpli
st.fr','88.191.81','2010-03-10 20:52:12.482616',NOW()):#012, reconnecting to DB
Mar 10 20:56:48 PHMCSMTP02 postfix/qmgr[12199]: 94CF911C9FC: from=<sq...@PH...>, size=441, nrcpt=1 (queue active)

Thanks

Bastien

Re: [Sqlgrey-users] lost connection event

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

The Mon, 15 Mar 2010 15:20:00 +0100,
Bastien LAMOUREUX <ba...@yo...> wrote:

> Hi, using SQLGrey Version: 1.6.8-1  since a 10 days on  our
> production setup, receiving around  600 000 message per day. 2
> servers hosting sqlgrey and 1 backend postgreSQL DB for sqlgrey. It
> does work really great, thanks for this great tool ;)
> 
> 
> Only little problem, I wanna solve is that really rarely (most of
> time, 1 or 2 times a day, sometimes 0), SQLGrey does encounter a
> concatenation problem and does not succeed to enter some data in DB.
> This does trigger a lost connection and reconnection event, therefore
> sending me an email to tell me that connection to database was lost
> and a second one one second later to tell me that connection is back.
> 
> I identified 2 mails which triggered this kind of problem . Any idea
> of what was so special with theses 2 emails? Or a way to tell SQLGrey
> to wait for 3 or 4 lost connections before sending alert email?
> (false positive are a bit annoying)


What probably happened is that the origin server contacted both MX at
nearly the same time (spam servers are known to do this). The first MX
managed to recognize the email and remove it from the connect table
before the second MX tried to fetch the entry in the same connect
table. This case is hard to detect and we don't have any workable
solution in the code yet.

Lionel

[Sqlgrey-users] How to whitelist a singular email address

[Bruce B. <bb...@bo...>]

Happy Easter to all!

What would be the suggested way to whitelist a singular email  
address?  May I simply add,  jo...@ao..., to  
clients_fqdn_whitelist.local or would I have to add it as a regex,  
such as   /^(johndoe)\@aol.com/     I can not find any example of  
this in either the documentation or the bundled version of  
clients_fqdn_whitelist.


P.S.  I see that some AOL common pool servers are whitelisted in the  
distributed clients_ip_whitelist...

64.12.137       # AOL (common pool) - http://postmaster.aol.com/ 
servers/imo.html
64.12.138       # AOL (common pool)
152.163.225   # AOL (common pool)
205.188.139.136 # AOL (common pool)
205.188.139.137 # AOL (common pool)
205.188.144.207 # AOL (common pool)
205.188.144.208 # AOL (common pool)
205.188.156.66  # AOL (common pool)
205.188.157     # AOL (common pool)
205.188.159.7   # AOL (common pool)


However, in this case the email came in from 64.12.206.  I'm  
reluctant to add 64.12.206 to clients_ip_whiltelist.local without  
direction from a more experienced sqlgrey user.  I fear it may  
inadvertently "over-whiten" my installation.

Thank you,
B. Bodger
Oklahoma City, OK

Re: [Sqlgrey-users] How to whitelist a singular email address

[Winfried N. <win...@ne...>]

Hi,

Bruce Bodger schrieb:
> What would be the suggested way to whitelist a singular email
> address?  May I simply add,  jo...@ao..., to
> clients_fqdn_whitelist.local or would I have to add it as a regex,
> such as   /^(johndoe)\@aol.com/     I can not find any example of
> this in either the documentation or the bundled version of
> clients_fqdn_whitelist.
>
>    
Non of the examples you mentioned. SQLgrey doesn't look at email 
addresses, but only
at hostnames/ip address of sending servers. If you really wanna 
whitelist that email
address, you should put some postfix exception rule in place, that will 
whiteliste the
mail before sqlgrey can block it (though this isn't a really good idea).

Winni

-- 
Winfried Neessen
Peter-Bauer-Str. 9
50823 Cologne

Phone: +49 221 2612601
Fax: +49 221 2612602
eMail: win...@ne...
PGP/GPG Key-ID: 0xda5cd8ef

Re: [Sqlgrey-users] How to whitelist a singular email address

[Bruce B. <bb...@bo...>]

On Apr 4, 2010, at 4:20 PM, Winfried Neessen wrote:

> Hi,
>
> Bruce Bodger schrieb:
>>
>> What would be the suggested way to whitelist a singular email
>> address?  May I simply add,  jo...@ao..., to
>> clients_fqdn_whitelist.local or would I have to add it as a regex,
>> such as   /^(johndoe)\@aol.com/
> Non of the examples you mentioned. SQLgrey doesn't look at email  
> addresses, but only
> at hostnames/ip address of sending servers. If you really wanna  
> whitelist that email
> address, you should put some postfix exception rule in place, that  
> will whiteliste the
> mail before sqlgrey can block it (though this isn't a really good  
> idea).

Thank you for the reply, Winni.  Since, as you state, SQLgrey only  
looks to the right of the "@" symbol, adding 64.12.206 to my  
clients_ip_whitelist.local might be the cleanest way.

Regards,
Bruce

Re: [Sqlgrey-users] How to whitelist a singular email address

[Kyle L. <la...@uc...>]

Bruce Bodger wrote:
> Since, as you state, SQLgrey only looks
> to the right of the "@" symbol, adding 64.12.206 to my
> clients_ip_whitelist.local might be the cleanest way.

For what it's worth, you'll probably have to add more than that. The list
used by SQLgrey to whitelist AOL is a bit stale; this is, to my knowledge,
the authoritative declaration of AOL's outbound mail servers:

	http://postmaster.aol.com/servers/imo.html

--Kyle

Re: [Sqlgrey-users] How to whitelist a singular email address

[Bruce B. <bb...@bo...>]

>
>> Since, as you state, SQLgrey only looks
>> to the right of the "@" symbol, adding 64.12.206 to my
>> clients_ip_whitelist.local might be the cleanest way.
>
> For what it's worth, you'll probably have to add more than that. The  
> list
> used by SQLgrey to whitelist AOL is a bit stale; this is, to my  
> knowledge,
> the authoritative declaration of AOL's outbound mail servers:
>
> 	http://postmaster.aol.com/servers/imo.html

Thanks a lot for that, Kyle.  But are they all "misbehaved"... i.e. do  
they all warrant adding to the whitelist?

Regards,
Bruce

Re: [Sqlgrey-users] How to whitelist a singular email address

[Kyle L. <la...@uc...>]

Bruce Bodger wrote:
> Thanks a lot for that, Kyle.  But are they all "misbehaved"... i.e. do  
> they all warrant adding to the whitelist?

I don't know. After whitelisting Google's SPF record (which includes
something like 70,000 /24 address blocks), I didn't feel so ambivalent
about whitelisting AOL's server list.

With whitelisting, one has to hope that the odds are in favor of the
whitelisted IP or IP range remaining free of spam-bots. In general,
for addresses assigned to mail servers, this is likely to be the case.
I realize this breaks the generality of the greylisting, but does not
appear to have a measurable impact on the overall effectiveness.

--Kyle