sqlgrey-users — Users of SQLgrey

Re: [Sqlgrey-users] bluehost.com fails with sqlgrey

[Lionel B. <lio...@bo...>]

Hi,

Le 05/02/2019 à 16:05, Karl O. Pinc a écrit :
> FYI.
>
> *.bluehost.com should probably be added to the clients_fqdn_whitelist.
> It seems to retry from a different class C every time.

Just found your message while reviewing my unread mails...

Done and thanks for the report.

Regards,

Lionel

[Sqlgrey-users] bluehost.com fails with sqlgrey

[Karl O. P. <ko...@me...>]

FYI.

*.bluehost.com should probably be added to the clients_fqdn_whitelist.
It seems to retry from a different class C every time.

Regards,

Karl <ko...@me...>
Free Software:  "You don't pay back, you pay forward."
                 -- Robert A. Heinlein

Re: [Sqlgrey-users] IPv6 issue

[Karl O. P. <ko...@me...>]

On 03/17/2018 09:42:41 PM, Will Lowe wrote:

> I recently upgraded my Fedora distro server to 27 and rebuilt my mail 
> server. But when I try to start sqlgrey I get the following:
> 
> 
> Mar 17 17:05:29 www.xxxxxxxxx.xxx systemd[1]: Started SQLgrey Postfix 
> Grey-listing Policy service.
> Mar 17 17:05:29 www.xxxxxxxxx.xxx sqlgrey[30294]: Resolved 
> [localhost]:2501 to [127.0.0.1]:2501, IPv4
> Mar 17 17:05:29 www.xxxxxxxxx.xxx sqlgrey[30294]: Resolved 
> [localhost]:2501 to [::1]:2501, IPv6
> Mar 17 17:05:29 www.xxxxxxxxx.xxx sqlgrey[30294]: Binding to TCP port 
> 2501 on host 127.0.0.1 with IPv4
> Mar 17 17:05:29 www.xxxxxxxxx.xxx sqlgrey[30294]: Binding to TCP port 
> 2501 on host ::1 with IPv6
> Mar 17 17:05:29 www.xxxxxxxxx.xxx sqlgrey[30294]: 2018/03/17-17:05:29 
> Can't connect to TCP port 2501 on ::1 [Address family not supported 
> by
> 
> protocol]
>                                                           at line 64
> in 
> file /usr/local/share/perl5/Net/Server/Proto/TCP.pm
> Mar 17 17:05:29 www.xxxxxxxxx.xxx sqlgrey[30294]: 2018/03/17-17:05:29 
> Server closing!
> Mar 17 17:05:29 www.xxxxxxxxx.xxx systemd[1]: sqlgrey.service: Main 
> process exited, code=exited, status=1/FAILURE
> Mar 17 17:05:29 www.xxxxxxxxx.xxx systemd[1]: sqlgrey.service: Unit 
> entered failed state.
> Mar 17 17:05:29 www.xxxxxxxxx.xxx systemd[1]: sqlgrey.service: Failed 
> with result 'exit-code'.
> 
> I have IPv6 disabled on this server. Is there an option I can use to 
> cause sqlgrey to not bind to an IPv6 address?

Use the "inet" config option and write 127.0.0.1 instead of
localhost.  Or use a  Unix socket which is slightly more secure and 
efficient.

Karl <ko...@me...>
Free Software:  "You don't pay back, you pay forward."
                 -- Robert A. Heinlein

[Sqlgrey-users] IPv6 issue

[Will L. <wl...@su...>]

Can anyone help me with this issue?

I recently upgraded my Fedora distro server to 27 and rebuilt my mail 
server. But when I try to start sqlgrey I get the following:


Mar 17 17:05:29 www.xxxxxxxxx.xxx systemd[1]: Started SQLgrey Postfix 
Grey-listing Policy service.
Mar 17 17:05:29 www.xxxxxxxxx.xxx sqlgrey[30294]: Resolved 
[localhost]:2501 to [127.0.0.1]:2501, IPv4
Mar 17 17:05:29 www.xxxxxxxxx.xxx sqlgrey[30294]: Resolved 
[localhost]:2501 to [::1]:2501, IPv6
Mar 17 17:05:29 www.xxxxxxxxx.xxx sqlgrey[30294]: Binding to TCP port 
2501 on host 127.0.0.1 with IPv4
Mar 17 17:05:29 www.xxxxxxxxx.xxx sqlgrey[30294]: Binding to TCP port 
2501 on host ::1 with IPv6
Mar 17 17:05:29 www.xxxxxxxxx.xxx sqlgrey[30294]: 2018/03/17-17:05:29 
Can't connect to TCP port 2501 on ::1 [Address family not supported by 
protocol]
                                                          at line 64 in 
