Gentoo Forums
Gentoo Forums
Gentoo Forums
Quick Search: in
sqlgrey questions -- RESOLVED
View unanswered posts
View posts from last 24 hours

 
Reply to topic    Gentoo Forums Forum Index Networking & Security
View previous topic :: View next topic  
Author Message
Fred Krogh
Veteran
Veteran


Joined: 07 Feb 2005
Posts: 1027
Location: Tujunga, CA

PostPosted: Wed May 04, 2016 9:54 pm    Post subject: sqlgrey questions -- RESOLVED Reply with quote

I think I have sqlgrey working using mysql, but all is not perfect. After a short period, in the from_awl table I see
Quote:
+-------------+---------------------+---------------+---------------------+---------------------+
| sender_name | sender_domain | src | first_seen | last_seen |
+-------------+---------------------+---------------+---------------------+---------------------+
| costcob2c_# | online.costco.com | 173.226.200 | 2016-05-04 12:45:35 | 2016-05-04 13:00:40 |
| -undef- | -undef- | 202.172.26 | 2016-05-04 12:10:49 | 2016-05-04 12:17:31 |
| mailer | infusionmail.com | 208.76.24 | 2016-05-04 11:40:50 | 2016-05-04 12:00:52 |
| -undef- | -undef- | 208.82.238 | 2016-05-04 11:47:21 | 2016-05-04 11:55:42 |
| bounce | mail.classmates.com | 208.84.41 | 2016-05-04 13:01:11 | 2016-05-04 13:21:12 |
| 1082-#-#-at | mail.cenew.top | 217.78.10.27 | 2016-05-04 13:05:39 | 2016-05-04 13:20:41 |
| 1076-#-#-at | mail.tinew.top | 23.247.67.116 | 2016-05-04 13:44:12 | 2016-05-04 13:59:14 |
| 1070-#-#-at | mail.qutime.top | 23.247.67.117 | 2016-05-04 12:45:07 | 2016-05-04 13:00:23 |
| dev7nrbf | hertz.tsce.net | 95.128.41 | 2016-05-04 11:41:56 | 2016-05-04 11:51:02 |
+-------------+---------------------+---------------+---------------------+---------------------+

My questions:

1. For all except the first one, I'd prefer not to get mail from those sites. I currently have reconnect_delay = 0, would changing that to 15 mean the others are not likely to get through.

2. In the table optout_domain, I have costco.com as one entry. I thought that meant that stuff from costco.com would not go through the grey listing process. Do I need some kind of wild card in front the entries in the optout_domain table so that things like this don't show up in from_awl.
I have also listed a bunch of email addresses that I don't want to greylist in optout_emailo as I know there are o.k.

3. These entries in from_awl. can I just delete them, and is there any way to keep the ones that got through not to come through again.

4. And spam is still coming through without any entries in from_awl. Any ideas on why?

5. In /etc/postfix/main.cf I have commented out "permit_sasl_authenticated,", as I thought not permitting something might eliminate unwatned stuff. Should I uncomment that?

6. [/quote]In sqlgrey.conf, I have optmethod = optout Might that be the problem?
Many thanks,
Fred


Last edited by Fred Krogh on Fri May 06, 2016 5:29 pm; edited 1 time in total
Back to top
View user's profile Send private message
Fred Krogh
Veteran
Veteran


Joined: 07 Feb 2005
Posts: 1027
Location: Tujunga, CA

PostPosted: Thu May 05, 2016 1:37 am    Post subject: Reply with quote

I can add another question which may clear up something from the previous? As I had things configured a user on my local network had a failure trying to send mail through my server. Message was "Relay access denied". I commented the following lines in postfix.cf
Quote:
#smtpd_recipient_restrictions =
# permit_sasl_authenticated,
# reject_invalid_hostname,
# reject_non_fqdn_hostname,
# reject_non_fqdn_sender,
# reject_non_fqdn_recipient,
# reject_unauth_destination
# reject_unknown_sender_domain,
# reject_unknown_recipient_domain,
# permit_mynetworks,
# check_policy_service inet:127.0.0.1:2501,
# reject_rbl_client sbl.spamhaus.org,
# reject_rbl_client cbl.abuseat.org,
# reject_rbl_client dul.dnsbl.sorbs.net,
# permit

