sqlgrey-users — Users of SQLgrey

Re: [Sqlgrey-users] Can't locate object method "connect"

[Bob A. <apt...@cy...>]

Hi,

James Medley wrote:
> I did the install for sqlgrey following directions in the INSTALL and
> HOWTO. My first errors, when trying to start, was it could not find
> DBI.pm where it was looking. I found it and manually copied it to the
> correct location, which may have been a mistake.

Rather than copying the *.pm files, add

  use lib /path/to/alternate/libs

to sqlgrey. Documentation for the 'use lib' pragma is not super obvious
- see `perldoc perlmodlib` for more info.

hth,

-- Bob

[Sqlgrey-users] Using multiple Connect tables

[Dave S. <dst...@ma...>]

We have a lot (read: millions) of records in our Connect table inside of =
24 hours. Purging it down to a reasonable size for speed is very difficult =
as we are using Postgres. We are considering having multiple Connect =
tables in different databases - perhaps databases that begin with A =
through Z, thus giving us Connect tables that are 26 times as small, and =
thus very fast and manageable.
=20
Has anyone attempted this code change?
=20
Comments?
=20
Thanks,
=20
Dave

This message has been certified virus-free by MailWise Filter - The real-ti=
me, intelligent, e-mail firewall used to scan inbound and outbound messages =
for SPAM, Viruses and Content. =0A=0A  For more information, please visit: http:=
//www.mailwise.com=0A

Re: [Sqlgrey-users] Can't locate object method "connect"

[Daniel J M. <dan...@au...>]

On Thu, 2007-03-29 at 11:24 -0500, James Medley wrote:
> Hello,
> 
> 
> I did the install for sqlgrey following directions in the INSTALL and
> HOWTO. My first errors, when trying to start, was it could not find
> DBI.pm where it was looking. I found it and manually copied it to the
> correct location, which may have been a mistake. Now my error is
> "Can't locate object method "connect" via package "DBI"
> at /usr/sbin/sqlgrey line 805." Any advice would be appreciated.

You need a DBD:: package to go with DBI specific to your database.

> 
-- 
Daniel J McDonald, CCIE # 2495, CISSP # 78281, CNX
Austin Energy
http://www.austinenergy.com

[Sqlgrey-users] Can't locate object method "connect"

[James M. <jm...@ae...>]

Hello,

I did the install for sqlgrey following directions in the INSTALL and  
HOWTO. My first errors, when trying to start, was it could not find  
DBI.pm where it was looking. I found it and manually copied it to the  
correct location, which may have been a mistake. Now my error is  
"Can't locate object method "connect" via package "DBI" at /usr/sbin/ 
sqlgrey line 805." Any advice would be appreciated.

SQLgrey 1.7.5 with Postfix 2.1.5 on a Mac G5 desktop OS 10.4.9

Thanks,
Jim

Re: [Sqlgrey-users] installing sqlgrey on Mac OS 10.4

[Kasey S. <ksp...@as...>]

Jim,

Installing perl and MySQL shouldn't be a problem (something like  
'sudo port install perl mysql').  If the extra modules that SQLgrey  
needs aren't in Darwin Ports (which I kinda doubt they are), you'll  
probably have to google a bit to find how and where to install them.

Once the modules (don't happen to remember what they are off the top  
of my head) are installed, the SQLgrey install procedure should work  
fairly normally.

The one other thing I can think of is making a new system account to  
run sqlgrey.  I'm sure there's a way to do this so that it doesn't  
show up as a standard user account in OS X.  Probably another google  
question.

Kasey

On Mar 28, 2007, at 8:06 AM, James Medley wrote:

> Hello,
>
> I thought (for a change) before I even attempt to install sqlgrey,  
> I would see if there might be a good, simple set of installation  
> instructions some where out there. I am running Postfix 2.1.5 on a  
> Mac G5 desktop OS 10.4.9. I recently (and successfully) loaded  
> postgresql using darwinports. The more simple the instructions, the  
> more successful I seem to be.
>
> Thanks Much,
> Jim
>
>
>
>
>
>
> -- 
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is
> believed to be clean.
> ---------------------------------------------------------------------- 
> ---
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to  
> share your
> opinions on IT & business topics through brief surveys-and earn cash
> http://www.techsay.com/default.php? 
> page=join.php&p=sourceforge&CID=DEVDEV________________________________ 
> _______________
> Sqlgrey-users mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users

