sqlgrey-users — Users of SQLgrey

[Sqlgrey-users] Email Address is not in use anymore

[<nc...@xn...>]

Dear Sender 

The mail address nc...@xn... is not used anymore. If you want to contact me. Please use my new address.

fir...@tr...

Firstname: Niccolo
Lastname: Camponovo


Best Regards
Niccolo Camponovo

[Sqlgrey-users] Excessive X-Greylist headers

[Alex <mys...@gm...>]

Hi,

It seems when a message is sent to multiple recipients, multiple
X-Greylist headers are added. When there are dozens of these, it
appears some systems are rejecting it due to the header being too
large.

How can I configure it to only include one copy of the X-Greylist header?

I'm using postfix. Is it possible to strip off all but one copy of the header?

X-Greylist: whitelisted by SQLgrey-1.8.0

Thanks,
Alex

[Sqlgrey-users] Problems with hotmail whitelisting

[Alex <mys...@gm...>]

Hi all,

I've been having an issue with a lot of spam from hotmail. Inside of
the FN is this:
'
X-Greylist: domain auto-whitelisted by SQLgrey-1.8.0

I'd like to disable the automatic whitelisting of hotmail and its IP
addresses. I see the following corresponding log entry:

Oct  1 10:23:17 mail02 sqlgrey: optin: greylisting active for 11...@ex...
Oct  1 10:23:17 mail02 sqlgrey: grey: unknown pattern:
bay004-omc2s2.hotmail.com, 65.54.190.77: using C-class (65.54.190).
Oct  1 10:23:17 mail02 sqlgrey: grey: domain awl match: updating
65.54.190(65.54.190.77), hotmail.com
Oct  1 10:23:17 mail02 postfix/smtpd[11933]: E309934B1BB:
client=bay004-omc2s2.hotmail.com[65.54.190.77]

I don't see any references to hotmail.com or 65.54 included in any
whitelist in /etc/sqlgrey, however. What am I missing here?

Thanks,
Alex

Re: [Sqlgrey-users] Temporary Disable

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

Thanks again, Dan. 

I ended up taking Karl's recommendation by removing the call in postfix's main.cf and sending it a 'reload'.  Starting and stopping sqlgrey on OS X is a bit more than just executing 'sqlgrey -k; sqlgrey -d'  :-)

Also, according to the comments in sqlgrey.conf, the reject code is "only used if reject_first_attempt/reject_early_reconnect = immed", the default being 'delay'.

Again, thanks to you both.

Regards,
Bruce



On Aug 7, 2014, at 2:43 PM, da...@ha... wrote:

> Yes, a restart of sqlgrey only though. Not postfix or anything else.
> You can do it like:
> $ sqlgrey -k; sqlgrey -d
> 
> its very fast.
> 
> 
> - Dan
> 
> On 2014-08-07 21:29, Bruce Bodger wrote:
>> Thanks, Dan.  I presume making changes to sqlgrey.conf would require a restart to take effect?
>> 
>> Bruce
>> 
>> 
>> On Aug 7, 2014, at 3:30 AM, da...@ha... wrote:
>> 
>>> On 2014-08-07 04:02, Karl O. Pinc wrote:
>>>> On 08/06/2014 07:42:20 PM, Bruce Bodger wrote:
>>>>> Good day,
>>>>> 
>>>>> I would like to temporarily disable sqlgrey.  Would adding * and *.*
>>>>> to client_fqdn_whitelist.local achieve this goal?  Is there a better
>>>>> way to TEMPORARILY disable greylisting?
>>>> Turn off the stuff added to check_policy_filter in
>>>> /etc/postfix/main.cf.  Comment it out and do
>>>> a "postfix reload".
>>>> 
>>> I've usually set:
>>> reject_code = dunno
>>> 
>>> in sqlgrey.conf when testing code changes in sqlgrey. This is like
>>> running in a "simulation mode", where everything works as normal, except
>>> you just let all the mail through anyway.
>>> I guess it depends on why you need to disable it.
>>> 
>>> 
>>> 
>>> - Dan
>>> 
>>> ------------------------------------------------------------------------------
>>> Infragistics Professional
>>> Build stunning WinForms apps today!
>>> Reboot your WinForms applications with our WinForms controls. 
>>> Build a bridge from your legacy apps to the future.
>>> http://pubads.g.doubleclick.net/gampad/clk?id=153845071&iu=/4140/ostg.clktrk
>>> _______________________________________________
>>> Sqlgrey-users mailing list
>>> Sql...@li...
>>> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users
>>> 
>> 
>> 
>> ------------------------------------------------------------------------------
>> Infragistics Professional
>> Build stunning WinForms apps today!
>> Reboot your WinForms applications with our WinForms controls. 
>> Build a bridge from your legacy apps to the future.
>> http://pubads.g.doubleclick.net/gampad/clk?id=153845071&iu=/4140/ostg.clktrk
>> _______________________________________________
>> Sqlgrey-users mailing list
>> Sql...@li...
>> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users
> 
> 
> ------------------------------------------------------------------------------
> Infragistics Professional
> Build stunning WinForms apps today!
> Reboot your WinForms applications with our WinForms controls. 
> Build a bridge from your legacy apps to the future.
> http://pubads.g.doubleclick.net/gampad/clk?id=153845071&iu=/4140/ostg.clktrk
> _______________________________________________
> Sqlgrey-users mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users
> 