file /usr/local/share/perl5/Net/Server/Proto/TCP.pm
Mar 17 17:05:29 www.xxxxxxxxx.xxx sqlgrey[30294]: 2018/03/17-17:05:29 
Server closing!
Mar 17 17:05:29 www.xxxxxxxxx.xxx systemd[1]: sqlgrey.service: Main 
process exited, code=exited, status=1/FAILURE
Mar 17 17:05:29 www.xxxxxxxxx.xxx systemd[1]: sqlgrey.service: Unit 
entered failed state.
Mar 17 17:05:29 www.xxxxxxxxx.xxx systemd[1]: sqlgrey.service: Failed 
with result 'exit-code'.

I have IPv6 disabled on this server. Is there an option I can use to 
cause sqlgrey to not bind to an IPv6 address?


Will

[Sqlgrey-users] Database access via IPv6

[Andrea <ml...@vp...>]

Hi all.


I'm running sqlgrey instances on different servers using a central database.

Soon the db server will only be accessible on IPv6, how can I configure
sqlgrey to connect to it?

So far I've tried

> db_host = fdb6:ab38:6c93:ba14:1d30:62b8:22a:11bc
and
> db_host = [fdb6:ab38:6c93:ba14:1d30:62b8:22a:11bc]

but in both cases sqlgrey refuses to start:

> Jan  5 09:14:48 srv2 sqlgrey: dbaccess: can't connect to DB: Unknown
MySQL server host '[fdb6' (0)


Thanks

Re: [Sqlgrey-users] IPv6 in clients_ip_whitelist.local

[Lionel B. <lio...@bo...>]

Hi,

On 04/09/2016 22:58, Henrik Christian Grove wrote:
> Hi
>
>
> Looking at my (private - this mail is from my work address) mail log, I
> can see that sqlgrey causes mail from gmail to be rejected at that
> moment, my best bet is that it's because it's coming by IPv6 (my server
> has IPv6 and so does google). Specifically I see messages like (actual
> email addresses edited):
>
> Sep  4 21:29:45 teresa sqlgrey: grey: new:
> 2607:f8b0:4002:0c05:0000:0000:0000:0247(2607:f8b0:4002:c05::247),
> so...@gm... -> me...@30...
>
> Sep  4 21:29:45 teresa postfix/smtpd[20171]: NOQUEUE: reject: RCPT from
> mail-yw0-x247.google.com[2607:f8b0:4002:c05::247]: 450 4.7.1
> <me...@30...>: Recipient address rejected: Greylisted for 5 minutes;
> from=<so...@gm...> to=<me...@30...> proto=ESMTP
> helo=<mail-yw0-x247.google.com>
>
> I tried adding
>
> 2607:f8b0:40/36 # gmail IPv6

Network notation isn't supported, the string should be a prefix of the
addresses to whitelist.
2607:f8b0:40 should work and is equivalent to the /40 network.

Best regards,

Lionel

[Sqlgrey-users] IPv6 in clients_ip_whitelist.local

[Henrik C. G. <sq...@30...>]

Hi


Looking at my (private - this mail is from my work address) mail log, I
can see that sqlgrey causes mail from gmail to be rejected at that
moment, my best bet is that it's because it's coming by IPv6 (my server
has IPv6 and so does google). Specifically I see messages like (actual
email addresses edited):

Sep  4 21:29:45 teresa sqlgrey: grey: new:
2607:f8b0:4002:0c05:0000:0000:0000:0247(2607:f8b0:4002:c05::247),
so...@gm... -> me...@30...

Sep  4 21:29:45 teresa postfix/smtpd[20171]: NOQUEUE: reject: RCPT from
mail-yw0-x247.google.com[2607:f8b0:4002:c05::247]: 450 4.7.1
<me...@30...>: Recipient address rejected: Greylisted for 5 minutes;
from=<so...@gm...> to=<me...@30...> proto=ESMTP
helo=<mail-yw0-x247.google.com>

I tried adding

2607:f8b0:40/36 # gmail IPv6

(Yes, that's a non-standard way of writing that range, it should have
been /40, and I have now corrected it, but that shouldn't matter? - It
looks like google has 2607:f8b0/32, but all the attempts so far has been
from 2607:f8b0:400/44)

to clients_ip_whitelist.local, but it doesn't seem to help? The last
greylisting message is from after I edited the file and restarted sqlgrey.


.Henrik

Re: [Sqlgrey-users] couple new user questions

[Philippe C. <sql...@pa...>]

On 6/27/2016 11:27 AM, Karl O. Pinc wrote:
> Sorry Lionel, I'm confirming that these should
> be whitelisted.  But, while I recall the below as the
> correct domains, I've not examined my logs and checked.


     Starting with confirmations.

