# sqlgrey questions -- RESOLVED

## Fred Krogh

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           |
> 
> +-------------+---------------------+---------------+---------------------+---------------------+
> ...

 

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,

   FredLast edited by Fred Krogh on Fri May 06, 2016 5:29 pm; edited 1 time in total

----------

## Fred Krogh

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,
> ...

 

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.

----------

## freke

 *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,
> ...

 

```
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.

----------

## Fred Krogh

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
> ...

 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
> ...

 

Following freke, I have this in main.cf: *Quote:*   

> smtpd_recipient_restrictions =
> 
>    permit mynetworks
> 
>    permit_sasl_authenticated
> ...

 

Also I have created a table,  *Quote:*   

> MariaDB [sqlgrey]> describe whitelist;
> 
> +----------------+--------------+------+-----+---------------------+-------+
> 
> | Field          | Type         | Null | Key | Default             | Extra |
> ...

 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

----------

## freke

 *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 =
> ...

 

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).

----------

## Fred Krogh

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.

----------

## Fred Krogh

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]
> ...

 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??

----------

## freke

 *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]
> ...

 

I think the virtual.db-warning can be fixed with

```
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?

```
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

----------

## Fred Krogh

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.

----------

