sqlgrey-users — Users of SQLgrey

[Sqlgrey-users] RELEASE: 1.5.4 bugfix

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

1.5.4 is on sourceforge with the fix for the regexps in whitelists (and 
the new smartc code...) discussed here.

1.4 is not affected.

Many thanks to Klaus Alexander Seistrup.

Lionel.

Re: [Sqlgrey-users] Re: Is clients_fqdn_whitelist.local still being used?

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

Klaus Alexander Seistrup wrote the following on 14.03.2005 23:56 :

>Lionel Bouton wrote:
>
>  
>
>>I've reproduced it on my server. Currently tracking down the cause...
>>    
>>
>
>Nice. :-)
>
>  
>

Found it (perl regexp compilation mixup, nullify the private connection 
detection code of the new smartc too). Packaging 1.5.4 with the fixes now.

Lionel

Re: [Sqlgrey-users] Re: Is clients_fqdn_whitelist.local still being used?

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

Lionel Bouton wrote:

> I've reproduced it on my server. Currently tracking down the cause...

Nice. :-)

--=20
Klaus Alexander Seistrup
SubZeroNet =B7 Copenhagen =B7 Denmark

Re: [Sqlgrey-users] Re: Is clients_fqdn_whitelist.local still being used?

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

Klaus Alexander Seistrup wrote the following on 14.03.2005 23:29 :

>On Mon, 14 Mar 2005 22:47:08 +0100, Lionel Bouton
><lio...@bo...> wrote:
>  
>

Hum, seems this is a long standing bug with regexps in fqdn whitelists. 
I've reproduced it on my server. Currently tracking down the cause...

Lionel.

Re: [Sqlgrey-users] Re: Is clients_fqdn_whitelist.local still being used?

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

On Mon, 14 Mar 2005 22:47:08 +0100, Lionel Bouton
<lio...@bo...> wrote:

>>> /^(mail|mx0|pop|imap)\.gmx\.(de|net|co\.uk)$/
>>> /^[mrw]proxy\.gmail\.com$/
>>>
>>> are being ignored, too...
>=20
> Just tested with 1.5.3. WORKSFORME(tm).
>=20
> When SQLgrey detects a change in the local file it logs a reload like
> this (even with 'quiet'):
> Mar 14 22:33:38 ns sqlgrey: reloading
> /etc/sqlgrey/clients_fqdn_whitelist.local
> just add a blank line somewhere and wait for an SMTP connexion...
>=20
> If it doesn't log a line like this, here's what could have happened:

The files get reloaded, but SQLgrey doesn't take into account the
clients_fqdn_whitelist.local file.  The clients_fqdn_whitelist.local
is being used, though.  This is what it looked like a few minutes ago
when I touched the two *.local files:

<snip>
reloading /etc/sqlgrey/clients_ip_whitelist.local
reloading /etc/sqlgrey/clients_fqdn_whitelist.local
domain_awl-match: updating gmx.co.uk, 213.165.64
from_awl-match: updating 64.233.184: x@y.z
whitelist: a@b.c, 81.7.132.92(linuxnews.dk) d@e.f
</snip>

> Probable solution: restart SQLgrey and don't handle these files on
> systems in the future, past or an alternate dimension.

My mailbox is running NTP and file times look sane:

<snip>
$ stat *.local
  File: `clients_fqdn_whitelist.local'
  Size: 1616            Blocks: 8          IO Block: 4096   regular file
Device: 2101h/8449d     Inode: 897494      Links: 1
Access: (0644/-rw-r--r--)  Uid: (    0/    root)   Gid: (    0/    root)
Access: 2005-03-14 23:16:03.000882252 +0100
Modify: 2005-03-14 23:12:02.050540707 +0100
Change: 2005-03-14 23:12:02.050540707 +0100
  File: `clients_ip_whitelist.local'
  Size: 683             Blocks: 8          IO Block: 4096   regular file
Device: 2101h/8449d     Inode: 897588      Links: 1
Access: (0644/-rw-r--r--)  Uid: (    0/    root)   Gid: (    0/    root)
Access: 2005-03-14 23:16:02.996882861 +0100
Modify: 2005-03-14 23:12:02.050540707 +0100
Change: 2005-03-14 23:12:02.050540707 +0100
$=20
</snip>

Is there any way (perhaps a perl switch, or something) to check if the
regexps used in the two files are all valid?

Or is the only other solution to upgrade to v1.5.3?

Cheers,