This is stuff I had uncommented for the grey listing. The mail now goes through. I thought this section was for the grey listing of incoming mail. So what needs to be done to get the grey lisiting to work without preventing mail bein sent from other computers on my local network? Many thanks for any ideas.
Back to top
View user's profile Send private message
freke
Guru
Guru


Joined: 23 Jan 2003
Posts: 493
Location: Somewhere in Denmark

PostPosted: Thu May 05, 2016 7:52 am    Post subject: Reply with quote

Fred Krogh wrote:
I can add another question which may clear up something from the previous? As I had things configured a user on my local network had a failure trying to send mail through my server. Message was "Relay access denied". I commented the following lines in postfix.cf
Quote:
#smtpd_recipient_restrictions =
# permit_sasl_authenticated,
# reject_invalid_hostname,
# reject_non_fqdn_hostname,
# reject_non_fqdn_sender,
# reject_non_fqdn_recipient,
# reject_unauth_destination
# reject_unknown_sender_domain,
# reject_unknown_recipient_domain,
# permit_mynetworks,
# check_policy_service inet:127.0.0.1:2501,
# reject_rbl_client sbl.spamhaus.org,
# reject_rbl_client cbl.abuseat.org,
# reject_rbl_client dul.dnsbl.sorbs.net,
# permit

This is stuff I had uncommented for the grey listing. The mail now goes through. I thought this section was for the grey listing of incoming mail. So what needs to be done to get the grey lisiting to work without preventing mail bein sent from other computers on my local network? Many thanks for any ideas.


Code:
smtpd_client_restrictions =
        sleep 10
        warn_if_reject reject_unknown_reverse_client_hostname
smtpd_helo_restrictions =
        warn_if_reject reject_invalid_helo_hostname
        reject_non_fqdn_helo_hostname
smtpd_sender_restrictions =
        reject_unknown_sender_domain
        reject_non_fqdn_sender
smtpd_relay_restrictions =
        permit_mynetworks
        permit_sasl_authenticated
        reject_unauth_destination
smtpd_recipient_restrictions =
        permit_mynetworks
        permit_sasl_authenticated
        reject_unknown_recipient_domain
        reject_non_fqdn_recipient
        check_policy_service inet:127.0.0.1:2501
        check_policy_service inet:127.0.0.1:12525
        check_policy_service unix:private/policyd-spf
smtpd_data_restrictions =
        reject_unauth_pipelining


This is (more or less) my smtpd restrictions part of main.cf - don't know if the sleep 10 in client_restrictions prevents any spam - but at least it forces the spambots to wait 10 seconds before sending anything to my server.
Also got a few warn_if_reject entries - instead of simply rejecting... I've seen a few legit connections getting rejected with invalid_helo_hostname.

As for SQLgrey - my understanding of optin/optout is for choosing for which recipients SQLgrey should be enabled for?
If you always want mail for a given domain to passthrough w/o greylisting you could add it to /etc/sqlgrey/clients_fqdn_whitelist.local
ie. *.costo.com

Also for the delay set to 0 - I don't really know what that does, I've kept the default 15 minutes. ie. a client must wait 15 minutes from being greylisted before it can try again and successfully send the mail to your server.
And yes - you kan just delete the entries in the AWL-table you don't want there.
Back to top
View user's profile Send private message
Fred Krogh
Veteran
Veteran


Joined: 07 Feb 2005
Posts: 1027
Location: Tujunga, CA

PostPosted: Fri May 06, 2016 1:59 am    Post subject: Reply with quote

