sqlgrey-users — Users of SQLgrey

Re: [Sqlgrey-users] Couple Problems with 1.6.0

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

On 6/28/05, Lionel Bouton <lio...@bo...> wrote:
> One possible culprit could be the way SQLgrey handles the database
> cleanups since 1.6.0 (the zombie process points to a forked child problem=
).

FWIW, Since FC4 released perl-Net-Server 0.88 I haven't had any
problems with sqlgrey locking up...

-Dave

Re: [Sqlgrey-users] warn_if_reject to populate the db

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

On Tue, 28 Jun 2005, Michel Bouissou wrote:

> Le Mardi 28 Juin 2005 16:39, Michael Storz a =E9crit :
> > We have SPF running together with sqlgrey (cron_job). The basic idee is=
,
> [...]
> > Lionel, if you program the forked process which will handle all the
> > propagations, moves, inserts then I will augment this process with our
> > algorithms
> >
> > - SPF-check
> > - MX-check
> > - A-check
>
> I'm afraid SQLgrey could become bloated with a lot of features that would=
n't
> be useful for the vast majority of its users, and which usefulness would =
need
> to be carefully studied before implementing, as that would make its
> configuration and understanding far much complicated...
>
> We have to be careful to avoid the M$ Word syndrome ;-)
>
>

These algorithms run for about 4 months at our site and they have proven
to be very successful. But it will always be a choice of Lionel to
incorporate our patches into sqlgrey and therefore have the combined power
of pushing sqlgrey further. BTW, I have a bunch of patches sitting in the
queue to be send ot Lionel. I still waiting to see if ne of these patches
is working correctly now.

If you mean with the term users the people who benefit from these
additions then we have a lot of users of this category :-)

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

Re: [Sqlgrey-users] warn_if_reject to populate the db

[Michel B. <mi...@bo...>]

Le Mardi 28 Juin 2005 16:45, Lionel Bouton a =E9crit :
>
> >Yes, it can be useful in this way _only_ with a manual whitelist. But =
why
> >bother, as the greylisting system will create its AWL automatically wi=
th
> > much less effort than having to maintain a manual WL ?
>
> The whitelist could be dynamically loaded from a central repository...

I don't like that much this central repository thing. The nice thing with=
=20
greylisting is that it is a purely dynamic system that doesn't need any=20
external output, and doesn't need to rely on other people's appreciation=20
(what you do when you use RBLs...)

SQLgrey currently has a central repository only for the whitelists that a=
re=20
"exceptions that don't work with greylisting", so this is understandable,=
 and=20
for the regexps, that haven't changed at all so far, and that are expecte=
d to=20
change very little (maybe once in the future if the .res. thing proves=20
useful ?)

Even now, people have no obligation to use the auto-update script, so sql=
grey=20
can continue to be used purely locally at the admin choice.

--=20
Michel Bouissou <mi...@bo...> OpenPGP ID 0xDDE8AC6E

Re: [Sqlgrey-users] warn_if_reject to populate the db

[Michel B. <mi...@bo...>]

Le Mardi 28 Juin 2005 16:39, Michael Storz a =E9crit :
> We have SPF running together with sqlgrey (cron_job). The basic idee is=
,
[...]
> Lionel, if you program the forked process which will handle all the
> propagations, moves, inserts then I will augment this process with our
> algorithms
>
> - SPF-check
> - MX-check
> - A-check

I'm afraid SQLgrey could become bloated with a lot of features that would=
n't=20
be useful for the vast majority of its users, and which usefulness would =
need=20
to be carefully studied before implementing, as that would make its=20
configuration and understanding far much complicated...

We have to be careful to avoid the M$ Word syndrome ;-)

--=20
Michel Bouissou <mi...@bo...> OpenPGP ID 0xDDE8AC6E

Re: [Sqlgrey-users] warn_if_reject to populate the db

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

Michel Bouissou wrote the following on 28.06.2005 16:24 :

>  
>
>>We could combine a domain whitelist with SPF checks: if the source 
>>domain is in the whitelist and the SPF checks are OK, don't greylist.
>>    
>>
>
>Yes, it can be useful in this way _only_ with a manual whitelist. But why 
>bother, as the greylisting system will create its AWL automatically with much 
>less effort than having to maintain a manual WL ?
>  
>

The whitelist could be dynamically loaded from a central repository...

But this example is probably not what would be coded, it doesn't bring
enough benefit for the trouble. The idea is there though: some
combinations are better done in Postfix, some other in the policy server.

Lionel.

Re: [Sqlgrey-users] warn_if_reject to populate the db

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

On Tue, 28 Jun 2005, Lionel Bouton wrote:

>
> Combining results of various checks at the Postfix level is rather
> cumbersome. This is why I added a reference to SPF in my TODO: my idea
> was that it wouldn't bring much benefit to greylist already known good
> MTAs. We could combine a domain whitelist with SPF checks: if the source
> domain is in the whitelist and the SPF checks are OK, don't greylist.
> This is far in the future, though, I'll need to break SQLgrey into
> modules first.
>

We have SPF running together with sqlgrey (cron_job). The basic idee is,

if count_from_awl >= X {
   result = check_for_spf;
   if result = 'pass' {
      move_domain_from_mail_to_domain_awl
   }
}

Lionel, if you program the forked process which will handle all the
propagations, moves, inserts then I will augment this process with our
algorithms

- SPF-check
- MX-check
- A-check

And it would be nice to have a field for every table entry about the
creating algorithm (grouping or one of the above), as I explained before.

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

Re: [Sqlgrey-users] warn_if_reject to populate the db

[Michel B. <mi...@bo...>]

Le Mardi 28 Juin 2005 16:05, Lionel Bouton a =E9crit :
>
> Combining results of various checks at the Postfix level is rather
> cumbersome.

I wouldn't say "cumbersome", I would say "flexible". It allows you to=20
configure exactly what you want, how you want it, with the tools of your=20
choice.