[Sqlgrey-users] installing sqlgrey on Mac OS 10.4

[James M. <jm...@ae...>]

Hello,

I thought (for a change) before I even attempt to install sqlgrey, I  
would see if there might be a good, simple set of installation  
instructions some where out there. I am running Postfix 2.1.5 on a  
Mac G5 desktop OS 10.4.9. I recently (and successfully) loaded  
postgresql using darwinports. The more simple the instructions, the  
more successful I seem to be.

Thanks Much,
Jim

Re: [Sqlgrey-users] the daemon is running but spam is coming...

[info <in...@da...>]

On Mon, 19 Mar 2007 08:00:08 -0500, Daniel J McDonald <dan...@au...> wrote:
> On Mon, 2007-03-19 at 13:15 +0100, info wrote:
>> Hi all,
>> i'm just setup sqlgrey 1.6.7 with postfix-2.2.9 in a trustix box
> [...]
>> smtpd_recipient_restriction =
> should be smtpd_recipient_restrictions
> 
> --
> Daniel J McDonald <dan...@au...>

Great, It works! Thanks Daniel for the feedback and sorry for my foolish question...

Re: [Sqlgrey-users] the daemon is running but spam is coming...

[Daniel J M. <dan...@au...>]

On Mon, 2007-03-19 at 13:15 +0100, info wrote:
> Hi all, 
> i'm just setup sqlgrey 1.6.7 with postfix-2.2.9 in a trustix box 
[...]
> smtpd_recipient_restriction =
should be smtpd_recipient_restrictions

-- 
Daniel J McDonald <dan...@au...>

Re: [Sqlgrey-users] SPF record for google (was google alerts)

[Daniel J M. <dan...@au...>]

On Mon, 2007-03-19 at 13:16 +0100, Michael Storz wrote:
> On Sun, 18 Mar 2007, Dan Faerch wrote:
> 
> > I know Lionel hates SPF, but would it be an idea to add SPF
> > functionality  to the whitelist process?
> >
> > Ie. instead of *.google.com do "SPF:google.com" and have sqlgrey do all
> > the lookups?
> > Since it is illegal in Denmark (where i live) to send mail to anyone who
> > didnt request it (read spam),  it would be cool for me to be able to add
> > "SPF:*.dk".
> >
> > Any ideas or comments anyone?
> >
> 
> Hi Dan,
> 
> I'm using SPF as part of Sqlgrey since long. I use it to transfer entries
> from from_awl to domain_awl. Every 5 minutes a cronjob inspects all newly
> created entries in from_awl if one of the following conditions hold:

The discussion was about google, whose alerts feature does not work with
greylisting, thus there would be no from_awl entries.


-- 
Daniel J McDonald <dan...@au...>

Re: [Sqlgrey-users] SPF record for google (was google alerts)

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

RGFuIEZhZXJjaCB3cm90ZToKCj4gSSBrbm93IExpb25lbCBoYXRlcyBTUEYsIGJ1dCB3b3VsZCBp
dCBiZSBhbiBpZGVhIHRvIGFkZCBTUEYKPiBmdW5jdGlvbmFsaXR5ICB0byB0aGUgd2hpdGVsaXN0
IHByb2Nlc3M/CgpUaGUgd2hpdGVsaXN0ZXIgZMOmbW9uIGRvZXMganVzdCB0aGF0OgogIGh0dHA6
Ly9wYWNrYWdlcy5kZWJpYW4ub3JnL3doaXRlbGlzdGVyCiAgaHR0cDovL3BhY2thZ2VzLnVidW50
dS5jb20vd2hpdGVsaXN0ZXIKCkNoZWVycywKCi0tIApLbGF1cyBBbGV4YW5kZXIgU2Vpc3RydXAK
aHR0cDovL2tsYXVzLnNlaXN0cnVwLmRrLwo=

Re: [Sqlgrey-users] SPF record for google (was google alerts)

[Michael S. <Mic...@lr...>]

On Sun, 18 Mar 2007, Dan Faerch wrote:

> I know Lionel hates SPF, but would it be an idea to add SPF
> functionality  to the whitelist process?
>
> Ie. instead of *.google.com do "SPF:google.com" and have sqlgrey do all
> the lookups?
> Since it is illegal in Denmark (where i live) to send mail to anyone who
> didnt request it (read spam),  it would be cool for me to be able to add
> "SPF:*.dk".
>
> Any ideas or comments anyone?
>

