Re: [exim] PGSQL problem

Top Page
Delete this message
Reply to this message
Author: Jeremy Harris
Date:  
To: exim-users
Subject: Re: [exim] PGSQL problem
On 28/02/14 09:04, Jacco van Gent wrote:
> 02-25 16:07, Jeremy Harris wrote:
>> On 24/02/14 20:56, Jacco van Gent wrote:
>>> I hope someone can help me. I tried searching for a solution but so far haven't found anything that would fix it.
>>>
>>> WHITELISTED_HOSTS = pgsql;SELECT from_address FROM lists WHERE to_address='any' AND list_type=1 AND from_address='${quote_pgsql:$sender_host_address}';
>>>
>>> This is a line in macros.conf, and this query does run successfully if the host is IPV4, if the host is IPV6 I get the following error message:
>>>
>>> temporarily rejected connection in "connect" ACL: PGSQL: query failed: ERROR: unterminated quoted string at or near "'2001"
>>>
>>> So the million dollar question here would be how to make sure the ipv6 address is actually terminated.
>>
>> Are you using the macro in a context that takes a list? These are
>> colon-separated by default, so an ipv6 address terminates a list
>> element. The list-separator can be changed; see
>>
>> http://www.exim.org/exim-html-current/doc/html/spec_html/ch-the_exim_run_time_configuration_file.html
>
> Hi thanks for the reaction,
>
> This is the full usage of this select:
>
> In exim4.conf:
>
> hostlist whitelisted_hosts = WHITELISTED_HOSTS
>
> and then in the connect acl:
>
> accept  message       = Authorized sender: $sender_host_address
>          #  condition     = ${if isip4{$sender_host_address}{yes}}
>            hosts         = +whitelisted_hosts

>
> in macros.conf
>
> WHITELISTED_HOSTS = pgsql;SELECT from_address FROM lists WHERE to_address='any' AND list_type=1 AND from_address='${quote_pgsql:$sender_host_address}';
>
> It is using $sender_host_address to search for any IP address in the database that has any in the to address filed and 1 in the list type. Now obviously this works for IPv4 addresses, but not for IPv6 addresses, and most probably because as you said PGSQL is treating the : as a list separator.


Exim, not pgsql, treating the : as a list separator.

>
> If I put in a condition such as the commented one above, this features works for IPv4 addresses without disconnecting IPv6 addresses. So it is partially fixed, the ideal fix would be to make it work for IPv6 addresses as well. I am not well enough into exim to make it work for both scenarios. I tried defining a hostlist :
>
> hostlist ipaddr = <; $sender_host_address
> and use this hostlist in the query, but that doesn't work :)


You've put the <; in the wrong place.

The problem is the return string from the pgsql lookup, being
interpreted by exim in a list context. First you need to
stop this list expansion using : as a separator, so prefix
the return with your <; - probably putting it immediately before
the word "pgsql" in the WHITELISTED_HOSTS definition is a good
place.

Second, you need to worry about how the pgsql lookup will return
multiple rows, as you want this to be converted to an exim list.

http://exim.org/exim-html-current/doc/html/spec_html/ch-file_and_database_lookups.html
section 20 says:

"If the result of the query yields more than one row, it is all
concatenated, with a newline between the data for each row."

- so actually the simplest list-sep to use in exim terms is a
newline not : or ;     Can we do this?  Over to...


http://exim.org/exim-html-current/doc/html/spec_html/ch-the_exim_run_time_configuration_file.html#SECTlistconstruct

and, bingo, there's an example doing exactly that.

-- 
Cheers,
    Jeremy