Mailing List Archive: Replacing wildlsearch with query-style lookups?

Replacing wildlsearch with query-style lookups?

Jan 24, 2024, 11:36 PM

Post #1 of 5 (104 views)

Hi,

I want to move my file lookups to database lookups (mariadb). While it is no problem with some of those file lookups because they become simple SELECTs. But I have a problem with (n)wildlsearch. Because I just don't know how I could replace them.
For example a sender-lookup for:

^.*@(.*\.)?domain\.tld$

How would I do look something like this up? I know that it would be easy to just look up a domain or a complete sender, but in this case I want for example block a domain and all possible subdomains from that domain.

Or I redirect some users that send out newsletters to special servers. I do that by doing a wildlsearch and have there an entry like:

^bounce.*@newsletter.domain.tld

Since there is always a random string coming after the bounce.
Is there a way to realize that with a database lookup? I know that there is apparently a regexp-lookup for mariadb (https://mariadb.com/kb/en/regexp/) but I think I cannot use that. Because I actually get the sender-address and would need to put the regexp in the database.

The only solution so far I come up with that I can put singular senders and domains in tables and have multiple ACLs and routers.
And then have an extra file for those cases where I need a regexp but then someone who needs to debug things, needs to look into several places (even though I could for example add some info into the log-messages, so that the debugging person has a better clue where to look).

How are others on the list solving this problem?

Best,

Niels

--
## subscription configuration (requires account):
## https://lists.exim.org/mailman3/postorius/lists/exim-users.lists.exim.org/
## unsubscribe (doesn't require an account):
## exim-users-unsubscribe@lists.exim.org
## Exim details at http://www.exim.org/
## Please use the Wiki with this list - http://wiki.exim.org/

Re: Replacing wildlsearch with query-style lookups? [ In reply to ]

exim-users at lists

Jan 25, 2024, 12:09 AM

Post #2 of 5 (104 views)

Permalink

On Thu, Jan 25, 2024 at 08:36:04AM +0100, Niels Kobsch?tzki via Exim-users wrote:
> The only solution so far I come up with that I can put singular
> senders and domains in tables and have multiple ACLs and routers.
> And then have an extra file for those cases where I need a regexp
> but then someone who needs to debug things, needs to look into
> several places (even though I could for example add some info into
> the log-messages, so that the debugging person has a better clue
> where to look).

That's right approach, in common.

The first question should be raised is "Why you want to use SQL database?"
May be to speed up lookups. It implies some technology, namely indexing.
But indexing has its own limitations, and is almost incompatible with
regex-like lookups. So you have to look for some algorithmic combinations
of different lookup types, to achieve speedup and limit complexity of code.
--
Eugene Berdnikov

--
## subscription configuration (requires account):
## https://lists.exim.org/mailman3/postorius/lists/exim-users.lists.exim.org/
## unsubscribe (doesn't require an account):
## exim-users-unsubscribe@lists.exim.org
## Exim details at http://www.exim.org/
## Please use the Wiki with this list - http://wiki.exim.org/

Re: Replacing wildlsearch with query-style lookups? [ In reply to ]

exim-users at lists

Jan 25, 2024, 12:45 AM

Post #3 of 5 (104 views)

Permalink

> Evgeniy Berdnikov via Exim-users <exim-users@lists.exim.org> hat am 25.01.2024 09:09 CET geschrieben:
>
>
> On Thu, Jan 25, 2024 at 08:36:04AM +0100, Niels Kobschätzki via Exim-users wrote:
> > The only solution so far I come up with that I can put singular
> > senders and domains in tables and have multiple ACLs and routers.
> > And then have an extra file for those cases where I need a regexp
> > but then someone who needs to debug things, needs to look into
> > several places (even though I could for example add some info into
> > the log-messages, so that the debugging person has a better clue
> > where to look).
>
> That's right approach, in common.
>
> The first question should be raised is "Why you want to use SQL database?"
> May be to speed up lookups. It implies some technology, namely indexing.
> But indexing has its own limitations, and is almost incompatible with
> regex-like lookups. So you have to look for some algorithmic combinations
> of different lookup types, to achieve speedup and limit complexity of code.

There are several reasons:
1) multiple servers and I have to keep the files in sync (yes, shared storage but I want to omit this)
2) There is another backend-application which can easily access the database but not the files and there are informations in that database that is useful for other users.

Niels

--
## subscription configuration (requires account):
## https://lists.exim.org/mailman3/postorius/lists/exim-users.lists.exim.org/
## unsubscribe (doesn't require an account):
## exim-users-unsubscribe@lists.exim.org
## Exim details at http://www.exim.org/
## Please use the Wiki with this list - http://wiki.exim.org/

Re: Replacing wildlsearch with query-style lookups? [ In reply to ]

exim-users at lists

Jan 25, 2024, 1:19 AM

Post #4 of 5 (104 views)

Permalink

On Thu, Jan 25, 2024 at 09:45:38AM +0100, Niels Kobsch?tzki via Exim-users wrote:
> > Evgeniy Berdnikov via Exim-users <exim-users@lists.exim.org> hat am 25.01.2024 09:09 CET geschrieben:
> >
> > The first question should be raised is "Why you want to use SQL database?"
> > May be to speed up lookups. It implies some technology, namely indexing.
> > But indexing has its own limitations, and is almost incompatible with
> > regex-like lookups. So you have to look for some algorithmic combinations
> > of different lookup types, to achieve speedup and limit complexity of code.
>
> There are several reasons:
> 1) multiple servers and I have to keep the files in sync (yes, shared storage but I want to omit this)

You may use rsync (with local file storage on each MTA host)
if you don't want to run shared storages.

> 2) There is another backend-application which can easily access the database but not the files and there are informations in that database that is useful for other users.

Does this application work with e-mail addresses? If yes, does it
run the same regex-like lookups as MTA does?
--
Eugene Berdnikov

--
## subscription configuration (requires account):
## https://lists.exim.org/mailman3/postorius/lists/exim-users.lists.exim.org/
## unsubscribe (doesn't require an account):
## exim-users-unsubscribe@lists.exim.org
## Exim details at http://www.exim.org/
## Please use the Wiki with this list - http://wiki.exim.org/

Re: Replacing wildlsearch with query-style lookups? [ In reply to ]

exim-users at lists

Jan 25, 2024, 3:02 AM

Post #5 of 5 (104 views)

Permalink

On 1/25/24 07:36, Niels Kobschätzki via Exim-users wrote:
> I want to move my file lookups to database lookups (mariadb).
> [...] I have a problem with (n)wildlsearch. Because I just don't
> know how I could replace them.
> For example a sender-lookup for:
>
> ^.*@(.*\.)?domain\.tld$

In postgres one wa way would be something like

sender_domains = pgsql;select substring(basedom from \
".*${quote_pgsq:$sender_address_domain}")

(using a column called "basedom" with entries like
"domain.tld").

Surely MariaDB can do similar?
--
Cheers,
Jeremy

--
## subscription configuration (requires account):
## https://lists.exim.org/mailman3/postorius/lists/exim-users.lists.exim.org/
## unsubscribe (doesn't require an account):
## exim-users-unsubscribe@lists.exim.org
## Exim details at http://www.exim.org/
## Please use the Wiki with this list - http://wiki.exim.org/