Hi Dan,

I'm using SPF as part of Sqlgrey since long. I use it to transfer entries
from from_awl to domain_awl. Every 5 minutes a cronjob inspects all newly
created entries in from_awl if one of the following conditions hold:

* the SPF-record declares this IP as authoritative for the sending domain
* the sending MTA is responsible for the sender domain, that means a
  MX records exist for the sender domain and points to this MTA (no check
  for reachability is done)
* the sender domain is a host domain and the sending MTA sends emails
  for this domain (only).


>From 19.250 entries in domain_awl, 48% came in via group domain, 0,75% via
A-Record check, 32% via MX-record check and 19% via SPF-Record check.

This fits perfectly into the design of Sqlgrey, because a SPF record is
always associated with a domain, therefore the result belongs into
domain_awl, not the whitelist.


Michael Storz
--
======================================================
Leibniz-Rechenzentrum      |    <mailto:St...@lr...>
Boltzmannstr. 1            |    Fax: +49 89 35831-9700
85748 Garching / Germany   |    Tel: +49 89 35831-8840
======================================================

[Sqlgrey-users] the daemon is running but spam is coming...

[info <in...@da...>]

Hi all, 
i'm just setup sqlgrey 1.6.7 with postfix-2.2.9 in a trustix box (two network cards). The sqlgrey daemon starts, postfix works fine, but i can't see anything about sqlgrey in the maillogs, and the spam still come into the server.

the listening ports:
root@trustix /etc/postfix# netstat -tanp | grep 25
tcp        0      0 127.0.0.1:2501          0.0.0.0:*               LISTEN      15280/perl
tcp        0      0 0.0.0.0:25              0.0.0.0:*               LISTEN      20265/smtpd

the daemon:
root@trustix /etc/postfix# ps ax | grep sqlgrey
15280 ?        Ss     0:00 /usr/bin/perl -w /usr/sbin/sqlgrey -d

root@trustix /var/log/mail# tail notice
Mar 19 12:39:14 trustix sqlgrey: 2007/03/19-12:39:14 sqlgrey (type Net::Server::Multiplex) starting! pid(15280)
Mar 19 12:39:14 trustix sqlgrey: Binding to TCP port 2501 on host localhost
Mar 19 12:39:14 trustix sqlgrey: Setting gid to "24 24"
Mar 19 12:39:14 trustix sqlgrey: Setting uid to "24"
Mar 19 12:39:14 trustix sqlgrey: perf: spent 0s cleaning: from_awl (0) domain_awl (0) connect (0)

extract from main.cf:
inet_interfaces = all
    ...
smtpd_recipient_restriction =
    permit_mynetworks
    reject_unknown_sender_domain
    reject_unauth_pipelining
    reject_unauth_destination
    check_policy_service inet:127.0.0.1:2501

I'm using SQLite, but i've noticed the same problem with mysql. This is my sqlgrey.conf:

root@trustix /etc/sqlgrey# cat sqlgrey.conf
 verbose = 0
 user = sqlgrey
 inet = 2501    # bind to localhost:2501
 pidfile = /var/run/sqlgrey.pid
 reconnect_delay = 1
 max_connect_age = 24
 awl_age = 60
 group_domain_level = 2
 db_type = SQLite
 db_name = sqlgrey

So it seems sqlgrey is not talking with postfix; if sqlgrey stops, postfix still accepts messages.
What can be the reason of this behaviour? Sqlgrey is running but inactive and spammers are filling up my server...

Thanks for help

info

Re: [Sqlgrey-users] SPF record for google (was google alerts)

[Daniel J M. <dan...@au...>]

On Sun, 2007-03-18 at 23:08 +0100, Lionel Bouton wrote:
> Dan Faerch wrote the following on 18.03.2007 21:47 :
> > Michael Storz wrote:
> >   
> >> Yesterday, I forgot to check for a SPF record. Here it is:
> >>
> >> dig +short txt _spf.google.com.
> >> "v=spf1 ip4:216.239.56.0/23 ip4:64.233.160.0/19 ip4:66.249.80.0/20
> >> ip4:72.14.192.0/18 ip4:209.85.128.0/17 ip4:66.102.0.0/20 ?all"
> >>
> >> Instead of using *.google.com we could use all the relevant ip addresses,
> >> or use both.
> >>   
> >>     
> > I know Lionel hates SPF,
> 
> I don't hate it. I even used it on both sides: publishing DNS TXT
> records and using them with SpamAssassin. I learned that on both sides
> it doesn't help much:

