sqlgrey-users — Users of SQLgrey

Re: [Sqlgrey-users] Another small update

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

Hehe.. You're running wild :)

Though im not quite following all the things youre talking about, help 
with the code is always appreciated. (Not usually being a perl-coder i 
have no idea what taint-mode actually is and so forth).

I did a diff of the version i have, and the one on your server. Probably 
due to running tidy, 5000+ lines were displayed.
Is there any way you can make the changes easier to read? Like, a diff 
for each new "feature" or major change. (sorta iterative/progressive diffs)

Sqlgrey is not my project, i just help out as co-developer (or something 
like that), but most of the code is made by Lionel Bouton and i must 
admit, i havent even read most of the code.
So unless I understand whats changed, bit by bit, i cant really put any 
of it into to the CVS (would be irresponsible of me right? ;).

Other than that, Lionel Bouton should be the one to accept code, if he's 
available at this time.

I hope that doesnt discourage you from helping making sqlgrey better. 
That is definitely not my intent.


- Dan Faerch


Bob Apthorpe wrote:
> Hi,
>
> I've run the refactored version for about a day now and I've cleaned up
> a runtime warning and added support for Time::HiRes if it's available[1].
>
> Things seem to be working properly. I have to fix the system() call in
> sendmail() that's breaking in taint-mode and review the updated 'killing
> spree' code (I suspect my quest for portable signal numbers broke some
> stuff.) One comment on taint mode: it took a handful of changes to make
> it run cleanly in taint mode which is IMHO pretty impressive.
>
> Also, I still have to work out some kinks where it still looks for the
> .conf file by default (rather than the .ini file that Config::Tiny
> expects) but it's simple to derive an .ini file from the original .conf
> file with:
>
> sqlgreyng --configfile=/etc/sqlgrey/sqlgrey.conf --showconfig=minimal \
> | tee /etc/sqlgrey/sqlgrey.ini
>
> In my case, it creates a file consisting of:
>
> ----
> db_pass=******
> db_type=mysql
> optmethod=optout
> reject_code=dunno
> ----
>
> with all other options set to their defaults. Use the above command with
> --showconfig=full to get a file with all the available options (good for
> converting an existing .conf file to an .ini) and --showconfig=default
> to show only the default settings (good for creating your own first .ini
> file.)
>
> To run the script, I modified the Debian init script to call sqlgreyng as:
>
>     /usr/sbin/sqlgreyng --configfile=/etc/sqlgrey/sqlgrey.ini -d
>
> Other than that, it seems to be stable. I think for right now I'm going
> to focus on the identified issues, the TODO file, migrating pieces into
> a module (`h2xs -AXn` is your friend...) and seeing if Regexp::Optimizer
> can help speed things up.
>
> -- Bob
>
> [1] Like Net::CIDR, it's ignored if it's not installed. So far it only
> gives more accurate timing stats in cleanup().
>
> -------------------------------------------------------------------------
> Using Tomcat but need to do more? Need to support web services, security?
> Get stuff done quickly with pre-integrated technology to make your job easier
> Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
> _______________________________________________
> Sqlgrey-users mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users
>
>   

[Sqlgrey-users] Another small update

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

Hi,

I've run the refactored version for about a day now and I've cleaned up
a runtime warning and added support for Time::HiRes if it's available[1].

Things seem to be working properly. I have to fix the system() call in
sendmail() that's breaking in taint-mode and review the updated 'killing
spree' code (I suspect my quest for portable signal numbers broke some
stuff.) One comment on taint mode: it took a handful of changes to make
it run cleanly in taint mode which is IMHO pretty impressive.

Also, I still have to work out some kinks where it still looks for the
.conf file by default (rather than the .ini file that Config::Tiny
expects) but it's simple to derive an .ini file from the original .conf
file with:

sqlgreyng --configfile=/etc/sqlgrey/sqlgrey.conf --showconfig=minimal \
| tee /etc/sqlgrey/sqlgrey.ini

In my case, it creates a file consisting of:

----
db_pass=******
db_type=mysql
optmethod=optout
reject_code=dunno
----

with all other options set to their defaults. Use the above command with
--showconfig=full to get a file with all the available options (good for
converting an existing .conf file to an .ini) and --showconfig=default
to show only the default settings (good for creating your own first .ini
file.)

To run the script, I modified the Debian init script to call sqlgreyng as:

    /usr/sbin/sqlgreyng --configfile=/etc/sqlgrey/sqlgrey.ini -d

Other than that, it seems to be stable. I think for right now I'm going
to focus on the identified issues, the TODO file, migrating pieces into
a module (`h2xs -AXn` is your friend...) and seeing if Regexp::Optimizer
can help speed things up.

-- Bob

[1] Like Net::CIDR, it's ignored if it's not installed. So far it only
gives more accurate timing stats in cleanup().

[Sqlgrey-users] Some thoughts on new greylisting tests & changes to sqlgrey

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

Hi,

I've been using policyd (http://policyd.sourceforge.net/) as my
greylisting policy daemon for the past few years and it works pretty well.

I like policyd because it's effective, actively developed, and
reasonably stable, it has a pretty decent feature set, and the developer
is easy to get along with. Its major disadvantage is that it's written
in C which limits how much I can extend it.

An example of the kind of extension that I'd like to make is the ability
to generate a 'reputation' for a network block or autonomous system (AS;
a collection of networks managed by the same entity; these are important
for routing.)

To generate a reputation for an AS, we need a way to derive AS from IP
address, and the ability to track the unreturned connections, successful
deliveries and attempted spamtrap deliveries from IP addresses.
Conveniently, policy daemons can gather connection and delivery data and
the Net::Abuse::Utils module makes IP->ASN mapping easy. With a bit of
logging and some statistics we should be able to come up with a
reasonable metric for the spamminess of an AS and adjust our greylisting
appropriately. Or grab SpamAssassin scores from the mail logs and track
average score per AS.

Another case: suppose I trust SenderBase ratings and want to greylist
networks that deliver a lot of mail and don't have matching rDNS, etc.
Or even better - tail the outbound mail logs and whitelist
sender/recipient pairs, but reverse them so messages from people
responding to sent mail are not delayed.

See the code below for an example of how easy it is to get this sort of
external data. Note that like DNSBL lookups, it may not be effective for
high-volume sites to run these sort of checks on all inbound mail. You
can reduce the load of lookups by sampling a subset of inbound mail,
caching and memoizing, otherwise you can change the code to only do
lookups if a parameter is set in the config file.

I've spent the past week or so digging into the sqlgrey code and making
a bunch of pendantic changes based on perltidy and perlcritic[1],
migrating configuration info into Config::Tiny while providing a
migration path and backwards compatibility with the existing
configuration file[2], adding filesystem independence with File::Spec,
adding some taint checks, and slowly trying to refactor the code into a
module (Mail::Postfix::Policy::Sqlgrey?) and a small driver program.

My current changes are posted at
http://apthorpe.cynistar.net/code/sqlgrey/sqlgreyng for review &
comment. Ignore the 'ng' bit - that's just to differentiate it from the
existing code, not to denote a fork or anything like that. I believe
I've preserved all of the application's functionality but I can't
guarantee it works. One reason for migrating the code into a formal
module is to add a test suite to make sure everything gets tested before
adding new functionality.

Anyway, take a look at policyd and at my mods and let me know what you
think.

Thanks,

-- 
Bob

[1] Compared to a lot of perl scripts, sqlgrey is pretty sensible so
nobody should get insulted that I spent a few days cleaning it up with
perltidy & perlcritic. I find that I learn much faster by reading code
and the tedious edits needed to satisfy perlcritic make you read a lot.
Besides, "Perl Best Practices" has a lot of good advice; I've used it
(by way of perlcritic) to clean up a bunch of my own scripts.

[2] Why Config::Tiny? The module is small and fast, the file format is
very similar to the existing sqlgrey.conf file, it allows sections so
you don't have to encode lists in scalars ('log' and 'log_override'.)
Who wants to write their own config file parsing routines anyway? :)

---- Sample code ----
#!/usr/bin/perl
use strict;
use warnings;

use Carp;
use English qw(-no_match_vars);
use Net::Abuse::Utils qw(:all);
use Net::SenderBase;

if ( !@ARGV ) {
    croak "Usage: $PROGRAM_NAME #.#.#.#\n";
}

my $addr = $ARGV[0];

my $report = "Looking up $addr:\n";

# Get ASN (routing) info
$report .= "ASN info>\n" . join( q{:}, get_asn_info($addr) ) . "\n";

# Get SenderBase mail stats
my $q = Net::SenderBase::Query->new(
    Transport => 'dns',
    Address   => $addr
);

my $r = $q->results();

$report .= "SenderBase info>\n" . $r->raw_data . "\n";

print $report;

exit 0;

---- Sample Output ----

Looking up 80.98.117.76:
ASN info>
6830:80.98.0.0/17:HU:ripencc:2001-07-16
SenderBase info>
0=1|1=UPC Magyarorszag
Kft.|2=7.1|3=6.6|4=482313|6=1057244176|7=106|8=221184|9=3650|45=N|46=15|48=24|49=1.00|50=Budapest|51=05|53=HU|54=19.0833|55=47.5

Re: [Sqlgrey-users] Output from Starting Sqlgrey

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

Yes.. My bad :)..

The messages are totally harmless.. I just haven't found a slick way to 
get rid of them yet :(..

So ill use this opportunity to make a short shoutout:
I am open to suggestions from any perl mongers out there.
The "..used only once: possible typo at..." happens because DBI is used 
by default, but sqlgrey is "ready" for "DBCluster".
However.. When DBCluster isnt loaded, the setting to DBCluster generate 
these funky looking errors.
 (and because warnings are enabled of course).
Anyone know a funky trick? Or do i actually have to create dummy lines 
that uses these vars to shut perl up?

- Dan

Ray Booysen wrote:
> Upgraded SQL grey to 1.74 and I am getting this on starting SQLGrey
>
>  * Starting SQLgrey ...
> Name "DBIx::DBCluster::DEBUG" used only once: possible typo at 
> /usr/sbin/sqlgrey line 2413.
> Name "DBIx::DBCluster::WRITE_HOSTS_NEVER_READ" used only once: possible 
> typo at /usr/sbin/sqlgrey line 827.
> Name "DBIx::DBCluster::CLUSTERS" used only once: possible typo at 
> /usr/sbin/sqlgrey line 818.             
>
> Any ideas?
>
> Regards
> Ray
>
>   

[Sqlgrey-users] Output from Starting Sqlgrey

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

Upgraded SQL grey to 1.74 and I am getting this on starting SQLGrey

 * Starting SQLgrey ...
Name "DBIx::DBCluster::DEBUG" used only once: possible typo at 
/usr/sbin/sqlgrey line 2413.
Name "DBIx::DBCluster::WRITE_HOSTS_NEVER_READ" used only once: possible 
typo at /usr/sbin/sqlgrey line 827.
Name "DBIx::DBCluster::CLUSTERS" used only once: possible typo at 
/usr/sbin/sqlgrey line 818.             

Any ideas?

Regards
Ray

-- 
Ray Booysen
ray...@rj...

Re: [Sqlgrey-users] prepared cache still active?

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

On Thu, 2006-10-05 at 18:33 +0200, Dan Faerch wrote:
> Hi Daniel.
> 
> 
> Daniel J McDonald wrote:
> > Oct  3 16:44:38 sa sqlgrey: warning: prepare_cached(SELECT 1 FROM
> > optout_domain WHERE domain = ?) statement handle DBI::st=HASH(0x853e7b4)
> > still Active at /usr/sbin/sqlgrey line 246 
> You probably have the buggy version of DBD::mysql.
> 
> See http://sourceforge.net/mailarchive/message.php?msg_id=36332668
> (its the mailing list archive of this list)
> 
> The last person who had this problem, downgraded the DBD::mysql and it 
> worked for him..

Looks like it was fixed in 3.0007; there was a release note about it, so
I tried an upgrade rather than a downgrade.

It appears to be working.  Thanks for the tip!


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

Re: [Sqlgrey-users] prepared cache still active?

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

Hi Daniel.


Daniel J McDonald wrote:
> Oct  3 16:44:38 sa sqlgrey: warning: prepare_cached(SELECT 1 FROM
> optout_domain WHERE domain = ?) statement handle DBI::st=HASH(0x853e7b4)
> still Active at /usr/sbin/sqlgrey line 246 
You probably have the buggy version of DBD::mysql.

See http://sourceforge.net/mailarchive/message.php?msg_id=36332668
(its the mailing list archive of this list)

The last person who had this problem, downgraded the DBD::mysql and it 
worked for him..

Hope this helps.
- Dan

Re: [Sqlgrey-users] Memoryleak in sqlgrey 1.6.7

[Steve H. <st...@th...>]

On Tue, 2006-09-12 at 21:46 +0200, Lionel Bouton wrote:


> Hum. Even with low traffic, I can see DBD-Pg 1.49 leaking on my system 
> too (and clearly the PostgreSQL process is growing, which makes me think 
> that there are server-side prepared statements being leaked by DBD-Pg). 


We have two servers  running sqlgrey and sharing a database.
One has a DBD-Pg version of   1.22 and the other has 1.49.
The one with 1.49 is the m/c that runs the postgresql database

We dont see the postgresql process  growing too much.

Load is about  7500 email per day shared across the two servers.


Steve

-- 
thorNET
Internet Services, Consultancy & Training
www.thornet.co.uk

[Sqlgrey-users] prepared cache still active?

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

What does this warning mean?  I get thousands of them a day on a
relatively new installation of sqlgrey 1.6.6:
Oct  3 16:44:38 sa sqlgrey: warning: prepare_cached(SELECT 1 FROM
optout_domain WHERE domain = ?) statement handle DBI::st=HASH(0x853e7b4)
still Active at /usr/sbin/sqlgrey line 246 


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

Re: [Sqlgrey-users] Memoryleak in sqlgrey 1.6.7

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

Casey Feskens wrote the following on 12.09.2006 16:25 :
> [...]
> For what it's worth, after reading through a bunch of DBD-Pg
> release notes and noticing all the memory leaks fixed in the 1.4x
> series, I rolled back to DBD-Pg 1.32.  Since then we've run
> nearly a day now without any gain in process size. 
>
>   

Hum. Even with low traffic, I can see DBD-Pg 1.49 leaking on my system 
too (and clearly the PostgreSQL process is growing, which makes me think 
that there are server-side prepared statements being leaked by DBD-Pg). 
At least SQLgrey is stable... Too bad the driver is not on par with the 
database.

I'll lurk around mailing-lists to see if the problem is known by the 
developper(s).

Lionel.

Re: [Sqlgrey-users] Memoryleak in sqlgrey 1.6.7

[Casey F. <cfe...@wi...>]

Lionel Bouton wrote:
> Casey Feskens wrote the following on 11.09.2006 20:30 :
>   
>> We've been testing an implementation of sqlgrey 1.6.7 and have
>> come across a memory leak fairly rapidly.  Under our current
>> load (30,000 msgs/day, 6000 users), sqlgrey's heap seems to be
>> growing by about 1MB every 5 minutes.
>>
>> Another copy of sqlgrey not actively in use does not seem to be
>> gaining any memory, so it looks like this issue only occurs when
>> the daemon is in active use.
>>
>> I noticed a previous thread on this list about memory leaks in
>> 1.6.7, but no resolution.  Has anyone else seen this:
>>
>> Our configuration:
>>
>> Solaris 9
>> Postfix 2.2.10
>> Perl 5.8.4
>> Sqlgrey 1.6.7
>>
>> Thanks for any help you may be able to provide.
>>
>>   
>>     
>
> I recently had an unrelated problem with DBD-Pg and investigated the 
> "Changes" files of DBI (Perl's database API) and DBD-Pg (Perl DBI's 
> PostgreSQL driver) by downloading the latest versions available from 
> CPAN. I've spotted reports of memory leaks fixed in DBI 1.52 (latest 
> available) and DBD-Pg 1.44 (current is 1.49, which, for me, solved a 
> prepared statement problem when DBD-Pg was compiled with GCC 4.1.1). You 
> should check your DBI version and if you use PostgreSQL, please check 
> DBD-Pg's version too.
>
> That said, given the simple algorithms used it's highly unlikely that 
> SQLgrey would suffer from memory leaks (you have to do pretty fancy 
> stuff to make Perl lost track of the memory it should free). I believe 
> most of the currently reported problems with 1.6.x are either bugs in 
> DBI or the DBD driver, which actually do pretty fancy stuff :-)
>
> Lionel.
>
>
>   
For what it's worth, after reading through a bunch of DBD-Pg
release notes and noticing all the memory leaks fixed in the 1.4x
series, I rolled back to DBD-Pg 1.32.  Since then we've run
nearly a day now without any gain in process size. 

