From: Dan M. <dan...@da...> - 2006-04-25 19:57:05
Attachments:
sqlgrey-update.pl
|
Attached is a Perl script to replace sqlgrey's "update_sqlgrey_config". The sqlgrey included "update_sqlgrey_config" is dependent on bash, wget, and md5sum. Instead, my attached Perl script uses Digest::MD5 and LWP::UserAgent. Pick your poison. :) This Perl script is of course distributed under the terms of the GNU General Public License. I'd appreciate comments or criticism! Dan -- A solution can be technically correct given certain assumptions, but if it ignores known details, it is not worthy of discussion. Maybe as technologists we want so much to believe that every problem has a solution, that sometimes we overlook the obvious. Maybe there are so many details to consider that we get confused. Maybe after a while we just stop caring. --Unknown |
From: Dan M. <dan...@da...> - 2006-04-25 20:37:50
Attachments:
sqlgrey-update.pl
|
On Tue, Apr 25, 2006 at 02:57:02PM -0500, Dan Mason wrote: > Attached is a Perl script to replace sqlgrey's "update_sqlgrey_config". Sorry for following up to my own post, but I saw a post-lunch error. The error did not inhibit the script's functionality, but it bothered me. Anyhow, the script is attached. Dan -- A solution can be technically correct given certain assumptions, but if it ignores known details, it is not worthy of discussion. Maybe as technologists we want so much to believe that every problem has a solution, that sometimes we overlook the obvious. Maybe there are so many details to consider that we get confused. Maybe after a while we just stop caring. --Unknown |
From: Steve H. <st...@th...> - 2006-05-09 08:21:40
|
We have been running v 1.7.1 for several months with no problems. However yesterday the sqlgrey daemon died twice with this in the logs: May 8 14:48:17 frigga sqlgrey: fatal: Quantifier follows nothing in regex; marked by <-- HERE in m/* <-- HERE scole[=\?\*~\.]+?smtp1[.*-]+thornet[.*-]+co[.*-]+uk|smtp1[.*-]+thornet[.*-]+co[.*-]+uk[=\?\*~\.]+?*scole|*scole/ at /usr/sbin/sqlgrey line 997. May 8 16:57:00 frigga sqlgrey: fatal: Quantifier follows nothing in regex; marked by <-- HERE in m/* <-- HERE scole[=\?\*~\.]+?smtp1[.*-]+thornet[.*-]+co[.*-]+uk|smtp1[.*-]+thornet[.*-]+co[.*-]+uk[=\?\*~\.]+?*scole|*scole/ at /usr/sbin/sqlgrey line 997. Does anyone know why ? Thanks Steve -- thorNET Internet Services, Consultancy & Training www.thornet.co.uk |
From: Steve H. <st...@th...> - 2006-05-09 09:25:15
|
It's now done it twice more: May 9 09:05:50 frigga sqlgrey: fatal: Quantifier follows nothing in regex; marked by <-- HERE in m/* <-- HERE scole[=\?\*~\.]+?smtp1[.*-]+thornet[.*-]+co[.*-]+uk|smtp1[.*-]+thornet[.*-]+co[.*-]+uk[=\?\*~\.]+?*scole|*scole/ at /usr/sbin/sqlgrey line 997. May 9 10:09:46 frigga sqlgrey: fatal: Quantifier follows nothing in regex; marked by <-- HERE in m/* <-- HERE scole[=\?\*~\.]+?smtp1[.*-]+thornet[.*-]+co[.*-]+uk|smtp1[.*-]+thornet[.*-]+co[.*-]+uk[=\?\*~\.]+?*scole|*scole/ at /usr/sbin/sqlgrey line 997. Does anyone know what's causing this? Thanks Steve -- thorNET Internet Services, Consultancy & Training www.thornet.co.uk |
From: Steve H. <st...@th...> - 2006-05-09 09:33:45
|
I think I have found it. After each 'fatal' log entry is one like this: May 9 09:05:51 frigga postfix/smtpd[10624]: NOQUEUE: reject: RCPT from nt03araq.keele.netcentral.co.uk[212.57.235.2]: 450 Server configuration problem; from=<He...@re...> to=<*sc...@sm...> proto=ESMTP helo=<nt03araq.keele.netcentral.co.uk> Note the recipient address of *sc...@sm.... It looks like the * in the user part is causing the problem Can someone post a patch to filter out invalid chars from the recipient address. -- thorNET Internet Services, Consultancy & Training www.thornet.co.uk |
From: Dan F. <da...@ha...> - 2006-05-09 18:13:55
|
Steve Heaven wrote: > Can someone post a patch to filter out invalid chars from the recipient > address. > I downloaded 1.7 to make a patch for you, when i noticed that 1.7.3 actually seem to correct handling of this issue.. Maybe upgrading to 1.7.3 an isnt an option for you? If not, then ive made a quick fix for you against 1.7.1: --- sqlgrey.org 2006-05-09 20:12:12.000000000 +0200 +++ sqlgrey 2006-05-09 20:12:37.000000000 +0200 @@ -994,7 +994,7 @@ $rcpt =~ s/\./\[\.\*-\]+/g; my ($rcpt_lhs, $rcpt_rhs) = split /\@/, $rcpt, 2; # BATV implementations use third or first alternative (first by abuse.net) - my $pat = qr/$rcpt_lhs[=\?\*~\.]+?$rcpt_rhs|$rcpt_rhs[=\?\*~\.]+?$rcpt_lhs|$rcpt_lhs/; + my $pat = qr/\Q$rcpt_lhs\E[=\?\*~\.]+?\Q$rcpt_rhs\E|\Q$rcpt_rhs\E[=\?\*~\.]+?\Q$rcpt_lhs\E|\Q$rcpt_lhs\E/; # replace address with capital RCPT to be save with deletes $user =~ s/(.+[\*=-])$pat(.*)/$1RCPT$2/; |
From: Steve H. <st...@th...> - 2006-09-06 12:21:11
|
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 |
From: Dave S. <dst...@ma...> - 2006-05-30 18:17:07
|
Good to know the stats - thanks. We run about 2.5 million records in our = connect table here. Sounds like MySQL is working well for you, and that's = a good tip. It may be better at handing large amount of 'change' data than = Postgres. =20 Postgres has a maintenance routine called a 'Vacuum' that needs to be run = on all DBs as its not good at maintaining itself. These can be automated, = but only in the latest versions. =20 Two followup questions: 1. What table format do you use? MyISAM or InnoDB ? 2. Do you run any sort of maintenance on the SQLGrey tables? =20 Thanks, =20 Dave Strickler MailWise LLC 617-933-5810 (direct) www.mailwise.com ( http://www.mailwise.com/ ) "Intelligent E-mail Protection" >>> Dan Faerch <da...@ha...> 12:41 PM Tuesday, May 30, 2006 >>> I dont know what "vacuumed" is, since i've never used Postgres. But i do run MySQL, and on my setup we have around 20 INSERT's a second=20 during daytime on each server, and i dont have to do any maintenance at=20 all.. The connect table on a random server at this very moment contains=20 131.081 records. It has 42mb of data and an "overhead" of 31mb. One could run "Optimize" to remove the overhead, but it doesnt matter=20 since its just unused, pre-alloceted diskspace for the table. (the=20 table-files dont auto-shrink on "delete" operations) The space will be mostly re-used tomorrow at time-prime as new records=20 are added faster, so basically MySQL seems to find a good size for you=20 table-file and works within that, saving the server for at lot of=20 resizing operations. - Dan Faerch Dave Strickler wrote: > Has anyone had experience in a large email volume shop, going from one = DB format to the other? I find the connect table needs to be vacuumed = daily under PostgreSQL. Are there such maintenance "problems" under MySQL = ? > =20 > =20 > =20 > Dave Strickler > MailWise LLC > 617-933-5810 (direct) > www.mailwise.com ( http://www.mailwise.com/ ) > "Intelligent E-mail Protection" > > > > > ---------------------------- > This message has been certified virus-free by MailWise Filter - The = real-time, intelligent, e-mail firewall used to scan inbound and outbound = messages for SPAM, Viruses and Content. For more info visit: http://www.ma= ilwise.com ( http://www.mailwise.com/ )=20 > =20 ------------------------------------------------------- All the advantages of Linux Managed Hosting--Without the Cost and Risk! Fully trained technicians. The highest number of Red Hat certifications in the hosting industry. Fanatical Support. Click to learn more http://sel.as ( http://sel.as/ )-us.falkag.net/sel?cmd=3Dlnk&kid=3D107521&b= id=3D248729&dat=3D121642 _______________________________________________ Sqlgrey-users mailing list Sql...@li...=20 https://lists.sourceforge.net/lists/listinfo/sqlgrey-users ---------------------------- This message has been certified virus-free by MailWise Filter - The real-time, intelligent, e-mail firewall used to scan inbound and outbound messages for SPAM, Viruses and Content. For more info visit: http://www.mailwise.com |
From: Dan F. <da...@ha...> - 2006-05-30 19:24:21
|
Dave Strickler wrote: > Two followup questions: > 1. What table format do you use? MyISAM or InnoDB ? > We use MyISAM and i believe it has become a pretty good table format.. Eg. our logservers each handle just below 2 gigs of data a day in one table and we never get messed up tables or other problems. > 2. Do you run any sort of maintenance on the SQLGrey tables? > > Nope.. The only maintenance done, is the build-in cleanup routines in SQLGrey (cleaning of old entries and such). We also run postfix-transports and userlists, and a spam/virus-quarantine system for all accounts on the same MySQL that runs SQLGrey on each box and MySQL has never let me down. Its lovely to have a system that just hums away in the corner ;) The only bad experience i have with MySQL is related to things like poweroutages or eg. a kernel-panic or the likes. That can mess up the table a bit, but MySQL comes with a nice little tool called myisamchk that usually repairs that without problems. And in my case, we started clustering our SQL's for the mailsystem using MySQL replication, after i added SQL-clustering support to SQLGrey. So should 1 database or table be lost, i can simply replicate it from another server without loosing 1 single record. |
From: Ray B. <rj_...@rj...> - 2006-05-31 07:32:17
|
Dan Faerch wrote: > Dave Strickler wrote: >> Two followup questions: >> 1. What table format do you use? MyISAM or InnoDB ? >> > We use MyISAM and i believe it has become a pretty good table format.. > Eg. our logservers each handle just below 2 gigs of data a day in one > table and we never get messed up tables or other problems. > >> 2. Do you run any sort of maintenance on the SQLGrey tables? >> >> > > Nope.. The only maintenance done, is the build-in cleanup routines in > SQLGrey (cleaning of old entries and such). > We also run postfix-transports and userlists, and a > spam/virus-quarantine system for all accounts on the same MySQL that > runs SQLGrey on each box and MySQL has never let me down. > Hi Dan Can I ask for spam/antivirus system you are using that uses MySQL? Regards Ray > Its lovely to have a system that just hums away in the corner ;) > > The only bad experience i have with MySQL is related to things like > poweroutages or eg. a kernel-panic or the likes. That can mess up the > table a bit, but MySQL comes with a nice little tool called myisamchk > that usually repairs that without problems. And in my case, we started > clustering our SQL's for the mailsystem using MySQL replication, after > i added SQL-clustering support to SQLGrey. So should 1 database or > table be lost, i can simply replicate it from another server without > loosing 1 single record. > > > > ------------------------------------------------------- > All the advantages of Linux Managed Hosting--Without the Cost and Risk! > Fully trained technicians. The highest number of Red Hat > certifications in > the hosting industry. Fanatical Support. Click to learn more > http://sel.as-us.falkag.net/sel?cmd=lnk&kid=107521&bid=248729&dat=121642 > _______________________________________________ > Sqlgrey-users mailing list > Sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlgrey-users -- Ray Booysen rj_...@rj... |
From: Dan F. <da...@ha...> - 2006-05-31 15:27:07
|
> Can I ask for spam/antivirus system you are using that uses MySQL? > Its only the quarantine system and its developed inhouse at work and is not available to the public, im afraid. A wee-bit off-topic though ;). I dont mind off-topic discussions, but lets try to keep them off the sqlgrey list. Anyone should feel free to email me directly with off-topic stuff, so we dont bother everyone else on the list :) - Dan |
From: Dave S. <dst...@ma...> - 2006-05-30 19:35:50
|
Very slick. We will definitely test this out on our end. =20 Are you using MyISAM or InnoDB for your table format? MyISAM seems too = limited... =20 Dave Strickler MailWise LLC 617-933-5810 (direct) www.mailwise.com ( http://www.mailwise.com/ ) "Intelligent E-mail Protection" >>> Dan Faerch <da...@ha...> 3:23 PM Tuesday, May 30, 2006 >>> Dave Strickler wrote: > Two followup questions: > 1. What table format do you use? MyISAM or InnoDB ? > =20 We use MyISAM and i believe it has become a pretty good table format..=20 Eg. our logservers each handle just below 2 gigs of data a day in one=20 table and we never get messed up tables or other problems. > 2. Do you run any sort of maintenance on the SQLGrey tables? > > =20 Nope.. The only maintenance done, is the build-in cleanup routines in=20 SQLGrey (cleaning of old entries and such). We also run postfix-transports and userlists, and a=20 spam/virus-quarantine system for all accounts on the same MySQL that=20 runs SQLGrey on each box and MySQL has never let me down. Its lovely to have a system that just hums away in the corner ;) The only bad experience i have with MySQL is related to things like=20 poweroutages or eg. a kernel-panic or the likes. That can mess up the=20 table a bit, but MySQL comes with a nice little tool called myisamchk=20 that usually repairs that without problems. And in my case, we started=20 clustering our SQL's for the mailsystem using MySQL replication, after = i=20 added SQL-clustering support to SQLGrey. So should 1 database or table=20 be lost, i can simply replicate it from another server without loosing = 1=20 single record. ------------------------------------------------------- All the advantages of Linux Managed Hosting--Without the Cost and Risk! Fully trained technicians. The highest number of Red Hat certifications in the hosting industry. Fanatical Support. Click to learn more http://sel.as ( http://sel.as/ )-us.falkag.net/sel?cmd=3Dlnk&kid=3D107521&b= id=3D248729&dat=3D121642 _______________________________________________ Sqlgrey-users mailing list Sql...@li...=20 https://lists.sourceforge.net/lists/listinfo/sqlgrey-users ---------------------------- This message has been certified virus-free by MailWise Filter - The real-time, intelligent, e-mail firewall used to scan inbound and outbound messages for SPAM, Viruses and Content. For more info visit: http://www.mailwise.com |
From: Dan F. <da...@ha...> - 2006-05-30 20:11:22
|
Dave Strickler wrote: > Very slick. We will definitely test this out on our end. > > Are you using MyISAM or InnoDB for your table format? MyISAM seems too limited... > We use MyISAM in practically all systems. If i remember correctly the differences are that InnoDB does real transactions and row locking, and MyISAM runs faster. However this is just as i recall and i didnt doublecheck. Also i seem to remember something about the license for InnoDB being commercial. I dont think MyISAM is limited unless you need transactions. - Dan Faerch > > Dave Strickler > MailWise LLC > 617-933-5810 (direct) > www.mailwise.com ( http://www.mailwise.com/ ) > "Intelligent E-mail Protection" > > > > > > >>>> Dan Faerch <da...@ha...> 3:23 PM Tuesday, May 30, 2006 >>> >>>> > Dave Strickler wrote: > >> Two followup questions: >> 1. What table format do you use? MyISAM or InnoDB ? >> >> > We use MyISAM and i believe it has become a pretty good table format.. > Eg. our logservers each handle just below 2 gigs of data a day in one > table and we never get messed up tables or other problems. > |
From: Dave S. <dst...@ma...> - 2006-05-30 20:49:43
|
Running v8.1 currently, but do not have auto-vac enabled as we are wary of = anything that could do a vac "at the wrong time". =20 Dave Strickler MailWise LLC 617-933-5810 (direct) www.mailwise.com ( http://www.mailwise.com/ ) "Intelligent E-mail Protection" >>> "Andrew Diederich" <and...@gm...> 3:01 PM Tuesday, May 30, = 2006 >>> On 5/30/06, Dave Strickler <dst...@ma...> wrote: > > Has anyone had experience in a large email volume shop, going from one = DB format to the other? I find the connect table needs to be vacuumed = daily under PostgreSQL. Are there such maintenance "problems" under MySQL = ? What version of postgres are you using? I believe 8.0 and 8.1 are much better about auto-vacuuming, or not needing it in the first place, than the 7.x versions. --=20 Andrew Diederich ------------------------------------------------------- All the advantages of Linux Managed Hosting--Without the Cost and Risk! Fully trained technicians. The highest number of Red Hat certifications in the hosting industry. Fanatical Support. Click to learn more http://sel.as ( http://sel.as/ )-us.falkag.net/sel?cmd=3Dlnk&kid=3D107521&b= id=3D248729&dat=3D121642 _______________________________________________ Sqlgrey-users mailing list Sql...@li...=20 https://lists.sourceforge.net/lists/listinfo/sqlgrey-users ---------------------------- This message has been certified virus-free by MailWise Filter - The real-time, intelligent, e-mail firewall used to scan inbound and outbound messages for SPAM, Viruses and Content. For more info visit: http://www.mailwise.com |
From: Dan F. <da...@ha...> - 2006-05-12 16:02:39
|
Dan Faerch wrote: > Steve Heaven wrote: >> Can someone post a patch to filter out invalid chars from the recipient >> address. >> > > I downloaded 1.7 to make a patch for you, when i noticed that 1.7.3 > actually seem to correct handling of this issue.. Maybe upgrading to > 1.7.3 an isnt an option for you? > > If not, then ive made a quick fix for you against 1.7.1: Did either of these solutions solve your problem? - Dan |
From: Steve H. <st...@th...> - 2006-05-16 14:35:39
|
On Fri, 2006-05-12 at 17:02, Dan Faerch wrote: > > If not, then ive made a quick fix for you against 1.7.1: > Did either of these solutions solve your problem? > I must confess that I havent had time to do either. I solved the immediate problem by blocking the offending email in postfix. Steve -- thorNET Internet Services, Consultancy & Training www.thornet.co.uk |
From: Dan F. <da...@ha...> - 2006-05-28 15:34:43
|
Allright.. Ive been putting this update off for a while, but its finally committed to CVS. It adds 3 new features. 1 small and 2 big ones. Ive tried to make the patch as un-intrusive as possible. Everything should work as it always has if you do not enabled theese features. My production environment is is running with all theese features enabled and has for several months without problems, but id still like it if someone would test this version to confirm that. Id like confirmation that it runs like it always has for ppl who dont use the new features. If someone tries the new features id love to get feedback on that as well. (Again, it HAS been thoroughly tested in my setup). New features: 1. reject_code option. By setting this in sqlgrey.conf you can change the default "450" response to eg. 451. 2. DBClustering Allows for use of several sql-servers for reading, while still writing to just 1 master server. See README.DBCLUSTER Tested and running in production on MySQL's. If anyone tries this on other SQL brands let me know how it works out. 3. Discrimination (aka. Selective Greylisting) Alternative approach to greylisting. Allows you to specify a set of rules (as regular expressions) that decides whom can bypass greylisting entirely, based on characteristics in eg. reverse-dns name, HELO string, sender address ect. See READ.DISCRIMINATION (Remember; you have to checkout CVS version, not download the packaged 1.7.3) Any feedback is greatly appreciated. :) - Dan Faerch |
From: Dave S. <dst...@ma...> - 2006-05-30 12:19:45
|
Has anyone had experience in a large email volume shop, going from one DB = format to the other? I find the connect table needs to be vacuumed daily = under PostgreSQL. Are there such maintenance "problems" under MySQL ? =20 =20 =20 Dave Strickler MailWise LLC 617-933-5810 (direct) www.mailwise.com ( http://www.mailwise.com/ ) "Intelligent E-mail Protection" ---------------------------- This message has been certified virus-free by MailWise Filter - The real-time, intelligent, e-mail firewall used to scan inbound and outbound messages for SPAM, Viruses and Content. For more info visit: http://www.mailwise.com |
From: Dan F. <da...@ha...> - 2006-05-30 16:42:08
|
I dont know what "vacuumed" is, since i've never used Postgres. But i do run MySQL, and on my setup we have around 20 INSERT's a second during daytime on each server, and i dont have to do any maintenance at all.. The connect table on a random server at this very moment contains 131.081 records. It has 42mb of data and an "overhead" of 31mb. One could run "Optimize" to remove the overhead, but it doesnt matter since its just unused, pre-alloceted diskspace for the table. (the table-files dont auto-shrink on "delete" operations) The space will be mostly re-used tomorrow at time-prime as new records are added faster, so basically MySQL seems to find a good size for you table-file and works within that, saving the server for at lot of resizing operations. - Dan Faerch Dave Strickler wrote: > Has anyone had experience in a large email volume shop, going from one DB format to the other? I find the connect table needs to be vacuumed daily under PostgreSQL. Are there such maintenance "problems" under MySQL ? > > > > Dave Strickler > MailWise LLC > 617-933-5810 (direct) > www.mailwise.com ( http://www.mailwise.com/ ) > "Intelligent E-mail Protection" > > > > > ---------------------------- > This message has been certified virus-free by MailWise Filter - The real-time, intelligent, e-mail firewall used to scan inbound and outbound messages for SPAM, Viruses and Content. For more info visit: http://www.mailwise.com > |
From: Andrew D. <and...@gm...> - 2006-05-30 19:01:23
|
On 5/30/06, Dave Strickler <dst...@ma...> wrote: > > Has anyone had experience in a large email volume shop, going from one DB format to the other? I find the connect table needs to be vacuumed daily under PostgreSQL. Are there such maintenance "problems" under MySQL ? What version of postgres are you using? I believe 8.0 and 8.1 are much better about auto-vacuuming, or not needing it in the first place, than the 7.x versions. -- Andrew Diederich |
From: Marc G. F. <sc...@hu...> - 2006-05-30 19:20:24
|
On Tue, 30 May 2006, Andrew Diederich wrote: > On 5/30/06, Dave Strickler <dst...@ma...> wrote: >> >> Has anyone had experience in a large email volume shop, going from one DB >> format to the other? I find the connect table needs to be vacuumed daily >> under PostgreSQL. Are there such maintenance "problems" under MySQL ? > > > What version of postgres are you using? I believe 8.0 and 8.1 are > much better about auto-vacuuming, or not needing it in the first > place, than the 7.x versions. 8.1 has a built-in autovacuum daemon, but its disabled by default ... the 8.x series are better are reusing space, but the space still needs to be freed up by a vacuum at some point ... what 8.x have reduced (but not eliminated) the need for is VACUUM FULL ... ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email . sc...@hu... MSN . sc...@hu... Yahoo . yscrappy Skype: hub.org ICQ . 7615664 |
From: Lionel B. <lio...@bo...> - 2006-09-09 11:52:01
|
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. |
From: Steve H. <st...@th...> - 2006-09-11 07:12:52
|
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 |