SPF and greylisting have nothing to do together. If you first integrate S=
PF=20
into SQLgrey, then why not integrate DNSBLs as well ? Then RHBLs... And=20
then...

Furthermore, my current traffic show that SPF actually stops very _little=
_=20
mail, so its efficiency is still very marginal, compared to greylisting t=
hat=20
stops the vast majority of junk...

> This is why I added a reference to SPF in my TODO: my idea=20
> was that it wouldn't bring much benefit to greylist already known good
> MTAs.

SPF doesn't define any "good" MTA by itself. It only lists "domain-approv=
ed"=20
MTAs. If spammerdomain.com defines spammermachine as authorized for the=20
domain, then it's OK for SPF. Mr. Joe Spammer can also put a "+all" SPF=20
record for his spammerdomain.com, and then any open proxy out there will =
be=20
"SPF approved" for relaying his spam...

> We could combine a domain whitelist with SPF checks: if the source=20
> domain is in the whitelist and the SPF checks are OK, don't greylist.

Yes, it can be useful in this way _only_ with a manual whitelist. But why=
=20
bother, as the greylisting system will create its AWL automatically with =
much=20
less effort than having to maintain a manual WL ?

--=20
Michel Bouissou <mi...@bo...> OpenPGP ID 0xDDE8AC6E

Re: [Sqlgrey-users] warn_if_reject to populate the db

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

Michel Bouissou wrote the following on 26.06.2005 15:37 :

>>1. localhost:2501 vs. Unix socket
>>Wouldn't a socket be slightly faster than TCP?
>>    
>>
>
>Probably, but works only if on the same machine. TCP is more general, so to 
>use sockets, Lionel would need to implement both methods. Given the little 
>amount of data transferred between Postfix and sqlgrey for a given 
>connection, using sockets would probably make a difference only on very high 
>volume system.
>
>  
>

Even there the difference would probably be unnoticable. There are far
more complex computations involved than a TCP/IP connection. Putting
back Unix sockets would be easy, but it will be one more parameter to
set up...

>>2. Running under control of master(8)
>>That would be convenient, start and stop with Postfix; are there other
>>benefits? Why the standalone choice?
>>    
>>
>
>Running under the control of master would be interesting to me as well.
>
>  
>

Being standalone allows SQLgrey to be put anywhere the admin see fit.
Although running under the control of master would be a definitive plus.
I'll add it to my TODO.

>>3. Database population commands
>>I'm totally lost with SQL (hence the poor choice of mysql), can someone
>>help with the manual commands I'd use to add to the database?
>>    
>>
>
>Reading the fine MySQL doc would probably help with the basic SQL commands, 
>but you'd better let the DB populate by itself.
>
>BTW, why state that MySQL is a poor choice ?
>  
>

It's often buggy and doesn't follow the standard ?

>  
>
>>4. Database population scripts
>>Is there something I could run against user's maildirs which would add
>>entries to the AWL? If not should I commission such a project from my
>>private farm of Perl coders[2]; I mean, would there be interest?
>>    
>>
>
>I don't think there's any interest in trying to artificially populate the DB. 
>Just let it run ;-)
>
>  
>

One could imagine various ways of (dynamically) modifying the DB to fine
tune the greylisting processus. Everyone is welcomed to experiment with
this, but I've no guidelines on this subject, the keyword is 'experiment'.

>>6. dyn_fqdn.regexp
>>That's quite an expression.
>>    
>>
>
>;-)
>  
>

I know Michel is proud of this one :-)

>  
>
>>I didn't figure the whole thing out, but I 
>>did look for a string "\.res\." which is commonly used for dynamic
>>space, e.g., *.res.rr.com. (residential customers.) Perhaps the second
>>dot should be any non-alpha character (-, _, ., digit), and to be safe
>>there should be at least 2 domain segments following and at least one
>>segment preceding (implied by the leading dot.)
>>    
>>
>
>I don't pretend that the regexp is perfect, it's only heuristic, but it would 
>be interesting adding your modification only if you find samples of existing 
>hostnames that don't get properly classified. (i.e. your example may already 
>get classified corresctly if the last byte of the IP address is part of the 
>name) The more things you add in the regexp, the longer it will take to 
>process, and the higher chances it could collide with other non-dynamic names 
>in an undesired manner.
>
>But feel free to experiment with your own copy and suggest improvements that 
>show useful...
>  
>

Yep, we can even redistribute the modifications you make to clients
running sqlgrey_update_config.

>>8. Beyond grey
>>This is a biggie which probably warrants its own thread. This is all
>>about spam abatement. What about integrating other antispam strategies
>>under the roof of the same policy service? Yes, this belongs in its own
>>thread. I'll write more of my thoughts about that later.
>>    
>>
>
>I think about it the Unix way. I prefer to use several distinct tools of my 
>choice, each one doing _one_ thing and doing it well. I wouldn't like any 
>bigger system that would integrate different methods, I personally prefer 
>doing my own cooking.
>
>Postfix can call several "policy services" without any problem...
>
>  
>

Combining results of various checks at the Postfix level is rather
cumbersome. This is why I added a reference to SPF in my TODO: my idea
was that it wouldn't bring much benefit to greylist already known good
MTAs. We could combine a domain whitelist with SPF checks: if the source
domain is in the whitelist and the SPF checks are OK, don't greylist.
This is far in the future, though, I'll need to break SQLgrey into
modules first.

>>Thanks, Lionel, this looks good so far. I went live with a small but
>>heavily-spammed domain yesterday evening, and no spam has been seen
>>there since. (The sqlgrey is last in a long list of restrictions with
>>numerous RBL checks.)
>>    
>>
>
>My 2 cents: Put SQLgrey _before_ RBLs. You'll save external network calls so 
>your system will run faster, and you'll save unnecessary load onto the RBL 
>servers as well...
>  
>