-- 
---------------------------------------------
Casey Feskens <cfe...@wi...>
System Administrator/Network Svcs. Consultant
Willamette Integrated Technology Services
Willamette University, Salem, OR
---------------------------------------------

Re: [Sqlgrey-users] Memoryleak in sqlgrey 1.6.7

[Dan M. <dan...@da...>]

On Mon, Sep 11, 2006 at 08:44:57PM -0600, Andrew Diederich wrote:
> Looks like I have perl-DBI-1.48-2, and I found DBD-Pg-1.47 
> in my .cpan directory, though no-where else, so I'm not even sure if
> I'm using DBD-Pg.

Andrew,

To determine which DBD you are using, just check the sqlgrey.conf.

For example:

  muratore [~] > grep ^db_type /usr/local/etc/sqlgrey/sqlgrey.conf
  db_type = mysql


Dan

-- 
|     Daniel R Mason      |   "An ignorant people is   |
|  dan...@da...  |   the blind instrument of  |
|     www.danmason.net    |    its own destruction."   |
|  Unix Systems Engineer  |  Simon Bolivar, Liberator  |

Re: [Sqlgrey-users] Memoryleak in sqlgrey 1.6.7

[Andrew D. <and...@gm...>]

Hello Casey,

Monday, September 11, 2006, 2:14:51 PM, Casey Feskens wrote:

> Lionel Bouton wrote:
>> I recently had an unrelated problem with DBD-Pg and investigated the
>> "Changes" files of DBI (Perl's database API) and DBD-Pg (Perl DBI's 
>> PostgreSQL driver) by downloading the latest versions available from 
>> CPAN. I've spotted reports of memory leaks fixed in DBI 1.52 (latest 
>> available) and DBD-Pg 1.44 (current is 1.49, which, for me, solved a 
>> prepared statement problem when DBD-Pg was compiled with GCC 4.1.1). You 
>> should check your DBI version and if you use PostgreSQL, please check 
>> DBD-Pg's version too.

> Thanks, for the tip.  We are running DBI 1.52 and DBD-Pg 1.49.
> I'm going to do some further profiling and see what I find out.

I use sqlgrey  on a SuSE 10.0 box with postgres, and I've seen the
postmaster leak.  If I restart sqlgrey the memory in postmaster
releases.  Looks like I have perl-DBI-1.48-2, and I found DBD-Pg-1.47
in my .cpan directory, though no-where else, so I'm not even sure if
I'm using DBD-Pg.

-- 
Best regards,
 Andrew                            mailto:and...@gm...

Re: [Sqlgrey-users] Memoryleak in sqlgrey 1.6.7

[Casey F. <cfe...@wi...>]

Lionel Bouton wrote:
> Casey Feskens wrote the following on 11.09.2006 20:30 :
>   
>> We've been testing an implementation of sqlgrey 1.6.7 and have
>> come across a memory leak fairly rapidly.  Under our current
>> load (30,000 msgs/day, 6000 users), sqlgrey's heap seems to be
>> growing by about 1MB every 5 minutes.
>>
>> Another copy of sqlgrey not actively in use does not seem to be
>> gaining any memory, so it looks like this issue only occurs when
>> the daemon is in active use.
>>
>> I noticed a previous thread on this list about memory leaks in
>> 1.6.7, but no resolution.  Has anyone else seen this:
>>
>> Our configuration:
>>
>> Solaris 9
>> Postfix 2.2.10
>> Perl 5.8.4
>> Sqlgrey 1.6.7
>>
>> Thanks for any help you may be able to provide.
>>
>>   
>>     
>
> I recently had an unrelated problem with DBD-Pg and investigated the 
> "Changes" files of DBI (Perl's database API) and DBD-Pg (Perl DBI's 
> PostgreSQL driver) by downloading the latest versions available from 
> CPAN. I've spotted reports of memory leaks fixed in DBI 1.52 (latest 
> available) and DBD-Pg 1.44 (current is 1.49, which, for me, solved a 
> prepared statement problem when DBD-Pg was compiled with GCC 4.1.1). You 
> should check your DBI version and if you use PostgreSQL, please check 
> DBD-Pg's version too.
>
> That said, given the simple algorithms used it's highly unlikely that 
> SQLgrey would suffer from memory leaks (you have to do pretty fancy 
> stuff to make Perl lost track of the memory it should free). I believe 
> most of the currently reported problems with 1.6.x are either bugs in 
> DBI or the DBD driver, which actually do pretty fancy stuff :-)
>
>   

Thanks, for the tip.  We are running DBI 1.52 and DBD-Pg 1.49.
I'm going to do some further profiling and see what I find out.

-- 
---------------------------------------------
Casey Feskens <cfe...@wi...>
System Administrator/Network Svcs. Consultant
Willamette Integrated Technology Services
Willamette University, Salem, OR
---------------------------------------------

Re: [Sqlgrey-users] Memoryleak in sqlgrey 1.6.7

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

Casey Feskens wrote the following on 11.09.2006 20:30 :
> We've been testing an implementation of sqlgrey 1.6.7 and have
> come across a memory leak fairly rapidly.  Under our current
> load (30,000 msgs/day, 6000 users), sqlgrey's heap seems to be
> growing by about 1MB every 5 minutes.
>
> Another copy of sqlgrey not actively in use does not seem to be
> gaining any memory, so it looks like this issue only occurs when
> the daemon is in active use.
>
> I noticed a previous thread on this list about memory leaks in
> 1.6.7, but no resolution.  Has anyone else seen this:
>
> Our configuration:
>
> Solaris 9
> Postfix 2.2.10
> Perl 5.8.4
> Sqlgrey 1.6.7
>
> Thanks for any help you may be able to provide.
>
>   

I recently had an unrelated problem with DBD-Pg and investigated the 
"Changes" files of DBI (Perl's database API) and DBD-Pg (Perl DBI's 
PostgreSQL driver) by downloading the latest versions available from 
CPAN. I've spotted reports of memory leaks fixed in DBI 1.52 (latest 
available) and DBD-Pg 1.44 (current is 1.49, which, for me, solved a 
prepared statement problem when DBD-Pg was compiled with GCC 4.1.1). You 
should check your DBI version and if you use PostgreSQL, please check 
DBD-Pg's version too.

That said, given the simple algorithms used it's highly unlikely that 
SQLgrey would suffer from memory leaks (you have to do pretty fancy 
stuff to make Perl lost track of the memory it should free). I believe 
most of the currently reported problems with 1.6.x are either bugs in 
DBI or the DBD driver, which actually do pretty fancy stuff :-)

Lionel.

[Sqlgrey-users] Memoryleak in sqlgrey 1.6.7

[Casey F. <cfe...@wi...>]

We've been testing an implementation of sqlgrey 1.6.7 and have
come across a memory leak fairly rapidly.  Under our current
load (30,000 msgs/day, 6000 users), sqlgrey's heap seems to be
growing by about 1MB every 5 minutes.

Another copy of sqlgrey not actively in use does not seem to be
gaining any memory, so it looks like this issue only occurs when
the daemon is in active use.

I noticed a previous thread on this list about memory leaks in
1.6.7, but no resolution.  Has anyone else seen this:

Our configuration:

Solaris 9
Postfix 2.2.10
Perl 5.8.4
Sqlgrey 1.6.7

Thanks for any help you may be able to provide.

-- 
---------------------------------------------
Casey Feskens <cfe...@wi...>
System Administrator/Network Svcs. Consultant
Willamette Integrated Technology Services
Willamette University, Salem, OR
---------------------------------------------

Re: [Sqlgrey-users] time in logs

[Steve H. <st...@th...>]

On Sat, 2006-09-09 at 13:51 +0200, Lionel Bouton wrote:

> Steve Heaven wrote the following on 06.09.2006 13:56 :
> > We have just installed sqlgrey1.7.1 on a new server.
> > It is working OK but the times in the logs are 5 hours behind the real 
> > time.
> > Is this a timezone problem? If so where does sqlgrey get it's timezone 
> > info from ?
> 
> SQLgrey uses syslog for logging. So the timestamps should be whatever 
> your syslog daemon thinks the time is (SQLgrey doesn't put any timestamp 
> in the logs, only the delays it introduces for some e-mails).
> 


The strange thing was that postfix, dovecot, sqlgrey & spamd  all log
to /var/log/maillog, but only sqlgrey was getting the time wrong.

I think it must have been a permissions thing. Postfix runs as user
'postfix', dovecot as 'dovecot', spamd as 'filter' and sqlgrey as
'sqlgrey'. All the other users were members of the group 'mail'. I made
sqlgrey a member of 'mail',  restarted sqlgrey and the time is now
logged correctly.

Steve


-- 
thorNET
Internet Services, Consultancy & Training
www.thornet.co.uk

Re: [Sqlgrey-users] time in logs

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

Steve Heaven wrote the following on 06.09.2006 13:56 :
> We have just installed sqlgrey1.7.1 on a new server.
> It is working OK but the times in the logs are 5 hours behind the real 
> time.
> Is this a timezone problem? If so where does sqlgrey get it's timezone 
> info from ?

SQLgrey uses syslog for logging. So the timestamps should be whatever 
your syslog daemon thinks the time is (SQLgrey doesn't put any timestamp 
in the logs, only the delays it introduces for some e-mails).

Lionel.

[Sqlgrey-users] time in logs

[Steve H. <st...@th...>]

We have just installed sqlgrey1.7.1 on a new server.
It is working OK but the times in the logs are 5 hours behind the real
time.
Is this a timezone problem? If so where does sqlgrey get it's timezone
info from ?

Thanks

Steve

-- 
thorNET
Internet Services, Consultancy & Training
www.thornet.co.uk

Re: [Sqlgrey-users] VERP issues

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

Hi Andrew,

implementing deverp_user is a very delicate thing. You want to catch most
of the verp-style addresses. On the other hand you do not want to
eliminate random chars which spammers put in their addresses to circumvene
filters.

Your first example indeed shows a case where a non-verp address was hit.
But that does not matter. The most you will need a few more entries in
your database for this sender_name. And this case is rare. On the other
hand, the reqex will match a lot of verb-addresses, not only BATV
ones.

The change you propose for your second example would return

   grbounce-#

instead of

   grbounce-#-RCPT or grbounce-#=RCPT

what I would expect. That means you discovered a new class of VERB
addresses, which should be handled separately.

On Fri, 11 Aug 2006, Andrew Findlay wrote:

> I am testing sqlgrey and have come across a problem with the
> deverp_user routine. It is missing some obvious VERP addresses and is
> messing up some obvious non-VERP ones.
>
> Here are some examples:
>
> perl -d sqlgrey.dist
> ...
>   DB<1> print
> deverp_user('bounce-32993-4906286','and...@sk...');
> bounce-#
>
> That shows what it *should* be doing. Now for some problems:
>
>   DB<2> print
> deverp_user('grbounce-JbS8DwUAAAC4pJBj3F9adwKL1ZerRRc_=andrew.findlay=skills-1st.co.uk', 'and...@sk...');
> grbounce-JbS8DwUAAAC4pJBj3F9adwKL1ZerRRc_=RCPT
>
> The source here is a Google alert service.
>
>   DB<3> print deverp_user('andrew.findlay','an...@ex...')
> RCPT.findlay
>   DB<5> print deverp_user('andrew.findlay','drew@x.y')
> anRCPT.findlay
>
> Here we see a very simple sender address being messed up by an even
> simpler sender address.
>
> The appended patch for 1.7.3 fixes both issues, but may prevent the
> correct de-VERPing of BATV addresses. I have not found any in my logs
> that match draft-levine-mass-batv-02, but it would seem fairly easy to
> recognise the simple 'prvs' scheme reliably.
>
> I think it may be best to change deverp_user to use one simple explicit
> pattern for each known VERP style rather than try to build clever
> regexes to match several at once.
>
> Andrew
>

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

[Sqlgrey-users] VERP issues

[Andrew F. <and...@sk...>]

I am testing sqlgrey and have come across a problem with the
deverp_user routine. It is missing some obvious VERP addresses and is
messing up some obvious non-VERP ones.

Here are some examples:

perl -d sqlgrey.dist
...
  DB<1> print
deverp_user('bounce-32993-4906286','and...@sk...');
bounce-#

That shows what it *should* be doing. Now for some problems:

  DB<2> print
deverp_user('grbounce-JbS8DwUAAAC4pJBj3F9adwKL1ZerRRc_=andrew.findlay=skills-1st.co.uk', 'and...@sk...');
grbounce-JbS8DwUAAAC4pJBj3F9adwKL1ZerRRc_=RCPT

The source here is a Google alert service.

  DB<3> print deverp_user('andrew.findlay','an...@ex...')
RCPT.findlay
  DB<5> print deverp_user('andrew.findlay','drew@x.y')
anRCPT.findlay

Here we see a very simple sender address being messed up by an even
simpler sender address.

The appended patch for 1.7.3 fixes both issues, but may prevent the
correct de-VERPing of BATV addresses. I have not found any in my logs
that match draft-levine-mass-batv-02, but it would seem fairly easy to
recognise the simple 'prvs' scheme reliably.

I think it may be best to change deverp_user to use one simple explicit
pattern for each known VERP style rather than try to build clever
regexes to match several at once.

Andrew
-- 
-----------------------------------------------------------------------
|                 From Andrew Findlay, Skills 1st Ltd                 |
| Consultant in large-scale systems, networks, and directory services |
|     http://www.skills-1st.co.uk/                +44 1628 782565     |
-----------------------------------------------------------------------
--- sqlgrey.dist	Thu Aug 10 13:22:04 2006
+++ sqlgrey	Fri Aug 11 11:46:08 2006
@@ -1005,14 +1005,15 @@
 
     # build pattern with the 3 alternatives to match recipient in originator
     # BATV implementations use third or first alternative (first by abuse.net)
-    my $pat = qr/$rcpt_lhs$at_sep_re$rcpt_rhs|$rcpt_rhs$at_sep_re$rcpt_lhs|$rcpt_lhs/;
+    # (removed third pattern - too simple)
+    my $pat = qr/$rcpt_lhs$at_sep_re$rcpt_rhs|$rcpt_rhs$at_sep_re$rcpt_lhs/;
 
     # replace address with capital RCPT to be safe with deletes
     # (MySQL matches case insensitive unfortunately)
     $user =~ s/(?<=[\*=\.-])$pat|$pat(?=[\*=\.-])/RCPT/;
 
     # strip frequently used bounce/return masks
-    $user =~ s/((bo|bounce|notice-return|notice-reply)[\._-])[0-9a-z-_\.]+$/$1#/g; # Added by JR
+    $user =~ s/((bo|bounce|notice-return|notice-reply)[\._-])[0-9a-zA-Z=_\.-]+$/$1#/g; # Added by JR
 
     # strip hexadecimal sequences
     # at the beginning only if user will contain at least 4 consecutive alpha chars

[Sqlgrey-users] statistics?

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

I've been piloting SQLGrey 1.6.6 with a dozen or so people, and it looks
like it is working.  But I'd like to get some formal, repeatable
statistics.  Before I start re-inventing the wheel and hacking up
amavis-stats to report the SQLGrey stats as well, what do most people
use to generate grey-listing statistics?


--=20
Daniel J McDonald, CCIE #2495, CNX, CISSP #78281
Austin Energy

gpg Key: http://austinnetworkdesign.com/pgp.key
Key fingerprint =3D B527 F53D 0C8C D38B DCC7  901D 2F19 A13A 22E8 A76A

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

[Steve P. <st...@co...>]

1. perl version 5.8.8, built for Gentoo linux.
2. Checksum is correct.
3. db_cluster is explicitly set to 'off'. Commenting the value  
completely also results in the warnings.

-Steve


On Aug 3, 2006, at 12:00 PM, Dan Faerch wrote:

> Looking at the source i can see i already tried to avoid theese  
> warnings
> and i cant reproduce them. So before i go nuts over this, i was hoping
> you could supply me with some info :)
>
> Could you tell me what version of perl youre running and verify  
> that md5
> of sqlgrey is:
> c4ae584b13ad655f9ed33edccf486d60
> and check that db_cluster in sqlgrey.conf is NOT set to 'on'
>
>
> - Dan
>
>
>
>
> Steve Pellegrin wrote:
>> Thanks for the update, Dan.
>>
>> When I run 1.7.4 *without* clustering, I get the following messages
>> on start-up:
>>
>> Name "DBIx::DBCluster::DEBUG" used only once: possible typo at /usr/
>> sbin/sqlgrey line 2413.
>> Name "DBIx::DBCluster::WRITE_HOSTS_NEVER_READ" used only once:
>> possible typo at /usr/sbin/sqlgrey line 827.
>> Name "DBIx::DBCluster::CLUSTERS" used only once: possible typo at /
>> usr/sbin/sqlgrey line 818.
>>
>> sqlgrey seems to run OK after this, but I imagine you will want to
>> investigate.
>>
>> -Steve
>>
>>
>> On Aug 2, 2006, at 8:48 PM, Dan Faerch wrote:
>>
>>
>>> sqlgrey 1.7.4 has just been released.
>>>
>>
>>
>> --------------------------------------------------------------------- 
>> ----
>> Take Surveys. Earn Cash. Influence the Future of IT
>> Join SourceForge.net's Techsay panel and you'll get the chance to  
>> share your
>> opinions on IT & business topics through brief surveys -- and earn  
>> cash
>> http://www.techsay.com/default.php? 
>> page=join.php&p=sourceforge&CID=DEVDEV
>> _______________________________________________
>> Sqlgrey-users mailing list
>> Sql...@li...
>> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users
>>
>>
>
>
> ---------------------------------------------------------------------- 
> ---
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to  
> share your
> opinions on IT & business topics through brief surveys -- and earn  
> cash
> http://www.techsay.com/default.php? 
> page=join.php&p=sourceforge&CID=DEVDEV
> _______________________________________________
> Sqlgrey-users mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users
>

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

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

Looking at the source i can see i already tried to avoid theese warnings 
and i cant reproduce them. So before i go nuts over this, i was hoping 
you could supply me with some info :)

Could you tell me what version of perl youre running and verify that md5 
of sqlgrey is:
c4ae584b13ad655f9ed33edccf486d60
and check that db_cluster in sqlgrey.conf is NOT set to 'on'


- Dan




Steve Pellegrin wrote:
> Thanks for the update, Dan.
>
> When I run 1.7.4 *without* clustering, I get the following messages  
> on start-up:
>
> Name "DBIx::DBCluster::DEBUG" used only once: possible typo at /usr/ 
> sbin/sqlgrey line 2413.
> Name "DBIx::DBCluster::WRITE_HOSTS_NEVER_READ" used only once:  
> possible typo at /usr/sbin/sqlgrey line 827.
> Name "DBIx::DBCluster::CLUSTERS" used only once: possible typo at / 
> usr/sbin/sqlgrey line 818.
>
> sqlgrey seems to run OK after this, but I imagine you will want to  
> investigate.
>
> -Steve
>
>
> On Aug 2, 2006, at 8:48 PM, Dan Faerch wrote:
>
>   
>> sqlgrey 1.7.4 has just been released.
>>     
>
>
> -------------------------------------------------------------------------
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to share your
> opinions on IT & business topics through brief surveys -- and earn cash
> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
> _______________________________________________
> Sqlgrey-users mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users
>
>   

Re: [Sqlgrey-users] multiple admin_mail possible?

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

I checked the source code and it seems this would be the way to do it:

adm...@do...,ad...@do...

Note there are NO spaces..

- Dan


Internet Helpdesk wrote:
> If I want more than one email address for admin_mail in sqlgrey.conf how 
> is this done properly?
>
> Can I comma separate them like:
> adm...@do..., ad...@do... ?
>
>