sqlgrey-users — Users of SQLgrey

Re: [Sqlgrey-users] DBD::MySQL ?

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

/dev/rob0 wrote the following on 25.06.2005 05:47 :

>1.7.0, HOWTO:
>[ ... ]
>## MySQL
>
># Dependancies :
>you need the DBD::MySQL perl module and a working MySQL server.
>[ ... ]
>
>cpan:
>[ ... ]
>Warning: Cannot install DBD::MySQL, don't know what it is.
>Try the command
>
>    i /DBD::MySQL/
>
>to find objects with matching identifiers.
>
>cpan> i /DBD::MySQL/
>Bundle          Bundle::DBD::mysql (J/JW/JWIED/Msql-Mysql-modules-1.2219.tar.gz)
>Module          DBD::mysql      (R/RU/RUDY/DBD-mysql-2.9008.tar.gz)
>Module          DBD::mysql::AutoTypes (G/GR/GRISHACE/DBD-mysql-AutoTypes-1.0.tar.gz)
>Module          DBD::mysql::GetInfo (R/RU/RUDY/DBD-mysql-2.9008.tar.gz)
>Module          DBD::mysql::Install (J/JW/JWIED/Msql-Mysql-modules-1.2219.tar.gz)
>Module          DBD::mysql::SimpleMySQL (L/LI/LINNIN/DBD-mysql-SimpleMySQL-0.5.tar.gz)
>Module          DBD::mysqlPP    (O/OY/OYAMA/DBD-mysqlPP-0.04.tar.gz)
>Module          DBIx::DBSchema::DBD::mysql (I/IV/IVAN/DBIx-DBSchema-0.26.tar.gz)
>Module          DBIx::TextIndex::DBD::mysql (D/DK/DKOCH/DBIx-TextIndex-0.25.tar.gz)
>Module          SQL::AnyDBD::Mysql (T/TB/TBONE/SQL-AnyDBD-0.02.tar.gz)
>10 items found
>
>So is it DBD::mysql I need?
>

Yes.

> Perhaps the HOWTO should be revised? Oh,
>BTW: "Dependancies" should be "Dependencies".
>  
>

Thanks, will do.

Lionel

[Sqlgrey-users] DBD::MySQL ?

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

1.7.0, HOWTO:
[ ... ]
## MySQL

# Dependancies :
you need the DBD::MySQL perl module and a working MySQL server.
[ ... ]

cpan:
[ ... ]
Warning: Cannot install DBD::MySQL, don't know what it is.
Try the command

    i /DBD::MySQL/

to find objects with matching identifiers.

cpan> i /DBD::MySQL/
Bundle          Bundle::DBD::mysql (J/JW/JWIED/Msql-Mysql-modules-1.2219.tar.gz)
Module          DBD::mysql      (R/RU/RUDY/DBD-mysql-2.9008.tar.gz)
Module          DBD::mysql::AutoTypes (G/GR/GRISHACE/DBD-mysql-AutoTypes-1.0.tar.gz)
Module          DBD::mysql::GetInfo (R/RU/RUDY/DBD-mysql-2.9008.tar.gz)
Module          DBD::mysql::Install (J/JW/JWIED/Msql-Mysql-modules-1.2219.tar.gz)
Module          DBD::mysql::SimpleMySQL (L/LI/LINNIN/DBD-mysql-SimpleMySQL-0.5.tar.gz)
Module          DBD::mysqlPP    (O/OY/OYAMA/DBD-mysqlPP-0.04.tar.gz)
Module          DBIx::DBSchema::DBD::mysql (I/IV/IVAN/DBIx-DBSchema-0.26.tar.gz)
Module          DBIx::TextIndex::DBD::mysql (D/DK/DKOCH/DBIx-TextIndex-0.25.tar.gz)
Module          SQL::AnyDBD::Mysql (T/TB/TBONE/SQL-AnyDBD-0.02.tar.gz)
10 items found

So is it DBD::mysql I need? Perhaps the HOWTO should be revised? Oh,
BTW: "Dependancies" should be "Dependencies".
-- 
    mail to this address is discarded unless "/dev/rob0"
    or "not-spam" is in Subject: header

Re: [Sqlgrey-users] Couple Problems with 1.6.0

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

