Greylist Auto Whitelist

Questions and answers about how to do stuff
Post Reply
MooreSys
Posts: 6
Joined: 08 Apr 2018 02:43

Greylist Auto Whitelist

Post by MooreSys »

How can I purge all the entries that have been whitelist from passing the greylist? Is there not a setting that allows them to only stay approved for a certain amount of time? I am getting a lot of SPAM through that appears to be just by passing the greylist feature because they have in the past already got through. It would be great if I could set on long they could be auto whitelisted.

David
User avatar
pdwalker
Posts: 1553
Joined: 18 Mar 2015 09:16

Re: Greylist Auto Whitelist

Post by pdwalker »

That's a good question.

I thought the information would be in the sqlgrey database, stored in a table with hundreds/thousands of entries, but instead I find the database is almost completely empty.

Does anyone know where the information is actually stored? or does anyone have an sqlgrey database with more entries than I have? I'd be curious to compare your sqlgrey database with mine:

Code: Select all

mysql -u sqlgrey -p`cat /etc/EFA-Config|awk -F: '/SQLGREYSQLPWD/ {print($2)}'` sqlgrey -e "show table status"

Code: Select all

+---------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| Name          | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation         | Checksum | Create_options | Comment |
+---------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| config        | InnoDB |      10 | Compact    |    2 |           8192 |       16384 |               0 |            0 |         0 |           NULL | 2017-10-25 03:57:13 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
| connect       | InnoDB |      10 | Compact    |    1 |          16384 |       16384 |               0 |        32768 |         0 |           NULL | 2017-10-25 03:57:13 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
| domain_awl    | InnoDB |      10 | Compact    |    4 |           4096 |       16384 |               0 |        16384 |         0 |           NULL | 2017-10-25 03:57:13 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
| from_awl      | InnoDB |      10 | Compact    |   34 |            481 |       16384 |               0 |        16384 |         0 |           NULL | 2017-10-25 03:57:13 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
| optin_domain  | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |            0 |         0 |           NULL | 2017-10-25 03:57:13 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
| optin_email   | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |            0 |         0 |           NULL | 2017-10-25 03:57:13 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
| optout_domain | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |            0 |         0 |           NULL | 2017-10-25 03:57:13 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
| optout_email  | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |            0 |         0 |           NULL | 2017-10-25 03:57:13 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
+---------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
MooreSys
Posts: 6
Joined: 08 Apr 2018 02:43

Re: Greylist Auto Whitelist

Post by MooreSys »

I think you may have misunderstood my posting. I want to know how to get them out, not in. How do you purge the records in the domain_awl and from_awl?


| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+---------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| config | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2018-03-30 20:05:59 | NULL | NULL | latin1_swedish_ci | NULL | | |
| connect | InnoDB | 10 | Compact | 33 | 496 | 16384 | 0 | 32768 | 0 | NULL | 2018-03-30 20:05:59 | NULL | NULL | latin1_swedish_ci | NULL | | |
| domain_awl | InnoDB | 10 | Compact | 69 | 237 | 16384 | 0 | 16384 | 0 | NULL | 2018-03-30 20:05:59 | NULL | NULL | latin1_swedish_ci | NULL | | |
| from_awl | InnoDB | 10 | Compact | 1175 | 111 | 131072 | 0 | 98304 | 0 | NULL | 2018-03-30 20:05:59 | NULL | NULL | latin1_swedish_ci | NULL | | |
| optin_domain | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2018-03-30 20:05:59 | NULL | NULL | latin1_swedish_ci | NULL | | |
| optin_email | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2018-03-30 20:05:59 | NULL | NULL | latin1_swedish_ci | NULL | | |
| optout_domain | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2018-03-30 20:05:59 | NULL | NULL | latin1_swedish_ci | NULL | | |
| optout_email | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2018-03-30 20:05:59 | NULL | NULL | latin1_swedish_ci | NULL | | |
User avatar
pdwalker
Posts: 1553
Joined: 18 Mar 2015 09:16

Re: Greylist Auto Whitelist

Post by pdwalker »