BlockChain.info:
======================================================================
Jun 27 11:37:01 ip-172-30-3-20 sqlgrey[4430]: whitelist: 
XXX...@ma..., 198.21.6.174(o1.mail.blockchain.info) -> 
XX...@pa...
======================================================================

An Outlook.com hosted Domain:
======================================================================
Jun 27 11:40:28 ip-172-30-3-20 sqlgrey[4430]: whitelist: 
XX...@XX..., 
65.55.169.120(mail-bl2on0120.outbound.protection.outlook.com) -> 
XX...@pa...
======================================================================


     And in the "repudiating my own advice" category:

StartSSL.com
======================================================================
Jun 27 11:32:41 ip-172-30-3-20 postfix/smtpd[12272]: NOQUEUE: reject: 
RCPT from mta2.startcomca.com[4.14.40.143]: 450 4.7.1 
<XX...@pa...>: Recipient address rejected: Greylisted for 5 
minutes; from=<no-...@st...> to=<XX...@pa...> 
proto=ESMTP helo=<mta2.startcomca.com>
======================================================================

     So they've apparently move to yet another domain "mta2.startcomca.com".


     I don't have a way to trigger an eNom forwarding e-mail 
(Mail2World.com) just now, but I might be able to get one from one of my 
users.  When I get one I'll pass it along.

                 -- Philippe Chaintreuil

Re: [Sqlgrey-users] couple new user questions

[Philippe C. <sql...@pa...>]

On 6/27/2016 10:49 AM, Lionel Bouton wrote:
>> # Small L.A. Law Firm
>> assantilaw.com
>> *.assantilaw.com
>
> For this and the following entries, is the domain name really the fqdn
> of one of the sources ?
> I ask because the less entries we add, the less CPU we use. So if none
> of their servers resolves to "assantilaw.com" this is superfluous.

	I don't remember, but their MX records are now googlemail.com, so I'm 
betting they've switched away from whatever homegrown, non-compliant 
setup they were using when this was an issues several years ago.  I'd 
now say, 100% ignore this one.

				-- Philippe Chaintreuil

Re: [Sqlgrey-users] couple new user questions

[Karl O. P. <ko...@me...>]

Sorry Lionel, I'm confirming that these should
be whitelisted.  But, while I recall the below as the
correct domains, I've not examined my logs and checked.


On 06/27/2016 10:07:20 AM, Lionel Bouton wrote:
> Hi,
> 
> Le 27/06/2016 16:55, Karl O. Pinc a écrit :
> > On 06/27/2016 09:29:47 AM, Philippe Chaintreuil wrote:
> >> On 6/27/2016 9:24 AM, Lionel Bouton wrote:
> >>> [...] You are then encouraged to report them here so that I can
> >>> keep track of domains which need whitelisting to perform well.
> >> # StartSSL hates greylisting -- when verifying domain ownership
> >> # via e-mail they expect you to receive an e-mailed code
> essentially
> >> # immediately and will not retry without you restarting the
> process.
> > I confirm this, as of last year.
> >
> >> startcom.org
> >> *.startcom.org
> >> startssl.com
> >> *.startssl.com
> >> # Outlook.com users, retries do not come from the same server.
> >> # [2016-03Mar-14]
> >> outbound.protection.outlook.com
> >> *.outbound.protection.outlook.com
> > I concur.  outlook.com is a pain.  They ignore RFC 
> > recommendations, spat out several retries immediately,
> > and then do not retry again for 30 minutes. (This
> > seems to be something that the outlook software does.)
> 
> Thanks :
> 
> *.startcom.org
> *.startssl.com
> *.outbound.protection.outlook.com
> 
> 
> Added to the public whitelists in the " Requested by MTA admins "
> section
> 
> If I have confirmation of the non-wildcard entries usefulness I'll 
> add
> them too.
> 
> All users running "update_sqlgrey_config" (either by crontab or
> manually
> will get these new entries).
> 
> Best regards,
> 
> Lionel
> 
> ------------------------------------------------------------------------------
> Attend Shape: An AT&T Tech Expo July 15-16. Meet us at AT&T Park in
> San
> Francisco, CA to explore cutting-edge tech and listen to tech
> luminaries
> present their vision of the future. This family event has something
> for
> everyone, including kids. Get more information and register today.
> http://sdm.link/attshape
> _______________________________________________
> Sqlgrey-users mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users
> 
> 




Karl <ko...@me...>
Free Software:  "You don't pay back, you pay forward."
                 -- Robert A. Heinlein

Re: [Sqlgrey-users] couple new user questions

[Lionel B. <lio...@bo...>]

Hi,