Many thanks freke for the response. I think the sending mail problem was fixed adding your "permit_mynetworks", under "smtpd_recipient_restrictions =". I have put entries like "*.costco.com" (no quotes) in /etc/sqlgrey/clients_fqdn_whitelist.local.
I have sqlgrey running, and I can tell that it is connected to mysql. But the spam is still coming through. Looking in the mysql database, there is no sign that anything is happening. I did delete all the entries in the tables "connect", and "from_awl". The uncommented lines I have in sqlgrey.conf are
Quote:
reconnect_delay = 5
max_connect_age = 24
awl_age = 60
group_domain_level = 2
db_type = mysql
db_name = sqlgrey
db_host = localhost
# db_port = default
db_user = sqlgrey
db_pass = It works!
When restarting sqlgrey, I get these messages (it seems to be working):
Quote:
May 5 16:27:18 mon1 sqlgrey[4946]: 2016/05/05-16:27:18 Server closing!
May 5 16:27:18 mon1 sqlgrey[6278]: Process Backgrounded
May 5 16:27:18 mon1 sqlgrey[6278]: 2016/05/05-16:27:18 sqlgrey (type Net::Server::Multiplex) starting
! pid(6278)
May 5 16:27:18 mon1 sqlgrey[6278]: Resolved [localhost]:2501 to [127.0.0.1]:2501, IPv4
May 5 16:27:18 mon1 sqlgrey[6278]: Binding to TCP port 2501 on host 127.0.0.1 with IPv4
May 5 16:27:18 mon1 sqlgrey[6278]: Setting gid to "973 973"
May 5 16:27:18 mon1 sqlgrey[6278]: Setting uid to "128"
May 5 16:27:19 mon1 sqlgrey[6278]: conf: warning: /etc/sqlgrey/clients_ip_whitelist.local not found o
r unreadable

Following freke, I have this in main.cf:
Quote:
smtpd_recipient_restrictions =
permit mynetworks
permit_sasl_authenticated
reject_invalid_hostname
reject_non_fqdn_hostname
reject_non_fqdn_sender
reject_non_fqdn_recipient
reject_unauth_destination
reject_unknown_sender_domain
reject_unknown_recipient_domain
check_policy_service inet:127.0.0.1:2501
check_policy_service inet:127.0.0.1:12525
check_policy_service unix:private/policyd-spf

Also I have created a table,
Quote:
MariaDB [sqlgrey]> describe whitelist;
+----------------+--------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------------------+-------+
| address | varchar(255) | NO | PRI | NULL | |
| last_sent_to | datetime | NO | | 0000-00-00 00:00:00 | |
| last_rcvd_from | datetime | NO | | 0000-00-00 00:00:00 | |
+----------------+--------------+------+-----+---------------------+-------+
and put a bunch of entries in it. This table was not created during the initialization, but I found a place that described how to create it. That table has a bunch of lower case email addresses in it.

I'm hoping someone can suggest where to look for why the spam keeps coming. Many Thanks,
Fred
Back to top
View user's profile Send private message
freke
Guru
Guru


Joined: 23 Jan 2003
Posts: 493
Location: Somewhere in Denmark

PostPosted: Fri May 06, 2016 5:45 am    Post subject: Reply with quote

Fred Krogh wrote:
Many thanks freke for the response. I think the sending mail problem was fixed adding your "permit_mynetworks", under "smtpd_recipient_restrictions =". I have put entries like "*.costco.com" (no quotes) in /etc/sqlgrey/clients_fqdn_whitelist.local.
I have sqlgrey running, and I can tell that it is connected to mysql. But the spam is still coming through. Looking in the mysql database, there is no sign that anything is happening. I did delete all the entries in the tables "connect", and "from_awl".
Following freke, I have this in main.cf:
Quote:
smtpd_recipient_restrictions =
permit mynetworks
permit_sasl_authenticated
reject_invalid_hostname
reject_non_fqdn_hostname
reject_non_fqdn_sender
reject_non_fqdn_recipient
reject_unauth_destination
reject_unknown_sender_domain
reject_unknown_recipient_domain
check_policy_service inet:127.0.0.1:2501
check_policy_service inet:127.0.0.1:12525
check_policy_service unix:private/policyd-spf