That would be my recommendation too. Only put RBLs first if you want to
have statistics about how much more greylisting brings to you if you
have RBLs already defined.

Lionel

Re: [Sqlgrey-users] Couple Problems with 1.6.0

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

One possible culprit could be the way SQLgrey handles the database
cleanups since 1.6.0 (the zombie process points to a forked child problem).

SQLgrey fork another processus which makes its own database connection
in order to asynchronously delete old entries (expired AWL and connect
entries).

If you want to make sure this isn't the problem, you can simply replace
the "$self->fork_cleanup()" calls by "$self->cleanup()". This will
revert to 1.4.x behaviour. If it solves the problem, then I'll have to
look more closely on how DBI reacts to forks...

Lionel.

Re: [Sqlgrey-users] verify(8) (and similar) with greylisting?

[Michel B. <mi...@bo...>]

Le Lundi 27 Juin 2005 21:43, /dev/rob0 a =E9crit :
>
> (Speaking of which, I took you up on the suggestion to put sqlgrey
> before RBL's. I think you were right about that, thanks.)

And if you want greylisting rejections to be real fast and avoid querying=
=20
DNSBLs for connections that don't come back, use
"reject_first_attempt =3D immed" in sqlgrey.conf

--=20
Michel Bouissou <mi...@bo...> OpenPGP ID 0xDDE8AC6E

Re: [Sqlgrey-users] verify(8) (and similar) with greylisting?

[Michel B. <mi...@bo...>]

Le Lundi 27 Juin 2005 21:43, /dev/rob0 a =E9crit :
>
> On Monday 27 June 2005 14:33, Michel Bouissou wrote:
> > Le Lundi 27 Juin 2005 21:27, /dev/rob0 a =E9crit :
> > > Airmail.net is one of those stupid ISP's which uses a callback like
> > > Postfix's verify(8) on all sender addresses ...
> >
> > I'm another one of these "stupid", event though I'm no ISP ;-)
>
> On a large scale I think it's quite reckless.

Possibly, but I don't have a scale large enough here for it to pose=20
problems ;-)

> Even on a small scale it ought to be limited in some way: perhaps do SP=
F
> checks and only verify SPF failures.

There are too many spammers out there, and too few SPF-enabled domains fo=
r SPF=20
checks to be enough...

> Note the "WARNING" text in the ADDRESS_VERIFICATION_README.
>
> Does it work well for you?

Perfectly, altough I had to build a small table of exceptions. Sometimes =
it=20
collides with greylisting one way or the other, but it usually just delay=
s=20
the first message a little longer (most often one hour or two).

> Does it come after other restrictions?=20

Yes, it comes absolutely last, after all possible other 2821-level checks=
 have=20
been performed, just before considering accepting the message. And of cou=
rse=20
I keep the results cached in a DB.

--=20
Michel Bouissou <mi...@bo...> OpenPGP ID 0xDDE8AC6E

Re: [Sqlgrey-users] verify(8) (and similar) with greylisting?

[/dev/rob0 <ro...@gm...>]

On Monday 27 June 2005 14:33, Michel Bouissou wrote:
> Le Lundi 27 Juin 2005 21:27, /dev/rob0 a =E9crit :
> > Airmail.net is one of those stupid ISP's which uses a callback like
> > Postfix's verify(8) on all sender addresses ...
>
> I'm another one of these "stupid", event though I'm no ISP ;-)

On a large scale I think it's quite reckless. Even on a small scale it=20
ought to be limited in some way: perhaps do SPF checks and only verify=20
SPF failures.

Note the "WARNING" text in the ADDRESS_VERIFICATION_README.

Does it work well for you? Does it come after other restrictions?=20
(Speaking of which, I took you up on the suggestion to put sqlgrey=20
before RBL's. I think you were right about that, thanks.)

> BTW, lists.sourceforge.net does it as well...

Do they really? Wow. Not surprising, though.

One of these days (when I have all my spam-fighting perfected ;) ) I=20
will switch to using my own server address for list postings, and quit=20
abusing poor GMX with the tons of spam they get for me. :)
=2D-=20
    mail to this address is discarded unless "/dev/rob0"
    or "not-spam" is in Subject: header

Re: [Sqlgrey-users] verify(8) (and similar) with greylisting?

[Michel B. <mi...@bo...>]

Le Lundi 27 Juin 2005 21:27, /dev/rob0 a =E9crit :
> Airmail.net is one of those stupid ISP's which uses a callback like
> Postfix's verify(8) on all sender addresses ...

I'm another one of these "stupid", event though I'm no ISP ;-)

BTW, lists.sourceforge.net does it as well...

--=20
Michel Bouissou <mi...@bo...> OpenPGP ID 0xDDE8AC6E

[Sqlgrey-users] verify(8) (and similar) with greylisting?

[/dev/rob0 <ro...@gm...>]

Airmail.net is one of those stupid ISP's which uses a callback like
Postfix's verify(8) on all sender addresses ...

Jun 27 18:59:57 sorry postfix/smtpd[5081]: connect from mx1.airmail.net[209.196.77.98]
Jun 27 18:59:57 sorry sqlgrey: grey: new: 209.196.77(209.196.77.98), -undef-@-undef- -> us...@ex...
Jun 27 18:59:58 sorry postfix/smtpd[5081]: NOQUEUE: reject: RCPT from mx1.airmail.net[209.196.77.98]: 450 <us...@ex...>: Recipient address rejected: Greylisted for 5 minutes; from=<> to=<us...@ex...> proto=SMTP helo=<mx1.airmail.net>
Jun 27 18:59:58 sorry postfix/smtpd[5081]: disconnect from mx1.airmail.net[209.196.77.98]

Does this need to be whitelisted? The smtp client did manage to have
the triggering mail accepted:

Jun 27 18:59:58 sorry postfix/smtp[5085]: 9417B4924: to=<us...@ph...>, relay=mx1.airmail.net[209.196.77.98], delay=7, status=sent (250 OK id=1Dmypq-000Mqe-Fe)