Le 27/06/2016 16:55, Karl O. Pinc a écrit :
> On 06/27/2016 09:29:47 AM, Philippe Chaintreuil wrote:
>> On 6/27/2016 9:24 AM, Lionel Bouton wrote:
>>> [...] You are then encouraged to report them here so that I can
>>> keep track of domains which need whitelisting to perform well.
>> # StartSSL hates greylisting -- when verifying domain ownership
>> # via e-mail they expect you to receive an e-mailed code essentially
>> # immediately and will not retry without you restarting the process.
> I confirm this, as of last year.
>
>> startcom.org
>> *.startcom.org
>> startssl.com
>> *.startssl.com
>> # Outlook.com users, retries do not come from the same server.
>> # [2016-03Mar-14]
>> outbound.protection.outlook.com
>> *.outbound.protection.outlook.com
> I concur.  outlook.com is a pain.  They ignore RFC 
> recommendations, spat out several retries immediately,
> and then do not retry again for 30 minutes. (This
> seems to be something that the outlook software does.)

Thanks :

*.startcom.org
*.startssl.com
*.outbound.protection.outlook.com


Added to the public whitelists in the " Requested by MTA admins " section

If I have confirmation of the non-wildcard entries usefulness I'll add
them too.

All users running "update_sqlgrey_config" (either by crontab or manually
will get these new entries).

Best regards,

Lionel

Re: [Sqlgrey-users] couple new user questions

[Karl O. P. <ko...@me...>]

On 06/27/2016 09:29:47 AM, Philippe Chaintreuil wrote:
> On 6/27/2016 9:24 AM, Lionel Bouton wrote:
> > [...] You are then encouraged to report them here so that I can
> > keep track of domains which need whitelisting to perform well.

> # StartSSL hates greylisting -- when verifying domain ownership
> # via e-mail they expect you to receive an e-mailed code essentially
> # immediately and will not retry without you restarting the process.

I confirm this, as of last year.

> startcom.org
> *.startcom.org
> startssl.com
> *.startssl.com

> # Outlook.com users, retries do not come from the same server.
> # [2016-03Mar-14]
> outbound.protection.outlook.com
> *.outbound.protection.outlook.com

I concur.  outlook.com is a pain.  They ignore RFC 
recommendations, spat out several retries immediately,
and then do not retry again for 30 minutes. (This
seems to be something that the outlook software does.)



Karl <ko...@me...>
Free Software:  "You don't pay back, you pay forward."
                 -- Robert A. Heinlein

Re: [Sqlgrey-users] couple new user questions

[Lionel B. <lio...@bo...>]

Hi again,

Le 27/06/2016 15:24, Lionel Bouton a écrit :
> [...]
> If there is a problem on some origin domains you can inspect the logs to
> find our what is going on with these domains and if they don't behave
> well enough for SQLgrey to auto-whitelist them add entries to
> /etc/sqlgrey/clients_fqdn_whitelist.local or
> /etc/sqlgrey/clients_ip_whitelist.local (see the original files for the
> format used). You are then encouraged to report them here so that I can
> keep track of domains which need whitelisting to perform well.

One thing I forgot because it seemed obvious to me: you want to
whitelist the fqdn/ip of the very first servers trying to send emails.
So for best results you want to follow your logs back to the first mail
transfer attempt to find out the fqdn or the ip range used then.

Best regards,

Lionel

Re: [Sqlgrey-users] couple new user questions

[Lionel B. <lio...@bo...>]

Hi,

Le 27/06/2016 16:29, Philippe Chaintreuil a écrit :
> On 6/27/2016 9:24 AM, Lionel Bouton wrote:
>> [...] You are then encouraged to report them here so that I can
>> keep track of domains which need whitelisting to perform well.
> 	Here is a small-time host my users have discovered somehow don't retry 
> and thus their e-mails don't come through, they may have fixed their 
> issues since I added them in the early 2010's:
>
> # Small L.A. Law Firm
> assantilaw.com
> *.assantilaw.com

For this and the following entries, is the domain name really the fqdn
of one of the sources ?
I ask because the less entries we add, the less CPU we use. So if none
of their servers resolves to "assantilaw.com" this is superfluous.

If anyone could confirm these entries that would speed up their inclusion.

Best regards,

Lionel

Re: [Sqlgrey-users] couple new user questions

[Philippe C. <sql...@pa...>]

On 6/27/2016 9:24 AM, Lionel Bouton wrote:
> [...] You are then encouraged to report them here so that I can
> keep track of domains which need whitelisting to perform well.

	Here is a small-time host my users have discovered somehow don't retry 
and thus their e-mails don't come through, they may have fixed their 
issues since I added them in the early 2010's:

# Small L.A. Law Firm
assantilaw.com
*.assantilaw.com

	Here are larger senders that probably should be considered for 
inclusion into global lists, outlook.com being the big one:

# Mail2World is what eNom.com is using for main forwarding these days.
# Retries don't necessarily come from the same machine.
# [ 2014-03Mar-14 ]
mail2world.com
*.mail2world.com

# StartSSL hates greylisting -- when verifying domain ownership
# via e-mail they expect you to receive an e-mailed code essentially
# immediately and will not retry without you restarting the process.
startcom.org
*.startcom.org
startssl.com
*.startssl.com

# Blockchain's 2-factor auth codes expire quickly, and greylisting
# makes them a pain.
blockchain.info
*.blockchain.info

# Outlook.com users, retries do not come from the same server.
# [2016-03Mar-14]
outbound.protection.outlook.com
*.outbound.protection.outlook.com

	Sorry, the comments are largely in "note-to-self/reminders" format, let 
me know if they're not clear.  I *think* the above are in chronological 
order, granted not many are explicitly dated.  The *. versions probably 
make the others redundant, but I'm paranoid.

				-- Philippe Chaintreuil

Re: [Sqlgrey-users] couple new user questions

[Lionel B. <lio...@bo...>]

Hi,

Le 27/06/2016 02:07, Jonathan Nichols a écrit :
> Just a couple of questions that I didn’t see covered in the archives…
>
> I see that there’s a list of pre-whitelisted servers. is this ever updated by the maintainers at any time?

Rarely. These pre-configured whitelists are under my direct control (the
script updating them fetch files on a web server that I maintain) and my
filter to allow new entries in is :
- they must be cases that auto-whitelisting doesn't handle efficiently,
- they must affect several users.

If this doesn't pass the first test this defeats the greylisting process
(greylisting is not whitelisting...).
If this doesn't pass the second test this might be a fluke or a
temporary situation.

>  Is this something that we can should just do manually? 

You can maintain whitelists yourself by creating files with ".local"
appended to the original name. These files are under your direct control
and won't ever be overwritten by SQLgrey.

>
> if t’s recommended to just deal with it manually, what web interface is recommended these days? 
>
> my setup is pretty straight forward, but there are a couple of different domains. manually adjusting the sql tables would be kind of a pain.

You should not have to adjust anything unless your users report delayed
emails from specific domains for an extended period (several days).

If there is a problem on some origin domains you can inspect the logs to
find our what is going on with these domains and if they don't behave
well enough for SQLgrey to auto-whitelist them add entries to
/etc/sqlgrey/clients_fqdn_whitelist.local or
/etc/sqlgrey/clients_ip_whitelist.local (see the original files for the
format used). You are then encouraged to report them here so that I can
keep track of domains which need whitelisting to perform well.

Best regards,

Lionel

Re: [Sqlgrey-users] couple new user questions

[Karl O. P. <ko...@me...>]

On Sun, 26 Jun 2016 19:07:54 -0500
Jonathan Nichols <jni...@pb...> wrote:

> Just a couple of questions that I didn’t see covered in the archives…
> 
> I see that there’s a list of pre-whitelisted servers. 
> Is this something that we can
> should just do manually? 

I never do.  The whole point is that it adjusts itself
once it receives incoming email.


Karl <ko...@me...>
Free Software:  "You don't pay back, you pay forward."
                 -- Robert A. Heinlein

[Sqlgrey-users] couple new user questions

[Jonathan N. <jni...@pb...>]

Just a couple of questions that I didn’t see covered in the archives…

I see that there’s a list of pre-whitelisted servers. is this ever updated by the maintainers at any time? Is this something that we can should just do manually? 

if t’s recommended to just deal with it manually, what web interface is recommended these days? 

my setup is pretty straight forward, but there are a couple of different domains. manually adjusting the sql tables would be kind of a pain.

thanks!
—
jonathan

Re: [Sqlgrey-users] duplicate entry

[Alex <mys...@gm...>]

Hi,