On 6/24/05, Lionel Bouton <lio...@bo...> wrote:
>=20
> That is really puzzling. Which version of:
> - perl,
> - DBI,
> - postgresql DBD driver,
> are you using, are they all coming with your distribution ?

perl-5.8.6-15
perl-DBI-1.48-4
perl-DBD-Pg-1.41-2
perl-Net-Server-0.87-4

All packages are shipped with FC4 except for perl-Net-Server which is
shipped by Fedora Extras.  Running postgresql 8 as well.

> - the connection with the database is borked.
> Either way, the code is designed to get around this (let the message
> pass if we can't get a straight answer from the database) and reconnect
> to the database hoping it is a temporary DB problem.

I'm guessing that the connection got borked, but for some reason it
didn't recover.  While sqlgrey has been running pretty smoothly since
yesterday, I still see a couple messages which it appears to recover
from:

Jun 24 00:03:54 forty sqlgrey: grey: domain awl match: updating
216.155.201(216.155.201.61), returns.groups.yahoo.com
Jun 24 00:05:04 forty sqlgrey: mail: failed to send: No child processes
Jun 24 00:05:04 forty sqlgrey: dbaccess: error: couldn't get reconnect
delay: ERROR:  prepared statement "dbdpg_73" does not exist
Jun 24 00:05:04 forty sqlgrey: warning: Use of uninitialized value in
concatenation (.) or string at /usr/sbin/sqlgrey line 1985.
Jun 24 00:05:04 forty sqlgrey: grey: reconnect ok:
68.142.198(68.142.198.207), ted...@sb... ->
dr...@gr... ()
Jun 24 00:05:04 forty sqlgrey: mail: failed to send: No child processes
Jun 24 00:05:04 forty sqlgrey: grey: from awl: 68.142.198,
ted...@sb... added
Jun 24 00:05:04 forty sqlgrey: mail: failed to send: No child processes
Jun 24 00:05:04 forty sqlgrey: dbaccess: warning: couldn't do query:
INSERT INTO from_awl (sender_name, sender_domain, src, first_seen,
last_seen) VALUES('ted.rees','sbcglobal.net','68.142.198','sql
error',NOW()): ERROR:  invalid input syntax for type timestamp: "sql
error" , reconnecting to DB
Jun 24 00:05:05 forty sqlgrey: mail: failed to send: No child processes
Jun 24 00:05:05 forty sqlgrey: warning: Use of uninitialized value in
concatenation (.) or string at /usr/sbin/sqlgrey line 2008.

> Even more surprising! I'm betting on a distro specific problem. A wild
> guess: is SELinux active? What happens if you deactivate it?

Nope, SELinux is disabled...

-Dave

[Sqlgrey-users] [RELEASE] 1.6.1

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

Hi,

some bugfixes for sqlgrey-logstats.pl:

http://sourceforge.net/project/showfiles.php?group_id=113566&package_id=155492&release_id=337419

Lionel.

[Sqlgrey-users] new update_sqlgrey_config script [was: SQLgrey Feature Requests]

[Gianpaolo D. M. <gia...@ge...>]

=20
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Lionel,

Find a new version of update_sqlgrey_config attached to this message.
I've ported the whole thing over to Perl as it is more portable
than a bash script (eg. FreeBSD misses md5sum and it's own md5 has
a different syntax).

See the enclosed pod documentation for the module requirements
and more information.

The script uses parts of your config parser, however I added
a fix against a possible race condition when $dflt{conf_dir}
is missing or unwriteable (see lines 246 and 247).

The script was tested on FreeBSD 5.4 and 5.3 machines.
It should work on other plattforms either as long as all
Perl modules are available.

Appreciating your feedback.

Gianpaolo

-----BEGIN PGP SIGNATURE-----
Version: PGP 8.1

iQA/AwUBQrwMuS+TvXspauGfEQLO9ACdELVHTC1LYofCW8P6w86GE1e9xeQAoJR8
LKsVlsVolKSvJ9NI57wZRf7l
=3DxQwU
-----END PGP SIGNATURE-----
=20

Re: [Sqlgrey-users] Couple Problems with 1.6.0

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

David Rees wrote the following on 23.06.2005 23:52 :

>I just upgraded to 1.6.0 from 1.4.8 yesterday and have had a couple
>problems with it.
>
>I noticed this morning that sqlgrey appeared to have stopped
>responding, then found these errors in /var/log/maillog:
>
>Jun 22 16:45:21 forty sqlgrey: dbaccess: error: couldn't get reconnect
>delay: ERROR:  prepared statement "dbdpg_17" does not exist
>  
>

That is really puzzling. Which version of:
- perl,
- DBI,
- postgresql DBD driver,
are you using, are they all coming with your distribution ?

This is the first time ever this error gets reported. This kind of error
happens either if the prepare_cached() fails or the execute() fails. In
both cases this means that either:
- there was an error in the SQL syntax,
- the connection with the database is borked.
Either way, the code is designed to get around this (let the message
pass if we can't get a straight answer from the database) and reconnect
to the database hoping it is a temporary DB problem.


>Jun 22 16:45:21 forty sqlgrey: warning: Use of uninitialized value in
>concatenation (.) or string at /usr/sbin/sqlgrey line 1985.
>Jun 22 16:45:21 forty sqlgrey: dbaccess: warning: couldn't do query:
>INSERT INTO from_awl (sender_name, sender_domain, src, first_seen,
>last_seen) VALUES('tcg
>rant','alumni.princeton.edu','64.97.160.33','sql error',NOW()): ERROR:
> invalid input syntax for type timestamp: "sql error" , reconnecting
>to DB
>Jun 22 16:45:21 forty sqlgrey: warning: Use of uninitialized value in
>concatenation (.) or string at /usr/sbin/sqlgrey line 2008.
>
>  
>

Given the first error, this is expected. I'll have to clean up a little
to avoid some of these errors though.

>Then it appears to have tried to restart itself later and I found
>these messages:
>
>Jun 23 02:58:53 forty sqlgrey: mail: failed to send: No child processes
>Jun 23 02:58:53 forty sqlgrey: dbaccess: error: couldn't get reconnect
>delay: ERROR:  prepared statement "dbdpg_6" does not exist
>  
>

Surprising again. AFAIK SQLgrey doesn't play with prepared statements in
a way that could explain this. If a prepared statement doesn't exist,
the call to prepare_cached() should automatically create it. So this
error is really unexpected.

>Jun 23 02:58:53 forty sqlgrey: warning: Use of uninitialized value in
>concatenation (.) or string at /usr/sbin/sqlgrey line 1985.
>
>I tried killing the sqlgrey process, but would only respond to a kill -9.
>  
>

Even more surprising! I'm betting on a distro specific problem. A wild
guess: is SELinux active? What happens if you deactivate it?

>Any ideas?  I'm running on Fedora Core 4.
>  
>

Hum, are there any other FC4 users around?

Lionel.

Re: [Sqlgrey-users] SQLGrey Feature Requests

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

Michel Bouissou wrote the following on 24.06.2005 09:13 :

>Le Jeudi 23 Juin 2005 16:49, Lionel Bouton a =E9crit :
> =20
>
>>I will release 1.6.1 and 1.7.1 with the fix.
>>   =20
>>
>
>Did you incorporate as well the patch I sent to sqlgrey-logstats.pl on=20
>tuesday, 09:32:12 ? I didn't see any answer about it...
> =20
>

Will be in too.

>
>I also have a little request/suggestion for sqlgrey logging : Could it b=
e=20
>possible that "from awl match:" entries could log the sender address in =
its=20
>"deverped" form (that corresponds to the actual from_awl entry) ?
>
>Otherwise (as it currently is with the complete sender address),=20
>sqlgrey-logstats.pl spits dozens of lines in its "From AWL" section for =
every=20
>mailing list that uses VERP and has a significant traffic. This is compl=
etely=20
>useless and makes a big report, which wouls be much nicer if it containe=
d a=20
>single line for the deverped sender address...
>
> =20
>

Ok.

Lionel

Re: [Sqlgrey-users] SQLGrey Feature Requests

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

Le Jeudi 23 Juin 2005 16:49, Lionel Bouton a =E9crit :
> I will release 1.6.1 and 1.7.1 with the fix.

Did you incorporate as well the patch I sent to sqlgrey-logstats.pl on=20
tuesday, 09:32:12 ? I didn't see any answer about it...


I also have a little request/suggestion for sqlgrey logging : Could it be=
=20
possible that "from awl match:" entries could log the sender address in i=
ts=20
"deverped" form (that corresponds to the actual from_awl entry) ?

Otherwise (as it currently is with the complete sender address),=20
sqlgrey-logstats.pl spits dozens of lines in its "From AWL" section for e=
very=20
mailing list that uses VERP and has a significant traffic. This is comple=
tely=20
useless and makes a big report, which wouls be much nicer if it contained=
 a=20
single line for the deverped sender address...

Cheers.

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

Re: [Sqlgrey-users] RELEASE: 1.5.9

[Who K. <qui...@me...>]

It would be nice if you put the download link in these release 
announcements.

Thanks,
Jim

P.S. Yes I'm going to fetch 1.6, but this was the todo tagged message in 
box.

Lionel Bouton wrote:

>Hi,
>
>SQLgrey 1.5.9 tarball is on sourceforge (RPMs should come shortly after,
>  
>

[Sqlgrey-users] Couple Problems with 1.6.0

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

I just upgraded to 1.6.0 from 1.4.8 yesterday and have had a couple
problems with it.

I noticed this morning that sqlgrey appeared to have stopped
responding, then found these errors in /var/log/maillog:

Jun 22 16:45:21 forty sqlgrey: dbaccess: error: couldn't get reconnect
delay: ERROR:  prepared statement "dbdpg_17" does not exist
Jun 22 16:45:21 forty sqlgrey: warning: Use of uninitialized value in
concatenation (.) or string at /usr/sbin/sqlgrey line 1985.
Jun 22 16:45:21 forty sqlgrey: dbaccess: warning: couldn't do query:
INSERT INTO from_awl (sender_name, sender_domain, src, first_seen,
last_seen) VALUES('tcg
rant','alumni.princeton.edu','64.97.160.33','sql error',NOW()): ERROR:
 invalid input syntax for type timestamp: "sql error" , reconnecting
to DB
Jun 22 16:45:21 forty sqlgrey: warning: Use of uninitialized value in
concatenation (.) or string at /usr/sbin/sqlgrey line 2008.

Then it appears to have tried to restart itself later and I found
these messages:

Jun 23 02:58:53 forty sqlgrey: mail: failed to send: No child processes
Jun 23 02:58:53 forty sqlgrey: dbaccess: error: couldn't get reconnect
delay: ERROR:  prepared statement "dbdpg_6" does not exist
Jun 23 02:58:53 forty sqlgrey: warning: Use of uninitialized value in
concatenation (.) or string at /usr/sbin/sqlgrey line 1985.

I tried killing the sqlgrey process, but would only respond to a kill -9.

Any ideas?  I'm running on Fedora Core 4.

-Dave

[Sqlgrey-users] Re: AW: AW: AW: SQLGrey Feature Requests

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

Gianpaolo Del Matto wrote the following on 23.06.2005 17:16 :

> 
>-----BEGIN PGP SIGNED MESSAGE-----
>Hash: SHA1
>
>Hello again
>
>Thank you.
>
>Btw: Are you planning to change the update_sqlgrey_config
>script in some way?
>
>I ask this as I'm fiddling around with the MYDIR
>thingie as the script does simply rely on
>/etc/sqlgrey directory and allows not to redefine
>tt through command line.
>Moreover it misses some sanity checks during
>file operations.
>  
>

Configurable MYDIR is an obvious improvement yes. More sanity checks is
always good too :-)

>Tell me if you could use some help.
>
>  
>

A nice patch in my mailbox is always welcomed!

Lionel

[Sqlgrey-users] Re: AW: AW: SQLGrey Feature Requests

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

Gianpaolo Del Matto wrote the following on 23.06.2005 16:14 :

> 
>-----BEGIN PGP SIGNED MESSAGE-----
>Hash: SHA1
>
>Hello Lionel
>
>My appologies, I am too late with my feedback
>before you release 1.6 :-/
>
>I found the issue with sqlgrey-logparser.pl and
>verified it with the 1.6 release.
>
>Problem was that it would not match anything taken
>from /var/log/maillog.
>I though first it had to be a FreeBSD specific bug,
>maybe from a different syslog format or such.
>
>However it turned out, that the regexp on line 207
>(1.6's logparser) did not match my line because my
>hostname contains dashes.
>
>So I changed the line
>
>m/^(\w{3} [\d ]\d \d\d:\d\d:\d\d) \w+ $self->{programname}: (\w+):
>(.*)$/o
>to
>m/^(\w{3} [\d ]\d
>\d\d:\d\d:\d\d)\s[\S]+\s$self->{programname}:\s(\w+):\s(.*)$/o
>
>This would also work, however I found myself safer using the \s to
>mark the whitespaces...
>m/^(\w{3} [\d ]\d \d\d:\d\d:\d\d) [\S]+ $self->{programname}: (\w+):
>(.*)$/o
>
>This should match any non-whitespace string enclosed by whitespaces
>after
>the initial date string followed by programname.
>
>  
>

Thanks, I will release 1.6.1 and 1.7.1 with the fix.

Lionel

Re: [Sqlgrey-users] Distributed DBs

[Josh E. <jo...@en...>]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Ray Booysen wrote:
> Has anyone done any testing with distributed sqlgrey databases?
> What I mean is that there are multiple machines running sqlgrey
> connecting to the same MySQL database on a single machine.

I have multiple Postfix machines with sqlgrey connecting over TCP to
a single MySQL machine; works great.

J
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (FreeBSD)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFCuGE8V/+PyAj2L+IRAk1TAJ9GxLu01z9E7d3ZPp/y9ji/8bMP6QCgrsQy
XNbDHNEDcjtmBzWf+zZpnng=
=Nn2n
-----END PGP SIGNATURE-----

Re: [Sqlgrey-users] Distributed DBs

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

Ray Booysen wrote the following on 21.06.2005 10:05 :

> Hi all
>
> Has anyone done any testing with distributed sqlgrey databases?  What
> I mean is that there are multiple machines running sqlgrey connecting
> to the same MySQL database on a single machine.


This is the configuration used in production by my employer (but with
PostgreSQL). Runs fine with 2 SQLgrey instances.

There is an an harmless race condition when 2 SQLgrey instances want to
create the same entry in connect. I've seen it only once in the logs :
the second SQLgrey instance gets an SQL error for the second INSERT in
the connect table, thinks something is fishy about the database and
reconnects to make sure the database connection is clean. In this case
you get an error message in the logs and a mail (if the admin_mail entry
is properly configured).
I'll clean this in the future and will do it ASAP if this becomes a
problem for someone (too much errors/e-mails).

Lionel.

Re: [Sqlgrey-users] Distributed DBs

[Who K. <qui...@me...>]

My setup runs a single sqlgrey mysql database on the system where my 
primary mx resides.

My secondary and tertiary mx use the same mysql database connecting 
remotely.

Works fine for me.

Ray Booysen wrote:

> Hi all
>
> Has anyone done any testing with distributed sqlgrey databases?  What 
> I mean is that there are multiple machines running sqlgrey connecting 
> to the same MySQL database on a single machine.
>
> Regards
> Ray Booysen
>
>
> -------------------------------------------------------
> SF.Net email is sponsored by: Discover Easy Linux Migration Strategies
> from IBM. Find simple to follow Roadmaps, straightforward articles,
> informative Webcasts and more! Get everything you need to get up to
> speed, fast. http://ads.osdn.com/?ad_id=7477&alloc_id=16492&op=click
> _______________________________________________
> Sqlgrey-users mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users

[Sqlgrey-users] Distributed DBs

[Ray B. <rj_...@rj...>]

Hi all

Has anyone done any testing with distributed sqlgrey databases?  What I 
mean is that there are multiple machines running sqlgrey connecting to 
the same MySQL database on a single machine.

Regards
Ray Booysen

Re: [Sqlgrey-users] [RELEASE] 1.7.0

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

Little bugfix to sqlgrey-logstats.pl, see attached patch.

Le Mardi 21 Juin 2005 09:11, Michel Bouissou a =E9crit :
> Le Mardi 21 Juin 2005 00:59, Lionel Bouton a =E9crit :
> > 1.7.0 is on sourceforge. Only tar.bz2, RPMs should come tomorrow.
>
> I've built them (without any patches ;-), available here if you wish :
> http://www.bouissou.net/sqlgrey/

RPMs including the patch available as well (sqlgrey-1.7.0-1MiB.*.rpm)

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

Re: [Sqlgrey-users] [RELEASE] 1.7.0

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

Le Mardi 21 Juin 2005 00:59, Lionel Bouton a =E9crit :
>
> 1.7.0 is on sourceforge. Only tar.bz2, RPMs should come tomorrow.

I've built them (without any patches ;-), available here if you wish :=20
http://www.bouissou.net/sqlgrey/

> I added a quicky: I realised I could make SQLgrey start even if the DB
> system is down without too much code. Now if your distribution mess up
> the service startup dependencies, SQLgrey will be patient and wait for
> the DB to come up (disabling greylisting meanwhile) instead of exiting
> on error.

Good idea.

Cheers.

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

[Sqlgrey-users] [RELEASE] 1.7.0

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

Hi,

1.7.0 is on sourceforge. Only tar.bz2, RPMs should come tomorrow.

This is 1.6.0 with Michel's work:
- connect cleanup on AWL entry creations,
- throttling.

I added a quicky: I realised I could make SQLgrey start even if the DB
system is down without too much code. Now if your distribution mess up
the service startup dependencies, SQLgrey will be patient and wait for
the DB to come up (disabling greylisting meanwhile) instead of exiting
on error.

Lionel.

Re: [Sqlgrey-users] [RELEASE] 1.6.0

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

Michel Bouissou wrote the following on 20.06.2005 23:45 :

>Le Lundi 20 Juin 2005 23:23, Lionel Bouton a =E9crit :
> =20
>
>>>+    $sender_name =3D~ s/^([[:alnum:]]+).*/$1/;
>>>     =20
>>>
>>This is somehow crude.
>>   =20
>>
>
>That's true. I also thought it was "quick and dirty, but efficient".=20
>Heuristic, fuzzy-logic programming ;-))
>
>The tradeoff was to assume that there was little chance that several ent=
ries=20
>with same "src", same "sender_domain", and "sender_names" with a match a=
t the=20
>beginning, AND that wouldn't match when "deverped" to from_awl would be =
in=20
>connect at the same time (remember a legitimate entry is not supposed to=
 stay=20
>in connect for long...)
>
>For if a given src/domain couple generates much mail, it will quickly go=
 to=20
>domain_awl, so such problems can occur only as long as it isn't there. I=
f it=20
>isn't yet in domain_awl and throttling is in action, there won't be many=
=20
>simultaneous entries in connect, so the chances of collisions are small.
>
>If a given src/domain couple generates few simultaneous entries (moderat=
e=20
>traffic), then there are little chances that collisions might happen in=20
>connect.
>
>Now I had considered the question with the following angle : What if a "=
LIKE%=20
>collision in connect" happens anyway ? The answer is that when moving on=
e=20
>entry in from_awl, we will delete from connect another entry that we=20
>shouldn't have deleted.
>
>What are the consequences ? The entry that got deleted will be "greylist=
ed=20
>again" instead of accepted immediately when it comes back. The final=20
>consequence is that in the rare case where we "delete the wrong entry", =
it=20
>will delay the "deleted entry message" by one more server retry. This wi=
ll=20
>happen only once for the given sender, of course.
>
>Given the fact that I assume that such collisions will be rather rare, I=
 had=20
>decided for myself that it wasn't a problem ;-)
>
> =20
>
>>The goal is to make sure that each connect entry=20
>>matching the from we put in from_awl (the result of deverp_user) is
>>cleaned. The problem is that in the srs' case, this ends like this :
>>LIKE 'srs0%'
>>-> all srs connect entries from one domain are cleaned up, but they
>>won't match the from_awl entry.
>>   =20
>>
>
>True. But the SRS origin domain will very soon go to domain_awl if it fo=
rwards=20
>a noticeable amount of mail to our domain, and if it isn't the case,=20
>collisions will be rather rare...
>
> =20
>
>>Hexadecimal sequences stripping in deverp_user would create the same
>>problem (but most probably with a lesser real-life impact).
>>
>>One better way (I think) is to do this :
>>$sender_name =3D deverp_user($sender_name);
>>$sender_name =3D~ s/#/%/g;
>>
>>This way the 'LIKE' below will have less chances (there still are some)
>>   =20
>>
>
>I'm not sure that a LIKE with several "%" would be legal and would work =
well.
>

Standard SQL and perf will be good (we already use indexes to select the
few connect entries that may be relevant).

patch 2 and 3 are already in with minor modifications (I realised some
error messages weren't clear enough from the start so I took the time to
clarify them and some of yours fall into the pack).

Lionel.

Re: [Sqlgrey-users] [RELEASE] 1.6.0

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

Le Lundi 20 Juin 2005 23:23, Lionel Bouton a =E9crit :
>
> >+ =A0 =A0$sender_name =3D~ s/^([[:alnum:]]+).*/$1/;
>
> This is somehow crude.

That's true. I also thought it was "quick and dirty, but efficient".=20
Heuristic, fuzzy-logic programming ;-))

The tradeoff was to assume that there was little chance that several entr=
ies=20
with same "src", same "sender_domain", and "sender_names" with a match at=
 the=20
beginning, AND that wouldn't match when "deverped" to from_awl would be i=
n=20
connect at the same time (remember a legitimate entry is not supposed to =
stay=20
in connect for long...)

For if a given src/domain couple generates much mail, it will quickly go =
to=20
domain_awl, so such problems can occur only as long as it isn't there. If=
 it=20
isn't yet in domain_awl and throttling is in action, there won't be many=20
simultaneous entries in connect, so the chances of collisions are small.

If a given src/domain couple generates few simultaneous entries (moderate=
=20
traffic), then there are little chances that collisions might happen in=20
connect.

Now I had considered the question with the following angle : What if a "L=
IKE%=20
collision in connect" happens anyway ? The answer is that when moving one=
=20
entry in from_awl, we will delete from connect another entry that we=20
shouldn't have deleted.

What are the consequences ? The entry that got deleted will be "greyliste=
d=20
again" instead of accepted immediately when it comes back. The final=20
consequence is that in the rare case where we "delete the wrong entry", i=
t=20
will delay the "deleted entry message" by one more server retry. This wil=
l=20
happen only once for the given sender, of course.

Given the fact that I assume that such collisions will be rather rare, I =
had=20
decided for myself that it wasn't a problem ;-)

> The goal is to make sure that each connect entry=20
> matching the from we put in from_awl (the result of deverp_user) is
> cleaned. The problem is that in the srs' case, this ends like this :
> LIKE 'srs0%'
> -> all srs connect entries from one domain are cleaned up, but they
> won't match the from_awl entry.

True. But the SRS origin domain will very soon go to domain_awl if it for=
wards=20
a noticeable amount of mail to our domain, and if it isn't the case,=20
collisions will be rather rare...

> Hexadecimal sequences stripping in deverp_user would create the same
> problem (but most probably with a lesser real-life impact).
>
> One better way (I think) is to do this :
> $sender_name =3D deverp_user($sender_name);
> $sender_name =3D~ s/#/%/g;
>
> This way the 'LIKE' below will have less chances (there still are some)

I'm not sure that a LIKE with several "%" would be legal and would work w=
ell.=20
I have never used it and would need to check the precise syntax...

> Any thought?

If you're sure that it works, that's good for me. It can solve the proble=
m you=20
mention, however I doubt this problem is of any real-world practical=20
incidence...

Cheers.

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

Re: [Sqlgrey-users] [RELEASE] 1.6.0

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

Hi,

I'm currently pushing Michel's patches in sqlgrey for the 1.7.0 release.

First note:

>diff -aurN sqlgrey-1.6.0-Original/sqlgrey sqlgrey-1.6.0-int1/sqlgrey
>--- sqlgrey-1.6.0-Original/sqlgrey	2005-06-16 23:43:07.000000000 +0200
>+++ sqlgrey-1.6.0-int1/sqlgrey	2005-06-19 09:30:49.000000000 +0200
>@@ -1200,6 +1200,13 @@
> 	      ' AND src = ' . $self->quote($host));
> }
> 
>+sub delete_domain_from_connect {
>+    my ($self, $domain, $host) = @_;
>+    $self->do("DELETE FROM $connect " .
>+	      'WHERE sender_domain = ' . $self->quote($domain) .
>+	      ' AND src = ' . $self->quote($host));
>+}
>+
> # Active domain AWL for a domain/IP
> sub move_domain_from_mail_to_domain_awl {
>     my ($self, $domain, $host) = @_;
>@@ -1343,10 +1350,12 @@
> sub delete_mail_ip_from_connect {
>     my ($self, $sender_name, $sender_domain, $addr) = @_;
> 
>+    $sender_name =~ s/^([[:alnum:]]+).*/$1/;
>  
>

This is somehow crude. The goal is to make sure that each connect entry
matching the from we put in from_awl (the result of deverp_user) is
cleaned. The problem is that in the srs' case, this ends like this :
LIKE 'srs0%'
-> all srs connect entries from one domain are cleaned up, but they
won't match the from_awl entry.
Hexadecimal sequences stripping in deverp_user would create the same
problem (but most probably with a lesser real-life impact).

One better way (I think) is to do this :
$sender_name = deverp_user($sender_name);
$sender_name =~ s/#/%/g;

This way the 'LIKE' below will have less chances (there still are some)
of matching entries that wouldn't match the from_awl entry we are creating.

>+    
>     $self->do("DELETE FROM $connect " .
>-	      'WHERE sender_name = ' . $self->quote($sender_name) .
>+	      'WHERE src = ' . $self->quote($addr) .
> 	      ' AND sender_domain = ' . $self->quote($sender_domain) .
>-	      ' AND src = ' . $self->quote($addr));
>+	      ' AND sender_name LIKE ' . $self->quote($sender_name . '%') );
>  
>

(becomes
' AND sender_name LIKE ' . $self->quote($sender_name) );
)