I'm not sure it will try again. Won't that mean it will be flagged as
spam?
-- 
    mail to this address is discarded unless "/dev/rob0"
    or "not-spam" is in Subject: header

Re: [Sqlgrey-users] warn_if_reject to populate the db

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

Hi Michel,

On Sun, 26 Jun 2005, Michel Bouissou wrote:

> More, checking my logs, I don't see any other ISP besides rr.com and Verizon
> that uses a ".res." part in their hostnames, and both rr.com and Verizon also
> put the IP address in... Verizon addresses also begin with "pool-", that the
> regexp already catches.
>
> Verizon samples:
>
> pool-151-200-35-11.res.east.verizon.net[151.200.35.11]
> pool-138-88-28-49.res.east.verizon.net[138.88.28.49]
>
> etc.
>
FYI, a grep through my logs show some other ISPs in addition:

host641191066a.pnpl.res.tor.fcibroadband.com
host6614614859.dsl.res.tor.fcibroadband.com

r63h83.res.gatech.edu

152-130-1-16.res.net.va.gov

216-201-133-129.res.logixcom.net
216-201-134-162.res.logixcom.net
216-201-149-243.res.logixcom.net

63-110-249-163.res.evv.cable.sigecom.net
65-218-56-81.res.evv.cable.sigecom.net
63-87-215-131.res.nb.cable.sigecom.net
65-195-96-103.res.nb.cable.sigecom.net
65-195-96-253.res.nb.cable.sigecom.net

host-se80.res.openband.net
host-se87.res.openband.net
host-si112.res.openband.net
host-si198.res.openband.net
host-sj22.res.openband.net
host-sk219.res.openband.net
host-sk249.res.openband.net
host-sk87.res.openband.net
host-sl48.res.openband.net
host487.res.openband.net
user308.res.openband.net
user353.res.openband.net
user414.res.openband.net
user445.res.openband.net

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

Re: [Sqlgrey-users] Couple Problems with 1.6.0

[Edward S. (s. list) <sq...@cr...>]

Edward Shornock (sqlgrey list) wrote:

>I'm experiencing the the problem now as well, and this just started
>Friday. I thought it might have had something to do with the upgrade to
>1.7, but after reverting back to 1.6 (which I had no problems with), the
>problem persists. I've also turned up the logging level trying to track
>this down. I'm using Debian Sid/unstable and PostgreSQL (7.4.8) as the
>DB system.
>
>Hopefully we can track this problem down fairly easily...
>  
>
More information (I hope it helps), just before the process hangs:

Jun 26 20:01:47 darkside postfix/smtpd[18921]: connect from
russian-caravan.cloud9.net[168.100.1.4]
Jun 26 20:01:48 darkside sqlgrey: 2005/06/26-20:01:48 CONNECT TCP Peer:
"127.0.0.1:56048" Local: "127.0.0.1:2501"
Jun 26 20:01:48 darkside sqlgrey: optin: greylisting active for
pos...@cr...
Jun 26 20:01:48 darkside sqlgrey: grey: unknown pattern:
russian-caravan.cloud9.net, 168.100.1.4: using C-class (168.100.1).
Jun 26 20:01:48 darkside sqlgrey: system: forked cleanup child (18925)
Jun 26 20:01:48 darkside sqlgrey: warning: ERROR:  prepared statement
"dbdpg_1" does not exist
Jun 26 20:01:48 darkside sqlgrey: warning: message type 0x43 arrived
from server while idle
Jun 26 20:01:48 darkside sqlgrey: warning: message type 0x5a arrived
from server while idle
Jun 26 20:01:48 darkside sqlgrey: grey: domain awl match: updating
168.100.1(168.100.1.4), postfix.org

...and at this point, sqlgrey has given up.
Jun 26 20:03:20 darkside sqlgrey: optin: greylisting active for
pos...@cr...
Jun 26 20:03:20 darkside sqlgrey: grey: unknown pattern:
camomile.cloud9.net, 168.100.1.3: using C-class (168.100.1).
Jun 26 20:03:20 darkside sqlgrey: perf: spent 0s cleaning: from_awl (0)
domain_awl (0) connect (0)
Jun 26 20:03:20 darkside sqlgrey: system: cleanup child exit (18925)
Jun 26 20:04:46 darkside postfix/anvil[18364]: statistics: max
connection rate 1/60s for (smtp:146.82.138.6) at Jun 26 19:54:46
Jun 26 20:04:46 darkside postfix/anvil[18364]: statistics: max
connection count 1 for (smtp:146.82.138.6) at Jun 26 19:54:46
Jun 26 20:04:46 darkside postfix/anvil[18364]: statistics: max cache
size 1 at Jun 26 19:54:46
Jun 26 20:05:00 darkside postfix/smtpd[18921]: warning: timeout on
127.0.0.1:2501 while reading input attribute name
Jun 26 20:05:00 darkside postfix/smtpd[18921]: warning: problem talking
to server 127.0.0.1:2501: Connection timed out
Jun 26 20:06:41 darkside postfix/smtpd[18921]: warning: timeout on
127.0.0.1:2501 while reading input attribute name
Jun 26 20:06:41 darkside postfix/smtpd[18921]: warning: problem talking
to server 127.0.0.1:2501: Connection timed out
Jun 26 20:06:41 darkside postfix/smtpd[18921]: NOQUEUE: reject: RCPT
from camomile.cloud9.net[168.100.1.3]: 450 Server configuration problem;
from=<own...@po...>
to=<pos...@cr...> proto=ESMTP
helo=<camomile.cloud9.net>
Jun 26 20:06:41 darkside postfix/cleanup[19161]: 8BD20D6B082:
message-id=<200...@da...>
Jun 26 20:06:41 darkside postfix/qmgr[3099]: 8BD20D6B082:
from=<dou...@da...>, size=1086,
nrcpt=1 (queue active)
Jun 26 20:06:41 darkside postfix/smtpd[18921]: disconnect from
camomile.cloud9.net[168.100.1.3]