> >  but would it be an idea to add SPF 
> > functionality  to the whitelist process?
> >
> > Ie. instead of *.google.com do "SPF:google.com" and have sqlgrey do all 
> > the lookups?
> > Since it is illegal in Denmark (where i live) to send mail to anyone who 
> > didnt request it (read spam),  it would be cool for me to be able to add 
> > "SPF:*.dk".
> >   
> 
> Assuming we adapt SQLgrey to allow asynchronous DNS lookups, how do you
> propose such entries would affect the behavior of SQLgrey?
I think just a whitelisting feature is proposed.  A whitelist entry
could be tagged "any address that matches an SPF record for domain..."
That may be particularly helpful for systems like Google and AOL

> How should SQLgrey cope with both false SPF positives and negatives?

I'm not certain what you mean.  The mechanism would be:
mail initiated from fo...@ex...
sqlgrey looks up example.com in whitelist, sees SPF:example.com
SPF is checked via DNS.
if sending host is listed by SPF, then message is passed, and
spamassassin/amavis/whatever gets to chew on it.
if sending host is not listed by SPF, then message is 450'ed for 5
minutes.

Since SPF records don't change very often, and the number of SPF record
that would ever be queried will be limited (they are explicitly listed
in the config file) they could be queried and cached at any time, not
just when a message is initiated from example.com.  Perhaps once a day
or TTL, whichever is greater.  Or maybe just at startup or when
update_sqlgrey_config is run.  That would eliminate the async nature
altogether.

>  Why
> should we use SPF for a part of the domain space and not all of it?

Why do we whitelist a part of the domain space but not all of it?
Because some parts don't work well with greylisting.  This would merely
be a shortcut method to describe whitelisted hosts, not mixing the two
concepts for any other purpose.

-- 
Daniel J McDonald, CCIE # 2495, CISSP # 78281, CNX
Austin Energy
http://www.austinenergy.com

Re: [Sqlgrey-users] SPF record for google (was google alerts)

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

Dan Faerch wrote the following on 18.03.2007 21:47 :
> Michael Storz wrote:
>   
>> Yesterday, I forgot to check for a SPF record. Here it is:
>>
>> dig +short txt _spf.google.com.
>> "v=spf1 ip4:216.239.56.0/23 ip4:64.233.160.0/19 ip4:66.249.80.0/20
>> ip4:72.14.192.0/18 ip4:209.85.128.0/17 ip4:66.102.0.0/20 ?all"
>>
>> Instead of using *.google.com we could use all the relevant ip addresses,
>> or use both.
>>   
>>     
> I know Lionel hates SPF,

I don't hate it. I even used it on both sides: publishing DNS TXT
records and using them with SpamAssassin. I learned that on both sides
it doesn't help much:
- after publishing SPF records we had to setup an authenticated SMTP
service which was not always usable by the staff working outside our
building (when they were behind firewalls for example, which was most of
the time...), then we setup a webmail which brought some problems of its
own (lack of history, suboptimal interface, ...).
- SpamAssassin had very low scores for SPF matches which clearly
demonstrated that success or failure doesn't really mean much for the
nature of the mails...

>  but would it be an idea to add SPF 
> functionality  to the whitelist process?
>
> Ie. instead of *.google.com do "SPF:google.com" and have sqlgrey do all 
> the lookups?
> Since it is illegal in Denmark (where i live) to send mail to anyone who 
> didnt request it (read spam),  it would be cool for me to be able to add 
> "SPF:*.dk".
>   

Assuming we adapt SQLgrey to allow asynchronous DNS lookups, how do you
propose such entries would affect the behavior of SQLgrey?
How should SQLgrey cope with both false SPF positives and negatives? Why
should we use SPF for a part of the domain space and not all of it?

Lionel

Re: [Sqlgrey-users] SPF record for google (was google alerts)

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

Michael Storz wrote:
> Yesterday, I forgot to check for a SPF record. Here it is:
>
> dig +short txt _spf.google.com.
> "v=spf1 ip4:216.239.56.0/23 ip4:64.233.160.0/19 ip4:66.249.80.0/20
> ip4:72.14.192.0/18 ip4:209.85.128.0/17 ip4:66.102.0.0/20 ?all"
>
> Instead of using *.google.com we could use all the relevant ip addresses,
> or use both.
>   
I know Lionel hates SPF, but would it be an idea to add SPF 
functionality  to the whitelist process?

Ie. instead of *.google.com do "SPF:google.com" and have sqlgrey do all 
the lookups?
Since it is illegal in Denmark (where i live) to send mail to anyone who 
didnt request it (read spam),  it would be cool for me to be able to add 
"SPF:*.dk".

Any ideas or comments anyone?

[Sqlgrey-users] SPF record for google (was google alerts)

[Michael S. <Mic...@lr...>]

On Sat, 17 Mar 2007, Lionel Bouton wrote:

> Michael Storz wrote the following on 17.03.2007 23:12 :
> > On Fri, 16 Mar 2007, Lionel Bouton wrote:
> >
> >
> >> McDonald, Dan wrote the following on 16.03.2007 18:31 :
> >>
> >>> I noticed that google does not play well with sqlgrey.  Got a few
> >>> complaints about google alert messages not being delivered
> >>>
> >>>
> >> Is it a recurring problem? Did you wait 24h to see if Google retried
> >> later? Are there entries in from_awl and domain_awl?
> >>
> >
> > I checked our system and we are having the same problem :-(
>
> Ok. I propose we trust *.google.com not being SPAM sources... Does it
> bother anyone if I add it to the official whitelist?

Yesterday, I forgot to check for a SPF record. Here it is:

dig +short txt _spf.google.com.
"v=spf1 ip4:216.239.56.0/23 ip4:64.233.160.0/19 ip4:66.249.80.0/20
ip4:72.14.192.0/18 ip4:209.85.128.0/17 ip4:66.102.0.0/20 ?all"

Instead of using *.google.com we could use all the relevant ip addresses,
or use both.

I have put the ip networks, I sent yesterday, in our
clients_ip_whitelist.local and we received a lot of news alerts.

Michael Storz
--
======================================================
Leibniz-Rechenzentrum      |    <mailto:St...@lr...>
Boltzmannstr. 1            |    Fax: +49 89 35831-9700
85748 Garching / Germany   |    Tel: +49 89 35831-8840
======================================================

Re: [Sqlgrey-users] google alerts

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

Michael Storz wrote the following on 17.03.2007 23:12 :
> On Fri, 16 Mar 2007, Lionel Bouton wrote:
>
>   
>> McDonald, Dan wrote the following on 16.03.2007 18:31 :
>>     
>>> I noticed that google does not play well with sqlgrey.  Got a few
>>> complaints about google alert messages not being delivered
>>>
>>>       
>> Is it a recurring problem? Did you wait 24h to see if Google retried
>> later? Are there entries in from_awl and domain_awl?
>>     
>
> I checked our system and we are having the same problem :-(

Ok. I propose we trust *.google.com not being SPAM sources... Does it
bother anyone if I add it to the official whitelist?

Lionel

Re: [Sqlgrey-users] google alerts

[Michael S. <Mic...@lr...>]

On Fri, 16 Mar 2007, Lionel Bouton wrote:

> McDonald, Dan wrote the following on 16.03.2007 18:31 :
> >
> > I noticed that google does not play well with sqlgrey.  Got a few
> > complaints about google alert messages not being delivered
> >
>
> Is it a recurring problem? Did you wait 24h to see if Google retried
> later? Are there entries in from_awl and domain_awl?

I checked our system and we are having the same problem :-( . It seems
that Google is generating these news alerts on the fly. The received lines
show the date of the transfer, whereas the date header shows an older
date.

Going through my logs and the database, the relevant ip addresses seem to
be:

nz-out-1112.google.com 64.233.162.176-183
nz-out-0506.google.com 64.233.162.224-239
nz-out-0708.google.com 64.233.162.240-251

py-out-1314.google.com 64.233.166.168-175
py-out-1112.google.com 64.233.166.176-183

nf-out-1516.google.com 64.233.182.160-167
nf-out-0910.google.com 64.233.182.184-191

wr-out-0506.google.com 64.233.184.224-239
wr-out-0708.google.com 64.233.184.240-251

wx-out-0506.google.com 66.249.82.224-239

ik-out-1112.google.com 66.249.90.176-183

ug-out-1516.google.com 66.249.92.160-167
ug-out-1314.google.com 66.249.92.168-175

qb-out-0506.google.com 72.14.204.224-239

hu-out-0506.google.com 72.14.214.224-239

ag-out-0708.google.com 72.14.246.240-251

an-out-0910.google.com 209.85.132.184-191
an-out-0708.google.com 209.85.132.240-251

mu-out-0910.google.com 209.85.134.184-191

These are the same ip addresses used to send emails with google.com,
gmail.com, googlegroups.com and googlemail.com. For these domains I find
entries in from_awl and domain_awl, but no single entry for
alerts.bounces.google.com.

Michael Storz
--
======================================================
Leibniz-Rechenzentrum      |    <mailto:St...@lr...>
Boltzmannstr. 1            |    Fax: +49 89 35831-9700
85748 Garching / Germany   |    Tel: +49 89 35831-8840
======================================================

Re: [Sqlgrey-users] google alerts

[Kenneth M. <kt...@ri...>]

Daniel,

We ran the sqlgrey in collection mode only for a few weeks. This
prepopulated all the domain_awl entries for Google mail. Then when
we enabled it, we did not have these problems.

Ken

On Fri, Mar 16, 2007 at 02:40:54PM -0500, Daniel J McDonald wrote:
> On Fri, 2007-03-16 at 19:19 +0100, Lionel Bouton wrote:
> > McDonald, Dan wrote the following on 16.03.2007 18:31 :
> > >
> > > I noticed that google does not play well with sqlgrey.  Got a few
> > > complaints about google alert messages not being delivered
> > >
> > 
> > Is it a recurring problem?
> 
> Yes.
> >  Did you wait 24h to see if Google retried
> > later? 
> 
> Google uses a different name for each attempt.
> 
> > Are there entries in from_awl and domain_awl?
> not for alerts.bounces.google.com, just for
> goo...@go...
> 
> > > and found
> > > this in my connect table:
> > > mysql> select count(*), src from connect where sender_domain rlike
> > > "alerts.bounces.google.com" group by src;
> > > +----------+----------------+
> > > | count(*) | src            |
> > > +----------+----------------+
> > > |      385 | 209.85.132     |
> > > |        1 | 209.85.132.185 |
> > > |        1 | 209.85.132.190 |
> > > |        1 | 209.85.132.191 |
> > > |      110 | 64.233.162     |
> > > |        1 | 64.233.162.183 |
> > > |      361 | 64.233.184     |
> > > |        4 | 66.249.92      |
> > > +----------+----------------+
> > > 8 rows in set (0.35 sec)
> > >
> > > So, I add to my /etc/sqlgrey/clients_fqdn_whitelist.local file:
> > > [mcdonalddj@sa sqlgrey]$ sudo grep google clients_fqdn_whitelist.local
> > > alerts.bounces.google.com
> > >
> > 
> > This will whitelist mails from the system called
> > "alerts.bounces.google.com" not mails with a return address in this domain.
> Ah, since it is a pool of servers in different netblocks with different
> names, that is a problem.
> 
> > If you want to use clients_fqdn_whitelist.local, you'll need to put
> > an-out-0910.google.com
> > in it.
> > 
> > Or like explained at the top of clients_fqdn_whitelist :
> > *.google.com
> 
> A pity, but I guess I need to do that... Done.  And mail is now flowing.
> 
> -- 
> Daniel J McDonald, CCIE # 2495, CISSP # 78281, CNX
> Austin Energy
> http://www.austinenergy.com
> 
> -------------------------------------------------------------------------
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to share your
> opinions on IT & business topics through brief surveys-and earn cash
> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
> _______________________________________________
> Sqlgrey-users mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users
> 

Re: [Sqlgrey-users] google alerts

[Daniel J M. <dan...@au...>]

On Fri, 2007-03-16 at 19:19 +0100, Lionel Bouton wrote:
> McDonald, Dan wrote the following on 16.03.2007 18:31 :
> >
> > I noticed that google does not play well with sqlgrey.  Got a few
> > complaints about google alert messages not being delivered
> >
> 
> Is it a recurring problem?

Yes.
>  Did you wait 24h to see if Google retried
> later? 

Google uses a different name for each attempt.

> Are there entries in from_awl and domain_awl?
not for alerts.bounces.google.com, just for
goo...@go...

> > and found
> > this in my connect table:
> > mysql> select count(*), src from connect where sender_domain rlike
> > "alerts.bounces.google.com" group by src;
> > +----------+----------------+
> > | count(*) | src            |
> > +----------+----------------+
> > |      385 | 209.85.132     |
> > |        1 | 209.85.132.185 |
> > |        1 | 209.85.132.190 |
> > |        1 | 209.85.132.191 |
> > |      110 | 64.233.162     |
> > |        1 | 64.233.162.183 |
> > |      361 | 64.233.184     |
> > |        4 | 66.249.92      |
> > +----------+----------------+
> > 8 rows in set (0.35 sec)
> >
> > So, I add to my /etc/sqlgrey/clients_fqdn_whitelist.local file:
> > [mcdonalddj@sa sqlgrey]$ sudo grep google clients_fqdn_whitelist.local
> > alerts.bounces.google.com
> >
> 
> This will whitelist mails from the system called
> "alerts.bounces.google.com" not mails with a return address in this domain.
Ah, since it is a pool of servers in different netblocks with different
names, that is a problem.

> If you want to use clients_fqdn_whitelist.local, you'll need to put
> an-out-0910.google.com
> in it.
> 
> Or like explained at the top of clients_fqdn_whitelist :
> *.google.com

A pity, but I guess I need to do that... Done.  And mail is now flowing.

-- 
Daniel J McDonald, CCIE # 2495, CISSP # 78281, CNX
Austin Energy
http://www.austinenergy.com

Re: [Sqlgrey-users] google alerts

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

McDonald, Dan wrote the following on 16.03.2007 18:31 :
>
> I noticed that google does not play well with sqlgrey.  Got a few
> complaints about google alert messages not being delivered
>

Is it a recurring problem? Did you wait 24h to see if Google retried
later? Are there entries in from_awl and domain_awl?

> and found
> this in my connect table:
> mysql> select count(*), src from connect where sender_domain rlike
> "alerts.bounces.google.com" group by src;
> +----------+----------------+
> | count(*) | src            |
> +----------+----------------+
> |      385 | 209.85.132     |
> |        1 | 209.85.132.185 |
> |        1 | 209.85.132.190 |
> |        1 | 209.85.132.191 |
> |      110 | 64.233.162     |
> |        1 | 64.233.162.183 |
> |      361 | 64.233.184     |
> |        4 | 66.249.92      |
> +----------+----------------+
> 8 rows in set (0.35 sec)
>
> So, I add to my /etc/sqlgrey/clients_fqdn_whitelist.local file:
> [mcdonalddj@sa sqlgrey]$ sudo grep google clients_fqdn_whitelist.local
> alerts.bounces.google.com
>

This will whitelist mails from the system called
"alerts.bounces.google.com" not mails with a return address in this domain.
>
>
> But it's still greylisting:
> mcdonalddj@sa sqlgrey]$ sudo tail -f /var/log/mail/info | grep
> alerts.bounces.google.com
> Mar 16 11:16:50 sa sqlgrey: grey: new: 209.85.132(209.85.132.186),
> 1ra...@al...
> -> **mumble**@austinenergy.com
> Mar 16 12:16:50 sa postfix/smtpd[2267]: NOQUEUE: reject: RCPT from
> an-out-0910.google.com[209.85.132.186]: 450 4.7.1
>

If you want to use clients_fqdn_whitelist.local, you'll need to put
an-out-0910.google.com
in it.

Or like explained at the top of clients_fqdn_whitelist :
*.google.com

Lionel.

[Sqlgrey-users] google alerts

[McDonald, D. <Dan...@au...>]

I noticed that google does not play well with sqlgrey.  Got a few
complaints about google alert messages not being delivered and found
this in my connect table:
mysql> select count(*), src from connect where sender_domain rlike
"alerts.bounces.google.com" group by src;
+----------+----------------+
| count(*) | src            |
+----------+----------------+
|      385 | 209.85.132     |
|        1 | 209.85.132.185 |
|        1 | 209.85.132.190 |
|        1 | 209.85.132.191 |
|      110 | 64.233.162     |
|        1 | 64.233.162.183 |
|      361 | 64.233.184     |
|        4 | 66.249.92      |
+----------+----------------+
8 rows in set (0.35 sec)

So, I add to my /etc/sqlgrey/clients_fqdn_whitelist.local file:
[mcdonalddj@sa sqlgrey]$ sudo grep google clients_fqdn_whitelist.local
alerts.bounces.google.com

But it's still greylisting:
mcdonalddj@sa sqlgrey]$ sudo tail -f /var/log/mail/info | grep
alerts.bounces.google.com
Mar 16 11:16:50 sa sqlgrey: grey: new: 209.85.132(209.85.132.186),
1raaaafqmhbs6njvy16d64xg60gxxf_mbngjses_45febcavn_4gzqch9ldqq5cg@alerts.b=
ounces.google.com -> **mumble**@austinenergy.com
Mar 16 12:16:50 sa postfix/smtpd[2267]: NOQUEUE: reject: RCPT from
an-out-0910.google.com[209.85.132.186]: 450 4.7.1
<**mumble**@austinenergy.com>: Recipient address rejected: Greylisted
for 5 minutes;
from=3D<1RAAAAFQMHBS6NJvY16d64XG60GxXf_MbNgJses_45feBcavn_4gzQch9lDQQ5CGx=
Lo6...@al...=
m> to=3D<**mumble**@austinenergy.com> proto=3DESMTP =
helo=3D<an-out-0910.google.com>

I restarted sqlgrey and also ran=20

[mcdonalddj@sa sqlgrey]$ sudo update_sqlgrey_config

but they are still being greylisted.

what am I doing wrong?

mcdonalddj@sa sqlgrey]$ rpm -q sqlgrey
sqlgrey-1.6.6-0.1.20060mdk
[mcdonalddj@sa sqlgrey]$ grep VERSION /usr/sbin/sqlgrey
my $VERSION =3D "1.6.6";
[mcdonalddj@sa sqlgrey]$ cat /etc/mandriva-release
Mandriva Linux Corporate Server release 2006.0 (Official) for i586
[mcdonalddj@sa sqlgrey]$ uname -a
Linux sa.austinenergy.com 2.6.12-26mdksmp #1 SMP Mon Sep 4 13:21:18 EDT
2006 i686 Intel(R) Pentium(R) 4 CPU 3.20GHz unknown GNU/Linux



