From: Andrew Koros (akoroske.uu.net)
Date: Fri Dec 07 2007 - 05:25:49 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

> Andrew Koros wrote:
>> Hello,
>>
>> I have postfix 2.4.6 on Fedora built with postgresql support.
>>
>> Being a service provider I want to be able to lookup IPs of hosts I
>> relay for from a postgresql database so that we can manage the IP's
>> easily without modifying the postfix configurations.
>>
>> The following is an excerpt from my main.cf
>>
>> mynetworks = 127.0.0.0/8,
>> hash:/etc/postfix/relay_ips,
>> proxy:pgsql:/etc/postfix/pgsql-relay_ips.cf
>>
>> The pgsql:/etc/postfix/pgsql-relay_ips.cf is a SQL query that calls a
>> postgresql function to check for a inet IP or subnet in a table and
>> gives a NULL result if the input is a hostname or if the requested IP
>> does not exist or is not a part of a listed subnet.
>>
>> e.g.
>>
>> postmap -q 192.168.0.12 pgsql:/etc/postfix/pgsql-relay_ips.cf
>>
>> gives a result like:
>> 192.168.0.0/28
>>
>> While
>> postmap -q host.example.com pgsql:/etc/postfix/pgsql-relay_ips.cf
>>
>> give a result like:
>> postmap: warning: table "pgsql:/etc/postfix/pgsql-relay_ips.cf":
>> empty lookup result for: "host.example.com" -- ignored
>>
>
> fix your query. why does "host.example.com" match anything in the
> queried table? if the table is used for something else, add an "AND"
> statement to ignore empty results.
>
No, "host.example.com" does not match anything in the queried table. It returns
a NULL or empty result.
In other words the SQL query only returns a value if the input was an IP found
in the database otherwise it returns a empty result. for example:

mydatabase=# SELECT get_relay_ips('192.168.0.12') AS ip;
        ip
-------------------
 192.168.0.0/28
(1 row)

But

mydatabase=# SELECT get_relay_ips('192.168.0.20') AS ip;
        ip
-------------------

(1 row)

becuase 192.168.0.20 is not part of the 192.168.0.0/28

And quering for the hostnames will not succeed as expected:

mydatabase=# SELECT get_relay_ips('host.example.com') AS ip;
 ip
----

(1 row)

Like I said it works OK and postfix is able to relay for IPs in the database and
deny relaying for anything else. The only problem is the extra warning messages
"....empty lookup result for ..." e.t.c. which I'd like to get rid of.

>> It works fine but I keep getting warning messages like the ones below:
>> Is there a way to get postfix to only query for the IP and not the
>> hostname?
>
> you can add a condition to your sql statement to only lookup IPs.
>
>> Secondly is there a way to get it to stop these warning
>> messages in the logs as it can be quit a lot when the server is busy.
>>
>> Dec 7 08:52:45 smtp-server postfix/proxymap[18778]: warning: table
>> "pgsql:/etc/postfix/pgsql-relay_ips.cf": empty lookup result for:
>> "host.example.com" -- ignored
>> Dec 7 08:52:45 smtp-server postfix/proxymap[18778]: warning: table
>> "pgsql:/etc/postfix/pgsql-relay_ips.cf": empty lookup result for:
>> ".example.com" -- ignored
>> Dec 7 08:52:45 smtp-server postfix/proxymap[18778]: warning: table
>> "pgsql:/etc/postfix/pgsql-relay_ips.cf": empty lookup result for: ".com"
>> -- ignored
>> Dec 7 08:52:45 smtp postfix/proxymap[18778]: warning: table
>> "pgsql:/etc/postfix/pgsql-relay_ips.cf": empty lookup result for:
>> "192.168.0.150" -- ignored
>>
>

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]