Re: [Sqlgrey-users] New entries SQLgrey's whitelist

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

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