# ps aux |grep sqlgrey
sqlgrey   3754  0.0  0.9  13660  8684 ?        Ss   15:43   0:00
/usr/bin/perl -w /usr/sbin/sqlgrey -d
postgres  3755  0.0  1.0  18076  9096 ?        S    15:43   0:00
postgres: sqlgrey sqlgrey 127.0.0.1
idle                                                      
sqlgrey  18925  0.0  0.0      0     0 ?        Z    20:01   0:00
[sqlgrey] <defunct>
root     26635  0.0  0.0   1624   452 pts/2    S+   22:42   0:00 grep
sqlgrey


strace on PID 3754 just sat there.  I restarted PostgreSQL, and then the
strace finally showed activity.

It seems that even though sqlgrey wasn't accepting connections The logs
at this time show:
Jun 26 22:56:51 darkside postfix/pickup[27380]: 08C7AD6B082: uid=132
from=<sqlgrey>
Jun 26 22:56:51 darkside postfix/qmgr[26777]: 08C7AD6B082:
from=<sq...@da...>, size=485, nrcpt=1
(queue active)
Jun 26 22:56:51 darkside amavis[15461]: (15461-06-3) LMTP::10024
/var/lib/amavis/tmp/amavis-20050626T190955-15461:
<sq...@da...> ->
<pos...@cr...> Received: SIZE=485 from
darkside.crazeecanuck.homelinux.net ([127.0.0.1]) by localhost (darkside
[127.0.0.1]) (amavisd-new, port 10024) with LMTP id 15461-06-3 for
<pos...@cr...>; Sun, 26 Jun 2005 22:56:51 -0400
(EDT)
Jun 26 22:56:51 darkside sqlgrey: dbaccess: error: couldn't get now()
from DB: FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly     This probably means the
server terminated abnormally     before or while processing the request.
Jun 26 22:56:51 darkside sqlgrey: system: forked cleanup child (27706)
Jun 26 22:56:51 darkside amavis[15461]: (15461-06-3) Checking:
<sq...@da...> ->
<pos...@cr...>
Jun 26 22:56:51 darkside sqlgrey: dbaccess: can't connect to DB: FATAL: 
the database system is shutting down
Jun 26 22:56:51 darkside sqlgrey: dbaccess: error: couldn't access
domain_awl table: no connection to the server
Jun 26 22:56:51 darkside sqlgrey: grey: domain awl match: updating
168.100.1(168.100.1.3), postfix.org
Jun 26 22:56:51 darkside sqlgrey: dbaccess: can't connect to DB: FATAL: 
the database system is shutting down
Jun 26 22:56:51 darkside sqlgrey: dbaccess: warning: couldn't do query:
UPDATE domain_awl SET last_seen = NOW(), first_seen = first_seen WHERE
sender_domain = NULL AND src = NULL: FATAL:  the database system is
shutting down , reconnecting to DB
JJun 26 22:56:53 darkside sqlgrey: 2005/06/26-22:56:53 CONNECT TCP Peer:
"127.0.0.1:52685" Local: "127.0.0.1:2501"
Jun 26 22:56:53 darkside sqlgrey: optin: greylisting active for
deb...@cr...
Jun 26 22:56:53 darkside sqlgrey: grey: unknown pattern:
murphy.debian.org, 146.82.138.6: using C-class (146.82.138).
Jun 26 22:56:53 darkside sqlgrey: dbaccess: can't connect to DB: FATAL: 
the database system is shutting down
Jun 26 22:56:53 darkside sqlgrey: dbaccess: error: couldn't get now()
from DB: FATAL:  the database system is shutting down
Jun 26 22:56:53 darkside sqlgrey: dbaccess: can't connect to DB: FATAL: 
the database system is shutting down
Jun 26 22:56:53 darkside sqlgrey: dbaccess: error: couldn't access
domain_awl table: FATAL:  the database system is shutting down
Jun 26 22:56:53 darkside sqlgrey: grey: domain awl match: updating
146.82.138(146.82.138.6), lists.debian.org
Jun 26 22:56:53 darkside sqlgrey: dbaccess: can't connect to DB: FATAL: 
the database system is shutting down
Jun 26 22:56:53 darkside sqlgrey: dbaccess: warning: couldn't do query:
UPDATE domain_awl SET last_seen = NOW(), first_seen = first_seen WHERE
sender_domain = NULL AND src = NULL: FATAL:  the database system is
shutting down , reconnecting to DB
Jun 26 22:56:53 darkside sqlgrey: 2005/06/26-22:56:53 CONNECT TCP Peer:
"127.0.0.1:52693" Local: "127.0.0.1:2501"
Jun 26 22:56:53 darkside sqlgrey: optin: greylisting active for
sp...@cr...
Jun 26 22:56:53 darkside sqlgrey: grey: unknown pattern:
cherry.ease.lsoft.com, 209.119.0.109: using C-class (209.119.0).
Jun 26 22:56:53 darkside sqlgrey: dbaccess: can't connect to DB: FATAL: 
the database system is shutting down
Jun 26 22:56:53 darkside sqlgrey: dbaccess: error: couldn't get now()
from DB: FATAL:  the database system is shutting down
Jun 26 22:56:53 darkside sqlgrey: dbaccess: can't connect to DB: could
not connect to server: Connection refused     Is the server running on
host "localhost" and accepting     TCP/IP connections on port 5432?
Jun 26 22:56:53 darkside sqlgrey: dbaccess: error: couldn't access
domain_awl table: could not connect to server: Connection refused     Is
the server running on host "localhost" and accepting     TCP/IP
connections on port 5432?
Jun 26 22:56:53 darkside sqlgrey: grey: domain awl match: updating
209.119.0(209.119.0.109), peach.ease.lsoft.com