Also I have created a table,
Quote:
MariaDB [sqlgrey]> describe whitelist;
+----------------+--------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------------------+-------+
| address | varchar(255) | NO | PRI | NULL | |
| last_sent_to | datetime | NO | | 0000-00-00 00:00:00 | |
| last_rcvd_from | datetime | NO | | 0000-00-00 00:00:00 | |
+----------------+--------------+------+-----+---------------------+-------+
and put a bunch of entries in it. This table was not created during the initialization, but I found a place that described how to create it. That table has a bunch of lower case email addresses in it.

I'm hoping someone can suggest where to look for why the spam keeps coming. Many Thanks,
Fred


I'm not sure you want the two last check_policy_service-lines from my main.cf - they're for mail-filter/policyd-weight and mail-filter/pypolicyd-spf...

You could try setting the loglevel to 3 or 4 in sqlgrey.conf and have a look at the logfile when it's processing a mail (and/or post the log-snippet here).
Back to top
View user's profile Send private message
Fred Krogh
Veteran
Veteran


Joined: 07 Feb 2005
Posts: 1027
Location: Tujunga, CA

PostPosted: Fri May 06, 2016 1:17 pm    Post subject: Reply with quote

Many thanks for the suggestions. I have done as you suggested loglevel=4, and created /etc/sqlgrey/clients_ip_whitelist.local just to get rid of a diagnostic when it starts. /var/log/messages shows sqlgrey starting (without the warning this time), and then nothing more despite more spam coming in. I have checked the mysql users when sqlgrey is not running, and sqlgrey is not listed, but it is listed when sqlgrey is running. So it seems that sqlgrey can look at the sqlgrey database. Any other idea? I'm really at a loss.
Back to top
View user's profile Send private message
Fred Krogh
Veteran
Veteran


Joined: 07 Feb 2005
Posts: 1027
Location: Tujunga, CA

PostPosted: Fri May 06, 2016 1:28 pm    Post subject: Reply with quote

Just tried something that seems work, but I think it means another user on my network will not be able to send mail. That is I commented out "permit mynetworks" just below "smtpd_recipient_restrictions =" in main.cf. I then saw this in my the logs:
Quote:
May 6 06:19:56 mon1 postfix/smtpd[9170]: warning: database /etc/postfix/virtual.db is older than source file /etc/postfix/virtual
May 6 06:19:56 mon1 postfix/smtpd[9170]: warning: valid_hostname: misplaced delimiter: .
May 6 06:19:56 mon1 postfix/smtpd[9170]: connect from unknown[5.255.61.163]
May 6 06:19:56 mon1 sqlgrey[9157]: 2016/05/06-06:19:56 CONNECT TCP Peer: "[127.0.0.1]:59366" Local: "[127.0.0.1]:2501"
May 6 06:19:56 mon1 sqlgrey[9157]: optin: greylisting active for fkrogh@mathalacarte.com
May 6 06:19:56 mon1 sqlgrey[9157]: grey: unknown RDNS: 5.255.61.163
May 6 06:19:56 mon1 sqlgrey[9157]: perf: spent 0s cleaning: from_awl (0) domain_awl (0) connect (0)
May 6 06:19:56 mon1 sqlgrey[9157]: grey: new: 5.255.61.163(5.255.61.163), mario_wilkinson@lerinrock.com -> fkrogh@mathalacarte.com
Might that warning about /etc/postfix/virtual.db indicate some sort of problem. I just put back in "permit mynetworrks", as I think there is a chance it bounced a mail that it should not have. What to do??
Back to top
View user's profile Send private message
freke
Guru
Guru


Joined: 23 Jan 2003
Posts: 493
Location: Somewhere in Denmark

