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
Greylist Auto Whitelist
Re: Greylist Auto Whitelist
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:
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 | | |
+---------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
Re: Greylist Auto Whitelist
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 | | |
| 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 | | |
Re: Greylist Auto Whitelist
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.
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.
Re: Greylist Auto Whitelist
So, the two relevant tables appear to be from_awl and domain_awl.
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)
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.
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 | |
+---------------+--------------+------+-----+---------------------+-----------------------------+
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);
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.
Re: Greylist Auto Whitelist
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.
-
- Posts: 33
- Joined: 08 May 2017 14:10
Re: Greylist Auto Whitelist
running under /etc/cron.monthly/sqlgrey_cleanup
Greetz
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)"
Re: Greylist Auto Whitelist
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.