the PostgreSQL logs showed:
2005-06-26 20:01:48 [18926] LOG:  connection authorized: user=sqlgrey
database=sqlgrey
2005-06-26 20:01:48 [3755] ERROR:  prepared statement "dbdpg_1" does not
exist

After restarting PGSQL, sqlgrey is working again. Note that I could
connect to the sqlgrey database just fine.  I'm now strace'ing the
sqlgrey process, and dumping the output to a log file, hopefully that
log will give a glue as to what's happening. at the time of hanging--I
have no idea at this point.

If there's anything else that I can do to help track down this bug,
please let me know...

Re: [Sqlgrey-users] Couple Problems with 1.6.0

[Edward S. (s. list) <sq...@cr...>]

David Rees wrote:

>Hmm, it's still getting stuck running 1.6.1 (didn't expect this to
>change based on the changelogs).  When it gets stuck, I notice that
>the sqlgrey process has a zombie process attached to it, perhaps it's
>getting stuck reaping a child or something?  The last thing in the log
>this time is:
>
>Jun 26 03:30:30 forty sqlgrey: perf: spent 0s cleaning: from_awl (0)
>domain_awl (0) connect (3)
>
>I've now turned up the log level to debug to try and trace this
>further.  Any other things I can try to trace this problem down?
>  
>
I'm experiencing the the problem now as well, and this just started
Friday. I thought it might have had something to do with the upgrade to
1.7, but after reverting back to 1.6 (which I had no problems with), the
problem persists. I've also turned up the logging level trying to track
this down. I'm using Debian Sid/unstable and PostgreSQL (7.4.8) as the
DB system.

Hopefully we can track this problem down fairly easily...

Re: [Sqlgrey-users] Couple Problems with 1.6.0

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

Hmm, it's still getting stuck running 1.6.1 (didn't expect this to
change based on the changelogs).  When it gets stuck, I notice that
the sqlgrey process has a zombie process attached to it, perhaps it's
getting stuck reaping a child or something?  The last thing in the log
this time is:

Jun 26 03:30:30 forty sqlgrey: perf: spent 0s cleaning: from_awl (0)
domain_awl (0) connect (3)

I've now turned up the log level to debug to try and trace this
further.  Any other things I can try to trace this problem down?

-Dave

Re: [Sqlgrey-users] warn_if_reject to populate the db

[Michel B. <mi...@bo...>]

Le Dimanche 26 Juin 2005 16:01, Michel Bouissou a =E9crit :
>
> > > I didn't figure the whole thing out, but I
> > > did look for a string "\.res\." which is commonly used for dynamic
> > > space, e.g., *.res.rr.com. (residential customers.) Perhaps the sec=
ond
> > > dot should be any non-alpha character (-, _, ., digit), and to be s=
afe
> > > there should be at least 2 domain segments following and at least o=
ne
> > > segment preceding (implied by the leading dot.)
> >
> > I don't pretend that the regexp is perfect, it's only heuristic, but =
it
> > would be interesting adding your modification only if you find sample=
s of
> > existing hostnames that don't get properly classified. (i.e. your exa=
mple
> > may already get classified corresctly if the last byte of the IP addr=
ess
> > is part of the name) The more things you add in the regexp, the longe=
r it
> > will take to process, and the higher chances it could collide with ot=
her
> > non-dynamic names in an undesired manner.
> >
> > But feel free to experiment with your own copy and suggest improvemen=
ts
> > that show useful...
>
> All the samples of *.res.rr.com that I can find in my logs also include=
 the
> IP address in their hostname, so SQLgrey's "smart" algo will already
> consider them as "dyamic/enduser" without the need for making the regex=
p
> more complex.

More, checking my logs, I don't see any other ISP besides rr.com and Veri=
zon=20
that uses a ".res." part in their hostnames, and both rr.com and Verizon =
also=20
put the IP address in... Verizon addresses also begin with "pool-", that =
the=20
regexp already catches.

Verizon samples:

pool-151-200-35-11.res.east.verizon.net[151.200.35.11]
pool-138-88-28-49.res.east.verizon.net[138.88.28.49]

etc.

--=20
Michel Bouissou <mi...@bo...> OpenPGP ID 0xDDE8AC6E

Re: [Sqlgrey-users] warn_if_reject to populate the db

[Michel B. <mi...@bo...>]

Le Dimanche 26 Juin 2005 15:37, Michel Bouissou a =E9crit :
>
> > I didn't figure the whole thing out, but I
> > did look for a string "\.res\." which is commonly used for dynamic
> > space, e.g., *.res.rr.com. (residential customers.) Perhaps the secon=
d
> > dot should be any non-alpha character (-, _, ., digit), and to be saf=
e
> > there should be at least 2 domain segments following and at least one
> > segment preceding (implied by the leading dot.)
>
> I don't pretend that the regexp is perfect, it's only heuristic, but it
> would be interesting adding your modification only if you find samples =
of
> existing hostnames that don't get properly classified. (i.e. your examp=
le
> may already get classified corresctly if the last byte of the IP addres=
s is
> part of the name) The more things you add in the regexp, the longer it =
will
> take to process, and the higher chances it could collide with other
> non-dynamic names in an undesired manner.
>
> But feel free to experiment with your own copy and suggest improvements
> that show useful...

All the samples of *.res.rr.com that I can find in my logs also include t=
he IP=20
address in their hostname, so SQLgrey's "smart" algo will already conside=
r=20
them as "dyamic/enduser" without the need for making the regexp more comp=
lex.

Some examples at random:
108-64.200-68.tampabay.res.rr.com
160-35.26-24.tampabay.res.rr.com
180-17.26-24.se.res.rr.com
193-135.207-68.elmore.res.rr.com
2-85.207-68.tampabay.res.rr.com
61.186.204.68.cfl.res.rr.com
67.147.204.68.cfl.res.rr.com
cpe-24-164-130-13.si.res.rr.com
cpe-24-165-149-211.midsouth.res.rr.com
cpe-24-166-0-41.indy.res.rr.com
cpe-24-166-232-151.columbus.res.rr.com
cpe-67-11-214-14.satx.res.rr.com
cpe-67-49-227-231.dc.res.rr.com
cpe-67-49-62-53.socal.res.rr.com
cpe-67-9-163-159.austin.res.rr.com

