CODEWITHSUNDEEP
postgresqlippostgresql-8.3
jzadeh
jzadeh

Reputation: 703

Check if the integer value of an IP address is in a private range in PostgreSQL

I am using MPP version of PostgreSQL that was forked off version 8.3

I have a large table containing a column called ip_address which is inet and another column called hash_ip_address which bigint. Every value in the hash_ip_address column is the converted value from the ip_address column using the hashinet function. I am trying to use the integer values to determine when an address is private or public. The problem I am having is I do not see a general way to check if the integer value of the IP address is private because some values are positive and some are negative like the example below:

select hashinet('10.10.0.1'::inet);
  hashinet
-------------
 -1939051471
(1 row)

select hashinet('10.0.0.4'::inet);
  hashinet
------------
 1510004552
(1 row)

Is there a way to determine if the an IP address is private using only the converted integer value form the hashinet function?

edit: Here is the defintion of the hashinet function from http://doxygen.postgresql.org/network_8c.html#a0baabf8b98dbbcc39c6c1c814f9d86f8

Datum hashinet ( PG_FUNCTION_ARGS )

Definition at line 527 of file network.c.

References hash_any(), ip_addrsize(), PG_GETARG_INET_PP, and VARDATA_ANY.

{
    inet       *addr = PG_GETARG_INET_PP(0);
    int         addrsize = ip_addrsize(addr);

    /* XXX this assumes there are no pad bytes in the data structure */
    return hash_any((unsigned char *) VARDATA_ANY(addr), addrsize + 2);
}

Upvotes: 0

Views: 1364

Answers (1)

Chris Travers
Chris Travers

Reputation: 26454

Wow. You are using an internal PostgreSQL function that is not in the documentation.

It looks to me like hashinet calls hash_any() which is a true hash so there is no way to do this.

If you need the ints to be meaningful and useful outside of merely tying an arbitrary int to a IP address, a better approach would be to create your ip addresses to int by creating a new function:

 CREATE FUNCTION inet_to_int(inet) RETURNS INT language sql immutable AS 
 $$
 SELECT $1 - '0.0.0.0'::inet;
 $$;

Then use that. From there your private ranges will correspond to int ranges and you can more easily check.

For example 10/8 addresses are BETWEEN 167772160 AND 184549375....

Upvotes: 0

Related Questions