sqlgrey-users — Users of SQLgrey

[Sqlgrey-users] clients_fqdn_whitelist candidate

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

# 14/05/2005
# smtp.mandriva.org[212.85.150.170]
smtp.mandriva.org
# Comment: Legit Mandriva (previously MandrakeSoft) Newsletter server
# Reason: Does not retry

--=20
Michel Bouissou <mi...@bo...> OpenPGP ID 0xDDE8AC6E
Appel de 200 Informaticiens pour le NON au Trait=E9 Constitutionnel
Europ=E9en: http://www.200informaticiens.ras.eu.org

Re: [Sqlgrey-users] slow performance

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

Michel Bouissou wrote the following on 13.05.2005 13:55 :

>Le Vendredi 13 Mai 2005 11:02, Lionel Bouton a =E9crit :
> =20
>
>>Could you post the output of 'postconf -n'? As SQLgrey's
>>only time consuming operations are SQL queries, I suspect something
>>other in your Postfix configuration is causing this. If you take SQLgre=
y
>>out of the loop is the answer from Postfix after "RCPT TO:" noticeably
>>quicker ?
>>   =20
>>
>
>I strongly suspect a DNS problem. He should check his resolv.conf is OK,=
 and=20
>that his machine name can resolve forward and reverse in DNS.
>
> =20
>
This was my first assumption too, but the policy service is accessed by
IP and deactivating it solves the problem.

Lionel.

Re: [Sqlgrey-users] slow performance

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

Beast wrote the following on 13.05.2005 11:17 :

>
>
> If I comment check_policy_service, then the response is quicker
> (arround 1 sec, with sqlgrey _always_ 15-20 sec).


Could you set the log level to debug :
loglevel = 4
in /etc/sqlgrey/sqlgrey.conf, send a couple of mails to your server and
send me the logs (postfix+sqlgrey) ?

Lionel

Re: [Sqlgrey-users] slow performance

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

Le Vendredi 13 Mai 2005 11:02, Lionel Bouton a =E9crit :
>
> Could you post the output of 'postconf -n'? As SQLgrey's
> only time consuming operations are SQL queries, I suspect something
> other in your Postfix configuration is causing this. If you take SQLgre=
y
> out of the loop is the answer from Postfix after "RCPT TO:" noticeably
> quicker ?

I strongly suspect a DNS problem. He should check his resolv.conf is OK, =
and=20
that his machine name can resolve forward and reverse in DNS.

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

Re: [Sqlgrey-users] slow performance

[Beast <be...@i6...>]

Lionel Bouton wrote:
> 
> 
> This is normal. Could you post the output of 'postconf -n'? As SQLgrey's
> only time consuming operations are SQL queries, I suspect something
> other in your Postfix configuration is causing this. If you take SQLgrey
> out of the loop is the answer from Postfix after "RCPT TO:" noticeably
> quicker ?
> 

This is related config in main.cf

smtpd_recipient_restrictions =
   reject_non_fqdn_recipient,
   permit_mynetworks,
   reject_unauth_destination,
   reject_unknown_recipient_domain,
   check_sender_access hash:/etc/postfix/sender_access,
   check_client_access hash:/etc/postfix/client_access,
#  reject_rbl_client relays.ordb.org,
#  reject_rbl_client sbl.spamhaus.org,
#  reject_rbl_client bl.spamcop.net,
#  reject_rbl_client sbl-xbl.spamhaus.org,
#  reject_rbl_client opm.blitzed.org,
#  reject_rbl_client list.dsbl.org,
#  reject_rbl_client dul.dnsbl.sorbs.net,
### sqlgrey
   check_policy_service inet:127.0.0.1:2501
   permit


If I comment check_policy_service, then the response is quicker (arround 
1 sec, with sqlgrey _always_ 15-20 sec).


-- 

--beast

Re: [Sqlgrey-users] slow performance

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

Beast wrote the following on 13.05.2005 10:35 :

> Lionel Bouton wrote:
>
>>
>> In which table ? SQLgrey uses a connect table for unknown clients and
>> then (if the clients reconnect) populates the from_awl and domain_awl
>> tables.
>>
>
> In connect table, other less than 6 entries.
>
>> To rule out any problem with the database itself, how much time does it
>> take to do a "SELECT count(DISTINCT sender_domain) FROM connect;" for
>> example ?
>>
> mysql> SELECT count(DISTINCT sender_domain) FROM connect;
> +-------------------------------+
> | count(DISTINCT sender_domain) |
> +-------------------------------+
> |                             6 |
> +-------------------------------+
> 1 row in set (0.05 sec)
>
>

This is normal. Could you post the output of 'postconf -n'? As SQLgrey's
only time consuming operations are SQL queries, I suspect something
other in your Postfix configuration is causing this. If you take SQLgrey
out of the loop is the answer from Postfix after "RCPT TO:" noticeably
quicker ?

Lionel

Re: [Sqlgrey-users] slow performance

[Beast <be...@i6...>]

Lionel Bouton wrote:
> Beast wrote the following on 12.05.2005 10:42 :
> 
> 
>>Hello,
>>
>>I run sqlgrey along with postfix on test environment (v1.5.5).
>>Time between "rcpt to" command and "450: grey listed for 1 minute" is
>>17 seconds, is this normal?
> 
> 
> 
> No, this is far from what is expected (should be always under the second
> and more in the 0.01 to 0.1 range). Especially with 1.5.x which is tuned
> for large workloads.
> 
> 
>>Database is mysql 4.1.7 and only contains 8 rows,
> 
> 
> 
> In which table ? SQLgrey uses a connect table for unknown clients and
> then (if the clients reconnect) populates the from_awl and domain_awl
> tables.
> 

In connect table, other less than 6 entries.

> To rule out any problem with the database itself, how much time does it
> take to do a "SELECT count(DISTINCT sender_domain) FROM connect;" for
> example ?
> 
mysql> SELECT count(DISTINCT sender_domain) FROM connect;
+-------------------------------+
| count(DISTINCT sender_domain) |
+-------------------------------+
|                             6 |
+-------------------------------+
1 row in set (0.05 sec)


-- 

--beast

Re: [Sqlgrey-users] slow performance

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

Beast wrote the following on 12.05.2005 10:42 :

>
> Hello,
>
> I run sqlgrey along with postfix on test environment (v1.5.5).
> Time between "rcpt to" command and "450: grey listed for 1 minute" is
> 17 seconds, is this normal?