--=20
Klaus Alexander Seistrup
SubZeroNet =B7 Copenhagen =B7 Denmark

Re: [Sqlgrey-users] Re: Is clients_fqdn_whitelist.local still being used?

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

Lionel Bouton wrote the following on 11.03.2005 14:23 :

> Klaus Alexander Seistrup wrote the following on 11.03.2005 14:06 :
>
>> I wrote:
>>
>>  
>>
>>> # GMX
>>> /^(mail|mx0|pop|imap)\.gmx\.(de|net|co\.uk)$/
>>>   
>>
>>
>> Gmail's outgoing servers:
>>
>> # Gmail
>> /^[mrw]proxy\.gmail\.com$/
>>
>> are being ignored, too...
>>
>>  
>>
> I'll look into it this evening or this week-end.
>

Just tested with 1.5.3. WORKSFORME(tm).

When SQLgrey detects a change in the local file it logs a reload like 
this (even with 'quiet'):
Mar 14 22:33:38 ns sqlgrey: reloading 
/etc/sqlgrey/clients_fqdn_whitelist.local
just add a blank line somewhere and wait for an SMTP connexion...

If it doesn't log a line like this, here's what could have happened:
The code works by checking the mtime of the *.local files (if the file 
doesn't exists it is considered having a 0 mtime). I chose this method 
and not a content check because the check is done on *each* request to 
SQLgrey. A stat call is much much faster than a file open and read...
The only reason why the reloading wouldn't work is if these *.local 
files traveled into the past. Either because at one time they were in 
the future or the last update put them in the past...

Probable solution: restart SQLgrey and don't handle these files on 
systems in the future, past or an alternate dimension.

Cheers,

Lionel.

Re: [Sqlgrey-users] SQLgrey and IPv6

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

On Fri, 11 Mar 2005 16:48:30 +0100, Lionel Bouton
<lio...@bo...> wrote:

>> Will it break anything if I re-create the tables manually and have
>> PostgreSQL use INET (or CIDR) for the IP address fields?
>=20
> Probably not.
> But I'd advise to use VARCHAR(39) instead. I won't support smart and
> classc type algorithms with anything other than VARCHAR.

I've had quite a few IPv6 connections in the past few days, so I
decided to change the original VARCHAR(15) to VARCHAR(39).

> Future automatic updates to the database might not apply cleanly on your
> database too...

Poor me...

Thanks for your help.

--=20
Klaus Alexander Seistrup
SubZeroNet =B7 Copenhagen =B7 Denmark

Re: [Sqlgrey-users] SQLgrey and IPv6

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

On Fri, 11 Mar 2005 16:42:30 +0100, Lionel Bouton
<lio...@bo...> wrote:

>> SQLgrey dies on IPv6 connections=20
>=20
> Not exactly, it reconnects to the database which puts unneeded load on
> both the DB and the SQLgrey host (if they are two different systems). In
> the end the resulting behavior is that greylisting is deactivated for IPv=
6.

You are right, of course.  It doesn't die.

Cheers,

--=20
Klaus Alexander Seistrup
SubZeroNet =B7 Copenhagen =B7 Denmark

Re: [Sqlgrey-users] SQLgrey and IPv6

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

Klaus Alexander Seistrup wrote the following on 11.03.2005 14:42 :

>I wrote:
>
>  
>
>>SQLgrey dies on IPv6 connections
>>    
>>
>
>Will it break anything if I re-create the tables manually and have
>PostgreSQL use INET (or CIDR) for the IP address fields?
>  
>

Probably not.
But I'd advise to use VARCHAR(39) instead. I won't support smart and 
classc type algorithms with anything other than VARCHAR.
Future automatic updates to the database might not apply cleanly on your 
database too...

Lionel (burried under work).

Re: [Sqlgrey-users] SQLgrey and IPv6

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

Klaus Alexander Seistrup wrote the following on 11.03.2005 14:38 :

>Well, SQLgrey dies on IPv6 connections (I have put a blank line after
>each log line):
>  
>

Not exactly, it reconnects to the database which puts unneeded load on 
both the DB and the SQLgrey host (if they are two different systems). In 
the end the resulting behavior is that greylisting is deactivated for IPv6.

Re: [Sqlgrey-users] SQLgrey and IPv6

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

I wrote:

> SQLgrey dies on IPv6 connections

Will it break anything if I re-create the tables manually and have
PostgreSQL use INET (or CIDR) for the IP address fields?

--=20
Klaus Alexander Seistrup
SubZeroNet =B7 Copenhagen =B7 Denmark

Re: [Sqlgrey-users] SQLgrey and IPv6

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

Well, SQLgrey dies on IPv6 connections (I have put a blank line after
each log line):

<snip>

new: 2001:878:100:12::136: und...@st... -> undisclosed@koldfront.=
dk

Warning: couldn't do query:

INSERT INTO connect (sender_name, sender_domain, src, rcpt,
first_seen) VALUES('undisclosed','stud.ku.dk','2001:878:100:12::136','undis=
cl...@ko...',
NOW()):

ERROR:  value too long for type character varying(15), reconnecting to DB

</snip>

--=20
Klaus Alexander Seistrup
SubZeroNet =B7 Copenhagen =B7 Denmark

Re: [Sqlgrey-users] Re: Is clients_fqdn_whitelist.local still being used?

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

Klaus Alexander Seistrup wrote the following on 11.03.2005 14:06 :

>I wrote:
>
>  
>
>># GMX
>>/^(mail|mx0|pop|imap)\.gmx\.(de|net|co\.uk)$/
>>    
>>
>
>Gmail's outgoing servers:
>
># Gmail
>/^[mrw]proxy\.gmail\.com$/
>
>are being ignored, too...
>
>  
>
I'll look into it this evening or this week-end.

[Sqlgrey-users] Re: Is clients_fqdn_whitelist.local still being used?

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

I wrote:

> # GMX
> /^(mail|mx0|pop|imap)\.gmx\.(de|net|co\.uk)$/

Gmail's outgoing servers:

# Gmail
/^[mrw]proxy\.gmail\.com$/

are being ignored, too...

--=20
Klaus Alexander Seistrup
SubZeroNet =B7 Copenhagen =B7 Denmark

[Sqlgrey-users] Is clients_fqdn_whitelist.local still being used?

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

I'm running SQLgrey 1.5.1, and I wonder if the
clients_fqdn_whitelist.local file is still being  taken into account.=20
I have, among other entries, in the file:

# GMX
/^(mail|mx0|pop|imap)\.gmx\.(de|net|co\.uk)$/

but then I discovered that gmx.co.uk had been added to the domain_awl
table.  I deleted the entry from PostgreSQL, but next time mx0.gmx.de
[213.165.64.100] connected the following was logged by SQLgrey:

new: 213.165.64: und...@gm... -> und...@sz...

I'm sure it wasn't like that before v1.5.1.

Anyone?

--=20
Klaus Alexander Seistrup
SubZeroNet =B7 Copenhagen =B7 Denmark

Re: [Sqlgrey-users] occasional sqlgrey crashes

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

Dave Pascoe wrote the following on 10.03.2005 21:38 :

>I am running 1.4.5 out of the FreeBSD Ports collection.  I run the
>latest 1.5 release on my main mail server and it runs great.
>
>On the FreeBSD box I keep getting these notifications:
>
>SQLgrey lost connection to: DBI:mysql:database=sqlgrey;host=localhost (max      
>warn message rate hit, throttling) 
>  
>

If I'm not mistaken this is the 1.4.5-specific bug where all 
null-originated messages trigger a bug and make SQLgrey reconnect itself 
to the database.
1.4.6 and beyond include the fix.

Lionel

Re: [Sqlgrey-users] occasional sqlgrey crashes

[Dave P. <dav...@gm...>]

I am running 1.4.5 out of the FreeBSD Ports collection.  I run the
latest 1.5 release on my main mail server and it runs great.

On the FreeBSD box I keep getting these notifications:

SQLgrey lost connection to: DBI:mysql:database=sqlgrey;host=localhost (max      
warn message rate hit, throttling) 

Is there a fix or something to tune for this?  

I would like to upgrade but1,5 isn't in the Ports collection yet and I
don't want to foul anything up on the FreeBSD box by installing
manually.  Not sure if that would fou lup a future Port-based install
anyway.

Dave

Re: [Sqlgrey-users] occasional sqlgrey crashes

[Max D. <Max...@lr...>]

Lionel Bouton wrote:

> Max Diehn wrote the following on 10.03.2005 14:25 :
> 
>> Hi All,
>>
>> someone ever noticed occasional sqlgrey crashes (it happens one time 
>> in, say, 20 Mio transactions processed) with this log:
>>
>> Mar 10 13:24:35 lxmhs17 sqlgrey: fatal: Modification of a read-only 
>> value attempted at 
>> /usr/lib/perl5/5.8.0/i586-linux-thread-multi/Sys/Syslog.pm line 296.
> 
> 
> 
> Which sqlgrey version? What are the previous log lines? If not yet done 
> can you reproduce it with debug in sqlgrey.conf?
> 

Actually I didn't want to bother You with that, because I still had an 
older version of sqlgrey in production (1.4.1).

Hence, if this is a non-issue to the other users up to now, I would say, 
  for the moment: just forget about it - if it ever happens again with 
the 1.5.*, I'll tell You.

I wasn't able to reproduce it - and I didn't find anything in the log, 
that looks interesting (I use debug as my log level anyway).

Maybe it has something to do with perl 5.8.0, which, for some people, 
doesn't have the best reputation - I don't know.

Here's the last lines from the log, anyway:

Mar 10 13:24:34 lxmhs17 sqlgrey: new: 222.108.41.160: 
hmn...@fs... -> dal...@ju...
Mar 10 13:24:34 lxmhs17 sqlgrey: request: client_address=222.108.41.160 
client_name=[222.108.41.160] recipient=dal...@ju... 
request=smtpd_access_policy sender=hmn...@fs... 
action=defer_if_permit Greylisted for 14 minutes

Mar 10 13:24:34 lxmhs17 sqlgrey: new: 221.212.232.138: 
su...@si... -> wm...@de...
Mar 10 13:24:34 lxmhs17 sqlgrey: request: client_address=221.212.232.138 
client_name=[221.212.232.138] recipient=wm...@de... 
request=smtpd_access_policy sender=su...@si... 
action=defer_if_permit Greylisted for 14 minutes


Cheers,
Max


> Cheers,
> 
> Lionel.
> 
> 
> -------------------------------------------------------
> SF email is sponsored by - The IT Product Guide
> Read honest & candid reviews on hundreds of IT Products from real users.
> Discover which products truly live up to the hype. Start reading now.
> http://ads.osdn.com/?ad_id=6595&alloc_id=14396&op=click
> _______________________________________________
> Sqlgrey-users mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users


-- 
Max Diehn

-------------------------------------------------
Leibniz-Rechenzentrum   !   <mailto:di...@lr...>
Barer Str. 21           !
80333 Muenchen, Germany !   Tel: +49 89 289-27823

Re: [Sqlgrey-users] occasional sqlgrey crashes

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

Max Diehn wrote the following on 10.03.2005 14:25 :

> Hi All,
>
> someone ever noticed occasional sqlgrey crashes (it happens one time 
> in, say, 20 Mio transactions processed) with this log:
>
> Mar 10 13:24:35 lxmhs17 sqlgrey: fatal: Modification of a read-only 
> value attempted at 
> /usr/lib/perl5/5.8.0/i586-linux-thread-multi/Sys/Syslog.pm line 296.


Which sqlgrey version? What are the previous log lines? If not yet done 
can you reproduce it with debug in sqlgrey.conf?

Cheers,

Lionel.

[Sqlgrey-users] occasional sqlgrey crashes

[Max D. <Max...@lr...>]

Hi All,

someone ever noticed occasional sqlgrey crashes (it happens one time in, 
say, 20 Mio transactions processed) with this log:

Mar 10 13:24:35 lxmhs17 sqlgrey: fatal: Modification of a read-only 
value attempted at 
/usr/lib/perl5/5.8.0/i586-linux-thread-multi/Sys/Syslog.pm line 296.

??

Max

Max Diehn

-------------------------------------------------
Leibniz-Rechenzentrum   !   <mailto:di...@lr...>
Barer Str. 21           !
80333 Muenchen, Germany !   Tel: +49 89 289-27823

Re: [Sqlgrey-users] SQLgrey and IPv6

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

On Sun, 06 Mar 2005 17:39:58 +0100, Lionel Bouton
<lio...@bo...> wrote:

>> I'm not sure.  Many tunnel brokers hand out /64 or /48 nets to end
>> users.  E.g., I have [2001:1448:89::]/48 from ngdc.dk.  My impression
>> is that IPv6/64 is somewhat similar to IPv4/32 (I'm not sure if
>> IPv6/48 should be compared to IPv4/24, though). but that is highly
>> subjective.
>=20
> I'm not sure IPv6/64 is the same as IPv4/32, I'll ask around as I know
> some ADSL users with IPv6 connectivity.

I didn't mean functionally equal to.  What I meant was that an IPv6/64
seems to have the same "value" ("value" as in "exchange rate") as an
IPv4/32.

> One more thing: I need to know who will be able to test IPv6 support
> (I'm not able to). For testing, you'll need at least a Postfix 2.2
> pre-release (not a Postfix with an unofficial IPv6 patch...) or 2.2.x
> when it's out and some IPv6 SMTP trafic.

I see that Wietse has just announced Postfix 2.2, so it might not take
long before I'm able to do testing with a genuine Postfix 2.2 and
IPv6.  However, Wietse writes in his announcement that the IPv6
support is from third party patches, so chances are the tests will
look the same with my Postfix 2.1.5 with Debian patches.

Cheers,

--=20
Klaus Alexander Seistrup
SubZeroNet =B7 Copenhagen =B7 Denmark

Re: [Sqlgrey-users] Change sqlgrey.conf to sqlgrey.conf-dist?

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

Josh Endries wrote the following on 08.03.2005 17:30 :

> 've never created a port, only used them, but since sqlgrey source
> compiles and installs almost perfectly out-of-the-box, I wouldn't
> think it would be too hard to make into a port. I can help you test
> it if you like. There are only a couple things in the Makefile that
> I need to tweak before installing (one path change and backup the
> config file :)).


The config file handling should be done outside SQLgrey's Makefile. 
Relying on the distribution to sorts things out with config files is the 
cleaner way.

RPM-based distributions warn user when the config file changed since its 
installation and usually creates rpmnew/rpmsave files. Gentoo's Portage 
never overwrites config file unless explicitly told to and then helps 
the user interactively merge the old and new configuration files.

FreeBSD ports should have one mechanism for that too (or the port 
maintainer should patch the Makefile), I don't want to force into the 
Makefile something tailored for FreeBSD.

Lionel.

Re: [Sqlgrey-users] Change sqlgrey.conf to sqlgrey.conf-dist?

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

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

Lionel Bouton wrote:
| Usually package maintainers do mark these files as "conf" file
with the
| appropriate package handling system. Gentoo and RPM based
distribution
| have appropriate package description already. Could someone teach
me how
| someone usually package software for FreeBSD ?

Okay, I wasn't sure who that change was made by. Usually FreeBSD (as
I know it) uses the ports tree, or packages created from these
ports. Basically you maintain the port's Makefile, and any patches
and extra stuff you need, and the Makefile fetches and installs the
source. Information on creating a port can be found in the Porter's
Handbook:

http://www.freebsd.org/doc/en_US.ISO8859-1/books/porters-handbook/

I've never created a port, only used them, but since sqlgrey source
compiles and installs almost perfectly out-of-the-box, I wouldn't
think it would be too hard to make into a port. I can help you test
it if you like. There are only a couple things in the Makefile that
I need to tweak before installing (one path change and backup the
config file :)).

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

iD8DBQFCLdMsV/+PyAj2L+IRAg/+AKCxV+AxMzRUsrT/peANXjiDvUVPGgCfdblv
ZuBoYlsLVm+OJH0kvadLYRU=
=p5Go
-----END PGP SIGNATURE-----

Re: [Sqlgrey-users] Change sqlgrey.conf to sqlgrey.conf-dist?

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

Josh Endries wrote the following on 08.03.2005 15:33 :

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hey everyone,
>
> Maybe this is only a problem when installing from source (FreeBSD),
> but make install blindly overwrites /etc/sqlgrey/sqlgrey.conf. Is it
> possible to either check for a currently-existing file or dumping
> into a .conf-dist file?


Usually package maintainers do mark these files as "conf" file with the 
appropriate package handling system. Gentoo and RPM based distribution 
have appropriate package description already. Could someone teach me how 
someone usually package software for FreeBSD ?

Lionel

[Sqlgrey-users] Change sqlgrey.conf to sqlgrey.conf-dist?

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

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

Hey everyone,

Maybe this is only a problem when installing from source (FreeBSD),
but make install blindly overwrites /etc/sqlgrey/sqlgrey.conf. Is it
possible to either check for a currently-existing file or dumping
into a .conf-dist file?

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

iD8DBQFCLbfDV/+PyAj2L+IRAiFRAJ9AZey33TIcgYQbWMUTcdi68tQxDgCff7xA
dFqDqm4dJ0ZI0K4p0yrWkTo=
=sG1X
-----END PGP SIGNATURE-----