etc.

--=20
Michel Bouissou <mi...@bo...> OpenPGP ID 0xDDE8AC6E

Re: [Sqlgrey-users] warn_if_reject to populate the db

[Michel B. <mi...@bo...>]

Le Dimanche 26 Juin 2005 15:01, /dev/rob0 a =E9crit :
>
> I've got it going now, was rather easy. I started in test mode, with
> "warn_if_reject check_policy_service inet:127.0.0.1:2501" in main.cf.
> Will this really populate my database? Since nothing is being rejected,
> it looks like non-returning triplets. Nothing is autowhitelisted until
> the first appearance after the greylist period, correct?

You're right, it won't populate much. Only senders that will come several=
=20
times (because they send several messages to the same recipient) in a 24h=
=20
period will end in the AWL tables.

But I'm not sure that this "warn_if_reject" temporary strategy is of any=20
interest, unless you operate a _very_ high volume server. You can as well=
=20
start from scratch with an empty DB and let it populate by itself. Rememe=
ber=20
that only the 1st mail from any given sender will be delayed...

> 1. localhost:2501 vs. Unix socket
> Wouldn't a socket be slightly faster than TCP?

Probably, but works only if on the same machine. TCP is more general, so =
to=20
use sockets, Lionel would need to implement both methods. Given the littl=
e=20
amount of data transferred between Postfix and sqlgrey for a given=20
connection, using sockets would probably make a difference only on very h=
igh=20
volume system.

> 2. Running under control of master(8)
> That would be convenient, start and stop with Postfix; are there other
> benefits? Why the standalone choice?

Running under the control of master would be interesting to me as well.

> 3. Database population commands
> I'm totally lost with SQL (hence the poor choice of mysql), can someone
> help with the manual commands I'd use to add to the database?

Reading the fine MySQL doc would probably help with the basic SQL command=
s,=20
but you'd better let the DB populate by itself.

BTW, why state that MySQL is a poor choice ?

> 4. Database population scripts
> Is there something I could run against user's maildirs which would add
> entries to the AWL? If not should I commission such a project from my
> private farm of Perl coders[2]; I mean, would there be interest?

I don't think there's any interest in trying to artificially populate the=
 DB.=20
Just let it run ;-)

> 6. dyn_fqdn.regexp
> That's quite an expression.

;-)

> I didn't figure the whole thing out, but I=20
> did look for a string "\.res\." which is commonly used for dynamic
> space, e.g., *.res.rr.com. (residential customers.) Perhaps the second
> dot should be any non-alpha character (-, _, ., digit), and to be safe
> there should be at least 2 domain segments following and at least one
> segment preceding (implied by the leading dot.)

I don't pretend that the regexp is perfect, it's only heuristic, but it w=
ould=20
be interesting adding your modification only if you find samples of exist=
ing=20
hostnames that don't get properly classified. (i.e. your example may alre=
ady=20
get classified corresctly if the last byte of the IP address is part of t=
he=20
name) The more things you add in the regexp, the longer it will take to=20
process, and the higher chances it could collide with other non-dynamic n=
ames=20
in an undesired manner.

But feel free to experiment with your own copy and suggest improvements t=
hat=20
show useful...

> 7. Coordination with infidels
> Greylisters, regardless of their MTA and choice of implementation, are
> all in this together. We're all going to run into the same issues with
> stupid and/or big providers which have problems getting real mail
> through greylisting.

So far, I don't have any problem running SQLgrey, and the problematic ser=
vers=20
IPs/names can be reported to this list for inclusion in the provided (and=
=20
auto-updating) whitelists...

> 8. Beyond grey
> This is a biggie which probably warrants its own thread. This is all
> about spam abatement. What about integrating other antispam strategies
> under the roof of the same policy service? Yes, this belongs in its own
> thread. I'll write more of my thoughts about that later.

I think about it the Unix way. I prefer to use several distinct tools of =
my=20
choice, each one doing _one_ thing and doing it well. I wouldn't like any=
=20
bigger system that would integrate different methods, I personally prefer=
=20
doing my own cooking.

Postfix can call several "policy services" without any problem...

> Thanks, Lionel, this looks good so far. I went live with a small but
> heavily-spammed domain yesterday evening, and no spam has been seen
> there since. (The sqlgrey is last in a long list of restrictions with
> numerous RBL checks.)

My 2 cents: Put SQLgrey _before_ RBLs. You'll save external network calls=
 so=20
your system will run faster, and you'll save unnecessary load onto the RB=
L=20
servers as well...

> [2] a/k/a /dev/wife. I might need some help with #3 above to get her
>     started, but OTOH she has some PostgreSQL knowledge.

About /dev/wife, please see below...

--=20
Michel Bouissou <mi...@bo...> OpenPGP ID 0xDDE8AC6E

[amant@blonde etc]$ su
Password: zyva
[root@blonde etc]# modprobe mariage
WARNING: Module "mariage.o" not under GPL license. Inserting "mariage.o"
         will taint the kernel.
Module "mariage.o" inserted with 18712 warnings. Please check system log.
blonde kernel: Discovering new devices...
blonde kernel: Found device: belle_mere
blonde kernel: Activated device: belle_mere. Have a nice day.
blonde kernel: Found device tree for /dev/mari
devfsd: Creating symlink /dev/coiffeur =3D> /dev/mari/portefeuille
devfsd: Creating symlink /dev/estheticienne =3D> /dev/mari/portefeuille
devfsd: Creating symlink /dev/robes =3D> /dev/mari/portefeuille
blonde kernel: Activated device: uterus. Please wait until completion.
blonde kernel: Found child device: lardon_#1
blonde kernel: Found child device: lardon_#2
devfsd: Device nibards owner changed to lardon_#2, group lardons,
        mode 660