No, this is far from what is expected (should be always under the second
and more in the 0.01 to 0.1 range). Especially with 1.5.x which is tuned
for large workloads.

>
> Database is mysql 4.1.7 and only contains 8 rows,


In which table ? SQLgrey uses a connect table for unknown clients and
then (if the clients reconnect) populates the from_awl and domain_awl
tables.

To rule out any problem with the database itself, how much time does it
take to do a "SELECT count(DISTINCT sender_domain) FROM connect;" for
example ?

Lionel.

[Sqlgrey-users] slow performance

[Beast <be...@i6...>]

Hello,

I run sqlgrey along with postfix on test environment (v1.5.5).
Time between "rcpt to" command and "450: grey listed for 1 minute" is 17 
seconds, is this normal?
Database is mysql 4.1.7 and only contains 8 rows, in postfix I did not 
use rbl yet.


-- 

--beast

Re: [Sqlgrey-users] Split of from_awl into dsn_awl needed

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

On Sat, 7 May 2005, Lionel Bouton wrote:

> Michael Storz wrote the following on 07.05.2005 00:13 :
>
> >Analyzing our from_awl, I found the following:
> >
> >The table has 365.208 entries from 178.026 different ip addresses.
> >>From these ip addresses
> >
> >- 129.210 have exactly one entry and this is with sender_domain =
> >  "-undef-"
> >- 38.904 have only entries without sender_domain = "-undef-"
> >- only 9.912 have entries with both kind of sender_domains
> >
> >If we split the from_awl in 2 tables
> >
> >- from_awl: sender_domain <> "-undef-"
> >- dsn_awl:  sender_domain = "-undef-"
> >
> >(...)
> >
> >
>
> Ok. I consider this a design bug. In the original design from_awl is the
> first step towards domain_awl. But DSNs can't go into domain_awl
> (obviously because of the lack of domain...). This won't make it in
> 1.6.0, but it is now in my TODO.
>

Well, I wouldn't call it a design bug, I would call it an optimization.
This sounds much more positiv :-) What I am trying is, to get all the
backscatter away from from_awl. At the moment backscatter mainly results
from DSNs and forwards as far as I can see. For both, I've suggested new
tables.

I am interested in, how stable do we get from_awl and domain_awl? Which
leads us to the question how stable are the relationships of email
communications. How many new communication partners are found, how many
old relationships will end? What is the percentage we can expect?

> >If we include connect_awl, I don't think we need a split of this table,
> >because the backscatter DSNs will propagate fast into the dsn_awl, only
> >normal DSNs will stay in connect_awl.
> >
> >
>
> This I don't understand. How do we make the difference between
> backscatter DSNs and normal DSNs ?

Sorry, my explanation was a little bit too short. What I meant was, most
of the times a spammer uses one of our domains as the originator of his
spams, the left side was generated. Therefore, DSNs coming back to us were
directed to a lot of different recipients. Aggregation will move such DSNs
very fast to dsn_awl. On the other side, if a local user makes an error
with a recipient address, most of these emails will not leave our system.
Only a few will be accepted by other systems and will then generate a DSN.
Therefore such DSNs will stay in connect_awl, because not enough DSNs are
available for aggregation. This is the reason why I said backscatter DSNs
will go to dsn_awl whereas normal DSNs will stay in connect_awl. Looking
at a single DSN, you are right, we can't decide if it is a backscatter DSN
or a normal DSN.

Michael Storz
-------------------------------------------------
Leibniz-Rechenzentrum   !   <mailto:St...@lr...>
Barer Str. 21           !   Fax: +49 89 2809460
80333 Muenchen, Germany !   Tel: +49 89 289-28840

Re: [Sqlgrey-users] Split of from_awl into dsn_awl needed

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

Michael Storz wrote the following on 07.05.2005 00:13 :

>Analyzing our from_awl, I found the following:
>
>The table has 365.208 entries from 178.026 different ip addresses.
>>From these ip addresses
>
>- 129.210 have exactly one entry and this is with sender_domain =
>  "-undef-"
>- 38.904 have only entries without sender_domain = "-undef-"
>- only 9.912 have entries with both kind of sender_domains
>
>If we split the from_awl in 2 tables
>
>- from_awl: sender_domain <> "-undef-"
>- dsn_awl:  sender_domain = "-undef-"
>
>(...)
>  
>

Ok. I consider this a design bug. In the original design from_awl is the
first step towards domain_awl. But DSNs can't go into domain_awl
(obviously because of the lack of domain...). This won't make it in
1.6.0, but it is now in my TODO.

>If we include connect_awl, I don't think we need a split of this table,
>because the backscatter DSNs will propagate fast into the dsn_awl, only
>normal DSNs will stay in connect_awl.
>  
>

This I don't understand. How do we make the difference between
backscatter DSNs and normal DSNs ?

Lionel.

Re: [Sqlgrey-users] Running several sqlgrey daemons in parallel

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

Michael Storz wrote the following on 06.05.2005 23:22 :

>Lionel,
>
>we would like to run 2 independant sqlgrey daemons in parallel on the same
>system, one for production, the other for test purposes. To be able to do
>this, we need 2 things:
>
>- be able to specify the logging name of the daemon. My suggestion is to
>  use as the defualt the process name of the daemon. You can get it by
>
>  my ($process_name) = $0 =~ m{.*/(.*)};
>  
>

Good idea. Done in my tree.

>  In addition a configuration variable to specify this name would be nice.
>  
>

I sure hope nobody prints sqlgrey.conf files or I won't be able to rest
under a tree without heavy branches falling on me...
"log_ident" conf entry added.

>- At the moment we can choose a different configuration file, but we are
>  unable to switch to a different configuration directory for all the
>  other files.
>

And this is something that bothers me too (forgot to put it in my TODO
though).

> Therefore we need a command line parameter for the
>  configuration directory in addition to the configuration file.
>  
>

In fact I'd prefer this to be a configuration variable. "conf_dir" conf
entry added.

This will be in 1.5.7 (working on a report tool now). 1.5.7 should be a
feature freeze before a stable 1.6.0.

Regards,

Lionel.

Re: [Sqlgrey-users] modular design

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

Michael Storz wrote the following on 06.05.2005 23:11 :