>>                Last_SQL_Error: Error 'Duplicate entry
>> '69.25.195-mk1.netatlantic.com-bounce-#' for key 'PRIMARY'' on query.
>> Default database: 'sqlgrey'. Query: 'INSERT INTO from_awl
>> (sender_name, sender_domain, src, first_seen, last_seen)
>> VALUES('bounce-#','mk1.netatlantic.com','69.25.195','2016-03-07
>> 11:51:51',NOW())'
>>   Replicate_Ignore_Server_Ids:
>
> It might happen in rare cases where the same source sends an email
> through two independent mail servers at the very same time that confirm
> an earlier attempt. They will both try to insert the same auto whitelist
> entry.
>
> Another possibility is that the dbcluster support uses slaves for all
> its reads and they are not synchronized with the master properly, which
> given the following seems the most likely explanation.

Yes, I'm pretty sure that's it as well.

> Note that if you don't need slaves to support your current load and
> greylisting is not critical you are better off without them. SQLgrey can
> withstand a database failure : it will attempt to reconnect but in the
> meantime it will stop filtering incoming mail to avoid any service
> disruption.

I've recreated the slaves making them read-only, and I believe that's
fixed the problem. Perhaps I should have done that at first.

Thanks for all your help.
Alex

Re: [Sqlgrey-users] duplicate entry

[Lionel B. <lio...@bo...>]

Le 07/03/2016 21:06, Alex a écrit :
> Hi,
>
> I've been using sqlgrey for a while with mariadb and noticed there is
> what appears to be a duplicate entry:
>
>                Last_SQL_Error: Error 'Duplicate entry
> '69.25.195-mk1.netatlantic.com-bounce-#' for key 'PRIMARY'' on query.
> Default database: 'sqlgrey'. Query: 'INSERT INTO from_awl
> (sender_name, sender_domain, src, first_seen, last_seen)
> VALUES('bounce-#','mk1.netatlantic.com','69.25.195','2016-03-07
> 11:51:51',NOW())'
>   Replicate_Ignore_Server_Ids:

It might happen in rare cases where the same source sends an email
through two independent mail servers at the very same time that confirm
an earlier attempt. They will both try to insert the same auto whitelist
entry.

Another possibility is that the dbcluster support uses slaves for all
its reads and they are not synchronized with the master properly, which
given the following seems the most likely explanation.

>
> I have the system set up with a master on one system and replicated to
> three others. This occurred on one of the slaves.
>
> This isn't the first time it's happened. Is this to be expected?

Not if the slaves forbid any modifications not going through the
replication process (I think that's not always true with MySQL/MariaDB)
and are kept in sync.

>
> Is it possible it's a problem with my setup?
>
> If I run "select count(*) from from_awl;" from the master and each of
> the slaves, the count differs by a hundred on one system, and multiple
> hundreds on another.

Then you have a replication setup problem.

>
> If the Relay_Log_Pos on the slave matches that of the master, doesn't
> it mean they're in sync?

That's more a question for MariaDB.

Note that if you don't need slaves to support your current load and
greylisting is not critical you are better off without them. SQLgrey can
withstand a database failure : it will attempt to reconnect but in the
meantime it will stop filtering incoming mail to avoid any service
disruption.

Best regards,

Lionel

[Sqlgrey-users] duplicate entry

[Alex <mys...@gm...>]

Hi,

I've been using sqlgrey for a while with mariadb and noticed there is
what appears to be a duplicate entry:

               Last_SQL_Error: Error 'Duplicate entry
'69.25.195-mk1.netatlantic.com-bounce-#' for key 'PRIMARY'' on query.
Default database: 'sqlgrey'. Query: 'INSERT INTO from_awl
(sender_name, sender_domain, src, first_seen, last_seen)
VALUES('bounce-#','mk1.netatlantic.com','69.25.195','2016-03-07
11:51:51',NOW())'
  Replicate_Ignore_Server_Ids:

I have the system set up with a master on one system and replicated to
three others. This occurred on one of the slaves.

This isn't the first time it's happened. Is this to be expected?

Is it possible it's a problem with my setup?

If I run "select count(*) from from_awl;" from the master and each of
the slaves, the count differs by a hundred on one system, and multiple
hundreds on another.

If the Relay_Log_Pos on the slave matches that of the master, doesn't
it mean they're in sync?

I'd sure appreciate any ideas you may have.
Thanks,
Alex

Re: [Sqlgrey-users] Problems SQLgrey and gmail

[Christian K. <ml+...@va...>]

On 2016-02-11 23:48, Jean-Michel Pouré - GOOZE wrote:
> Le vendredi 11 décembre 2015 à 07:53 +0100, Christian Kivalo a écrit :
>> These was a script posted to the postfix mailinglist to generate a 
>> postscreen whitelist from spf records
>> 
>> https://github.com/stevejenkins/postwhite
> 
> 
> I am having exactly the same problem. 
> Google retries using a different email server ... 
> An email can take ages to arrive.
> 
> What is the effect of Postwhite exactly?
> How does it comply with sqlgrey?
You could generate the ip whitelist from googles spf records to use with 
sqlgrey.

I removed sqlgrey from my setup some time ago, it didn't provide much 
benefit but delayed mail for my users. I haven't noticed any increase in 
spam.

> Kind regards,
> Jean-Michel
> 
> ------------------------------------------------------------------------------
> Site24x7 APM Insight: Get Deep Visibility into Application Performance
> APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
> Monitor end-to-end web transactions and take corrective actions now
> Troubleshoot faster and improve end-user experience. Signup Now!
> http://pubads.g.doubleclick.net/gampad/clk?id=272487151&iu=/4140
> _______________________________________________
> Sqlgrey-users mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users

Re: [Sqlgrey-users] Problems SQLgrey and gmail

[Jean-Michel P. - G. <jm...@go...>]

Le vendredi 11 décembre 2015 à 07:53 +0100, Christian Kivalo a écrit :
> These was a script posted to the postfix mailinglist to generate a 
> postscreen whitelist from spf records
> 
> https://github.com/stevejenkins/postwhite


I am having exactly the same problem. 
Google retries using a different email server ... 
An email can take ages to arrive.

What is the effect of Postwhite exactly?
How does it comply with sqlgrey?

Kind regards,
Jean-Michel

Re: [Sqlgrey-users] Add primary keys to tables?

[incredibleh0lg <lis...@li...>]

Hi.

Thanks @Lionel for the long explanation. I really like to discuss this :)

So let’s go into it.
> On 9 Feb 2016, at 17:28, Lionel Bouton <lio...@bo...> wrote:
> 
> Hi,
> 
> Le 09/02/2016 14:38, incredibleh0lg a écrit :
>> [...]
>> Another point made in this discussion was to give a use case: Third party applications using the data in the database. In my case, I’d like to provide a similar functionality as SQLgrey Webinterface provides. So present the user with relevant entries - e.g. in the connect table - and let him decide what to do with it.
> 
> I don't think it's a good example because you definitely don't want to
> modify the connect table content in the back of SQLgrey : this is the
> table it uses to implement the basic greylisting algorithm. So if you
> want to modify the connect table you actually want to modify the
> greylisting algorithm. It seems to me that the two sane approaches if
> that's what you really want would be to :
> - implement your changes in SQLgrey itself, not in a separate application,
> - replace SQLgrey completely or fork it to implement your own algorithm
> if what you want to do is very different or solves a specific problem
> which isn't commonly encountered by SQLgrey uers.
> 
> The same limitations apply to the config/from_awl/domain_awl tables (awl
> means *automatic* white list), if you try to modify them behind
> SQLgrey's back you are actually designing a new greylisting program.
> 
> The only tables I created for external configuration are the
> opt[in|out]_[email|domain] tables.
Ok, my bad (or more: my English skills :)) I see and treat SQLgrey’s database 
as what it is - a database used by an external tool. The “relevant entries” is 
something I still try to get my head around, as I will have to implement some 
logic *outside* SQLgrey (+ it’s database) to filter entries. This is definitely not 
SQLgrey’s job. It is and will always be the greylisting solution of my choice.
My “problem”, I try to solve, is to provide some kind of integrated UI where
users can manage their own Greylisting settings (opting out, whitelisting one 
specific email etc.) SQLgrey should do what it is really good at: greylisting.

> 
>> In the current format this means, that the request has to carry the WHOLE entry as parameter to get it moved or deleted.
> 
> In fact the connect table is a special case and doesn't even guarantee
> that each entry is unique so you can't event select a single entry
> deterministically.
> The tables that are supposed to be managed externally only have a single
> column : so the "WHOLE" entry is actually a single value which is
> obviously known by any application which would be designed to insert or
> delete entries (update doesn't make sense for these tables)... See below
> if "obviously" is not a given for you.
Fair enough. I was just thinking about an option to get an optional surrogate 
key (to stick with your terminology - every day a new word / term :D) to make 
it easier to address an entry. As far as I understand the logic for whitelisting an 
email currently “sitting in” greylisting, we have to create a corresponding entry 
with the data from the ‘connect' table within the ‘from_awl’ table and delete the 
entry in ‘connect’. At the moment I would have to submit 4 different values. As 
the entries in ‘connect’ are not necessarily unique, a surrogate key would make 
this easier. But life isn’t always easy, is it :)

> 
>> This is pretty wasteful and it is the reason why I asked if somebody tested a slightly adjusted schema where generated primary keys are added by the database.In theory, this shouldn’t break the application.
> 
> It wouldn't break SQLgrey but it would make developing the application
> managing SQLgrey's configuration tables more complex and bug-prone (see
> a detailed explanation below).
> 
> I think you are probably approaching the SQLgrey database and an
> external application's database as a single entity (see why I think so
> below too), which I don't think is a good way to approach the external
> configuration of SQLgrey.
See above, I wasn’t really specific enough. So sorry again :)

