mynetworks from database with pgsql - troubles with netmasks
Harakiri
there is a limitation within postfix and sql database
backends. Using the pgsql support - postfix only
queries for values and if something is returned than
its valid - if nothing its not - there is no netmask
check for example.
Postfix simply does a
"select * from mynetworks where ip='single_ip'"
Postfix does a simple lookup of the IP address,
instead of investigating if the IP address is included
in any netmasks in the table.
Lets say i send an email from 192.168.0.99 but have
configured my database for the net 192.168.0.1/24
i would have to enter every single IP address into
this database to match postfix query.
Anyone has a SQL procedure that will calculate if
192.168.0.99 is in the net 192.168.0.1/24 for example
?
I found a similar post but the stored procedure does
not really handle this
http://www.irbs.net/internet/postfix/0304/3074.html
I *have* to use a sql database backend, so no other
option is viable.
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam
protection around
http://mail.yahoo.com
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Wietse Venema
> Hi,
>
> there is a limitation within postfix and sql database
> backends. Using the pgsql support - postfix only
> queries for values and if something is returned than
> its valid - if nothing its not - there is no netmask
> check for example.
Are you suggesting that Postfix should make 32 database queries
per IPv4 address, one for each possible netmask value?
Are you suggesting that Postfix should make 128 database queries
per IPv6 address, one for each possible netmask value?
Would not that be incredibly expensive?
Wietse
Darren Pilgrim
> there is a limitation within postfix and sql database
> backends. Using the pgsql support - postfix only
> queries for values and if something is returned than
> its valid - if nothing its not - there is no netmask
> check for example.
>
> Postfix simply does a
>
> "select * from mynetworks where ip='single_ip'"
>
> Postfix does a simple lookup of the IP address,
> instead of investigating if the IP address is included
> in any netmasks in the table.
>
> Lets say i send an email from 192.168.0.99 but have
> configured my database for the net 192.168.0.1/24
>
> i would have to enter every single IP address into
> this database to match postfix query.
>
> Anyone has a SQL procedure that will calculate if
> 192.168.0.99 is in the net 192.168.0.1/24 for example
> ?
>
> I found a similar post but the stored procedure does
> not really handle this
>
> http://www.irbs.net/internet/postfix/0304/3074.html
>
> I *have* to use a sql database backend, so no other
> option is viable.
Fair enough, but why do you need to do a SQL lookup to resolve
$mynetworks? Normally, $mynetworks is static (hard-coded) and you use
SASL or a client access map to permit relay from addresses you can't
anticipate.
Harakiri
> Are you suggesting that Postfix should make 32
> database queries
> per IPv4 address, one for each possible netmask
> value?
The only thing im suggesting to you is, reading a
message fully before hitting reply.
All i asked for was a stored procedure which converts
the postfix queries and checks if the ip is within a
given net.
NO where in my post did i suggest postfix doing that
because i know that it would result in many queries.
Andrej Ricnik-Bay
> Lets say i send an email from 192.168.0.99 but have
> configured my database for the net 192.168.0.1/24
Not quite sure what problem you see there ... /24 defines
all addresses from 192.168.0.1 to 192.168.0.254, and
.99 is smack-bang in the middle of that?
Cheers,
Andrej
--
Please don't top post, and don't use HTML e-Mail :} Make your quotes concise.
Jim Rice
> On 5/11/06, Harakiri <harak...@yahoo.com> wrote:
>
> > configured my database for the net 192.168.0.1/24
> Not quite sure what problem you see there ... /24 defines
> all addresses from 192.168.0.1 to 192.168.0.254, and
> .99 is smack-bang in the middle of that?
Try 192.168.0.0/24 instead.
Wietse Venema
> > Are you suggesting that Postfix should make 32
> > database queries
> > per IPv4 address, one for each possible netmask
> > value?
>
> The only thing im suggesting to you is, reading a
> message fully before hitting reply.
>
> All i asked for was a stored procedure which converts
> the postfix queries and checks if the ip is within a
> given net.
>
> NO where in my post did i suggest postfix doing that
> because i know that it would result in many queries.
One Postfix query is one IP address. How would one convert the
query to the right netmask without making 32 queries?
Wietse
Victor Duchovni
The original (non-SQL) Postgres query language was capable of expressing
containment relational operators for points in regions, ... and likely
IP addresses in netblocks. With suitable extended relational operatators
one can ask:
select cidr.prefix from cidr where ip CONTAINED_IN cidr
So this is in doable in principle, given the right database, but I have
no experience with the query language of PostreSQL.
--
Viktor.
P.S. Morgan Stanley is looking for a New York City based, Senior Unix
system/email administrator to architect and sustain the Unix email
environment. If you are interested, please drop me a note.
Disclaimer: off-list followups get on-list replies or get ignored.
Please do not ignore the "Reply-To" header.
To unsubscribe from the postfix-users list, visit
http://www.postfix.org/lists.html or click the link below:
<mailto:majo...@postfix.org?body=unsubscribe%20postfix-users>
If my response solves your problem, the best way to thank me is to not
send an "it worked, thanks" follow-up. If you must respond, please put
"It worked, thanks" in the "Subject" so I can delete these quickly.
Andrej Ricnik-Bay
> The original (non-SQL) Postgres query language was capable of expressing
> containment relational operators for points in regions, ... and likely
> IP addresses in netblocks. With suitable extended relational operatators
> one can ask:
>
> select cidr.prefix from cidr where ip CONTAINED_IN cidr
>
> So this is in doable in principle, given the right database, but I have
> no experience with the query language of PostreSQL.
Looks to me like it can be done ...
http://www.postgresql.org/docs/7.2/static/datatype-net-types.html#DATATYPE-CIDR
http://www.pgadmin.org/docs/dev/pg/functions-net.html\
Devdas Bhagat
<snip>
> The original (non-SQL) Postgres query language was capable of expressing
> containment relational operators for points in regions, ... and likely
> IP addresses in netblocks. With suitable extended relational operatators
> one can ask:
>
> select cidr.prefix from cidr where ip CONTAINED_IN cidr
>
test=# SELECT * from mynetworks;
id | range
----+----------------
1 | 192.168.1.0/24
2 | 192.168.2.0/24
(2 rows)
test=# SELECT id from mynetworks where range >>= '192.168.1.1';
id
----
1
(1 row)
test=# SELECT id from mynetworks where range >>= '192.168.0.1';
id
----
(0 rows)
test=# \d mynetworks
Table "public.mynetworks"
Column | Type | Modifiers
--------+---------+------------------------------------------------------------
id | integer | not null default
nextval('public.mynetworks_id_seq'::text)
range | inet |
Devdas Bhagat
mouss
instead of significant bits.
He just needs to know that an ip A is in a network defined by IP N and
mask M if and only if
inet_aton(A) && inet_aton(N) = inet_aton(N) && inet_aton(M)
He may want to precompute inet_aton(N) and inet_aton(M) if really needed.
Victor Duchovni
> On 10/05/06 16:23 -0400, Victor Duchovni wrote:
> <snip>
> > The original (non-SQL) Postgres query language was capable of expressing
> > containment relational operators for points in regions, ... and likely
> > IP addresses in netblocks. With suitable extended relational operatators
> > one can ask:
> >
> > select cidr.prefix from cidr where ip CONTAINED_IN cidr
> >
> This should be feasible with a query.
>
> test=# SELECT * from mynetworks;
> id | range
> ----+----------------
> 1 | 192.168.1.0/24
> 2 | 192.168.2.0/24
> (2 rows)
>
> test=# SELECT id from mynetworks where range >>= '192.168.1.1';
> id
> ----
> 1
> (1 row)
>
Yep, that will do the job quite nicely.
Harakiri
what do you do if you have INET addresses AND netmasks
- ok you could make 2 seperate columns one is just a
string and another is the type inet....
like
SELECT id from mynetworks where range >>=>
'192.168.1.1' or ip = '192.168.1.1'
however - an ANSI SQL stored procedure would be really
appreciated - what if you change the SQL backend ?
thanks
Victor Duchovni
> this is pretty nice, but there is still a problem -
> what do you do if you have INET addresses AND netmasks
> - ok you could make 2 seperate columns one is just a
> string and another is the type inet....
>
> like
>
> SELECT id from mynetworks where range >>=>
> '192.168.1.1' or ip = '192.168.1.1'
>
> however - an ANSI SQL stored procedure would be really
> appreciated - what if you change the SQL backend ?
Clearly ANSI SQL has no relational operators for CIDR. If the input
keys were one hex digit per nibble (e.g. C0A80101) one could use
select block.id where block.start <= '%s' and '%s' <= block.end
because address ordering would be lexical, but this is not the case,
so ANSI SQL cannot AFAIK do the job.
Devdas Bhagat
> this is pretty nice, but there is still a problem -
> what do you do if you have INET addresses AND netmasks
> - ok you could make 2 seperate columns one is just a
> string and another is the type inet....
Fix the data structure. Or use a view.
Devdas Bhagat
Harakiri
not a single sql query something like
1. convert the ip to hex/int representation
2. select all values from mynetworks
3. convert all values from mynetworks to hex/int
representation
4. check if ip is within range < or >
since im not a database programmer or sql guru i
thought somebody already have done something similar
--- Victor Duchovni
<Victor....@MorganStanley.com> wrote:
Andrej Ricnik-Bay
> however - an ANSI SQL stored procedure would be really
> appreciated - what if you change the SQL backend ?
In that case happy coding :) ... ANSI SQL knows nothing about
network data types; you're on your own in parsing and validating
strings. Speaking of which: the standardisation in terms of
stored procedures is pretty much non-existing, too.
> thanks
mouss
> I see - therefor i thought about a stored procedure
> not a single sql query something like
>
> 1. convert the ip to hex/int representation
> 2. select all values from mynetworks
> 3. convert all values from mynetworks to hex/int
> representation
> 4. check if ip is within range < or >
>
>
A long time ago, I have experimented the following in mysql:
inet_aton(%s) & inet_aton(netmask) = inet_aton(network) &
inet_aton(netmask)
if you convert data before storing it (perform the inet_aton and the rhs
'&'), then
inet_aton(%s) & bin_mask = bin_net
(provided bin_net & bin_mask = bin_net) is equivalent, but data is less
readable.
using ranges is possible, but make sure you get the ordering right
(always use network order, even if manually testing).
Harakiri
a few months ago it was suggested using the postgres
cidr specific datatype to query for mynetworks from
postfix - this worked great - however
we have been testing with postfix 2.3 now - and it no
longer works because postfix is not using the IP
address of the sender anymore but the senders hostname
for the query - does anyone know anything about this
change ?
thank you
--- Devdas Bhagat <dev...@dvb.homelinux.org> wrote:
Victor Duchovni
> Hello,
>
> a few months ago it was suggested using the postgres
> cidr specific datatype to query for mynetworks from
> postfix - this worked great - however
> we have been testing with postfix 2.3 now - and it no
> longer works because postfix is not using the IP
> address of the sender anymore but the senders hostname
> for the query - does anyone know anything about this
> change ?
No change, the table has always been searched by both name and IP address.
To avoid name lookups:
mynetworks = !pcre:/etc/postfix/notaddress, pgsql:...
notaddress:
if !/^\d+\.\d+\.\d+\.\d+$/
# -"- for IPv6?
/^/ not-an-address
endif
This is only necessary if non address lookup keys trigger lookup errors
(rather than simply "not found") when searching a Postgres CIDR table.
--
Viktor.
Harakiri
thanks for your response - however your information do
not match with our experiences.
> No change, the table has always been searched by
> both name and IP address.
> To avoid name lookups:
>
This is untrue - prior to postfix 2.3 the mynetworks
query ALWAYS used IP address and NEVER hostnames. We
have multiple productive systems who are using this
lookup - with 2.3 the following error is thrown and no
checks will be made
Sep 13 18:37:14 debian postfix/smtpd[17686]: warning:
pgsql query failed: fatal error from host
myHostName:54310: ERROR: invalid input synta
x for type inet: "somedomain.somewhere"?
Sep 13 18:37:14 debian postfix/smtpd[17686]: fatal:
pgsql:/etc/postfix/pgsql-mynetworks.cf(0,lock|fold_fix):
table lookup problem
Furthermore, the suggested workaround using pcrec does
not work - the regular expression does - i tested this
but in both cases - either its a hostname or an ip
address - the pgsql query is not executed - postfix
only executes
!pcre:/etc/postfix/notaddress
It also makes no sense to do a hostname lookup for
"mynetworks" its an IP Address value not a string -
even when looking at the documentation - no where does
it say anything about hostnames
http://www.postfix.org/postconf.5.html
"Specify a list of network addresses or
network/netmask patterns, separated by commas and/or
whitespace."
So - why and when was this changed between 2.2 and 2.3
?
--- Victor Duchovni
<Victor....@MorganStanley.com> wrote:
Wietse Venema
> Hello Victor,
>
> thanks for your response - however your information do
> not match with our experiences.
>
> > No change, the table has always been searched by
> > both name and IP address.
> > To avoid name lookups:
>
> This is untrue - prior to postfix 2.3 the mynetworks
> query ALWAYS used IP address and NEVER hostnames. We
Which query? You report a problem but don't report
configuration information?
Wietse
Harakiri
"postfix 2.3 broke query for mynetworks from database
with pgsql ?"
already contains the query - it was just not quoted by
the first reply.
here it is again :
SELECT id from mynetworks where range >>='%s'
Wietse Venema
> The initial Post of this Thread
> "postfix 2.3 broke query for mynetworks from database
> with pgsql ?"
> already contains the query - it was just not quoted by
> the first reply.
>
> here it is again :
>
> SELECT id from mynetworks where range >>='%s'
I don't care what you do in PGSQL.
I need to know what you do in POSTFIX.
Harakiri
>
> > here it is again :
> >
> > SELECT id from mynetworks where range >>='%s'
>
> I don't care what you do in PGSQL.
>
> I need to know what you do in POSTFIX.
Sigh, that is exactly what i do in postfix i do not
know how to elaborate this anymore.
main.cf :
mynetworks =
pgsql:$config_directory/pgsql-mynetworks.cf
query = SELECT id from postfix_mynetworks where
range >>='%s'
In Versions prior 2.3 - only the IP address was
substituted with the %s in postfix - now when
performing the mynetworks check - also a hostname is
substituted with %s
Wietse Venema
> > > here it is again :
> > >
> > > SELECT id from mynetworks where range >>='%s'
> >
> > I don't care what you do in PGSQL.
> >
> > I need to know what you do in POSTFIX.
>
> Sigh, that is exactly what i do in postfix i do not
> know how to elaborate this anymore.
>
> main.cf :
>
> mynetworks =
> pgsql:$config_directory/pgsql-mynetworks.cf
Thanks for replying the first part.
For the next part of the answer: where do you USE mynetworks in
Postfix?
Wietse
Harakiri
--- Wietse Venema <wie...@porcupine.org> wrote:
> For the next part of the answer: where do you USE
> mynetworks in
> Postfix?
Im not quiet sure i understand what you mean - its
used for client checks in main.cf nowhere else
smtpd_recipient_restrictions = permit_mynetworks, reject
Victor Duchovni
> Hello Victor,
>
> thanks for your response - however your information do
> not match with our experiences.
>
> > No change, the table has always been searched by
> > both name and IP address.
> > To avoid name lookups:
> >
>
> This is untrue - prior to postfix 2.3 the mynetworks
> query ALWAYS used IP address and NEVER hostnames. We
> have multiple productive systems who are using this
> lookup - with 2.3 the following error is thrown and no
> checks will be made
>
> Sep 13 18:37:14 debian postfix/smtpd[17686]: warning:
> pgsql query failed: fatal error from host
> myHostName:54310: ERROR: invalid input synta
> x for type inet: "somedomain.somewhere"?
> Sep 13 18:37:14 debian postfix/smtpd[17686]: fatal:
> pgsql:/etc/postfix/pgsql-mynetworks.cf(0,lock|fold_fix):
> table lookup problem
And yet the code has not changed, mynetworks lookups have always been
name and IP via namadr_list_match():
src/global/namadr_list.h:
#define namadr_list_init(f, p) \
match_list_init((f), (p), 2, match_hostname, match_hostaddr)
#define namadr_list_match match_list_match
#define namadr_list_free match_list_free
This first tries the hostname, then the address.
Wietse Venema
> > For the next part of the answer: where do you USE
> > mynetworks in
> > Postfix?
>
> Im not quiet sure i understand what you mean - its
> used for client checks in main.cf nowhere else
>
> smtpd_recipient_restrictions = permit_mynetworks, reject
try this:
postconf | grep mynetworks
This will give an idea of where mynetworks is being used.
The behavior of "permit_mynetworks" has not changed since Postfix
was released many years ago. The other mynetworks uses may have
changed, but I don't want to spend a lot of time reading Postfix
source if you can simply provide the information.
Wietse
Victor Duchovni
Specifically, matching against "mynetworks" has always involved both
hostname and address lookups, though only IP addresses (and CIDR blocks
when not in tables) are documented as valid list elements. This is code
re-use, there is no "address-only" match_list interface in Postfix at
this time. Perhaps this could be improved, but it has not changed in 2.3,
and though perhaps somewhat unexpected, is not a bug...
Proposed: src/global/addr_list.h
...
#define ADDR_LIST MATCH_LIST
#define addr_list_init(f, p) match_list_init((f), (p), 1, match_hostaddr)
#define addr_list_match match_list_match
#define addr_list_free match_list_free
...
Then permit_mynetworks() use addr_list_init(), addr_list_match() and
addr_list_free() to process mynetworks. The motivation would be to
support clever tables (e.g. PostgreSQL) that understand addresses and
break (syntax) when presented with hostnames. The tables would still
have to work correctly with both IPv4 and IPv6 lookup keys.
Perhaps instead of Postfix having to discern the right subset of keys
for the table lookup, a "stored procedure" can be use to eliminate
unsuitable keys?
In the short-term, my "!pcre:/etc/postfix/notaddress" hack works for
IPv4 only environments.
Harakiri
--- Wietse Venema <wie...@porcupine.org> wrote:
try this:
>
> postconf | grep mynetworks
>
> This will give an idea of where mynetworks is being
> used.
Yes, the other values are the default ones here :
mynetworks =
pgsql:$config_directory/pgsql-mynetworks.cf
mynetworks_style = subnet
parent_domain_matches_subdomains =
debug_peer_list,fast_flush_domains,mynetworks,permit_mx_backup_networks,qmqpd_authorized_clients,relay_domains,smtpd_access_maps
proxy_read_maps = $local_recipient_maps $mydestination
$virtual_alias_maps $virtual_alias_domains
$virtual_mailbox_maps $virtual_mailbox_domains
$relay_recipient_maps $relay_domains $canonical_maps
$sender_canonical_maps $recipient_canonical_maps
$relocated_maps $transport_maps $mynetworks
smtpd_client_event_limit_exceptions =
${smtpd_client_connection_limit_exceptions:$mynetworks}
Harakiri
--- Victor Duchovni
<Victor....@MorganStanley.com> wrote:
> Specifically, matching against "mynetworks" has
> always involved both
> hostname and address lookups, though only IP
> addresses (and CIDR blocks
> when not in tables) are documented as valid list
> elements. This is code
> re-use, there is no "address-only" match_list
> interface in Postfix at
> this time. Perhaps this could be improved, but it
> has not changed in 2.3,
> and though perhaps somewhat unexpected, is not a
> bug...
Sorry, yet again i can only tell you this does not
match the experiences on multiple productive servers
with postfix 2.2.10 - it has always worked like this.
Maybe you are mixing up address lookups (sender/user
names) with mynetworks checks in the source - i dont
know, it really doesnt make any sense to query
hostnames for myNETWORKS.
> In the short-term, my
> "!pcre:/etc/postfix/notaddress" hack works for
> IPv4 only environments.
It does not for us - as i said the pgsql lookup is not
performed no matter what !pcre:/etc/postfix/notaddress
returns - our current workaround is now do a regular
expression in postgres
SELECT id FROM mynetworks WHERE '%s' ~*
'^\\d{1,3}\.\\d{1,3}\.\\d{1,3}\.\\d{1,3}$' AND range
>>=inet('%s'::text);
this does not work for IPv6 tho
Wietse Venema
> try this:
> >
> > postconf | grep mynetworks
> >
> > This will give an idea of where mynetworks is being
> > used.
>
> Yes, the other values are the default ones here :
>
> smtpd_client_event_limit_exceptions =
> ${smtpd_client_connection_limit_exceptions:$mynetworks}
> smtpd_recipient_restrictions = permit_mynetworks, reject
As early as the Postfix alpha release, permit_mynetworks has always
looked up the client hostname in addition to the client IP address.
smtpd_client_event_limit_exceptions also has always searched by
client hostname and IP address, ever since it was introduced with
Postfix 2.2.
I suspect that you are looking at a change in PGSQL instead of Postfix.
Wietse
Victor Duchovni
>
>
> --- Victor Duchovni
> <Victor....@MorganStanley.com> wrote:
>
> > Specifically, matching against "mynetworks" has
> > always involved both
> > hostname and address lookups, though only IP
> > addresses (and CIDR blocks
> > when not in tables) are documented as valid list
> > elements. This is code
> > re-use, there is no "address-only" match_list
> > interface in Postfix at
> > this time. Perhaps this could be improved, but it
> > has not changed in 2.3,
> > and though perhaps somewhat unexpected, is not a
> > bug...
>
> Sorry, yet again i can only tell you this does not
> match the experiences on multiple productive servers
> with postfix 2.2.10 - it has always worked like this.
Experience is not necessarily the whole truth. The mynetworks matching
code in Postfix 1.0.x, 1.1.x, 2.0.x, 2.1.x, 2.2.x, 2.3.x and 2.4-YYYYMMDD
snapshots performs a lookup on both the hostname and the IP address.
If your experience is different, something else is responsible for the
difference.