>>Hum... There are problems with separating the propagations from the
>>greylisting.
>>* It will create stale entries in the bottom awls which will be fed by
>>the greylister itself due to race conditions between the greylister and
>>the separate daemons/scripts (not bad, just annoying and reflect what
>>can already happen when multiple SQLgrey instances access the same DB).
>>* You'll have more overhead because the propagation algorithms will have
>>to query the database for the entries they have to move, now SQLgrey
>>only query the src it is working on, the external daemons will have to
>>select these srcs by querying the database.
>>* You'll have to schedule the propagation algorithms carefully : not to
>>slow or you will lose awl perfs, not to fast or you will bring the DB
>>down to its knees. Today the scheduling is not needed as the propagation
>>algorithms are event-driven (and so are automagically at the ideal point).
>>
>>The event-driven aspect is quite important if you want to:
>>- maintain control of what happens on the global scale,
>>- avoid querying large amounts of data to extract which part should be
>>processed.
>>    
>>
>
>I'm not sure, if I understand this correctly. As the underlying database
>engine does not allow transactions, you will always have the possibility
>of interference of parallel running daemons, which access the same data.
>If the sequence of operations (insert, delete, update) are carefully
>planned with the parallel access in mind, no big problems should occur.
>
>  
>

Indeed no big problems will occur. The annoying effects (that can
already happen and are nothing to be afraid of) I'm speaking of are the
awl entries that could be created in from_awl although an entry in
domain_awl supercedes it.

The main problem I see with separate independant daemons is that the
propagation algorithms must select from the whole awl tables the entries
they want to handle. I don't like this for two reasons, this :
- is inefficient on a purely design standpoint (you have to query the
database for an information you could get directly from the greylister),
- causes load spikes.

What I would prefer to see is some key points in the code where you
could register hooks. Let's say for example that every time an entry is
ready to be added to the from_awl, any registered hook will be able to
short-circuit the default behaviour of adding the entry to from_awl and
do whatever it wants with the entry. You could then add the propagation
to higher-level awls at this point.

>We are running our external propagation algorithms every 5 minutes and it
>does not seem to bring mysql down to its knees. Since the scripts only
>request all the new data of the last 6 minutes, this is not much load for
>mysql. The processing of the data however does need some time, since heavy
>DNS queries are done, which in case of spammer domains may take a while to
>complete or get a timeout. With the momentary desing of sqlgrey -
>multiplexing - it is not possible to do this event-driven, response time
>would be terrible. To allow DNS based queries, sqlgrey has to go to
>prefork, where several threads run in parallel like the implementaton of
>amavisd-new.
>  
>

As long as SQLgrey can answer in a timely fashion (and frankly it should
or we'll have serious problems) prefork can only bring marginal speedups
(and probably slowdowns if not tuned properly). Nothing prevents a fork
in SQLgrey's code (or a module's one for that matter) as is already done
for the cleanups though. For example, if I understand correctly, the DNS
query only comes after the greylisting, the answer to this query isn't
needed to return an answer to Postfix. You could then fork, returning
your answer to the main code while processing the data asynchronously
(in fact I could already implement forking in the code to do some DB
processing asynchronously, mainly the AWL propagations).

In the example above where the entry is about to be added to from_awl,
the hook could fork, tell SQLgrey to let the message pass (and decide if
you want the from_awl entry to be created by SQLgrey or not) and
meanwhile do whatever you want with the "src, sender_name,
sender_domain, rcpt, first_time" array. You could do DNS queries at this
point or if you want, you can avoid forking and push this information to
another daemon through a socket or even log the entry for future
batch-processing if you feel like it.

>I would love to hack some perl code together to implement at least some of
>these features. Unfortunately, I'm not allowed to do it, because I have to
>manage some other projects for our messing system. Therefore, I hope you
>are keen to implement these features :-)
>
>  
>

Not everything, not tonight :-) But these paths are interesting and help
generate other ideas.

Thanks,

Lionel.

[Sqlgrey-users] Correction: Split of from_awl into dsn_awl needed

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

Sorry, mixed the numbers of entries wih the one of ip addrs:

On Sat, 7 May 2005, Michael Storz wrote:

> Analyzing our from_awl, I found the following:
>
> The table has 365.208 entries from 178.026 different ip addresses.
> From these ip addresses
>
> - 129.210 have exactly one entry and this is with sender_domain =
>   "-undef-"
> - 38.904 have only entries without sender_domain = "-undef-"
> - only 9.912 have entries with both kind of sender_domains
>
> If we split the from_awl in 2 tables
>
> - from_awl: sender_domain <> "-undef-"
> - dsn_awl:  sender_domain = "-undef-"
>
> we get a massive reduction of entries in the from_awl and also a massive
> reduction of table size, since we do not have to store sender_name and
> sender_domain in dsn_awl.
>
> In our case, dsn_awl would have 139.122 entries and from_awl 48.816

In our case, dsn_awl would have 139.122 entries/ip addresses and from_awl
226.086 entries from 48.816 ip addresses.

> entries. Since we know which table to query based on
> sender_domain/sender_name no additional table lookups are needed.
>
> Another advantage would be that the from_awl does not change so much as
> before, because all the DSNs which result as backscatter of the spammers
> are excluded now. And we could decide to use a different awl_age for the
> tables.
>
> If we include connect_awl, I don't think we need a split of this table,
> because the backscatter DSNs will propagate fast into the dsn_awl, only
> normal DSNs will stay in connect_awl.
>
> Michael Storz
> -------------------------------------------------
> Leibniz-Rechenzentrum   !   <mailto:St...@lr...>
> Barer Str. 21           !   Fax: +49 89 2809460
> 80333 Muenchen, Germany !   Tel: +49 89 289-28840
>

Michael Storz
-------------------------------------------------
Leibniz-Rechenzentrum   !   <mailto:St...@lr...>
Barer Str. 21           !   Fax: +49 89 2809460
80333 Muenchen, Germany !   Tel: +49 89 289-28840

[Sqlgrey-users] Split of from_awl into dsn_awl needed

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

Analyzing our from_awl, I found the following:

The table has 365.208 entries from 178.026 different ip addresses.
From these ip addresses

- 129.210 have exactly one entry and this is with sender_domain =
  "-undef-"
- 38.904 have only entries without sender_domain = "-undef-"
- only 9.912 have entries with both kind of sender_domains

If we split the from_awl in 2 tables

- from_awl: sender_domain <> "-undef-"
- dsn_awl:  sender_domain = "-undef-"

we get a massive reduction of entries in the from_awl and also a massive
reduction of table size, since we do not have to store sender_name and
sender_domain in dsn_awl.

In our case, dsn_awl would have 139.122 entries and from_awl 48.816
entries. Since we know which table to query based on
sender_domain/sender_name no additional table lookups are needed.

Another advantage would be that the from_awl does not change so much as
before, because all the DSNs which result as backscatter of the spammers
are excluded now. And we could decide to use a different awl_age for the
tables.

If we include connect_awl, I don't think we need a split of this table,
because the backscatter DSNs will propagate fast into the dsn_awl, only
normal DSNs will stay in connect_awl.

Michael Storz
-------------------------------------------------
Leibniz-Rechenzentrum   !   <mailto:St...@lr...>
Barer Str. 21           !   Fax: +49 89 2809460
80333 Muenchen, Germany !   Tel: +49 89 289-28840

[Sqlgrey-users] Running several sqlgrey daemons in parallel

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

Lionel,

we would like to run 2 independant sqlgrey daemons in parallel on the same
system, one for production, the other for test purposes. To be able to do
this, we need 2 things:

- be able to specify the logging name of the daemon. My suggestion is to
  use as the defualt the process name of the daemon. You can get it by

  my ($process_name) = $0 =~ m{.*/(.*)};

  In addition a configuration variable to specify this name would be nice.

- At the moment we can choose a different configuration file, but we are
  unable to switch to a different configuration directory for all the
  other files. Therefore we need a command line parameter for the
  configuration directory in addition to the configuration file.

Michael Storz
-------------------------------------------------
Leibniz-Rechenzentrum   !   <mailto:St...@lr...>
Barer Str. 21           !   Fax: +49 89 2809460
80333 Muenchen, Germany !   Tel: +49 89 289-28840

Re: [Sqlgrey-users] modular design

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

On Sat, 30 Apr 2005, Lionel Bouton wrote:

> Michael Storz wrote the following on 29.04.2005 16:10 :
>
...

> >If the code would be separated into several packages,
> >other people could implement daemons for different MTAs like sendmail with
> >milter, exim or qmail. All of these daemons would be able to use the
> >package for grey-, black- and whitelisting. Since we are not using
> >postfix, we had to struggle to code glueware which emulates the postfix
> >policy protocol.
> >
>
> Spliting the code will probably happen sooner or later. SQLgrey is
> starting to look a little too bloated to my taste... I would like to
> avoid this for 1.6.0 though, because it will probably take some time and
> heavy surgery :-)
>

I agree. I thought it would be nice to have it for a 2.X release.
...

>
> >Second: For smaller sites it is definitely nice to have one daemon, which
> >makes all the work. Just install the software and let it run. In our case
> >however, I would like to be able to tune the system in such a way that it
> >fits our needs. E.g. I would like to separate the checking of the
> >databases from the different propagation algorithms, which transports data
> >from one table to another, into separate daemons or scripts.
> >
>
> Hum... There are problems with separating the propagations from the
> greylisting.
> * It will create stale entries in the bottom awls which will be fed by
> the greylister itself due to race conditions between the greylister and
> the separate daemons/scripts (not bad, just annoying and reflect what
> can already happen when multiple SQLgrey instances access the same DB).
> * You'll have more overhead because the propagation algorithms will have
> to query the database for the entries they have to move, now SQLgrey
> only query the src it is working on, the external daemons will have to
> select these srcs by querying the database.
> * You'll have to schedule the propagation algorithms carefully : not to
> slow or you will lose awl perfs, not to fast or you will bring the DB
> down to its knees. Today the scheduling is not needed as the propagation
> algorithms are event-driven (and so are automagically at the ideal point).
>
> The event-driven aspect is quite important if you want to:
> - maintain control of what happens on the global scale,
> - avoid querying large amounts of data to extract which part should be
> processed.

I'm not sure, if I understand this correctly. As the underlying database
engine does not allow transactions, you will always have the possibility
of interference of parallel running daemons, which access the same data.
If the sequence of operations (insert, delete, update) are carefully
planned with the parallel access in mind, no big problems should occur.

We are running our external propagation algorithms every 5 minutes and it
does not seem to bring mysql down to its knees. Since the scripts only
request all the new data of the last 6 minutes, this is not much load for
mysql. The processing of the data however does need some time, since heavy
DNS queries are done, which in case of spammer domains may take a while to
complete or get a timeout. With the momentary desing of sqlgrey -
multiplexing - it is not possible to do this event-driven, response time
would be terrible. To allow DNS based queries, sqlgrey has to go to
prefork, where several threads run in parallel like the implementaton of
amavisd-new.
...

>
> >What kind of whitelist tables are possible? Well, we have 5 variables:
> >
> >- IP: IP adress of sending email server
> >- ON: Originator Name
> >- OD: Originator Domain
> >- RN: Recipient Name
> >- RD: Recipient Domain
> >
> >This leads to 32 different possibilities:
> >
> >
>
> This is a little more complex than that... You can add to these 5
> variables : time (probably first/last), helo, hits and some other values
> you can get through the policy protocol (SASL auth, fqdn). But you can
> probably blow huge holes in the matrix by removing the combinations that
> don't make sense (ON without OD isn't really useful for example)...
>

I agree, there are a lot more variables to consider. What I tried was to
see all possibilities from the variables we use at the moment in from_awl
and domain_awl for whitelisting. As you can see, 2 of your new tables fit
nicely in this concept, whereas the other 2 bring a new dimension to this:

- exception processing of whitelist tables.

This is something which could be valuable for other whitelists too. E.g. I
think every automatic whitelist should have an exception table, which is
manually configured. At the moment I have no example, but I could imagine
that at some point in the future I would wish to express that some ip addr
and/or domain should not be propagated to the next awl.

> >I'll stop here, because this is a lot of information to think about. But
> >hopefully I showed some ideas of where sqlgrey could evolve into.
> >
> >
>
> And I thank you. Quite ambitious! This will take some time to get there...

I would love to hack some perl code together to implement at least some of
these features. Unfortunately, I'm not allowed to do it, because I have to
manage some other projects for our messing system. Therefore, I hope you
are keen to implement these features :-)

Michael Storz
-------------------------------------------------
Leibniz-Rechenzentrum   !   <mailto:St...@lr...>
Barer Str. 21           !   Fax: +49 89 2809460
80333 Muenchen, Germany !   Tel: +49 89 289-28840

Re: FULL versus SMART (was Re: [Sqlgrey-users] Spammer alex@hotmail.com benefits from from_awl)

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

On Sat, 30 Apr 2005, Lionel Bouton wrote:

> Michael Storz wrote the following on 29.04.2005 11:28 :
>
> >- Higher delay FULL can have compared to SMART:
> >  The right medizine against that are good whitelists!
> >
>
> whitelists are good for delays I agree. But until you see a new IP, you
> don't put it in your whitelists...
>
> > Good means the
> >  number of whitelists and the content of whitelists. In addition to the
> >  standard sqlgrey algorithms for filling the tables via traffic analysis,
> >  we have implemented our own algorithms :-)
> >
> >  * fast propagation (fills from_awl): This algorithm is based on the
> >    trust we have about a sending MTA. If we trust it, we accept the
> >    email, even if there is no entry about this triple in the whitelists.
> >  * MX-check (fills domain_awl): if outgoing and incoming MTAs are the
> >    same, put an entry in domain_awl.
> >  * A-check (fills domain_awl): if sending MTA sends emails for its
> >    hostname only, put it in domain_awl.
> >
> >  These additional algorithms give us a lot of entries in our from_awl and
> >  domain_awl and therefore reduce the delay significantly. And the last 2
> >  algorithms only work with FULL, not with SMART, with the current design
> >  of sqlgrey. Sorry for you, guys :-)
> >
> >
> >
>
> How do you implement them ? One way to do it would be to modify
> SQLgrey's log to log the whole IP address of each new connection (in
> fact I was sorry to not have them in log, so I'll have the log format
> changed to do this) and have a little daemon tailing the log file
> looking for
> "^<syslog_header> sqlgrey: grey: new: (<ip>) .*" lines
> doing DNS requests and adding entries in the database.

Since we are using FULL instead of SMART, the full ip address is included
in table from_awl. cron starts my script every 5 minutes. At the moment it
selects all entries used in the last 6 minutes (last_seen) and in the
future all new entries in the last 6 minutes. For these entries DNS
lookups are made and relevant records inserted in domain_awl.

>
> >  About additional whitelists, forward_awl/rcpt_awl is one of them. At the
> >  moment fast propagation replaces this table, because most of the time we
> >  accept immidiately all the spam mails from forwarding where the remote
> >  MTA does not use greylisting, but at the cost of many unnecessary
> >  entries in from_awl.
> >
> >  Another one would be prevalidation as implented in other greylisting
> >  software. here you put the tuple originator/recipient without an ip
> >  address in a table for every email you send out.
> >
> >
>
> Interesting. How is it done? The policy service is called before
> permit_mynetworks and the policy service is taught the value of
> "mynetworks" ?
> This can be problematic :
> Here is part of my smtpd_recipient_restrictions :
> (...)
>         permit_mynetworks
>         reject_unknown_recipient_domain
>         reject_unauth_destination
>         check_helo_access hash:/etc/postfix/smtp_helo_blacklist
>         reject_unlisted_recipient
>         check_policy_service inet:127.0.0.1:2501
>
> If I move check_policy_service before permit_my_networks, I'll have lots
> of crap passing through the policy service which will be rejected later.
> One solution would be to have two separate policy services, one called
> before permit_mynetworks which would only process "mynetworks" sources
> in order to whitelist some tuples and the classic greylister later.
> That's for the distant future of SQLgrey :-)
>

I can't say how it could be done with postfix. In our case we have
different queues for incomming and outgoing emails. We can programm our
own daemon for outgoing emails, which call an interface, which put the
relevant records in the prevalidation table.

> >  Actually, what I want is to strengthen the trust in the sending MTA,
> >  e.g. to use the domain from HELO/ELHO
> >
>
> how do use this domain (isn't it an hostname btw)? I'm thinking about
> MXs/relays for several domains.
>

I would use it as string value with no special meaning. Instead of
checking for the ip address only, I would check for the tuple (ip address,
helo domain string) in the various tables. Normmaly there should be no
difference checking the ip adress or this tuple. I assume the following
differences could exist, where multiple entries exist for one ip address
but different helo domain strings:

- virtual outgoing MTAs are running on the same ip address, each of them
  announcing a different virtual mailserver via the helo domain. Another
  evidence for this would be several PTR records for this ip address which
  show the different virtual servers
- dynamic ip addresses: every time a dynamic ip address is assigned to a
  new MTA, this MTA will use a different helo domain
- spammer, who tries to get more randomness into the header of an email
  via the logged hel domain in received lines. He would use a different
  helo domain for every sent email.

If using the helo domain would indeed strenghten the trust in the sending
MTA or if it is just useful for traffic analysis and better understanding
of what different MTAs are doing, we would see, if we are using this
field.


Michael Storz
-------------------------------------------------
Leibniz-Rechenzentrum   !   <mailto:St...@lr...>
Barer Str. 21           !   Fax: +49 89 2809460
80333 Muenchen, Germany !   Tel: +49 89 289-28840

[Sqlgrey-users] RELEASE: 1.5.6 is on sourceforge

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

Hi,

IPv6 and optin-optout at last!

- IPv6
The database layout changed to accomodate IPv6 needs. SQLite 3.2.1,
MySQL 4.0.24 and PostgreSQL 8.0.1 migrations where tested.

Unfortunately I don't have any IPv6 connectivity to test the whole code,
so please report (I'm especially interested by smart and classc
configurations).

- Optin-optout
see README.OPTINOUT
oups, just realised it isn't in the spec file yet, it won't be installed
by a rpm built directly from the tar.bz2...

Happy greylisting,

Lionel.

Re: [Sqlgrey-users] Bug report

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

Jim Richardson wrote the following on 01.05.2005 16:21 :

> I had a vpn hiccup last night that lasted about 10 minutes. Sqlgrey
> reported the Lost DB, but then died.
> The logs contain the entries below if it helps. I am running 1.5.4
>
> May  1 05:53:48 server23 sqlgrey: Error: couldn't get now() from DB:
> Lost connection to MySQL server during query
> May  1 05:53:48 server23 sqlgrey: Forked cleanup child (16690)
> May  1 05:56:57 server23 sqlgrey: Can't connect to DB: Can't connect
> to MySQL server on 'server82.vpn.domain.tld' (110)
> May  1 05:56:57 server23 sqlgrey: Can't connect to DB: Can't connect
> to MySQL server on 'server82.vpn.domain.tld' (110)
> May  1 05:57:16 server23 sqlgrey: fatal: Can't call method
> "prepare_cached" on unblessed reference at /usr/sbin/sqlgrey line 203.
> May  1 06:00:25 server23 sqlgrey: Can't connect to DB: Can't connect
> to MySQL server on 'server82.vpn.domain.tld' (110)
> May  1 06:00:25 server23 sqlgrey: fatal: Can't call method "do" on
> unblessed reference at /usr/sbin/sqlgrey line 177.
>

Seems I found the problem (createdb was accessing the database handler
without checking it was sane and automaticaly created one non-blessed
hash in the process).
This bug should only be triggered with MySQL and SQLite (although being
disconnect from a SQLite DB should be quite uncommon :-)).

The fix is in my tree, I'll probably release 1.5.6 with it, IPv6 and
optin/optout this evening (GMT time).

Lionel.

[Sqlgrey-users] Bug report

[Jim R. <ji...@ri...>]

I had a vpn hiccup last night that lasted about 10 minutes. Sqlgrey reported the Lost DB, but then died.
The logs contain the entries below if it helps. I am running 1.5.4

May  1 05:53:48 server23 sqlgrey: Error: couldn't get now() from DB: Lost connection to MySQL server during query
May  1 05:53:48 server23 sqlgrey: Forked cleanup child (16690)
May  1 05:56:57 server23 sqlgrey: Can't connect to DB: Can't connect to MySQL server on 'server82.vpn.domain.tld' (110)
May  1 05:56:57 server23 sqlgrey: Can't connect to DB: Can't connect to MySQL server on 'server82.vpn.domain.tld' (110)
May  1 05:57:16 server23 sqlgrey: fatal: Can't call method "prepare_cached" on unblessed reference at /usr/sbin/sqlgrey line 203.
May  1 06:00:25 server23 sqlgrey: Can't connect to DB: Can't connect to MySQL server on 'server82.vpn.domain.tld' (110)
May  1 06:00:25 server23 sqlgrey: fatal: Can't call method "do" on unblessed reference at /usr/sbin/sqlgrey line 177.

Re: [Sqlgrey-users] modular design

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

Le Samedi 30 Avril 2005 01:08, Lionel Bouton a =E9crit :
>
>>From the whitelist site logs, 39 different IPs have checked the
> whitelists freshness this month.

I'm probably directy or indirectly responsible for 3 to 6 of them...

> 31 users are subscribed to this list.
> So I guess SQLgrey isn't on 1% of the worldwide mailservers yet :-)

Rather surprising. More and more sites are using some greylisting, and, a=
mong=20
the different greylisting systems that I have checked out, SQLgrey is the=
=20
best, from far.

--=20
Michel Bouissou <mi...@bo...> OpenPGP ID 0xDDE8AC6E
Appel de 200 Informaticiens pour le NON au Trait=E9 Constitutionnel
Europ=E9en: http://www.200informaticiens.ras.eu.org

Re: [Sqlgrey-users] modular design

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

Michael Storz wrote the following on 29.04.2005 16:10 :

>Since modular design was mentioned in some of the last emails, I will try
>to describe my ideas about a modular design of sqlgrey.
>
>First of all, I would like to separate all MTA specific parts from the
>part of the software which deals with grey-, white- and maybe
>blacklisting. This would have several benefits.
>
>1) From the discussion on this list I have the feeling only a few
>sites are using sqlgrey,
>

From the whitelist site logs, 39 different IPs have checked the
whitelists freshness this month. 31 users are subscribed to this list.
So I guess SQLgrey isn't on 1% of the worldwide mailservers yet :-)


> although I think it is one of the best
>implementations.
>

Thanks!

>  If the code would be separated into several packages,
>other people could implement daemons for different MTAs like sendmail with
>milter, exim or qmail. All of these daemons would be able to use the
>package for grey-, black- and whitelisting. Since we are not using
>postfix, we had to struggle to code glueware which emulates the postfix
>policy protocol.
>  
>

Spliting the code will probably happen sooner or later. SQLgrey is
starting to look a little too bloated to my taste... I would like to
avoid this for 1.6.0 though, because it will probably take some time and
heavy surgery :-)

>2) A separation of the code would allow to split functions into different
>daemons and/or scripts. E.g. prevalidation would be driven by outgoing
>emails, which in our case (not postfix) uses totally different daemons.
>Another example are our MX- and A-checks for filling the domain_awl. These
>are scripts startet by cron every 5 minutes. For these scripts I had to
>copy large amounts of code out from sqlgrey and to modify it to use it
>without a reference to netserver-daemon. It would be much easier for such
>scripts to just have a use-statement.
>  
>

Agreed.

>Second: For smaller sites it is definitely nice to have one daemon, which
>makes all the work. Just install the software and let it run. In our case
>however, I would like to be able to tune the system in such a way that it
>fits our needs. E.g. I would like to separate the checking of the
>databases from the different propagation algorithms, which transports data
>from one table to another, into separate daemons or scripts.
>

Hum... There are problems with separating the propagations from the
greylisting.
* It will create stale entries in the bottom awls which will be fed by
the greylister itself due to race conditions between the greylister and
the separate daemons/scripts (not bad, just annoying and reflect what
can already happen when multiple SQLgrey instances access the same DB).
* You'll have more overhead because the propagation algorithms will have
to query the database for the entries they have to move, now SQLgrey
only query the src it is working on, the external daemons will have to
select these srcs by querying the database.
* You'll have to schedule the propagation algorithms carefully : not to
slow or you will lose awl perfs, not to fast or you will bring the DB
down to its knees. Today the scheduling is not needed as the propagation
algorithms are event-driven (and so are automagically at the ideal point).

The event-driven aspect is quite important if you want to:
- maintain control of what happens on the global scale,
- avoid querying large amounts of data to extract which part should be
processed.

> This is the
>reason, why I requested the field first_seen in from_awl and domain_awl,
>which allows me to process all new fields independant from sqlgrey. This
>means I must be able to switch on and off all of the algorithms, which are
>used by sqlgrey in the moment.
>  
>


>Third: If I am able to swith on and off all of the algorithms, checking,
>propagation and maintenance, then I am also able to decide which of the
>algorithms I want to use, when running sqlgrey. E.g. a smaller site would
>not need the connect_awl and rcpt_awl and would propagate entries directly
>to from_awl. We, however, would use all of these tables for checking and
>use separate scripts to propagate entries from connect_awl to from_awl or
>rcpt_awl.
>
>  
>
Switching these algorithms can be done in sqlgrey.conf.

>Fourth: This leads to another modular design request: sequence of
>checks and propagations to execute. Do I first try to aggregate entries
>from connect_awl to rcpt_awl or to from_awl?
>

I was thinking about this too. I'm not sure if the order will have a
huge influence, I think the aggregation level for each propagation will
though.

> Could it be that one site
>prefers rcpt_awl first and another from_awl? There must be a
>sequentialization of these actions and a site should be able to determine
>it.
>
>Fifth: Let us make a step back and look at the overall design.
>Greylisting by itself has nothing to do with spam like e.g. SpamAssassin.
>A lot of people do confuse this. The influence on spam and virus infected
>emails is merely a side effect of greylisting (but at the end the reason
>why we are using greylisting). And the algorithm of greylisting ends at
>the moment we accept an email after a successful retry.
>
>The next step, the propagation of the triple to connect_awl or the tuple
>to from_awl/rcpt_awl has to do with whitelisting. I would like to turn our
>attention more to the whitelisting part of the software and separate it
>from the mere usage with greylisting.
>
>The first thing would be a rename of the tables from _awl = autowhitelist
>to just _wl. Why? Because several methodes exists to fill these tables
>with information.
>

But there are all more or less automatic :-) I tend to consider awls to
expire automatically and wls to be more static. This is just a name
though, not so important.

> These can be traffic analysis, like the aggregation
>algorithms, these can be other propagation algorithms, like our MX- and
>A-checks, which take entries from one table and propagate them to another
>table based on some conditions. But there are also algorithms possible
>like feeding back information from SpamAssassin into white- (or
>blacklists). Besides the renaming, the consequence would be to include (at
>least) to other fields in every entry:
>
>* name of algorithm, which created this entry, e.g. we already use
>  different algorithms to populate from_awl as well as domain_awl, and we
>  would really be able to tell the source of an entry when we examine and
>  analyze the tables.
>
>  
>

Good idea.

>* since the entries are then not automatically included, but maybe also
>  manually entered, in addition to first_seen, last_seen, we would need
>  an expiration date, to distinguish entries which should be deleted
>  automatically from entries which should stay. And different algorithms
>  could also mean different expiration dates, maybe one algorithm requests
>  4 days till expiration and another 35 days. In addition this would allow
>  incremental extension or reduction of expiration, maybe based on a spam
>  count.
>
>  
>

Makes sense.

>What kind of whitelist tables are possible? Well, we have 5 variables:
>
>- IP: IP adress of sending email server
>- ON: Originator Name
>- OD: Originator Domain
>- RN: Recipient Name
>- RD: Recipient Domain
>
>This leads to 32 different possibilities:
>  
>

This is a little more complex than that... You can add to these 5
variables : time (probably first/last), helo, hits and some other values
you can get through the policy protocol (SASL auth, fqdn). But you can
probably blow huge holes in the matrix by removing the combinations that
don't make sense (ON without OD isn't really useful for example)...

>I'll stop here, because this is a lot of information to think about. But
>hopefully I showed some ideas of where sqlgrey could evolve into.
>  
>

And I thank you. Quite ambitious! This will take some time to get there...

Lionel.

Re: FULL versus SMART (was Re: [Sqlgrey-users] Spammer alex@hotmail.com benefits from from_awl)

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

Michael Storz wrote the following on 29.04.2005 11:28 :

>- Higher delay FULL can have compared to SMART:
>  The right medizine against that are good whitelists!
>

whitelists are good for delays I agree. But until you see a new IP, you
don't put it in your whitelists...

> Good means the
>  number of whitelists and the content of whitelists. In addition to the
>  standard sqlgrey algorithms for filling the tables via traffic analysis,
>  we have implemented our own algorithms :-)
>
>  * fast propagation (fills from_awl): This algorithm is based on the
>    trust we have about a sending MTA. If we trust it, we accept the
>    email, even if there is no entry about this triple in the whitelists.
>  * MX-check (fills domain_awl): if outgoing and incoming MTAs are the
>    same, put an entry in domain_awl.
>  * A-check (fills domain_awl): if sending MTA sends emails for its
>    hostname only, put it in domain_awl.
>
>  These additional algorithms give us a lot of entries in our from_awl and
>  domain_awl and therefore reduce the delay significantly. And the last 2
>  algorithms only work with FULL, not with SMART, with the current design
>  of sqlgrey. Sorry for you, guys :-)
>
>  
>

How do you implement them ? One way to do it would be to modify
SQLgrey's log to log the whole IP address of each new connection (in
fact I was sorry to not have them in log, so I'll have the log format
changed to do this) and have a little daemon tailing the log file
looking for
"^<syslog_header> sqlgrey: grey: new: (<ip>) .*" lines
doing DNS requests and adding entries in the database.

>  About additional whitelists, forward_awl/rcpt_awl is one of them. At the
>  moment fast propagation replaces this table, because most of the time we
>  accept immidiately all the spam mails from forwarding where the remote
>  MTA does not use greylisting, but at the cost of many unnecessary
>  entries in from_awl.
>
>  Another one would be prevalidation as implented in other greylisting
>  software. here you put the tuple originator/recipient without an ip
>  address in a table for every email you send out.
>  
>

Interesting. How is it done? The policy service is called before
permit_mynetworks and the policy service is taught the value of
"mynetworks" ?
This can be problematic :
Here is part of my smtpd_recipient_restrictions :
(...)
        permit_mynetworks
        reject_unknown_recipient_domain
        reject_unauth_destination
        check_helo_access hash:/etc/postfix/smtp_helo_blacklist
        reject_unlisted_recipient
        check_policy_service inet:127.0.0.1:2501

If I move check_policy_service before permit_my_networks, I'll have lots
of crap passing through the policy service which will be rejected later.
One solution would be to have two separate policy services, one called
before permit_mynetworks which would only process "mynetworks" sources
in order to whitelist some tuples and the classic greylister later.
That's for the distant future of SQLgrey :-)

>- Trust in the sending MTA:
>  SMART reduces trust in the sending MTA about the handling of temporary
>  errors in a well behaved way based on the relevant RFCs.
>
>  Well behaved means
>
>  * trying to retransmit a message several times until a timeout of 3 - 5
>    days occur.
>  * retransmitting emails in a timely manner (minutes) and not only once
>    in 24 hours
>
>  For me this is the reason to use FULL. I want to get trust in the
>  sending MTA, the more the better. And if I have trust in a MTA, I will
>  accept emails as fast as I can.
>
>  
>

I understand your goals.

>  Actually, what I want is to strengthen the trust in the sending MTA,
>  e.g. to use the domain from HELO/ELHO
>

how do use this domain (isn't it an hostname btw)? I'm thinking about
MXs/relays for several domains.

Lionel.

[Sqlgrey-users] modular design

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

Since modular design was mentioned in some of the last emails, I will try
to describe my ideas about a modular design of sqlgrey.

First of all, I would like to separate all MTA specific parts from the
part of the software which deals with grey-, white- and maybe
blacklisting. This would have several benefits.

1) From the discussion on this list I have the feeling only a few
sites are using sqlgrey, although I think it is one of the best
implementations.  If the code would be separated into several packages,
other people could implement daemons for different MTAs like sendmail with
milter, exim or qmail. All of these daemons would be able to use the
package for grey-, black- and whitelisting. Since we are not using
postfix, we had to struggle to code glueware which emulates the postfix
policy protocol.

2) A separation of the code would allow to split functions into different
daemons and/or scripts. E.g. prevalidation would be driven by outgoing
emails, which in our case (not postfix) uses totally different daemons.
Another example are our MX- and A-checks for filling the domain_awl. These
are scripts startet by cron every 5 minutes. For these scripts I had to
copy large amounts of code out from sqlgrey and to modify it to use it
without a reference to netserver-daemon. It would be much easier for such
scripts to just have a use-statement.

Second: For smaller sites it is definitely nice to have one daemon, which
makes all the work. Just install the software and let it run. In our case
however, I would like to be able to tune the system in such a way that it
fits our needs. E.g. I would like to separate the checking of the
databases from the different propagation algorithms, which transports data
from one table to another, into separate daemons or scripts. This is the
reason, why I requested the field first_seen in from_awl and domain_awl,
which allows me to process all new fields independant from sqlgrey. This
means I must be able to switch on and off all of the algorithms, which are
used by sqlgrey in the moment.

Third: If I am able to swith on and off all of the algorithms, checking,
propagation and maintenance, then I am also able to decide which of the
algorithms I want to use, when running sqlgrey. E.g. a smaller site would
not need the connect_awl and rcpt_awl and would propagate entries directly
to from_awl. We, however, would use all of these tables for checking and
use separate scripts to propagate entries from connect_awl to from_awl or
rcpt_awl.

Fourth: This leads to another modular design request: sequence of
checks and propagations to execute. Do I first try to aggregate entries
from connect_awl to rcpt_awl or to from_awl? Could it be that one site
prefers rcpt_awl first and another from_awl? There must be a
sequentialization of these actions and a site should be able to determine
it.

Fifth: Let us make a step back and look at the overall design.
Greylisting by itself has nothing to do with spam like e.g. SpamAssassin.
A lot of people do confuse this. The influence on spam and virus infected
emails is merely a side effect of greylisting (but at the end the reason
why we are using greylisting). And the algorithm of greylisting ends at
the moment we accept an email after a successful retry.

The next step, the propagation of the triple to connect_awl or the tuple
to from_awl/rcpt_awl has to do with whitelisting. I would like to turn our
attention more to the whitelisting part of the software and separate it
from the mere usage with greylisting.

The first thing would be a rename of the tables from _awl = autowhitelist
to just _wl. Why? Because several methodes exists to fill these tables
with information. These can be traffic analysis, like the aggregation
algorithms, these can be other propagation algorithms, like our MX- and
A-checks, which take entries from one table and propagate them to another
table based on some conditions. But there are also algorithms possible
like feeding back information from SpamAssassin into white- (or
blacklists). Besides the renaming, the consequence would be to include (at
least) to other fields in every entry:

* name of algorithm, which created this entry, e.g. we already use
  different algorithms to populate from_awl as well as domain_awl, and we
  would really be able to tell the source of an entry when we examine and
  analyze the tables.

* since the entries are then not automatically included, but maybe also
  manually entered, in addition to first_seen, last_seen, we would need
  an expiration date, to distinguish entries which should be deleted
  automatically from entries which should stay. And different algorithms
  could also mean different expiration dates, maybe one algorithm requests
  4 days till expiration and another 35 days. In addition this would allow
  incremental extension or reduction of expiration, maybe based on a spam
  count.

What kind of whitelist tables are possible? Well, we have 5 variables:

- IP: IP adress of sending email server
- ON: Originator Name
- OD: Originator Domain
- RN: Recipient Name
- RD: Recipient Domain

This leads to 32 different possibilities:

Name of whitelist                               IP   ON   OD   RN   RD
========================================================================
reconnect ok / connection_wl                    X    X    X    X    X
                                                X    X    X    X    -
                                                X    X    X    -    X
from_wl                                         X    X    X    -    -
------------------------------------------------------------------------
                                                X    X    -    X    X
                                                X    X    -    X    -
                                                X    X    -    -    X
                                                X    X    -    -    -
------------------------------------------------------------------------
                                                X    -    X    X    X
                                                X    -    X    X    -
                                                X    -    X    -    X
domain_wl                                       X    -    X    -    -
------------------------------------------------------------------------
rcpt_wl (forward_wl)                            X    -    -    X    X
                                                X    -    -    X    -
                                                X    -    -    -    X
client_ip_whitelist / src_wl                    X    -    -    -
-
------------------------------------------------------------------------
preval_wl (prevalidation                        -    X    X    X    X
                                                -    X    X    X    -
                                                -    X    X    -    X
                                                -    X    X    -    -
------------------------------------------------------------------------
                                                -    X    -    X    X
                                                -    X    -    X    -
                                                -    X    -    -    X
                                                -    X    -    -    -
------------------------------------------------------------------------
                                                -    -    X    X    X
                                                -    -    X    X    -
                                                -    -    X    -    X
                                                -    -    X    -    -
------------------------------------------------------------------------
optout_rcpt                                     -    -    -    X    X
                                                -    -    -    X    -
optout_domain                                   -    -    -    -    X
no check, all emails whitelisted                -    -    -    -    -
------------------------------------------------------------------------

If you try to make a directed graph of the evolution/dependancies of the
tables based on number of variables, then you get 6 levels:

1. level:  1 node  (5 X)
2. level:  5 nodes (4 X)
3. level: 10 nodes (3 X)
4. level: 10 nodes (2 X)
5. level:  5 nodes (1 X)
6. level:  1 node  (0 X)

I visualized this as a 3 dimensional object with a tool called Zometool,
(see http://www.zometool.com/build.html). From this you can see, that
there is no sequential path through the whitelists, as I said above.

I'll stop here, because this is a lot of information to think about. But
hopefully I showed some ideas of where sqlgrey could evolve into.

Regards,
Michael Storz
-------------------------------------------------
Leibniz-Rechenzentrum   !   <mailto:St...@lr...>
Barer Str. 21           !   Fax: +49 89 2809460
80333 Muenchen, Germany !   Tel: +49 89 289-28840