I understood you perfectly.

To answer your question means knowing where and how the information is actually stored. Since my data is not what I expected it to be, I was looking for confirmation that my data is atypical rather than typical and from there we can work out a solution to your problem.
User avatar
pdwalker
Posts: 1553
Joined: 18 Mar 2015 09:16

Re: Greylist Auto Whitelist

Post by pdwalker »

So, the two relevant tables appear to be from_awl and domain_awl.

Code: Select all

MariaDB [sqlgrey]> describe domain_awl;
+---------------+--------------+------+-----+---------------------+-----------------------------+
| Field         | Type         | Null | Key | Default             | Extra                       |
+---------------+--------------+------+-----+---------------------+-----------------------------+
| sender_domain | varchar(255) | NO   | PRI | NULL                |                             |
| src           | varchar(39)  | NO   | PRI | NULL                |                             |
| first_seen    | timestamp    | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| last_seen     | timestamp    | NO   | MUL | 0000-00-00 00:00:00 |                             |
+---------------+--------------+------+-----+---------------------+-----------------------------+

Code: Select all

MariaDB [sqlgrey]> describe from_awl;
+---------------+--------------+------+-----+---------------------+-----------------------------+
| Field         | Type         | Null | Key | Default             | Extra                       |
+---------------+--------------+------+-----+---------------------+-----------------------------+
| sender_name   | varchar(64)  | NO   | PRI | NULL                |                             |
| sender_domain | varchar(255) | NO   | PRI | NULL                |                             |
| src           | varchar(39)  | NO   | PRI | NULL                |                             |
| first_seen    | timestamp    | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| last_seen     | timestamp    | NO   | MUL | 0000-00-00 00:00:00 |                             |
+---------------+--------------+------+-----+---------------------+-----------------------------+
And luckily those two fields have date related fields.

So, if you want to delete records older than a certain age, you can delete them based on either the first_seen or the last_seen timestamps.

If you want to delete the records that are older than a certain age, you can do that easily with the following where condition (assuming first_seen)

Code: Select all

where unix_timestamp(first_seen)  < UNIX_TIMESTAMP(NOW() - INTERVAL 60 DAY);
Having said that, if your spammers pass your greylisting, then this is not going to be effective as it means they are using a proper mailer to deliver mail and the next time they send you a message, they'll be greylisted, they'll resend, and then they'll be whitelisted again - so you'll be introducing mail delays without reducing the amount of spam you get.

The fly-by-night spammers won't make it past the greylisting in the first place.

So I think you'll need to look for another solution in how to deal with those particular spammers.
MooreSys
Posts: 6
Joined: 08 Apr 2018 02:43

Re: Greylist Auto Whitelist

Post by MooreSys »

Do you know the command to actually do the delete them while I research for a better solution? I am not extremely good at Linux based SQL command. I know Microsoft SQL, but just not the Linux style SQL.
Odon Garma
Posts: 33
Joined: 08 May 2017 14:10

Re: Greylist Auto Whitelist

Post by Odon Garma »

running under /etc/cron.monthly/sqlgrey_cleanup

Code: Select all

#!/bin/bash

mysql -u sqlgrey -p`cat /etc/EFA-Config|awk -F: '/SQLGREYSQLPWD/ {print($2)}'` sqlgrey -e "DELETE FROM sqlgrey.from_awl   WHERE unix_timestamp(first_seen) < unix_timestamp(now() - INTERVAL 30 DAY)"
mysql -u sqlgrey -p`cat /etc/EFA-Config|awk -F: '/SQLGREYSQLPWD/ {print($2)}'` sqlgrey -e "DELETE FROM sqlgrey.domain_awl WHERE unix_timestamp(first_seen) < unix_timestamp(now() - INTERVAL 30 DAY)"
Greetz
User avatar
pdwalker
Posts: 1553
Joined: 18 Mar 2015 09:16

Re: Greylist Auto Whitelist

Post by pdwalker »

Yes, Odon's script above will do the job, but let me remind you that this is not really a fix to the actual problem you are trying to solve.
Post Reply