blonde kernel: Removing inactive device: foufoune
devfsd: Creating symlink /dev/foufoune =3D> /dev/migraine
devfsd: Modifying symlink /dev/brain =3D> /dev/random
WARNING: Superuser privileges removed from user: mari
WARNING: You will be logged out. Have a nice day, anyway try to.
[mari@blonde etc]$ ls /sex
Permission denied
[mari@blonde etc]$ rmmod mariage
bash: rmmod: command not found
[mari@blonde etc]$ /sbin/rmmod mariage
rmmod: Operation not permitted
[mari@blonde etc]$ su
Password: zyva
su: Incorrect password.
[mari@blonde etc]$ damned je suis refait !
bash: damned: command not found
[mari@blonde etc]$ /sbin/insmod maitresse
maitresse: Operation not permitted

-+- GSM in topless - Bien configurer son mariage -+-

[Sqlgrey-users] warn_if_reject to populate the db

[/dev/rob0 <ro...@gm...>]

I've got it going now, was rather easy. I started in test mode, with 
"warn_if_reject check_policy_service inet:127.0.0.1:2501" in main.cf. 
Will this really populate my database? Since nothing is being rejected, 
it looks like non-returning triplets. Nothing is autowhitelisted until 
the first appearance after the greylist period, correct?

Other things I'm wondering, and please forgive me if they're in the 
archives[1]:

1. localhost:2501 vs. Unix socket
Wouldn't a socket be slightly faster than TCP?

2. Running under control of master(8)
That would be convenient, start and stop with Postfix; are there other 
benefits? Why the standalone choice?

3. Database population commands
I'm totally lost with SQL (hence the poor choice of mysql), can someone 
help with the manual commands I'd use to add to the database?

4. Database population scripts
Is there something I could run against user's maildirs which would add 
entries to the AWL? If not should I commission such a project from my 
private farm of Perl coders[2]; I mean, would there be interest?

5. External files vs. database tables
$conf_dir/clients_*_whitelist* - why flat files vs. having additional 
database tables?

6. dyn_fqdn.regexp
That's quite an expression. I didn't figure the whole thing out, but I 
did look for a string "\.res\." which is commonly used for dynamic 
space, e.g., *.res.rr.com. (residential customers.) Perhaps the second 
dot should be any non-alpha character (-, _, ., digit), and to be safe 
there should be at least 2 domain segments following and at least one 
segment preceding (implied by the leading dot.)

7. Coordination with infidels
Greylisters, regardless of their MTA and choice of implementation, are 
all in this together. We're all going to run into the same issues with 
stupid and/or big providers which have problems getting real mail 
through greylisting. I didn't see a list or forum at greylisting.org. 
What is being done to coordinate with outsiders? I personally have 
subscribed to the postgrey list, where just this morning a thread of 
general grey interest was started (well, just one post so far.)

8. Beyond grey
This is a biggie which probably warrants its own thread. This is all 
about spam abatement. What about integrating other antispam strategies 
under the roof of the same policy service? Yes, this belongs in its own 
thread. I'll write more of my thoughts about that later.

Thanks, Lionel, this looks good so far. I went live with a small but 
heavily-spammed domain yesterday evening, and no spam has been seen 
there since. (The sqlgrey is last in a long list of restrictions with 
numerous RBL checks.)



[1] Is it just me, or are Sourceforge list archives atrocious?

[2] a/k/a /dev/wife. I might need some help with #3 above to get her
    started, but OTOH she has some PostgreSQL knowledge.
-- 
    mail to this address is discarded unless "/dev/rob0"
    or "not-spam" is in Subject: header

Re: [Sqlgrey-users] RPM spec file

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

On 6/26/05, Michel Bouissou <mi...@bo...> wrote:
> Le Dimanche 26 Juin 2005 10:15, David Rees a =E9crit :
> >
> > Apparently, Copyright in the specfile was deprecated in rpm 4 and now
> > no longer works with the version shipped with Fedora Core 4 (4.4).
> > Copyright should be replaced with License which let me build using the
> > spec file.
>=20
> However the current .spec file works perfectly with Mandrake 10.1, that u=
ses
> RPM 4 as well...
>=20
> If changes were to be made, care should be taken not to create
> incompatibility problems for other distros... ;-)

As I understand it, as long as you have RPM 4 or higher, License
should work fine.  That should cover any distro released in the past
few years.

-Dave

Re: [Sqlgrey-users] RPM spec file

[Michel B. <mi...@bo...>]

Le Dimanche 26 Juin 2005 10:15, David Rees a =E9crit :
>
> Apparently, Copyright in the specfile was deprecated in rpm 4 and now
> no longer works with the version shipped with Fedora Core 4 (4.4).
> Copyright should be replaced with License which let me build using the
> spec file.

However the current .spec file works perfectly with Mandrake 10.1, that u=
ses=20
RPM 4 as well...

If changes were to be made, care should be taken not to create =20
incompatibility problems for other distros... ;-)

--=20
Michel Bouissou <mi...@bo...> OpenPGP ID 0xDDE8AC6E

[Sqlgrey-users] RPM spec file

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

Apparently, Copyright in the specfile was deprecated in rpm 4 and now
no longer works with the version shipped with Fedora Core 4 (4.4).=20
Copyright should be replaced with License which let me build using the
spec file.

-Dave

--- sqlgrey.spec.orig
+++ sqlgrey.spec
@@ -6,7 +6,7 @@
 Name:      %{name}
 Version:   %{ver}
 Release:   %{rel}
-Copyright: GPL
+License:   GPL
 Vendor:    Lionel Bouton <lio...@bo...>
 Url:       http://sqlgrey.sourceforge.net
 Packager:  Lionel Bouton <lio...@bo...>