the newline actually did the trick !
WHITELISTED_HOSTS = <\n pgsql;SELECT from_address FROM lists WHERE to_address='any' AND list_type=1 AND from_address='${quote_pgsql:$sender_host_address}';
This works correctly for both IPv4 and IPv6 addresses.
<; did not work but <\n does work so this is solved.
Thanks very much, it seems the exim user list has some pretty responsive and helpfull people around !
________________________________
Van: Jacco van Gent
Verzonden: vrijdag 28 februari 2014 17:38
Aan: exim-users@???
Onderwerp: Re: [exim] PGSQL problem
Thanks again for responding, much appreciated.
I tried your suggestions but to no avail.
Conceptionally I might be on the wrong track, but bear with me on this one:
Connections comes in, and gets to the point of the connect acl where the config states
accept message = Authorized sender: $sender_host_address
hosts = +whitelisted_hosts
this list is defined here:
hostlist whitelisted_hosts = WHITELISTED_HOSTS
and the actual sql statement 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}';
So this list can only be constructed the minute a connection arrives, as that is the moment we know the value of $sender_host_adddress.
Now the database couldn't possibly return any values other than blank at this moment as there are no records in the database full stop. But if there were, it would only return something if the $sender_host_address is actually in the database with list type=1 and to address any.
The error message in the list specifically states PGSQL: query failed: ERROR: unterminated quoted string at or near "'2001", this cannot possibly be something PGSQL returned, as there is nothing in the database.
It would have made sense if this list was populated at run time by exim and stored into the list, but it seems it is done at connect time, as I can add a host to the db, and right after connect with it and be told I'm banned. Then go back in, delete the address from the database and right after connect and be accepted again. (there is a similar blacklist host list, with the exact same problem.
In any case there is no record of adress 2001:x in the Pgsql database, so I am guessing either PGSQL or Exim have trouble parsing 2001: or any other IPv6 address. And I am looking for a way to parse this in another way.
I actually succeeded by changing $sender_host_address into $(mask:$sender_host_address/128} which indeed let the query succeed, but this would mean I have to store the full IPV6 adress including mask into the database, and this has other implications.
I hope I am on the right path here, but are not sure. Thanks for your insights.
Author: Jeremy Harris
Date: 2014-02-28 15:37 +100
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
