Menu
▾
▴
sqlgrey-users — Users of SQLgrey
You can subscribe to this list here.
| 2004 |
Jan
|
Feb
|
Mar
|
Apr
|
May
|
Jun
|
Jul
|
Aug
|
Sep
|
Oct
(10) |
Nov
(37) |
Dec
(66) |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2005 |
Jan
(52) |
Feb
(136) |
Mar
(65) |
Apr
(38) |
May
(46) |
Jun
(143) |
Jul
(60) |
Aug
(33) |
Sep
(79) |
Oct
(29) |
Nov
(13) |
Dec
(14) |
| 2006 |
Jan
(25) |
Feb
(26) |
Mar
(4) |
Apr
(9) |
May
(29) |
Jun
|
Jul
(9) |
Aug
(11) |
Sep
(10) |
Oct
(9) |
Nov
(45) |
Dec
(8) |
| 2007 |
Jan
(82) |
Feb
(61) |
Mar
(39) |
Apr
(7) |
May
(9) |
Jun
(16) |
Jul
(2) |
Aug
(22) |
Sep
(2) |
Oct
|
Nov
(4) |
Dec
(5) |
| 2008 |
Jan
|
Feb
|
Mar
(5) |
Apr
(2) |
May
(8) |
Jun
|
Jul
(10) |
Aug
|
Sep
|
Oct
|
Nov
|
Dec
|
| 2009 |
Jan
|
Feb
|
Mar
|
Apr
(32) |
May
|
Jun
(7) |
Jul
|
Aug
(38) |
Sep
(3) |
Oct
|
Nov
(4) |
Dec
|
| 2010 |
Jan
(36) |
Feb
(32) |
Mar
(2) |
Apr
(19) |
May
|
Jun
(1) |
Jul
|
Aug
|
Sep
|
Oct
(6) |
Nov
(8) |
Dec
|
| 2011 |
Jan
(3) |
Feb
|
Mar
(5) |
Apr
|
May
(2) |
Jun
(1) |
Jul
|
Aug
(3) |
Sep
|
Oct
|
Nov
|
Dec
(6) |
| 2012 |
Jan
|
Feb
|
Mar
(2) |
Apr
|
May
|
Jun
|
Jul
|
Aug
|
Sep
(2) |
Oct
|
Nov
|
Dec
|
| 2013 |
Jan
|
Feb
|
Mar
|
Apr
|
May
(1) |
Jun
|
Jul
|
Aug
(1) |
Sep
(1) |
Oct
|
Nov
(6) |
Dec
(10) |
| 2014 |
Jan
(8) |
Feb
|
Mar
|
Apr
|
May
|
Jun
(3) |
Jul
(34) |
Aug
(6) |
Sep
|
Oct
(1) |
Nov
|
Dec
|
| 2015 |
Jan
|
Feb
|
Mar
|
Apr
|
May
|
Jun
(18) |
Jul
(13) |
Aug
(30) |
Sep
(4) |
Oct
(1) |
Nov
|
Dec
(4) |
| 2016 |
Jan
(2) |
Feb
(10) |
Mar
(3) |
Apr
|
May
|
Jun
(11) |
Jul
|
Aug
|
Sep
(2) |
Oct
|
Nov
|
Dec
|
| 2017 |
Jan
(1) |
Feb
|
Mar
|
Apr
|
May
|
Jun
|
Jul
|
Aug
|
Sep
|
Oct
|
Nov
|
Dec
|
| 2018 |
Jan
|
Feb
|
Mar
(2) |
Apr
|
May
|
Jun
|
Jul
|
Aug
|
Sep
|
Oct
|
Nov
|
Dec
|
| 2019 |
Jan
|
Feb
(1) |
Mar
|
Apr
|
May
|
Jun
(1) |
Jul
|
Aug
|
Sep
|
Oct
|
Nov
|
Dec
|
|
From: Lionel B. <lio...@bo...> - 2005-09-14 14:53:35
|
Michael Storz wrote the following on 14.09.2005 16:43 : >On Wed, 14 Sep 2005, Steffen Plotner wrote: > > > >>Hi Lionel, >> >>I have reverted to sqlgrey-1.6.5.tar.bz2 and within minutes of >>restarting postfix/sqlgrey I can see duplications in the connect table. >>The dump below shows the duplications in the count column on the far >>right (duplications show with a count > 1). >> >>I wonder if anybody else sees this kind of behavior? >> >> >> >> > >Hi Steffen, > >I don't think there is a problem. It seems you forgot that in table >connect 4 items are stored sender_name, sender_domain, src AND rcpt. >If an email goes to several recipients than you get several entries with >stored sender_name, sender_domain and src. Just use > >select * from connect where src = "IP-ADDR" order by rcpt; > >with IP-ADDR replaced by one of the entries where count > 1. This should >sjow that the difference in the entries are in field rcpt. > > The first table extract showed several entries for the same RCPT... As a side note, there isn't a UNIQUE INDEX on the entries in this table (which would have prevented even a bug ins SQLgrey to create multiple entries), because... MySQL doesn't support them on such long rows :-( Lionel. |
|
From: Michael S. <Mic...@lr...> - 2005-09-14 14:43:25
|
On Wed, 14 Sep 2005, Steffen Plotner wrote: > Hi Lionel, > > I have reverted to sqlgrey-1.6.5.tar.bz2 and within minutes of > restarting postfix/sqlgrey I can see duplications in the connect table. > The dump below shows the duplications in the count column on the far > right (duplications show with a count > 1). > > I wonder if anybody else sees this kind of behavior? > > Hi Steffen, I don't think there is a problem. It seems you forgot that in table connect 4 items are stored sender_name, sender_domain, src AND rcpt. If an email goes to several recipients than you get several entries with stored sender_name, sender_domain and src. Just use select * from connect where src = "IP-ADDR" order by rcpt; with IP-ADDR replaced by one of the entries where count > 1. This should sjow that the difference in the entries are in field rcpt. Michael Storz ------------------------------------------------- Leibniz-Rechenzentrum ! <mailto:St...@lr...> Barer Str. 21 ! Fax: +49 89 2809460 80333 Muenchen, Germany ! Tel: +49 89 289-28840 |
|
From: Lionel B. <lio...@bo...> - 2005-09-14 13:52:55
|
Steffen Plotner wrote the following on 14.09.2005 15:07 : >Hi Lionel, > >I have reverted to sqlgrey-1.6.5.tar.bz2 and within minutes of >restarting postfix/sqlgrey I can see duplications in the connect table. >The dump below shows the duplications in the count column on the far >right (duplications show with a count > 1). > > Is there any way you could switch to another database ? PostgreSQL (provided with your distribution and probably the most standard-compliant database supported by SQLgrey) for example. Testing MySQL 4.1.10a will be a long process for me (not in Portage) and I'm wondering if it could be an odd 'INTERVAL' syntax bug. MySQL is known for its quirkness in some places, changes of behaviour between versions (already had to modify SQLgrey to take one into account) and there already is a specific case for MySQL in the INTERVAL handling. If the bug exists with PostgreSQL too it would surprise me and I've not yet a lead to what would be responsible in this case. If PosgreSQL solves your problem, then I'll ask you to run some SQL commands on your MySQL installation to isolate the problem. Lionel. |
|
From: Steffen P. <swp...@am...> - 2005-09-14 13:07:53
|
Hi Lionel, I have reverted to sqlgrey-1.6.5.tar.bz2 and within minutes of restarting postfix/sqlgrey I can see duplications in the connect table. The dump below shows the duplications in the count column on the far right (duplications show with a count > 1). I wonder if anybody else sees this kind of behavior? select sender_name, sender_domain, src, count(sender_name+sender_domain+src) as count from connect group by sender_name, sender_domain, src; +-------------------------------------------------+--------------------- +-----------------+-------+ | sender_name | sender_domain | src | count | +-------------------------------------------------+--------------------- +-----------------+-------+ | -undef- | -undef- | 168.159.186 | 1 | | -undef- | -undef- | 200.228.0.130 | 1 | | -undef- | -undef- | 203.92.82.98 | 1 | | -undef- | -undef- | 205.254.25.211 | 1 | | aagbe.aagbe | brantbrant.net | 218.187.100.249 | 1 | | aagnl.aagnl | alvaalva.com | 218.187.100.249 | 1 | | account | chase.com | 62.193.214.159 | 4 | | admin | fivecolleges.edu | 24.60.30.10 | 1 | | antifraud | chase.com | 62.193.214 | 1 | | b.dealchamp.0-5e1cf85-519f.amherst.edu.-dacetto | 22.asp070.com | 69.6.65 | 1 | | confirm | chase.com | 62.193.214.159 | 4 | | greggfi | fusemail.com | 221.141.131.14 | 1 | | morgantnwpaxczqu | verizon.net | 70.22.253.236 | 1 | | mrimmer | unb.ca | 206.47.199 | 1 | | nmkidiqafukyo | go.com | 220.190.37.160 | 1 | | north | takuyakimura.com | 201.34.80.220 | 1 | | rtertee3e | ert354tg.dyndns.org | 61.111.231.68 | 1 | | sara.goldstein | teachforamerica.org | 216.82.244 | 1 | | service | chase.com | 62.193.214.159 | 4 | | support | chase.com | 62.193.214 | 1 | | support | chase.com | 62.193.214.159 | 1 | | swig | wigco.com | 207.190.209 | 1 | | symantecsmtpgateway | timetrak.com | 69.223.125 | 1 | +-------------------------------------------------+--------------------- +-----------------+-------+=20 Steffen -----Original Message----- From: sql...@li... [mailto:sql...@li...] On Behalf Of Lionel Bouton Sent: Wednesday, September 14, 2005 3:55 AM To: sql...@li... Subject: Re: [Sqlgrey-users] Duplicate connect records Steffen Plotner wrote the following on 14.09.2005 05:53 : >Hi Lionel, >=20 >Thank you for the info about the connect table - good to know that I am on the right track. What do we have you ask? >=20 >We run RedHat AS4 on grey1.amherst.edu and grey2.amherst.edu servers. The mysql database backend is located on another server also running RedHat AS4 (no mysql replication). All machines are sync'd with NTP to our DNS servers. >=20 >Versions: >sqlgrey-1.7.0-1.noarch.rpm from sourceforge, using rpmbuild -ta sqlgrey-<version>.tar.bz2 >IO-Multiplex-1.08.tar.gz >Net-Server-0.88.tar.gz >perl-5.8.5 >perl-DBI-1.40 >mysql-4.1.10a >postfix-2.1.5 >=20 >Should I be running the 1.6.5 version (stable version)? > =20 > If you don't want to be a beta tester, yes :-) I don't see any obvious problem with the software versions, I'll take time to check the code to find out if something introduced a bug in 1.7.x and I'll be back to you. Lionel. ------------------------------------------------------- SF.Net email is sponsored by: Tame your development challenges with Apache's Geronimo App Server. Download it for free - -and be entered to win a 42" plasma tv or your very own Sony(tm)PSP. Click here to play: http://sourceforge.net/geronimo.php _______________________________________________ Sqlgrey-users mailing list Sql...@li... https://lists.sourceforge.net/lists/listinfo/sqlgrey-users |
|
From: Lionel B. <lio...@bo...> - 2005-09-14 07:55:34
|
Steffen Plotner wrote the following on 14.09.2005 05:53 : >Hi Lionel, > >Thank you for the info about the connect table - good to know that I am on the right track. What do we have you ask? > >We run RedHat AS4 on grey1.amherst.edu and grey2.amherst.edu servers. The mysql database backend is located on another server also running RedHat AS4 (no mysql replication). All machines are sync'd with NTP to our DNS servers. > >Versions: >sqlgrey-1.7.0-1.noarch.rpm from sourceforge, using rpmbuild -ta sqlgrey-<version>.tar.bz2 >IO-Multiplex-1.08.tar.gz >Net-Server-0.88.tar.gz >perl-5.8.5 >perl-DBI-1.40 >mysql-4.1.10a >postfix-2.1.5 > >Should I be running the 1.6.5 version (stable version)? > > If you don't want to be a beta tester, yes :-) I don't see any obvious problem with the software versions, I'll take time to check the code to find out if something introduced a bug in 1.7.x and I'll be back to you. Lionel. |
|
From: Steffen P. <swp...@am...> - 2005-09-14 03:53:50
|
Hi Lionel, =20 Thank you for the info about the connect table - good to know that I am = on the right track. What do we have you ask? =20 We run RedHat AS4 on grey1.amherst.edu and grey2.amherst.edu servers. = The mysql database backend is located on another server also running = RedHat AS4 (no mysql replication). All machines are sync'd with NTP to = our DNS servers. =20 Versions: sqlgrey-1.7.0-1.noarch.rpm from sourceforge, using rpmbuild -ta = sqlgrey-<version>.tar.bz2 IO-Multiplex-1.08.tar.gz Net-Server-0.88.tar.gz perl-5.8.5 perl-DBI-1.40 mysql-4.1.10a postfix-2.1.5=20 =20 Should I be running the 1.6.5 version (stable version)?=20 =20 Steffen =20 ________________________________=20 Steffen Plotner=20 Systems Administrator/Programmer=20 Systems & Networking=20 Amherst College=20 PO BOX 5000=20 Amherst, MA 01002-5000=20 Tel (413) 542-2348=20 Fax (413) 542-2626=20 Email: swp...@am...=20 ________________________________=20 ________________________________ From: sql...@li... on behalf of Lionel = Bouton Sent: Tue 9/13/2005 7:16 PM To: sql...@li... Subject: Re: [Sqlgrey-users] Duplicate connect records Steffen Plotner wrote the following on 13.09.2005 22:23 : > Hello, >=20 > For the time being I deleted the contents of the connect table and > within minutes I can see duplications: >=20 > select sender_name, sender_domain, src, > count(sender_name+sender_domain+src) from connect group by > sender_name, sender_domain, src; >=20 > mysql> select sender_name, sender_domain, src, > count(sender_name+sender_domain+src) as count from connect group by > sender_name, sender_domain, src; > > = +---------------------------------------------------+--------------------= -------+-----------------+-------+ > | sender_name | > sender_domain | src | count | > = +---------------------------------------------------+--------------------= -------+-----------------+-------+ > | -undef- | > -undef- | 12.36.12 | 1 | > | -undef- | > -undef- | 217.70.144.197 | 1 | > | admin | > amherst.edu | 24.34.176.205 | 1 | > | salinas | > cableinet.co.uk | 209.60.100.70 | 8 | > | skotowych | > utpress.utoronto.ca | 128.100.205 | 1 | > | stockdal | > rilcomfg.com | 200.168.17.124 | 1 | > | wendy | > marketriskadvisory.com | 69.49.238 | 1 | > = +---------------------------------------------------+--------------------= -------+-----------------+-------+ > One example above is the salinas sender_name. >=20 > Steffen > > = ------------------------------------------------------------------------ > *From:* sql...@li... > [mailto:sql...@li...] *On Behalf Of > *Steffen Plotner > *Sent:* Tuesday, September 13, 2005 3:50 PM > *To:* sql...@li... > *Subject:* [Sqlgrey-users] Duplicate connect records > > Hi Lionel, > > I would like to thank you first for the sqlgrey development. It has > been a huge asset to Amherst College. > > We have encountered an issue today with some emails coming from so far > 2 specific IP addresses that are being continuously being greylisted. > > A quick view of the connect table reveals duplicate connect records. > > mysql> SELECT * FROM connect WHERE src =3D '205.231.86'; > = +-------------+-------------------------+------------+-------------------= -----+---------------------+ > > | sender_name | sender_domain | src | > rcpt | first_seen | > = +-------------+-------------------------+------------+-------------------= -----+---------------------+ > > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-13 06:16:01 | > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-13 04:02:40 | > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-13 12:06:00 | > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-12 17:12:41 | > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-12 17:12:41 | > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-12 16:51:13 | > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-12 16:51:13 | > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-13 05:26:01 | > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-13 02:56:22 | > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-13 13:12:41 | > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-12 21:06:00 | > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-13 08:46:00 | > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-13 10:59:21 | > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-13 10:59:21 | > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-12 19:59:21 | > > > I have put 205.231.86.62 into whitelist.local - mail is going through > from that IP. I have enabled SQL queries in mysql and can see that > SELECT statements that check if there was a connection within the > previous 5 minutes. Then within the last 24 hours and before 5 minutes > ago. Upon that it inserts a connect record. I have debugged those > statements and found that they produce records. > > What are your recommendations? Have you seen this before? > Never! As you saw in the SELECT statements, SQLgrey begins to check if a previous entry exists, then if one exists it is supposed (and never was witnessed to do something else) to either switch to the from_awl table and let the mail pass or signal a "reconnect to early" without creating connect entries depending on the result of its checks. Which SQLgrey/MySQL versions are you using? How did you get SQLgrey (pre-packaged or directly from sourceforge)? Is anything unusual about your setup (MySQL replication, multiple systems whose clocks aren't synchronised with NTP, ...)? > Should I trash the connect table? > If it becomes too large (to the point where performance might suffer on your setup), yes, otherwise you should let SQLgrey do the cleaning. Lionel. ------------------------------------------------------- SF.Net email is sponsored by: Tame your development challenges with Apache's Geronimo App Server. = Download it for free - -and be entered to win a 42" plasma tv or your very own Sony(tm)PSP. Click here to play: http://sourceforge.net/geronimo.php _______________________________________________ Sqlgrey-users mailing list Sql...@li... https://lists.sourceforge.net/lists/listinfo/sqlgrey-users |
|
From: Lionel B. <lio...@bo...> - 2005-09-13 23:16:46
|
Steffen Plotner wrote the following on 13.09.2005 22:23 : > Hello, > > For the time being I deleted the contents of the connect table and > within minutes I can see duplications: > > select sender_name, sender_domain, src, > count(sender_name+sender_domain+src) from connect group by > sender_name, sender_domain, src; > > mysql> select sender_name, sender_domain, src, > count(sender_name+sender_domain+src) as count from connect group by > sender_name, sender_domain, src; > > +---------------------------------------------------+---------------------------+-----------------+-------+ > | sender_name | > sender_domain | src | count | > +---------------------------------------------------+---------------------------+-----------------+-------+ > | -undef- | > -undef- | 12.36.12 | 1 | > | -undef- | > -undef- | 217.70.144.197 | 1 | > | admin | > amherst.edu | 24.34.176.205 | 1 | > | salinas | > cableinet.co.uk | 209.60.100.70 | 8 | > | skotowych | > utpress.utoronto.ca | 128.100.205 | 1 | > | stockdal | > rilcomfg.com | 200.168.17.124 | 1 | > | wendy | > marketriskadvisory.com | 69.49.238 | 1 | > +---------------------------------------------------+---------------------------+-----------------+-------+ > One example above is the salinas sender_name. > > Steffen > > ------------------------------------------------------------------------ > *From:* sql...@li... > [mailto:sql...@li...] *On Behalf Of > *Steffen Plotner > *Sent:* Tuesday, September 13, 2005 3:50 PM > *To:* sql...@li... > *Subject:* [Sqlgrey-users] Duplicate connect records > > Hi Lionel, > > I would like to thank you first for the sqlgrey development. It has > been a huge asset to Amherst College. > > We have encountered an issue today with some emails coming from so far > 2 specific IP addresses that are being continuously being greylisted. > > A quick view of the connect table reveals duplicate connect records. > > mysql> SELECT * FROM connect WHERE src = '205.231.86'; > +-------------+-------------------------+------------+------------------------+---------------------+ > > | sender_name | sender_domain | src | > rcpt | first_seen | > +-------------+-------------------------+------------+------------------------+---------------------+ > > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-13 06:16:01 | > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-13 04:02:40 | > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-13 12:06:00 | > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-12 17:12:41 | > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-12 17:12:41 | > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-12 16:51:13 | > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-12 16:51:13 | > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-13 05:26:01 | > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-13 02:56:22 | > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-13 13:12:41 | > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-12 21:06:00 | > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-13 08:46:00 | > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-13 10:59:21 | > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-13 10:59:21 | > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-12 19:59:21 | > > > I have put 205.231.86.62 into whitelist.local - mail is going through > from that IP. I have enabled SQL queries in mysql and can see that > SELECT statements that check if there was a connection within the > previous 5 minutes. Then within the last 24 hours and before 5 minutes > ago. Upon that it inserts a connect record. I have debugged those > statements and found that they produce records. > > What are your recommendations? Have you seen this before? > Never! As you saw in the SELECT statements, SQLgrey begins to check if a previous entry exists, then if one exists it is supposed (and never was witnessed to do something else) to either switch to the from_awl table and let the mail pass or signal a "reconnect to early" without creating connect entries depending on the result of its checks. Which SQLgrey/MySQL versions are you using? How did you get SQLgrey (pre-packaged or directly from sourceforge)? Is anything unusual about your setup (MySQL replication, multiple systems whose clocks aren't synchronised with NTP, ...)? > Should I trash the connect table? > If it becomes too large (to the point where performance might suffer on your setup), yes, otherwise you should let SQLgrey do the cleaning. Lionel. |
|
From: Steffen P. <swp...@am...> - 2005-09-13 20:23:44
|
Hello, =20 For the time being I deleted the contents of the connect table and within minutes I can see duplications: =20 select sender_name, sender_domain, src, count(sender_name+sender_domain+src) from connect group by sender_name, sender_domain, src; =20 mysql> select sender_name, sender_domain, src, count(sender_name+sender_domain+src) as count from connect group by sender_name, sender_domain, src; +---------------------------------------------------+------------------- --------+-----------------+-------+ | sender_name | sender_domain | src | count | +---------------------------------------------------+------------------- --------+-----------------+-------+ | -undef- | -undef- | 12.36.12 | 1 | | -undef- | -undef- | 217.70.144.197 | 1 | | admin | amherst.edu | 24.34.176.205 | 1 | | salinas | cableinet.co.uk | 209.60.100.70 | 8 | | skotowych | utpress.utoronto.ca | 128.100.205 | 1 | | stockdal | rilcomfg.com | 200.168.17.124 | 1 | | wendy | marketriskadvisory.com | 69.49.238 | 1 | +---------------------------------------------------+------------------- --------+-----------------+-------+ One example above is the salinas sender_name. =20 Steffen ________________________________ From: sql...@li... [mailto:sql...@li...] On Behalf Of Steffen Plotner Sent: Tuesday, September 13, 2005 3:50 PM To: sql...@li... Subject: [Sqlgrey-users] Duplicate connect records Hi Lionel,=20 I would like to thank you first for the sqlgrey development. It has been a huge asset to Amherst College.=20 We have encountered an issue today with some emails coming from so far 2 specific IP addresses that are being continuously being greylisted. A quick view of the connect table reveals duplicate connect records.=20 mysql> SELECT * FROM connect WHERE src =3D '205.231.86';=20 +-------------+-------------------------+------------+------------------ ------+---------------------+=20 | sender_name | sender_domain | src | rcpt | first_seen |=20 +-------------+-------------------------+------------+------------------ ------+---------------------+=20 | nobody | designjet-online.hp.com | 205.231.86 | mkf...@am... | 2005-09-13 06:16:01 |=20 | nobody | designjet-online.hp.com | 205.231.86 | mkf...@am... | 2005-09-13 04:02:40 |=20 | nobody | designjet-online.hp.com | 205.231.86 | mkf...@am... | 2005-09-13 12:06:00 |=20 | nobody | designjet-online.hp.com | 205.231.86 | mkf...@am... | 2005-09-12 17:12:41 |=20 | nobody | designjet-online.hp.com | 205.231.86 | mkf...@am... | 2005-09-12 17:12:41 |=20 | nobody | designjet-online.hp.com | 205.231.86 | mkf...@am... | 2005-09-12 16:51:13 |=20 | nobody | designjet-online.hp.com | 205.231.86 | mkf...@am... | 2005-09-12 16:51:13 |=20 | nobody | designjet-online.hp.com | 205.231.86 | mkf...@am... | 2005-09-13 05:26:01 |=20 | nobody | designjet-online.hp.com | 205.231.86 | mkf...@am... | 2005-09-13 02:56:22 |=20 | nobody | designjet-online.hp.com | 205.231.86 | mkf...@am... | 2005-09-13 13:12:41 |=20 | nobody | designjet-online.hp.com | 205.231.86 | mkf...@am... | 2005-09-12 21:06:00 |=20 | nobody | designjet-online.hp.com | 205.231.86 | mkf...@am... | 2005-09-13 08:46:00 |=20 | nobody | designjet-online.hp.com | 205.231.86 | mkf...@am... | 2005-09-13 10:59:21 |=20 | nobody | designjet-online.hp.com | 205.231.86 | mkf...@am... | 2005-09-13 10:59:21 |=20 | nobody | designjet-online.hp.com | 205.231.86 | mkf...@am... | 2005-09-12 19:59:21 |=20 I have put 205.231.86.62 into whitelist.local - mail is going through from that IP. I have enabled SQL queries in mysql and can see that SELECT statements that check if there was a connection within the previous 5 minutes. Then within the last 24 hours and before 5 minutes ago. Upon that it inserts a connect record. I have debugged those statements and found that they produce records. What are your recommendations? Have you seen this before?=20 Should I trash the connect table?=20 Steffen=20 ________________________________=20 Steffen Plotner=20 Systems Administrator/Programmer=20 Systems & Networking=20 Amherst College=20 PO BOX 5000=20 Amherst, MA 01002-5000=20 Tel (413) 542-2348=20 Fax (413) 542-2626=20 Email: swp...@am...=20 ________________________________=20 |
|
From: Steffen P. <swp...@am...> - 2005-09-13 19:50:26
|
Hi Lionel,=20
I would like to thank you first for the sqlgrey development. It has been =
a huge asset to Amherst College.=20
We have encountered an issue today with some emails coming from so far 2 =
specific IP addresses that are being continuously being greylisted.
A quick view of the connect table reveals duplicate connect records.=20
mysql> SELECT * FROM connect WHERE src =3D '205.231.86';=20
+-------------+-------------------------+------------+-------------------=
-----+---------------------+=20
| sender_name | sender_domain | src | rcpt =
| first_seen |=20
+-------------+-------------------------+------------+-------------------=
-----+---------------------+=20
| nobody | designjet-online.hp.com | 205.231.86 | =
mkf...@am... | 2005-09-13 06:16:01 |=20
| nobody | designjet-online.hp.com | 205.231.86 | =
mkf...@am... | 2005-09-13 04:02:40 |=20
| nobody | designjet-online.hp.com | 205.231.86 | =
mkf...@am... | 2005-09-13 12:06:00 |=20
| nobody | designjet-online.hp.com | 205.231.86 | =
mkf...@am... | 2005-09-12 17:12:41 |=20
| nobody | designjet-online.hp.com | 205.231.86 | =
mkf...@am... | 2005-09-12 17:12:41 |=20
| nobody | designjet-online.hp.com | 205.231.86 | =
mkf...@am... | 2005-09-12 16:51:13 |=20
| nobody | designjet-online.hp.com | 205.231.86 | =
mkf...@am... | 2005-09-12 16:51:13 |=20
| nobody | designjet-online.hp.com | 205.231.86 | =
mkf...@am... | 2005-09-13 05:26:01 |=20
| nobody | designjet-online.hp.com | 205.231.86 | =
mkf...@am... | 2005-09-13 02:56:22 |=20
| nobody | designjet-online.hp.com | 205.231.86 | =
mkf...@am... | 2005-09-13 13:12:41 |=20
| nobody | designjet-online.hp.com | 205.231.86 | =
mkf...@am... | 2005-09-12 21:06:00 |=20
| nobody | designjet-online.hp.com | 205.231.86 | =
mkf...@am... | 2005-09-13 08:46:00 |=20
| nobody | designjet-online.hp.com | 205.231.86 | =
mkf...@am... | 2005-09-13 10:59:21 |=20
| nobody | designjet-online.hp.com | 205.231.86 | =
mkf...@am... | 2005-09-13 10:59:21 |=20
| nobody | designjet-online.hp.com | 205.231.86 | =
mkf...@am... | 2005-09-12 19:59:21 |=20
I have put 205.231.86.62 into whitelist.local - mail is going through =
from that IP. I have enabled SQL queries in mysql and can see that =
SELECT statements that check if there was a connection within the =
previous 5 minutes. Then within the last 24 hours and before 5 minutes =
ago. Upon that it inserts a connect record. I have debugged those =
statements and found that they produce records.
What are your recommendations? Have you seen this before?=20
Should I trash the connect table?=20
Steffen=20
________________________________=20
Steffen Plotner=20
Systems Administrator/Programmer=20
Systems & Networking=20
Amherst College=20
PO BOX 5000=20
Amherst, MA 01002-5000=20
Tel (413) 542-2348=20
Fax (413) 542-2626=20
Email: swp...@am...=20
________________________________=20
|
|
From: Lionel B. <lio...@bo...> - 2005-09-07 23:12:03
|
Matthew Goldman wrote the following on 08.09.2005 00:15 : >W Hotels and their e-mail service do not seem to play nice. > >mail.optimumreturn.com (216.65.196.14) > >Aug 30 03:06:28 echidna postfix/smtpd[22944]: NOQUEUE: reject: RCPT from >mail.optimumreturn.com[216.65.196.14]: 450 <ma...@go...>: >Recipient address rejected: Greylisted for 2 minutes; >from=<cus...@wh...> to=<ma...@go...> >proto=ESMTP helo=<mail.optimumreturn.com> >Aug 31 03:34:01 echidna sqlgrey: spam: 216.65.196: >cus...@wh... -> ma...@go... at >2005-08-30 03:06:28 > > mail.optimumreturn.com added to client_fqdn_whitelist, will be automatically pushed to installations running update_sqlgrey_config. As Optimumreturn is in fact an ASP solution vendor, this can solve problems for other sources... I was a little worried when I saw "direct marketing" in their buzzwords, but it seems they aren't blatant SPAM sources. As always, please signal bogus whitelist entries. Thanks, Lionel |
|
From: Matthew G. <ma...@go...> - 2005-09-07 22:36:06
|
W Hotels and their e-mail service do not seem to play nice. mail.optimumreturn.com (216.65.196.14) Aug 30 03:06:28 echidna postfix/smtpd[22944]: NOQUEUE: reject: RCPT from mail.optimumreturn.com[216.65.196.14]: 450 <ma...@go...>: Recipient address rejected: Greylisted for 2 minutes; from=<cus...@wh...> to=<ma...@go...> proto=ESMTP helo=<mail.optimumreturn.com> Aug 31 03:34:01 echidna sqlgrey: spam: 216.65.196: cus...@wh... -> ma...@go... at 2005-08-30 03:06:28 Thank you. Please cc me on replies. |
|
From: Folkert v. H. <fo...@va...> - 2005-09-04 10:37:05
|
> >Version 0.3 enables you to: > >One can now also add an e-mail address/domain directly to the whitelist. > >One can now also edit the greylisted hosts. > Maybe worth mentioning that the interface supports MySQL DBs only. Took= =20 > me a while this morning to wake up and spot the difference between "My"= =20 > and "Postgre" ;-) True. Altough changing things for postgresql shouldn't be too hard: all db-functions are in db.inc.php. Folkert van Heusden --=20 Try MultiTail! Multiple windows with logfiles, filtered with regular expressions, colored output, etc. etc. www.vanheusden.com/multitail/ ---------------------------------------------------------------------- Get your PGP/GPG key signed at www.biglumber.com! ---------------------------------------------------------------------- Phone: +31-6-41278122, PGP-key: 1F28D8AE, www.vanheusden.com |
|
From: HaJo S. <ha...@ha...> - 2005-09-04 04:55:58
|
Folkert van Heusden wrote: > Version 0.3 enables you to: > One can now also add an e-mail address/domain directly to the whitelist. > One can now also edit the greylisted hosts. Maybe worth mentioning that the interface supports MySQL DBs only. Took me a while this morning to wake up and spot the difference between "My" and "Postgre" ;-) -- HaJo Schatz <ha...@ha...> http://www.HaJo.Net PGP-Key: http://www.hajo.net/hajonet/keys/pgpkey_hajo.txt |
|
From: Lionel B. <lio...@bo...> - 2005-09-03 19:09:02
|
Folkert van Heusden wrote the following on 03.09.2005 20:51 : >Version 0.3 enables you to: >One can now also add an e-mail address/domain directly to the whitelist. >One can now also edit the greylisted hosts. > > > By the way, it's downloadable on the following page: http://www.vanheusden.com/sgwi/ |
|
From: Folkert v. H. <fo...@va...> - 2005-09-03 18:51:29
|
Version 0.3 enables you to: One can now also add an e-mail address/domain directly to the whitelist. One can now also edit the greylisted hosts. Folkert van Heusden --=20 Try MultiTail! Multiple windows with logfiles, filtered with regular expressions, colored output, etc. etc. www.vanheusden.com/multitail/ ---------------------------------------------------------------------- Get your PGP/GPG key signed at www.biglumber.com! ---------------------------------------------------------------------- Phone: +31-6-41278122, PGP-key: 1F28D8AE, www.vanheusden.com |
|
From: Folkert v. H. <fo...@va...> - 2005-08-31 17:14:42
|
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi, I wrote a web-interface for sqlgrey. It's here: http://www.vanheusden.com/sgwi/sqlgreywebinterface-0.1.tgz Folkert van Heusden - -- Try MultiTail! Multiple windows with logfiles, filtered with regular expressions, colored output, etc. etc. www.vanheusden.com/multitail/ - ---------------------------------------------------------------------- Get your PGP/GPG key signed at www.biglumber.com! - ---------------------------------------------------------------------- Phone: +31-6-41278122, PGP-key: 1F28D8AE, www.vanheusden.com -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.1 (GNU/Linux) iIMEARECAEMFAkMV5Xw8Gmh0dHA6Ly93d3cudmFuaGV1c2Rlbi5jb20vZGF0YS1z aWduaW5nLXdpdGgtcGdwLXBvbGljeS5odG1sAAoJEDAZDowfKNiudcYAoJKjgoPQ shM75uzmvrNz0jM3Q7D+AJ0XnclXv5M3aPfXro3/14QBIRy5Nw== =Apvh -----END PGP SIGNATURE----- |
|
From: Lionel B. <lio...@bo...> - 2005-08-18 20:49:03
|
Ray Booysen wrote the following on 18.08.2005 21:56 : > > Hi Lionel > > This brings me to a point that I have been thinking about for a > while. How large is this white list going to become over time? > Surely over time this file is going to get quite large? Could be. But we have orders of magnitude of growth before it could mean a slowdown and it would only occur when updating them or starting SQLgrey, not when it is already running. > Another point is that shouldn't it form a table in the sql-grey > database? > The earliest versions of SQLgrey had a whitelist table, but it was dropped because it wasn't convenient enough (and it was based on a proprietary PostgreSQL data type). The whitelists being in files and separated in official/local versions have several benefits: - the official versions are easily synced with a repository, which means that each admin regularly launching update_sqlgrey_config benefits from what others have already found automatically, - it's trivial to add entries in the local versions when needed (pending their addition in the official version), - in the common cases, the whitelist entries are put in a hash in memory which is by far faster than querying the database, the regexp and wildcard entries are in a list (no way of using a hash here) which is slower but still far faster than SQL queries (they wouldn't be able to benefit from indices if put in database anyway). In fact, if you find out that a handful of IPs/fqdns are trusted sources of trafic and that are related to a fair percentage of your SQL queries (run the log parsing tool to have the top AWL matches) you can add them to the local whitelists to lighten the load on the database. To sum it up, by nature the database is the best tool for the greylisting/AWL work (all the related tables get regular INSERT, UPDATE and DELETE operations, which would be costly in processing with plainfiles and a pain to distribute amongst several hosts), the files are more suited to ponctual adjustements. Lionel |
|
From: Ray B. <rj_...@rj...> - 2005-08-18 19:56:46
|
Lionel Bouton wrote: > Hi, > > This is a first: a software company asked for entries in the whitelist > of all greylisting implementation. Their (Ciphire Labs) software needs > a challenge-response over SMTP which must complete in less than 60 > minutes. Greylisting usually is ok, but several implementations > recommend 2 hours for the reconnect delay which breaks their soft. > > To be on the safe side their 6 servers dedicated to this > challenge-response system are now in the whitelist on the public > configuration repository. The next update_sqlgrey_config run will > update your installations. > > It seems unlikely to me that spam will ever get sent from these > addresses but if you get problems with them, please report here. > > Lionel. Hi Lionel This brings me to a point that I have been thinking about for a while. How large is this white list going to become over time? Surely over time this file is going to get quite large? Another point is that shouldn't it form a table in the sql-grey database? Regards Ray -- Ray Booysen rj_...@rj... |
|
From: Lionel B. <lio...@bo...> - 2005-08-18 19:32:55
|
Hi, This is a first: a software company asked for entries in the whitelist of all greylisting implementation. Their (Ciphire Labs) software needs a challenge-response over SMTP which must complete in less than 60 minutes. Greylisting usually is ok, but several implementations recommend 2 hours for the reconnect delay which breaks their soft. To be on the safe side their 6 servers dedicated to this challenge-response system are now in the whitelist on the public configuration repository. The next update_sqlgrey_config run will update your installations. It seems unlikely to me that spam will ever get sent from these addresses but if you get problems with them, please report here. Lionel. |
|
From: Michael S. <Mic...@lr...> - 2005-08-16 09:17:45
|
On Mon, 15 Aug 2005, Lionel Bouton wrote: > Depends on my free time... There's a lot to do in the 1.7.x branch and I > may very well start by splitting the code into more manageable modules > which will delay new functionnalities but then speed the development. > Hi Lionel, don't begin with splitting the code in different modules. This is nothing for the 1.7-branch. I think for the new structure we should start a 2.0-branch, in parallel to the 1.X-branches, because it needs a lot of time and testing to get the new structure back into production. I already began with this restructuring to an OO-structure and put many hours of my free time into it. But it is still far away from a prototype. I think there is still a lot to do for the 1.X-branches. Regards, Michael Storz ------------------------------------------------- Leibniz-Rechenzentrum ! <mailto:St...@lr...> Barer Str. 21 ! Fax: +49 89 2809460 80333 Muenchen, Germany ! Tel: +49 89 289-28840 |
|
From: Lionel B. <lio...@bo...> - 2005-08-15 12:42:42
|
Sascha Lucas wrote the following on 15.08.2005 10:27 : >> Ok, adding only the first header would indeed being meaningful in the >> example above. I was thinking of the future evolutions of SQLgrey and >> didn't explained myself well. >> >> The above works only because we don't use AWLs based on recipients... >> *yet*. In the future, SQLgrey will have an intermediate connect_awl >> table (with src, sender and recipient) and a rcpt_awl (with only src, >> recipients) in addition > > > Ok. Thats what I didn't know :-). > > Lionel: Thanks a lot for your long explanation. Can you tell me > roughly when SQLgrey will have rcpt_awl? > Depends on my free time... There's a lot to do in the 1.7.x branch and I may very well start by splitting the code into more manageable modules which will delay new functionnalities but then speed the development. > BTW: I just want to say that I (and many mail-users) are happy with > sqlgrey. > Knowing that people benefit from my work is a good motivation for me :-) Thanks! Lionel. |
|
From: Lionel B. <lio...@bo...> - 2005-08-15 12:37:41
|
Sascha Lucas wrote the following on 15.08.2005 10:28 : >> They might already do : optout email addresses are already added to >> the headers. >> >> But this is irrelevant if the sender or one of the mail gateway along >> the path follows the security recommendation of RFC2822 : send a >> separate copy of the message to the "To: and CC:" recipient and >> another to the "Bcc:" recipients. >> Anyway, I'll make the header configurable with some macros (already >> in my TODO), so that the local admin can make the decision. > > > Does this mean that Bcc: a@b.c and Bcc: d@e.f can see each other? Well > I don't know. As stated in the RFC2822, it is up to the implementation to decide. This said I'm not aware of any implementation showing each BCC to each other. > > But here in germany the people of data privacy (not me :-)), wouldn't > even be glad about RCPT-specific headers regardless of To:, CC: and Bcc:. > > BTW: does anybody know how the postfix-users mailinglist works? This > is the list, where I first saw multiple X-Greylist: headers. So I know > someone else is getting this mail. For now I had to look in the > postfix-logfile to see who the others are, but with RCPT-specific > headers every one can see. > > If the header is customizable (in future versions), there should be a > big warning in the config file. > This is the plan. The defaults won't show any "RCPT TO" value to be on the safe side. Lionel. |
|
From: Sascha L. <sas...@ru...> - 2005-08-15 08:28:24
|
> They might already do : optout email addresses are already added to the > headers. > > But this is irrelevant if the sender or one of the mail gateway along the > path follows the security recommendation of RFC2822 : send a separate copy of > the message to the "To: and CC:" recipient and another to the "Bcc:" > recipients. > Anyway, I'll make the header configurable with some macros (already in my > TODO), so that the local admin can make the decision. Does this mean that Bcc: a@b.c and Bcc: d@e.f can see each other? Well I don't know. But here in germany the people of data privacy (not me :-)), wouldn't even be glad about RCPT-specific headers regardless of To:, CC: and Bcc:. BTW: does anybody know how the postfix-users mailinglist works? This is the list, where I first saw multiple X-Greylist: headers. So I know someone else is getting this mail. For now I had to look in the postfix-logfile to see who the others are, but with RCPT-specific headers every one can see. If the header is customizable (in future versions), there should be a big warning in the config file. Thanks, Sascha. |
|
From: Sascha L. <sas...@ru...> - 2005-08-15 08:27:33
|
> Ok, adding only the first header would indeed being meaningful in the example > above. I was thinking of the future evolutions of SQLgrey and didn't > explained myself well. > > The above works only because we don't use AWLs based on recipients... *yet*. > In the future, SQLgrey will have an intermediate connect_awl table (with src, > sender and recipient) and a rcpt_awl (with only src, recipients) in addition Ok. Thats what I didn't know :-). Lionel: Thanks a lot for your long explanation. Can you tell me roughly when SQLgrey will have rcpt_awl? BTW: I just want to say that I (and many mail-users) are happy with sqlgrey. Thanks, Sascha. |
|
From: Lionel B. <lio...@bo...> - 2005-08-13 08:18:44
|
Michael Storz wrote the following on 13.08.2005 07:04 : >On Fri, 12 Aug 2005, Lionel Bouton wrote: > > > >>X-Greylist: delayed 01:21:26.853989 by SQLgrey-1.6.5 for ad...@ex... >>X-Greylist: recipient auto-whitelisted by SQLgrey-1.6.5 for >>for...@ex... >>X-Greylist: greylisting inactive for cri...@ex... in >>SQLgrey-1.6.5 >> >> >> > >This will mean, that in some rare situations BCCed recipients will see >each other :-( > > They might already do : optout email addresses are already added to the headers. But this is irrelevant if the sender or one of the mail gateway along the path follows the security recommendation of RFC2822 : send a separate copy of the message to the "To: and CC:" recipient and another to the "Bcc:" recipients. Anyway, I'll make the header configurable with some macros (already in my TODO), so that the local admin can make the decision. Lionel. |
18 messages has been excluded from this view by a project administrator.
