Multiple lookup entries in an SQL table

[Antonio Leding]

Hello Postfix Community,

I wanted to ask about the expected behavior if there are multiple entries in an SQL table for the same lookup (IP address, network, domain, etc.) which specify either the same or different actions (REJECT, OK, etc.).

---

example #1

1.2.3.4 OK
1.2.3.4 REJECT

example #2

example.com REJECT
example.com REJECT

example #3

5.6.7.8 OK
5.6.7.8 OK

---

I know that access(5) states the specific search order for the various allowable lookup items but I did not find any discussion as to what happens when there is more than one identical searchable item entry.

Thanks in advance…

[Viktor Dukhovni]

On Fri, Feb 19, 2021 at 11:13:57PM +0000, Antonio Leding wrote:

> I wanted to ask about the expected behavior if there are multiple
> entries in an SQL table for the same lookup (IP address, network,
> domain, etc.) which specify either the same or different actions
> (REJECT, OK, etc.).

As documented, the LDAP, Posgresql and MySQL table drivers combine
multiple answers by intercalating commas between the individual values.
This works sensibly for e.g. returning multiple email addresses, but not
in contexts where comma-separated values are not expected.

It is your responsibility to write queries that return an answer that
conforms to the expected value syntax.

--
    Viktor.

[Antonio Leding]

Ok…

So if I have the following:

example.com OK
example.com REJECT

Then the correct Postfix lookup behavior is to return OK,REJECT

Do I understand correctly?

Also, I do understand that this type of config would be a corner case and likely not really something to be used so this is really more negative testing…

---

On 19 Feb 2021, at 15:33, Viktor Dukhovni wrote:

On Fri, Feb 19, 2021 at 11:13:57PM +0000, Antonio Leding wrote:

I wanted to ask about the expected behavior if there are multiple
entries in an SQL table for the same lookup (IP address, network,
domain, etc.) which specify either the same or different actions
(REJECT, OK, etc.).

As documented, the LDAP, Posgresql and MySQL table drivers combine
multiple answers by intercalating commas between the individual values.
This works sensibly for e.g. returning multiple email addresses, but not
in contexts where comma-separated values are not expected.

It is your responsibility to write queries that return an answer that
conforms to the expected value syntax.

--
Viktor.

[Wietse Venema]

Antonio Leding:
> Ok?
>
> So if I have the following:
>
> example.com OK
> example.com REJECT
>
> Then the correct Postfix lookup behavior is to return OK,REJECT

That is what the database client does.

However, there is no Postfix code that wants "OK,REJECT" as
a lookup result.

        Wietse

[Dan Mahoney (Gushi)]

From a database point of view, unless you have an ORDER BY statement in your query, the order returned could be either (unless postfix’s code is sorting them).  

If postfix only wants a single result, then your query would need a LIMIT statement in it.

> On Feb 19, 2021, at 5:19 PM, Wietse Venema <[hidden email]> wrote:
>
> Antonio Leding:
>> Ok?
>>
>> So if I have the following:
>>
>> example.com OK
>> example.com REJECT
>>
>> Then the correct Postfix lookup behavior is to return OK,REJECT
>
> That is what the database client does.
>
> However, there is no Postfix code that wants "OK,REJECT" as
> a lookup result.
>
> Wietse

[Antonio Leding]

Thanks Dan & Weitse - very much appreciated…

---

On 19 Feb 2021, at 17:23, Dan Mahoney wrote:

From a database point of view, unless you have an ORDER BY statement in your query, the order returned could be either (unless postfix’s code is sorting them).

If postfix only wants a single result, then your query would need a LIMIT statement in it.

On Feb 19, 2021, at 5:19 PM, Wietse Venema <[hidden email]> wrote:

Antonio Leding:

Ok?

So if I have the following:

example.com OK
example.com REJECT

Then the correct Postfix lookup behavior is to return OK,REJECT

That is what the database client does.

However, there is no Postfix code that wants "OK,REJECT" as
a lookup result.

Wietse