--=20
Daniel J McDonald, CCIE # 2495, CISSP # 78281, CNX
Austin Energy
http://www.austinenergy.com

Re: [Sqlgrey-users] sqlgrey-1.7.5 (dev)

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

Dan Faerch wrote the following on 15.03.2007 17:52 :
> Lionel Bouton wrote:
>   
>> I just noticed that you didn't tag the release in CVS for 1.7.4 and
>> 1.7.5. Please do so in the future, it makes it far easier to check the
>> sources of  the version one user is reporting a bug for 
>>     
> Sure.. Im used to SVN, not CVS, so i didnt notice that we were using 
> tags (and dont know how to tag either. Not yet anyway).
>   

man cvs :-)

>> (I just got a
>> log extract with a line where a concatenation error happened with 1.7.5).
>>   
>>     
> Only in 1.7.5?

That was for this bug report. Anyway the problem was an UTF-8 MySQL
database again.

Lionel

Re: [Sqlgrey-users] sqlgrey-1.7.5 (dev)

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

Lionel Bouton wrote:
>
> I just noticed that you didn't tag the release in CVS for 1.7.4 and
> 1.7.5. Please do so in the future, it makes it far easier to check the
> sources of  the version one user is reporting a bug for 
Sure.. Im used to SVN, not CVS, so i didnt notice that we were using 
tags (and dont know how to tag either. Not yet anyway).
> (I just got a
> log extract with a line where a concatenation error happened with 1.7.5).
>   
Only in 1.7.5? Because i think ppl have mentioned concat warnings as far 
back as 1.7.3 if i remember correctly. Also i noticed them myself on our 
system, but since they're very likely, very harmless, i didnt bother.
On my servers, i think it happens when recieving mail to a local address 
(without @) . I imagine its a split into 2 vars, where the $domain'ish 
var is undef.

- Dan

Re: [Sqlgrey-users] sqlgrey-1.7.5 (dev)

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

Dan Faerch wrote the following on 15.02.2007 22:23 :
> sqlgrey 1.7.5 has just been released.
>
>  - Changed db_cleanup. clean time stored in db for better handling,
>    especially in  clustered environments
>  - Fix for harmless warnings about "possible typo"
>  - Fix for sqlgrey dying if syslog is offline
>  - Filled feature req from Riaan Kok. Support "postfix attributes on both
>    sides".. Ie:  "client_name !~ helo_name"
>
> - Dan
>   

I just noticed that you didn't tag the release in CVS for 1.7.4 and
1.7.5. Please do so in the future, it makes it far easier to check the
sources of  the version one user is reporting a bug for (I just got a
log extract with a line where a concatenation error happened with 1.7.5).

Lionel