Re: [Sqlgrey-users] Temporary Disable

[<da...@ha...>]

Yes, a restart of sqlgrey only though. Not postfix or anything else.
You can do it like:
$ sqlgrey -k; sqlgrey -d

its very fast.


- Dan

On 2014-08-07 21:29, Bruce Bodger wrote:
> Thanks, Dan.  I presume making changes to sqlgrey.conf would require a restart to take effect?
>
> Bruce
>
>
> On Aug 7, 2014, at 3:30 AM, da...@ha... wrote:
>
>> On 2014-08-07 04:02, Karl O. Pinc wrote:
>>> On 08/06/2014 07:42:20 PM, Bruce Bodger wrote:
>>>> Good day,
>>>>
>>>> I would like to temporarily disable sqlgrey.  Would adding * and *.*
>>>> to client_fqdn_whitelist.local achieve this goal?  Is there a better
>>>> way to TEMPORARILY disable greylisting?
>>> Turn off the stuff added to check_policy_filter in
>>> /etc/postfix/main.cf.  Comment it out and do
>>> a "postfix reload".
>>>
>> I've usually set:
>> reject_code = dunno
>>
>> in sqlgrey.conf when testing code changes in sqlgrey. This is like
>> running in a "simulation mode", where everything works as normal, except
>> you just let all the mail through anyway.
>> I guess it depends on why you need to disable it.
>>
>>
>>
>> - Dan
>>
>> ------------------------------------------------------------------------------
>> Infragistics Professional
>> Build stunning WinForms apps today!
>> Reboot your WinForms applications with our WinForms controls. 
>> Build a bridge from your legacy apps to the future.
>> http://pubads.g.doubleclick.net/gampad/clk?id=153845071&iu=/4140/ostg.clktrk
>> _______________________________________________
>> Sqlgrey-users mailing list
>> Sql...@li...
>> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users
>>
>
>
> ------------------------------------------------------------------------------
> Infragistics Professional
> Build stunning WinForms apps today!
> Reboot your WinForms applications with our WinForms controls. 
> Build a bridge from your legacy apps to the future.
> http://pubads.g.doubleclick.net/gampad/clk?id=153845071&iu=/4140/ostg.clktrk
> _______________________________________________
> Sqlgrey-users mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users

Re: [Sqlgrey-users] Temporary Disable

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

Thanks, Dan.  I presume making changes to sqlgrey.conf would require a restart to take effect?

Bruce


On Aug 7, 2014, at 3:30 AM, da...@ha... wrote:

> On 2014-08-07 04:02, Karl O. Pinc wrote:
>> On 08/06/2014 07:42:20 PM, Bruce Bodger wrote:
>>> Good day,
>>> 
>>> I would like to temporarily disable sqlgrey.  Would adding * and *.*
>>> to client_fqdn_whitelist.local achieve this goal?  Is there a better
>>> way to TEMPORARILY disable greylisting?
>> Turn off the stuff added to check_policy_filter in
>> /etc/postfix/main.cf.  Comment it out and do
>> a "postfix reload".
>> 
> I've usually set:
> reject_code = dunno
> 
> in sqlgrey.conf when testing code changes in sqlgrey. This is like
> running in a "simulation mode", where everything works as normal, except
> you just let all the mail through anyway.
> I guess it depends on why you need to disable it.
> 
> 
> 
> - Dan
> 
> ------------------------------------------------------------------------------
> Infragistics Professional
> Build stunning WinForms apps today!
> Reboot your WinForms applications with our WinForms controls. 
> Build a bridge from your legacy apps to the future.
> http://pubads.g.doubleclick.net/gampad/clk?id=153845071&iu=/4140/ostg.clktrk
> _______________________________________________
> Sqlgrey-users mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users
> 

Re: [Sqlgrey-users] Temporary Disable

[<da...@ha...>]

On 2014-08-07 04:02, Karl O. Pinc wrote:
> On 08/06/2014 07:42:20 PM, Bruce Bodger wrote:
>> Good day,
>>
>> I would like to temporarily disable sqlgrey.  Would adding * and *.*
>> to client_fqdn_whitelist.local achieve this goal?  Is there a better
>> way to TEMPORARILY disable greylisting?
> Turn off the stuff added to check_policy_filter in
> /etc/postfix/main.cf.  Comment it out and do
> a "postfix reload".
>
I've usually set:
reject_code = dunno

in sqlgrey.conf when testing code changes in sqlgrey. This is like
running in a "simulation mode", where everything works as normal, except
you just let all the mail through anyway.
I guess it depends on why you need to disable it.



- Dan

Re: [Sqlgrey-users] Temporary Disable

[Karl O. P. <ko...@me...>]

On 08/06/2014 07:42:20 PM, Bruce Bodger wrote:
> Good day,
> 
> I would like to temporarily disable sqlgrey.  Would adding * and *.*
> to client_fqdn_whitelist.local achieve this goal?  Is there a better
> way to TEMPORARILY disable greylisting?

Turn off the stuff added to check_policy_filter in
/etc/postfix/main.cf.  Comment it out and do
a "postfix reload".


Karl <ko...@me...>
Free Software:  "You don't pay back, you pay forward."
                 -- Robert A. Heinlein

[Sqlgrey-users] Temporary Disable

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

Good day,

I would like to temporarily disable sqlgrey.  Would adding * and *.* to client_fqdn_whitelist.local achieve this goal?  Is there a better way to TEMPORARILY disable greylisting?

Thank you in advance.

~Bruce

Re: [Sqlgrey-users] sqlgrey fails when configured with multiple machines

[<da...@ha...>]

On 2014-07-23 00:22, Alex wrote:
> I may have made a mistake. Would you confirm for me that this change
> is made in the setconfig() function? The version I have has a few
> patches from fedora, and this may have affected my patch.

All i did was add this line in the setconfig() function:
  return undef if $param ne 'version'; # Only die for 'version'

It was added on the line, right above the line starting with "$self->mydie".

Re: [Sqlgrey-users] sqlgrey fails when configured with multiple machines

[Alex <mys...@gm...>]

Hi,

On Tue, Jul 22, 2014 at 6:16 PM, Alex <mys...@gm...> wrote:

> Hi,
>
> On Mon, Jul 21, 2014 at 8:41 PM, Alex <mys...@gm...> wrote:
>
>> Hi,
>>
>>
>> On Fri, Jul 18, 2014 at 3:12 AM, Dan Faerch <da...@ha...> wrote:
>> >
>> > On 2014-07-18T00:30:38 CEST, Alex wrote:
>> > > Then, about seven minutes into my testing, sqlgrey quit and died on
>> > > all three systems:
>> > >
>> > > Jul 17 18:02:22 mail02 sqlgrey: fatal: setconfig error at
>> > > /usr/sbin/sqlgrey line 195.
>> > > Jul 17 18:02:36 mail03 sqlgrey: fatal: setconfig error at
>> > > /usr/sbin/sqlgrey line 195.
>> > > Jul 17 18:03:03 mail01 sqlgrey: fatal: setconfig error at
>> > > /usr/sbin/sqlgrey line 195.
>> > >
>> >
>> > So this is very interesting and useful log information. Thats a bug,
>> > right there.. I think i know what this is. Ill make a patch and get
>> > back to you.
>>
>> Thanks very much. I've applied the patch. I'll be watching it over the
>> next few days, and hope to be doing some more testing with the changes soon.
>>
>
> Found out today when the link between mail02 (the db server) and mail03
> was broken that the patch is not fixing the problem. Did you test this
> locally before?
>

I may have made a mistake. Would you confirm for me that this change is
made in the setconfig() function? The version I have has a few patches from
fedora, and this may have affected my patch.

Thanks,
Alex

Re: [Sqlgrey-users] sqlgrey fails when configured with multiple machines

[Alex <mys...@gm...>]

Hi,

On Mon, Jul 21, 2014 at 8:41 PM, Alex <mys...@gm...> wrote:

> Hi,
>
>
> On Fri, Jul 18, 2014 at 3:12 AM, Dan Faerch <da...@ha...> wrote:
> >
> > On 2014-07-18T00:30:38 CEST, Alex wrote:
> > > Then, about seven minutes into my testing, sqlgrey quit and died on
> > > all three systems:
> > >
> > > Jul 17 18:02:22 mail02 sqlgrey: fatal: setconfig error at
> > > /usr/sbin/sqlgrey line 195.
> > > Jul 17 18:02:36 mail03 sqlgrey: fatal: setconfig error at
> > > /usr/sbin/sqlgrey line 195.
> > > Jul 17 18:03:03 mail01 sqlgrey: fatal: setconfig error at
> > > /usr/sbin/sqlgrey line 195.
> > >
> >
> > So this is very interesting and useful log information. Thats a bug,
> > right there.. I think i know what this is. Ill make a patch and get
> > back to you.
>
> Thanks very much. I've applied the patch. I'll be watching it over the
> next few days, and hope to be doing some more testing with the changes soon.
>

Found out today when the link between mail02 (the db server) and mail03 was
broken that the patch is not fixing the problem. Did you test this locally
before?

Thanks,
Alex

Re: [Sqlgrey-users] sqlgrey fails when configured with multiple machines

[Alex <mys...@gm...>]

Hi,

On Fri, Jul 18, 2014 at 3:12 AM, Dan Faerch <da...@ha...> wrote:
>
> On 2014-07-18T00:30:38 CEST, Alex wrote:
> > Then, about seven minutes into my testing, sqlgrey quit and died on
> > all three systems:
> >
> > Jul 17 18:02:22 mail02 sqlgrey: fatal: setconfig error at
> > /usr/sbin/sqlgrey line 195.
> > Jul 17 18:02:36 mail03 sqlgrey: fatal: setconfig error at
> > /usr/sbin/sqlgrey line 195.
> > Jul 17 18:03:03 mail01 sqlgrey: fatal: setconfig error at
> > /usr/sbin/sqlgrey line 195.
> >
>
> So this is very interesting and useful log information. Thats a bug,
> right there.. I think i know what this is. Ill make a patch and get
> back to you.

Thanks very much. I've applied the patch. I'll be watching it over the next
few days, and hope to be doing some more testing with the changes soon.

> > > In this case 100s.
> >
> > I still have mine set for 1s, but I'd like an "indefinite" option for
> No you have your "sqlgrey -> mysql" timeout at 1s. This value is for
> "postfix -> sqlgrey".

I must have been very tired and not thinking clearly. Appreciate your help.

Thanks,
Alex

[Sqlgrey-users] sqlgrey-logstats.pl bug?

[Edgar P. <ed...@pe...>]

When I ran sqlgrey-logstats.pl with no command line arguments there was
no message to inform me I needed to provide any.  Here is a diff to add
usage message if no argument given:

136d135
<     if ((@ARGV == 0) && (-t STDIN))   { pod2usage(1) }

Thanks for the great program.  Here is an example of its greatness:


 ------
| Spam |
 ------

167.89.3: 1
            bounces+1106098-a8f4-user=ema...@se...: 1

now go to http://sendgrid.info

[Sqlgrey-users] [SOLVED] Re: update_sqlgrey_config on FreeBSD

[Edgar P. <ed...@pe...>]

Not sure why it took so long to come up with the solution, but here is
the final diff for bin/update_sqlgrey_config after installing gnu coreutils:

< #!/usr/local/bin/bash
---
> #!/bin/bash
4c4
< MD5SUM=`which gmd5sum 2>/dev/null`
---
> MD5SUM=`which md5sum 2>/dev/null`
27c27
< MYDIR=/usr/local/etc/sqlgrey
---
> MYDIR=/etc/sqlgrey
51c51
<     cd $MYDIR
---
>     cd ~sqlgrey
62c62
< TOUPDATE=`gmd5sum -c $MYTMP/MD5SUMS 2>/dev/null | grep FAILED | cut
-d: -f1`
---
> TOUPDATE=`md5sum -c $MYTMP/MD5SUMS 2>/dev/null | grep FAILED | cut -d:
-f1`
88c88
< gmd5sum -c MD5SUMS >/dev/null 2>/dev/null
---
> md5sum -c MD5SUMS >/dev/null 2>/dev/null