Any thought?

Lionel

Re: [Sqlgrey-users] [RELEASE] 1.6.0

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

Le Vendredi 17 Juin 2005 14:21, Lionel Bouton a =E9crit :
> 1.6.0 is out on sourceforge.

Please find attached the set of patches for adding connect_throttling and=
=20
connect_delete patch to 1.6.0.

I've also produced a set of RPMs of 1.6.0 including these patches, availa=
ble=20
at http://www.bouissou.net/sqlgrey/

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

Re: [Sqlgrey-users] [RELEASE] 1.6.0

[Ray B. <rj_...@rj...>]

Lionel Bouton wrote:

>At last!
>
>1.6.0 is out on sourceforge. Changes from 1.5.9 include cleanups in the
>log parser and in the documentation. SQLgrey now logs the actual IP and
>the client ID it computes (either IP or class-C) instead of the client
>ID alone.
>
>Lionel.
>
>
>-------------------------------------------------------
>SF.Net email is sponsored by: Discover Easy Linux Migration Strategies
>from IBM. Find simple to follow Roadmaps, straightforward articles,
>informative Webcasts and more! Get everything you need to get up to
>speed, fast. http://ads.osdn.com/?ad_id=7477&alloc_id=16492&op=click
>_______________________________________________
>Sqlgrey-users mailing list
>Sql...@li...
>https://lists.sourceforge.net/lists/listinfo/sqlgrey-users
>  
>
Woops, ignore me :P
Its the stable ebuild link that I need :)

Re: [Sqlgrey-users] [RELEASE] 1.6.0

[Ray B. <rj_...@rj...>]

Lionel Bouton wrote:

>At last!
>
>1.6.0 is out on sourceforge. Changes from 1.5.9 include cleanups in the
>log parser and in the documentation. SQLgrey now logs the actual IP and
>the client ID it computes (either IP or class-C) instead of the client
>ID alone.
>
>Lionel.
>
>
>-------------------------------------------------------
>SF.Net email is sponsored by: Discover Easy Linux Migration Strategies
>from IBM. Find simple to follow Roadmaps, straightforward articles,
>informative Webcasts and more! Get everything you need to get up to
>speed, fast. http://ads.osdn.com/?ad_id=7477&alloc_id=16492&op=click
>_______________________________________________
>Sqlgrey-users mailing list
>Sql...@li...
>https://lists.sourceforge.net/lists/listinfo/sqlgrey-users
>  
>
Hi Lionel

Thanks for the hard work.  Could you please update the dev ebuild?

Regards
Ray