> 
> In a single database for any moderately complex application surrogate
> keys are nearly always the best choice for various reasons. For a
> database dedicated to a trivial service like SQLgrey this is clearly not
> the case.
Agreed. Just the point I tried to make in my previous email.

> 
> Let's use a practical example to demonstrate.
> Take the optout_email table (the other configuration tables are similar:
> all have a single column) and create an application managing its content
> (creating entries when users want to optout and deleting them when they
> want to activate greylisting again).
> If you use surrogate keys in the optout_email table the first question
> you have to ask yourself is : does my application separate database's
> user objects store :
> - user emails,
> - references to the surrogate keys in optout_email,
> - both
> and what is used among them to reference the optout_email entries.
> 
> You made clear that don't want to use the user emails to reference
> optout_email entries so the only 2 choices for you remaining are the
> last ones.
> But actually if you store the references to surrogate keys you will most
> probably want to keep a copy of the email address too. Not only because
> it would be convenient in your application which would probably access
> the Postfix user database (or a database used to create the Postfix one
> which obviously must have the email addresses itself) but also because
> optout_email won't have the email if the user didn't choose to opt out.
> If you want to avoid storing the address at all costs you would make the
> application and Postfix depend on a SQLgrey change to store all your
> users' email addresses to solve this. This is not SQLgrey's role: it's a
> greylisting service, not an email user database. If you want to create
> your user database you obviously won't try to do so by installing a
> greylisting service.
Well, I wouldn’t mind to use the email address - although this can be tricky
in itself, depending on how the data gets transfered (http-encoded email 
addresses can be a pain, esp. with + addresses, which I personally use 
extensively).
I absolutely get your point and agree 100%. My application should not 
“hijack” SQLgrey’s schema (and was never planned to do it). Otherwise it 
would be worth implementing my own greylisting service. Which would be 
an interesting challenge in itself, but not very high on my long ToDo list…

> 
> In conclusion there's no real choice once you have chosen to use
> surrogate keys : you will have to store both the email and the reference
> to the surrogate key in the external database in one way or another.
> 
> So now if you really want to use the surrogate keys instead of the email
> itself you have to maintain consistency between your reference and the
> email addresses stored in two separate databases with 2 different
> codebases managed by 2 different sets of people. This is clearly (I hope
> so anyway) not something you want to do *at all*. By avoiding surrogate
> keys SQLgrey actually forces a potential separate database to be
> inherently consistent with its own database.
> 
> In case this isn't crystal clear, as an example of the consistency
> problems consider these common scenarios :
> - admins create entries manually until enough users ask for direct
> access and install the configuration application,
> - admins use batches to create initial entries or maintain a subset of
> the optout_email table in parallel to the configuration application,
> these batches probably aren't aware of the existence of the
> configuration application and don't know how to update its database,
> - after a crash, on restoring the 2 separate databases (SQLgrey and
> configuration application) you can get a more recent SQLgrey database
> with entries not (yet) referenced by the other restored database version,
> - ...
> All these scenario imply that there will be entries in optout_email not
> yet referenced by the configuration application.
> When creating new users with addresses already in optout_email, how will
> you create the references to these entries ? The only way is to check
> that for each user you create in your application there isn't already an
> entry in the optout_email table with the same email address. So for new
> users your reference to the optout_email table is the email address and
> for existing users the reference is the surrogate key : it's bad design
> and a loud and clear invitation to bugs.
Absolutely agreed. And on this special occasion, I think it is absolutely fine to
not normalise the data. It is simply not necessary or helpful. The complete
opposite is the case: this would definitely have an performance impact.

> 
> Now let's see how you would design the application without surrogate keys.
> The application has its user objects with their own email attribute.
> - when you want to activate optout, you use your application's email
> attribute to find or create an entry in the optout_email table if needed,
> - when you want to deactivate it, you find and delete the entry with the
> same email,
> - when you change the email address of a user, you remove the old entry
> (if it exists) and create a new one if applicable.
> This is *trivial* logic. An environment where this is difficult to do
> would have to prevent you to make the most basic SQL queries imaginable.
> 
> Why should we even consider a change only useful to people starting to
> develop external tools by shooting themselves in the foot when choosing
> their development environment ?
Couldn’t agree more. 

Well, I think it was worth asking anyway. Thanks again for the very 
detailed answer.

All the best,
Holger

Re: [Sqlgrey-users] Add primary keys to tables?

[Jean-Michel P. - G. <jm...@go...>]

Le mardi 09 février 2016 à 00:14 +0100, Lionel Bouton a écrit :
> Jean-Michel Pouré seemed to imply that it could have performance
> benefits bat I can't see how. There's not a single access to the
> database that could even use an OID, every object must be accessed
> based
> on conditions on its attributes (parts of email addresses, which are
> the
> only information relevant to SQLgrey's database a mail server can
> transmit to it).


No I don't meant that. As it is the database structure is good.