Re: [Sqlgrey-users] Patches for SQLgrey

[Michal L. <ml...@lo...>]

Lionel Bouton wrote:

> We should fix the case where a spammer sends a message to all MX of a
> domain at the same time. The problem is that several SQLgrey instances
> sharing the same database do the following (simplifying a bit) :
> - check that the message doesn't match a whitelist (no),
> - check if it is already in the connect table (it isn't),
> - try to create an entry in the "connect" table.
> Only one instance can complete the last step, all others get an SQL
> error (and it's completely normal).

I don't think this can happen with current SQLgrey as the connect table
doesn't have a unique or primary index:

sub create_connect_table {
    # Note: no primary key, Mysql can't handle 500+ byte primary keys
    # connect should not become big enough to make it a problem
    $self->do("CREATE TABLE $tablename " .
              '(sender_name varchar(64) NOT NULL, ' .
              'sender_domain varchar(255) NOT NULL, ' .
              'src varchar(39) NOT NULL, ' .
              'rcpt varchar(255) NOT NULL, ' .
              'first_seen timestamp NOT NULL)')
        or $self->mydie(...);
}

sub create_connect_indexes($) {
    my $self = shift;
    $self->do("CREATE INDEX $connect" . '_idx ' .
             "ON $connect (src, sender_domain, sender_name)")
        or $self->mydie(...);
    $self->do("CREATE INDEX $connect" . '_fseen ' .
             "ON $connect (first_seen)")
        or $self->mydie(...);
}

Does the problem actually exist at all?

Michal