On 07/19/2014 05:00 PM, Edgar Pettijohn wrote:
> Just realized what the goal of `md5sums -c $MYTMP/MD5SUMS 2>/dev/null
> | grep FAILED | cut -d: -f1` was.  Unfortunantly md5 doesn't give the
> same error message, so the grep and cut don't do what they are
> intended to do.  Sorry for the noise thus far.
>
>
> On 07/19/2014 11:53 AM, Edgar Pettijohn wrote:
>> Well I've definantly narrowed it down. This line seems to be the culprit:
>>
>>
>> TOUPDATE=`md5 -c $MYTMP/MD5SUMS 2>/dev/null | grep FAILED | cut -d: -f1
>>
>> When I change TOUPDATE=clients_fqdn_whitlist (the file locally
>> changed to test script) it runs without error and updates the file. 
>> Playing around with this I had to change cut -d: -f1 to cut -d " "
>> -f3 to have the same result as a linux machine.  However, that alone
>> has not fixed the problem.  I'm not sure that md5 -c and md5sum -c
>> have the same effect.  The wording in the man pages are definantly
>> different, but to me it seems the intent is the same.  Any help is
>> appreciated.
>>
>> Thanks
>> On 07/18/2014 09:17 PM, Edgar Pettijohn wrote:
>>> Hello list,
>>>
>>> I'm trying to get update_sqlgrey_config to work on FreeBSD 10. 
>>> Following is a diff of changes I've made compared to the stock
>>> version.  It still doesn't appear to be working correctly.  I'm
>>> going to add some echo's and see if I can't pinpoint the issue, but
>>> hopefully someone out there has the answer already.
>>>
>>> Thanks
>>>
>>> 1c1
>>> < #!/usr/local/bin/bash
>>> ---
>>> > #!/bin/bash
>>> 4c4
>>> < MD5SUM=`which md5 2>/dev/null`
>>> ---
>>> > MD5SUM=`which md5sum 2>/dev/null`
>>> 7c7
>>> <     echo "md5 not found in PATH, can't continue"
>>> ---
>>> >     echo "md5sum not found in PATH, can't continue"
>>> 27c27
>>> < MYDIR=/usr/local/etc/sqlgrey
>>> ---
>>> > MYDIR=/etc/sqlgrey
>>> 51c51
>>> <     cd /usr/local/etc/sqlgrey
>>> ---
>>> >     cd ~sqlgrey
>>> 56c56
>>> <  
>>> ---
>>> >
>>> 62c62
>>> < TOUPDATE=`md5 -c $MYTMP/MD5SUMS 2>/dev/null | grep FAILED | cut
>>> -d: -f1`
>>> ---
>>> > TOUPDATE=`md5sum -c $MYTMP/MD5SUMS 2>/dev/null | grep FAILED | cut
>>> -d: -f1`
>>> 88c88
>>> < md5 -c MD5SUMS >/dev/null 2>/dev/null
>>> ---
>>> > md5sum -c MD5SUMS >/dev/null 2>/dev/null
>>>
>>>
>>>
>>> ------------------------------------------------------------------------------
>>> Want fast and easy access to all the code in your enterprise? Index and
>>> search up to 200,000 lines of code with a free copy of Black Duck
>>> Code Sight - the same software that powers the world's largest code
>>> search on Ohloh, the Black Duck Open Hub! Try it now.
>>> http://p.sf.net/sfu/bds
>>>
>>>
>>> _______________________________________________
>>> Sqlgrey-users mailing list
>>> Sql...@li...
>>> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users
>>
>>
>>
>> ------------------------------------------------------------------------------
>> Want fast and easy access to all the code in your enterprise? Index and
>> search up to 200,000 lines of code with a free copy of Black Duck
>> Code Sight - the same software that powers the world's largest code
>> search on Ohloh, the Black Duck Open Hub! Try it now.
>> http://p.sf.net/sfu/bds
>>
>>
>> _______________________________________________
>> Sqlgrey-users mailing list
>> Sql...@li...
>> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users
>
>
>
> ------------------------------------------------------------------------------
> Want fast and easy access to all the code in your enterprise? Index and
> search up to 200,000 lines of code with a free copy of Black Duck
> Code Sight - the same software that powers the world's largest code
> search on Ohloh, the Black Duck Open Hub! Try it now.
> http://p.sf.net/sfu/bds
>
>
> _______________________________________________
> Sqlgrey-users mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users

Re: [Sqlgrey-users] update_sqlgrey_config on FreeBSD

[Edgar P. <ed...@pe...>]

Just realized what the goal of `md5sums -c $MYTMP/MD5SUMS 2>/dev/null |
grep FAILED | cut -d: -f1` was.  Unfortunantly md5 doesn't give the same
error message, so the grep and cut don't do what they are intended to
do.  Sorry for the noise thus far.


On 07/19/2014 11:53 AM, Edgar Pettijohn wrote:
> Well I've definantly narrowed it down. This line seems to be the culprit:
>
>
> TOUPDATE=`md5 -c $MYTMP/MD5SUMS 2>/dev/null | grep FAILED | cut -d: -f1
>
> When I change TOUPDATE=clients_fqdn_whitlist (the file locally changed
> to test script) it runs without error and updates the file.  Playing
> around with this I had to change cut -d: -f1 to cut -d " " -f3 to have
> the same result as a linux machine.  However, that alone has not fixed
> the problem.  I'm not sure that md5 -c and md5sum -c have the same
> effect.  The wording in the man pages are definantly different, but to
> me it seems the intent is the same.  Any help is appreciated.
>
> Thanks
> On 07/18/2014 09:17 PM, Edgar Pettijohn wrote:
>> Hello list,
>>
>> I'm trying to get update_sqlgrey_config to work on FreeBSD 10. 
>> Following is a diff of changes I've made compared to the stock
>> version.  It still doesn't appear to be working correctly.  I'm going
>> to add some echo's and see if I can't pinpoint the issue, but
>> hopefully someone out there has the answer already.
>>
>> Thanks
>>
>> 1c1
>> < #!/usr/local/bin/bash
>> ---
>> > #!/bin/bash
>> 4c4
>> < MD5SUM=`which md5 2>/dev/null`
>> ---
>> > MD5SUM=`which md5sum 2>/dev/null`
>> 7c7
>> <     echo "md5 not found in PATH, can't continue"
>> ---
>> >     echo "md5sum not found in PATH, can't continue"
>> 27c27
>> < MYDIR=/usr/local/etc/sqlgrey
>> ---
>> > MYDIR=/etc/sqlgrey
>> 51c51
>> <     cd /usr/local/etc/sqlgrey
>> ---
>> >     cd ~sqlgrey
>> 56c56
>> <  
>> ---
>> >
>> 62c62
>> < TOUPDATE=`md5 -c $MYTMP/MD5SUMS 2>/dev/null | grep FAILED | cut -d:
>> -f1`
>> ---
>> > TOUPDATE=`md5sum -c $MYTMP/MD5SUMS 2>/dev/null | grep FAILED | cut
>> -d: -f1`
>> 88c88
>> < md5 -c MD5SUMS >/dev/null 2>/dev/null
>> ---
>> > md5sum -c MD5SUMS >/dev/null 2>/dev/null
>>
>>
>>
>> ------------------------------------------------------------------------------
>> Want fast and easy access to all the code in your enterprise? Index and
>> search up to 200,000 lines of code with a free copy of Black Duck
>> Code Sight - the same software that powers the world's largest code
>> search on Ohloh, the Black Duck Open Hub! Try it now.
>> http://p.sf.net/sfu/bds
>>
>>
>> _______________________________________________
>> Sqlgrey-users mailing list
>> Sql...@li...
>> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users
>
>
>
> ------------------------------------------------------------------------------
> Want fast and easy access to all the code in your enterprise? Index and
> search up to 200,000 lines of code with a free copy of Black Duck
> Code Sight - the same software that powers the world's largest code
> search on Ohloh, the Black Duck Open Hub! Try it now.
> http://p.sf.net/sfu/bds
>
>
> _______________________________________________
> Sqlgrey-users mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users

Re: [Sqlgrey-users] update_sqlgrey_config on FreeBSD

[Edgar P. <ed...@pe...>]

Well I've definantly narrowed it down. This line seems to be the culprit:


TOUPDATE=`md5 -c $MYTMP/MD5SUMS 2>/dev/null | grep FAILED | cut -d: -f1

When I change TOUPDATE=clients_fqdn_whitlist (the file locally changed
to test script) it runs without error and updates the file.  Playing
around with this I had to change cut -d: -f1 to cut -d " " -f3 to have
the same result as a linux machine.  However, that alone has not fixed
the problem.  I'm not sure that md5 -c and md5sum -c have the same
effect.  The wording in the man pages are definantly different, but to
me it seems the intent is the same.  Any help is appreciated.

Thanks
On 07/18/2014 09:17 PM, Edgar Pettijohn wrote:
> Hello list,
>
> I'm trying to get update_sqlgrey_config to work on FreeBSD 10. 
> Following is a diff of changes I've made compared to the stock
> version.  It still doesn't appear to be working correctly.  I'm going
> to add some echo's and see if I can't pinpoint the issue, but
> hopefully someone out there has the answer already.
>
> Thanks
>
> 1c1
> < #!/usr/local/bin/bash
> ---
> > #!/bin/bash
> 4c4
> < MD5SUM=`which md5 2>/dev/null`
> ---
> > MD5SUM=`which md5sum 2>/dev/null`
> 7c7
> <     echo "md5 not found in PATH, can't continue"
> ---
> >     echo "md5sum not found in PATH, can't continue"
> 27c27
> < MYDIR=/usr/local/etc/sqlgrey
> ---
> > MYDIR=/etc/sqlgrey
> 51c51
> <     cd /usr/local/etc/sqlgrey
> ---
> >     cd ~sqlgrey
> 56c56
> <  
> ---
> >
> 62c62
> < TOUPDATE=`md5 -c $MYTMP/MD5SUMS 2>/dev/null | grep FAILED | cut -d: -f1`
> ---
> > TOUPDATE=`md5sum -c $MYTMP/MD5SUMS 2>/dev/null | grep FAILED | cut
> -d: -f1`
> 88c88
> < md5 -c MD5SUMS >/dev/null 2>/dev/null
> ---
> > md5sum -c MD5SUMS >/dev/null 2>/dev/null
>
>
>
> ------------------------------------------------------------------------------
> Want fast and easy access to all the code in your enterprise? Index and
> search up to 200,000 lines of code with a free copy of Black Duck
> Code Sight - the same software that powers the world's largest code
> search on Ohloh, the Black Duck Open Hub! Try it now.
> http://p.sf.net/sfu/bds
>
>
> _______________________________________________
> Sqlgrey-users mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users

[Sqlgrey-users] update_sqlgrey_config on FreeBSD

[Edgar P. <ed...@pe...>]

Hello list,

I'm trying to get update_sqlgrey_config to work on FreeBSD 10. 
Following is a diff of changes I've made compared to the stock version. 
It still doesn't appear to be working correctly.  I'm going to add some
echo's and see if I can't pinpoint the issue, but hopefully someone out
there has the answer already.

Thanks

1c1
< #!/usr/local/bin/bash
---
> #!/bin/bash
4c4
< MD5SUM=`which md5 2>/dev/null`
---
> MD5SUM=`which md5sum 2>/dev/null`
7c7
<     echo "md5 not found in PATH, can't continue"
---
>     echo "md5sum not found in PATH, can't continue"
27c27
< MYDIR=/usr/local/etc/sqlgrey
---
> MYDIR=/etc/sqlgrey
51c51
<     cd /usr/local/etc/sqlgrey
---
>     cd ~sqlgrey
56c56
<  
---
>
62c62
< TOUPDATE=`md5 -c $MYTMP/MD5SUMS 2>/dev/null | grep FAILED | cut -d: -f1`
---
> TOUPDATE=`md5sum -c $MYTMP/MD5SUMS 2>/dev/null | grep FAILED | cut -d:
-f1`
88c88
< md5 -c MD5SUMS >/dev/null 2>/dev/null
---
> md5sum -c MD5SUMS >/dev/null 2>/dev/null

[Sqlgrey-users] Patch: Only die in setconfig during upgrades.

[<da...@ha...>]

I've pushed the following patch to git:
Fiixes the scenario where, if db-cleanup runs while DB server is down,
sqlgrey will die.


--- a/sqlgrey
+++ b/sqlgrey
@@ -482,6 +482,7 @@ sub setconfig($$$) {
                                    'WHERE parameter = ?');
     if (!defined $sth or !$sth->execute($param)) {
        $self->mylog('dbaccess', 0, "error: couldn't access $config
table: $DBI::errstr");
+       return undef if $param ne 'version'; # Only die for 'version'
problems, which happen at startup.
        $self->mydie('setconfig error',
                     'Can\'t continue: config table unreadable');
     }




You can download the patched version here:
https://sourceforge.net/p/sqlgrey/code/ci/master/tree/sqlgrey?format=raw


- Dan

Re: [Sqlgrey-users] sqlgrey fails when configured with multiple machines

[Dan F. <da...@ha...>]

On 2014-07-18T00:30:38 CEST, Alex wrote:
> Then, about seven minutes into my testing, sqlgrey quit and died on
> all three systems:
>
> Jul 17 18:02:22 mail02 sqlgrey: fatal: setconfig error at
> /usr/sbin/sqlgrey line 195.
> Jul 17 18:02:36 mail03 sqlgrey: fatal: setconfig error at
> /usr/sbin/sqlgrey line 195.
> Jul 17 18:03:03 mail01 sqlgrey: fatal: setconfig error at
> /usr/sbin/sqlgrey line 195.
>

So this is very interesting and useful log information. Thats a bug, 
right there.. I think i know what this is. Ill make a patch and get 
back to you.

> > In this case 100s.
>
> I still have mine set for 1s, but I'd like an "indefinite" option for
No you have your "sqlgrey -> mysql" timeout at 1s. This value is for 
"postfix -> sqlgrey".

> I don't know if my 7m test hit some magic limit or something else
I think its the db-cleanup that tries to run. It saves a timestamp to 
the db and apparently that action isn't fault tolerant.

> So how do we explain it continuing beyond 100s when you've explicitly
> defined the timeout period to be 100s?

Because the defined timeout period is for Postfix. It tells postfix how 
long it will wait for a policy-daemon (like sqlgrey). "tester.pl" isnt 
using Postfix, it is connecting to sqlgrey directly and postfix timeout 
value is not in play at all.
This just means, that if it was Postfix that had made the connection to 
sqlgrey (and not tester.pl), it would have given up after 100 seconds 
and displayed "Server configuration problem".

Re: [Sqlgrey-users] sqlgrey fails when configured with multiple machines

[Alex <mys...@gm...>]

Hi,

> > I did some tests this evening by basically disconnecting the server
> > with the master mysql database, and it caused all mail on the two
> > remaining systems that were still running to bounce with the "4.3.5
> > Server configuration problem".
> If you made the configuration change on all your hosts, i dont know what
> you are experiencing and your mail contains no new information,
> technical or otherwise, to go on.  And that, paired with the fact that
> im fairly certain how this works and can see in my tests that it is
> indeed working as expected, simply makes me unable to come up with
> guesses as to whats troubling your system.

The problem is simply that when the server with the master sql database is
running on goes down, mail is stopped on all three systems. The two systems
that remain running just respond with temporary bounce messages instead of
responding with "dunno" or otherwise forwarding on the message.

> So heres a run where mysql-server has downed its interface just for 10
> seconds:
> ----
> $ time ./tester.pl --client-ip 10.0.0.1
> action=dunno
>
> real    0m3.062s
> user    0m0.056s
> sys     0m0.004s
> ----
> "action=dunno" means sqlgrey passes no judgment. Which in turn means
> "let it through". This "conclusion" is reached within 3 seconds (you can
> see that  at the line "real     0m3.062s").

Okay, I did some more testing. Live testing. At first I was surprised to
see the systems continued to deliver mail after stopping entirely the
master mysqld on mail02 because I knew I was having some kind of problem. I
monitored it for a while, made sure it was actually continuing to deliver
mail (which it was), and looking at the tons of sqlgrey logs reporting it
couldn't properly communicate with the database.

Then, about seven minutes into my testing, sqlgrey quit and died on all
three systems:

Jul 17 18:02:22 mail02 sqlgrey: fatal: setconfig error at /usr/sbin/sqlgrey
line 195.
Jul 17 18:02:36 mail03 sqlgrey: fatal: setconfig error at /usr/sbin/sqlgrey
line 195.
Jul 17 18:03:03 mail01 sqlgrey: fatal: setconfig error at /usr/sbin/sqlgrey
line 195.

The testing began here:

Jul 17 17:53:30 mail01 sqlgrey: dbaccess: error: couldn't get now() from
DB:
Jul 17 17:53:32 mail02 sqlgrey: dbaccess: error: couldn't get now() from DB:
Jul 17 17:53:33 mail03 sqlgrey: dbaccess: error: couldn't get now() from DB:

Between those times were hundreds of "Server configuration..." postfix
errors because sqlgrey had died.

> And this is an example of sqlgrey not running
> ----
> $ time ./tester.pl --client-ip 10.0.0.1
> Connect failed: IO::Socket::INET: connect: Connection refused
> ----
>
>
> Finding out how long postfix will wait is as simple as:
> ----
> $ postconf smtpd_policy_service_timeout
> smtpd_policy_service_timeout = 100s
> ----
> In this case 100s.

I still have mine set for 1s, but I'd like an "indefinite" option for the
case where I'm taking the main mysql system down for maintenance, or an
unexpected event occurs where I cannot reach the system for an undetermined
amount of time.

I don't know if my 7m test hit some magic limit or something else happened,
but I can test again if necessary, although I'd like your input first.

> When i point a my sqlgrey to a server behind a packet-dropping-firewall
> and rerun the test
> ----
> $ time ./tester.pl --client-ip 10.0.0.1
> ----
> i literally had to ctl-c manually after ~6 minutes.  Which is way more
> than 100s, of course. So THAT would result in "Server configuration
> problem". Another thing that could give "Server configuration problem",
> would be if any garbage output (ie. an internal error from sqlgrey) was
> to be printed out to the socket. But even that would be visible by
> testing like this.

So how do we explain it continuing beyond 100s when you've explicitly
defined the timeout period to be 100s?

Thanks,
Alex

Re: [Sqlgrey-users] sqlgrey fails when configured with multiple machines

[<da...@ha...>]

On 2014-07-17T05:51:53 CEST, Alex wrote:
> I did some tests this evening by basically disconnecting the server
> with the master mysql database, and it caused all mail on the two
> remaining systems that were still running to bounce with the "4.3.5
> Server configuration problem".
If you made the configuration change on all your hosts, i dont know what
you are experiencing and your mail contains no new information,
technical or otherwise, to go on.  And that, paired with the fact that
im fairly certain how this works and can see in my tests that it is
indeed working as expected, simply makes me unable to come up with
guesses as to whats troubling your system.

What i CAN do, is show you how to test better, to pinpoint where the
issue may lie.

The way i tested this manually, was by simply "telnetting" to the
postgrey service and talking to it. That may be a bit cumbersome, so
fortunately Michael Ludvig has included a testscript in the tar-ball,
simply called "tester.pl".

On my system, a normal run looks like this:
----
$ ./tester.pl --client-ip 10.0.0.1
action=451 Greylisted for 5 minutes (16)
----
By adding "time" to the beginning of the command, we can see how much
time it took to complete.


So heres a run where mysql-server has downed its interface just for 10
seconds:
----
$ time ./tester.pl --client-ip 10.0.0.1
action=dunno

real	0m3.062s
user	0m0.056s
sys	0m0.004s
----
"action=dunno" means sqlgrey passes no judgment. Which in turn means
"let it through". This "conclusion" is reached within 3 seconds (you can
see that  at the line "real	0m3.062s").


And this is an example of sqlgrey not running
----
$ time ./tester.pl --client-ip 10.0.0.1
Connect failed: IO::Socket::INET: connect: Connection refused
----


Finding out how long postfix will wait is as simple as:
----
$ postconf smtpd_policy_service_timeout
smtpd_policy_service_timeout = 100s
----
In this case 100s.


When i point a my sqlgrey to a server behind a packet-dropping-firewall
and rerun the test
----
$ time ./tester.pl --client-ip 10.0.0.1
----
i literally had to ctl-c manually after ~6 minutes.  Which is way more
than 100s, of course. So THAT would result in "Server configuration
problem". Another thing that could give "Server configuration problem",
would be if any garbage output (ie. an internal error from sqlgrey) was
to be printed out to the socket. But even that would be visible by
testing like this.

As the predominant theory (and the only theory with a positive test so
far) is the timeout theory, I think you'd have to to try running this
command while you're experiencing the problem. This should help to
either prove or disprove that its a timeout problem and may even catch
any garbage output if that was the case..

- Dan

Re: [Sqlgrey-users] sqlgrey fails when configured with multiple machines

[Alex <mys...@gm...>]

Hi,

Dan, I'm hoping you can still help me, because I'm still doing something
wrong.

> I searched a long time for a way to configure the default
> policy-daemon-response in postfix from "defer_if_permit" to "dunno", but
> found nothing. I even stared at the source to postfix for a while, to see
> if it was in there, as an undocumented option. I couldnt find anything to
> suggest it.
>
> So i ended up creating an ultra simple "policy-daemon-proxy", whos only
> job was to talk to the real policy server, have faster timeout and always
> report "dunno" if something goes wrong. A really silly hack and it just
> underlines why this option should exist in postfix.
>
> Then, i went with "sqlgrey" and all my problems disappeared ;)

I did some tests this evening by basically disconnecting the server with
the master mysql database, and it caused all mail on the two remaining
systems that were still running to bounce with the "4.3.5 Server
configuration problem".

You mention here that sqlgrey has solved your problems and I apparently
don't understand how you have it configured to no longer reply with a
temporary error and somehow bypass the greylisting?

The messages aren't queued, they're just rejected, albeit temporarily, but
we can't create this single point of failure...

Thanks again for your help.
Alex

Re: [Sqlgrey-users] sqlgrey fails when configured with multiple machines

[Alex <mys...@gm...>]

Hi,

On Sun, Jul 6, 2014 at 7:07 AM, Dan Faerch <da...@ha...> wrote:
>
> > It's only based on the fact that there is no stalling or any delays here
> > - it happens immediately when sqlgrey isn't running at all.
>
> You are now talking about how Postfix reacts to a missing policy-daemon
> (sqlgrey is a postfix policy-daemon).
> As this is not something I or sqlgrey can influence, this is not what im
> talking about at all.
>
> I am ONLY talking about the issue you specified in your original mail,
> which was (slightly summarized):
>  - You had "..configured using the DBCLUSTER.."
>  - and when "..one machine goes down, all three fail.."
>  - with error "..4.3.5 Server configuration problem.."
>
> And as such, i believe the issue was a mysql connection attempt that took
> too long. This is now solved by setting timeout to 1 second.

Okay, I didn't understand that the inability to reach mysql because the
server was down would be considered a timeout.

I was also thinking about the more general case and wasn't explaining
myself very well. While I'm concerned about sqlgrey itself dying (and now
understand it's a misplaced concern), I wanted to be sure a complete loss
of the server itself was also solved with the mysql timeout parameter
you've mentioned.

I now only wish there was a way for postfix to fail silently and pass the
mail on, instead of returning any error, temporary or not, to the user.

I have a lot to read an re-read (especially about mysql replication). Very
much appreciate your help.

Thanks again for everything.
Alex

Re: [Sqlgrey-users] sqlgrey fails when configured with multiple machines

[Dan F. <da...@ha...>]

Karl O. Pinc wrote:
> On 07/06/2014 06:07:58 AM, Dan Faerch wrote:
>
> If someone was really worried about sqlgrey dying then there's
> probably a way to run it from inetd.  But that just pushes the problem of a
> dead daemon back to inetd, so the right thing to do is work from inittab.

Indeed.
I had issues with postgrey +10 years ago, before i switched to sqlgrey.
And i had based an internal policy-daemon upon that codebase as well.
Which then experienced the same problem and i simply couldnt track down
the bug.
Sometimes they would just stop responding, but they were still running.

I searched a long time for a way to configure the default
policy-daemon-response in postfix from "defer_if_permit" to "dunno", but
found nothing. I even stared at the source to postfix for a while, to see
if it was in there, as an undocumented option. I couldnt find anything to
suggest it.

So i ended up creating an ultra simple "policy-daemon-proxy", whos only
job was to talk to the real policy server, have faster timeout and always
report "dunno" if something goes wrong. A really silly hack and it just
underlines why this option should exist in postfix.

Then, i went with "sqlgrey" and all my problems disappeared ;)