PostPosted: Fri May 06, 2016 3:06 pm    Post subject: Reply with quote

Fred Krogh wrote:
Just tried something that seems work, but I think it means another user on my network will not be able to send mail. That is I commented out "permit mynetworks" just below "smtpd_recipient_restrictions =" in main.cf. I then saw this in my the logs:
Quote:
May 6 06:19:56 mon1 postfix/smtpd[9170]: warning: database /etc/postfix/virtual.db is older than source file /etc/postfix/virtual
May 6 06:19:56 mon1 postfix/smtpd[9170]: warning: valid_hostname: misplaced delimiter: .
May 6 06:19:56 mon1 postfix/smtpd[9170]: connect from unknown[5.255.61.163]
May 6 06:19:56 mon1 sqlgrey[9157]: 2016/05/06-06:19:56 CONNECT TCP Peer: "[127.0.0.1]:59366" Local: "[127.0.0.1]:2501"
May 6 06:19:56 mon1 sqlgrey[9157]: optin: greylisting active for fkrogh@mathalacarte.com
May 6 06:19:56 mon1 sqlgrey[9157]: grey: unknown RDNS: 5.255.61.163
May 6 06:19:56 mon1 sqlgrey[9157]: perf: spent 0s cleaning: from_awl (0) domain_awl (0) connect (0)
May 6 06:19:56 mon1 sqlgrey[9157]: grey: new: 5.255.61.163(5.255.61.163), mario_wilkinson@lerinrock.com -> fkrogh@mathalacarte.com
Might that warning about /etc/postfix/virtual.db indicate some sort of problem. I just put back in "permit mynetworrks", as I think there is a chance it bounced a mail that it should not have. What to do??


I think the virtual.db-warning can be fixed with
Code:
postmap virtual
Basically it say that /etc/postfix/virtual has been edited but /etc/postfix/virtual.db hasn't been updated.

What the output of postconf -n?

Code:
May 06 16:58:19 [postfix/smtpd] connect from smtp1.az1.qemailserver.com[162.247.218.51]
May 06 16:58:20 [postfix/smtpd] Anonymous TLS connection established from smtp1.az1.qemailserver.com[162.247.218.51]: TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)
May 06 16:58:31 [sqlgrey] perf: spent 0s cleaning: from_awl (0) domain_awl (0) connect (0)_
May 06 16:58:31 [sqlgrey] grey: new: 162.247.218(162.247.218.51), survey@code42.com -> admin@vlh.dk_
May 06 16:58:32 [postfix/smtpd] NOQUEUE: reject: RCPT from smtp1.az1.qemailserver.com[162.247.218.51]: 450 4.7.1 <admin@vlh.dk>: Recipient address rejected: Greylisted for 5 minutes; from=<survey@code42.com> to=<admin@vlh.dk> proto=ESMTP helo=<smtp1.az1.qemailserver.com>
May 06 16:58:33 [postfix/smtpd] disconnect from smtp1.az1.qemailserver.com[162.247.218.51] ehlo=2 starttls=1 mail=1 rcpt=0/1 data=0/1 rset=1 quit=1 commands=6/8


This is a snippet of an incoming connection on my mailserver;
1st the connection is established
2nd sqlgrey is registering a new sender / not in it's db <- Here the MySQL-db is updated ie. 162.247.218.51 is added to the connect-table
3rd Postfix rejects the client for a minimum of 5 minutes before it's allowed to try again
Back to top
View user's profile Send private message
Fred Krogh
Veteran
Veteran


Joined: 07 Feb 2005
Posts: 1027
Location: Tujunga, CA

PostPosted: Fri May 06, 2016 5:28 pm    Post subject: Reply with quote

I think things are working now. I've had two spam come through with nothing showing up in the sqlgrey data base, but many many more are blocked. My error (stupid as usual) was
permit mynetworks
instead of
permit_mynetworks.
Thanks for the help.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic    Gentoo Forums Forum Index Networking & Security All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum