Some time ago, I thought I needed to be able to programmatically calculate properly aligned networks containing the netmask in dotted-quad (as opposed to CIDR) format (i.e. 192.168.1.0/255.255.255.0). That need is [thankfully] all but a distant memory, but as I was perusing the 8.4.3 documentation this afternoon, I glanced at the network address functions and operators and recalled some of the difficulties I encountered when coding the initial utility in Perl. I thought about exercising some of those network address functions by redoing it in PostgreSQL.
The Helper Functions
The first task was to create a function to calculate the decimal (BIGINT) equivalent of a properly formatted IPv4 address. PostgreSQL's INET and CIDR datatypes are ideal for this because of the address validation they provide (which makes life significantly easier).
CREATE OR REPLACE FUNCTION inet_to_longip(v_t INET)
RETURNS BIGINT AS /*{{{*/
$inet_to_longip$
DECLARE
t TEXT[];
i BIGINT;
BEGIN
t := REGEXP_SPLIT_TO_ARRAY(HOST(v_t), E'\\.');
i := (t[1]::BIGINT << 24) + (t[2]::BIGINT << 16) +
(t[3]::BIGINT << 8) + t[4]::BIGINT;
RETURN i;
END;
$inet_to_longip$ LANGUAGE plpgsql STRICT IMMUTABLE; /*}}}*/
Once we can obtain the decimal (BIGINT) version of the IP address, we need to be able to return it to an INET or CIDR datatype. I couldn't find in the documentation where each of the two datatypes was simply a string, but that's what I discovered as I naively concatenated all the numbers and dots into one. In the interest of full disclosure, I found the basis of this function on the obsolete Wiki of an apparently defunct Linux firewall project that stored the rules in a PostgreSQL v7.4 database.
CREATE OR REPLACE FUNCTION longip_to_inet(v_i BIGINT)
RETURNS INET AS /*{{{*/
$longip_to_inet$
DECLARE
t INET;
BEGIN
t = ((v_i >> 24) & 255::BIGINT) || '.' ||
((v_i >> 16) & 255::BIGINT) || '.' ||
((v_i >> 8) & 255::BIGINT) || '.' ||
(v_i & 255::BIGINT);
RETURN t;
END;
$longip_to_inet$ LANGUAGE plpgsql STRICT IMMUTABLE; /*}}}*/
The last
required piece is a function to determine the most significant bit of what will become the new netmask in the resultant properly-aligned network.
CREATE OR REPLACE FUNCTION netmask_msb(v_i BIGINT)
RETURNS INTEGER AS /*{{{*/
$netmask_msb$
DECLARE
n INTEGER;
BEGIN
FOR n IN REVERSE 31 .. 1 LOOP
IF (v_i & (1 << n)) > 0 THEN
RETURN n;
END IF;
END LOOP;
RETURN n;
END;
$netmask_msb$ LANGUAGE plpgsql STRICT IMMUTABLE; /*}}}*/
A Bonus Function
As an added bonus, I decided to write a quick function to determine the natural class of the network. Years ago, there was no such thing as CIDR, and this was as granular (with classes A, B, and C, anyway) as a block of IP addresses could be.
CREATE OR REPLACE FUNCTION natural_class(v_t INET)
RETURNS CHAR AS /*{{{*/
$natural_class$
DECLARE
o1 BIGINT := inet_to_longip(v_t) >> 28;
BEGIN
CASE
WHEN o1 >> 3 = 0 THEN RETURN('A');
WHEN o1 >> 2 = 2 THEN RETURN('B');
WHEN o1 >> 1 = 6 THEN RETURN('C');
WHEN o1 = 14 THEN RETURN('D');
WHEN o1 = 15 THEN RETURN('E');
END CASE;
RETURN NULL;
END;
$natural_class$ LANGUAGE plpgsql STRICT IMMUTABLE; /*}}}*/
Now that we have the ability to convert the addresses in both directions and do some minor bit-banging to determine what the new netmask should be, let's put it all together. For my purposes, all I needed was the new network and netmask in dotted-quad format. Below, you'll find a few more pieces of information that may or may not be of interest. I can definitely see a use for outputting the data in binary format.
CREATE OR REPLACE FUNCTION align( /*{{{*/
IN v_t1 INET, IN v_t2 INET,
OUT oldstart INET, OUT oldend INET, OUT oldhosts INTEGER,
OUT newstart INET, OUT newend INET, OUT newhosts INTEGER,
OUT cidrmask INT, OUT dqmask INET) RETURNS RECORD AS
$align$
DECLARE
v_oldstart INET := HOST(v_t1);
v_oldend INET := HOST(v_t2);
--
v_oldstart_long BIGINT := inet_to_longip(v_oldstart);
v_oldend_long BIGINT := inet_to_longip(v_oldend);
ipmask BIGINT := v_oldstart_long # v_oldend_long;
maskmsb INTEGER := netmask_msb(ipmask);
new_ipmask BIGINT := (1 << (maskmsb + 1)) - 1;
v_newstart_long BIGINT := v_oldstart_long & ~new_ipmask;
v_newend_long BIGINT := v_oldend_long | new_ipmask;
v_newstart INET := longip_to_inet(v_newstart_long);
v_newend INET := longip_to_inet(v_newend_long);
v_newnet INET :=SET_MASKLEN(v_newstart::cidr,
32 - (maskmsb + 1));
BEGIN
if (v_t1 > v_t2) THEN
RAISE EXCEPTION
'Starting address must be lower than ending address';
END IF;
oldstart := v_oldstart;
oldend := v_oldend;
oldhosts := v_t2 - v_t1 + 1;
newstart := v_newstart;
newend := v_newend;
newhosts := v_newend - v_newstart + 1;
cidrmask := MASKLEN(v_newnet);
dqmask := NETMASK(v_newnet);
RETURN;
END;
$align$ LANGUAGE plpgsql STRICT IMMUTABLE; /*}}}*/
Next Step(s)
There are probably a few inefficiencies in the code I've presented in this tutorial of sorts. I'll leave finding and fixing them (and the binary output) as an exercise for the reader. Admittedly, some of the functionality could be made easier by creating casts to go between the two data formats.
Conclusion
That's all there is to it. Now, when you're trying to configure a VPN device that will be installed at a remote location and all the information you have is information from the receptionist via voicemail (e.g. "Our allowable numbers from the internet people are sixty-six point eighty-eight point one hundred and two point thirty-three to sixty-six point eighty-eight point one hundred and two point thirty-seven), you have access to enough information to mostly configure it before you ship it ahead of your trip to wherever that device may be going (hopefully someplace warm).
Let me know what you think.
