Creating an IPv6 suffix from MAC address using PL/pgSQL

So right now I am writing an application for my university – a server application controlling the access to the network by means of VMPS. Anyways, we need to assign IPv4 and IPv6 addresses to registered computers for the use of DHCP. As we all know, IPv6 addresses are generated based on the MAC address of the network adapter.

Since PostgreSQL has an excellent support for inet types, we chose to stick with that. And since I believe the best way to design a DB-based application is to put as much logic into  the database as possible, I chose to generate the IPv6 addresses on the DB’s side. But when it came to generating them, it turned out that the only thing you can do with a MAC address (PostgreSQL type macaddr) is cast it to text. I mean, what a joke! So I came up with a solution to generate the MAC-depended suffix needed for the generation of IPv6 address. In case it proves useful to anybody, here you go:

CREATE OR REPLACE FUNCTION mac_create_suffix(macaddr)
RETURNS text AS '
DECLARE
  mac ALIAS FOR $1;
  mac_switched macaddr;
  ret text;
BEGIN
  mac_switched := (SELECT mac_switch_7b(mac));
  ret := mac_switched::text;
  ret := substring(ret from 1 for 2) || substring(ret from 4 for 2) || '':'' ||
    substring(ret from 7 for 2) || ''ff'' || '':'' ||
    ''fe'' || substring(ret from 10 for 2) || '':'' ||
    substring(ret from 13 for 2) || substring(ret from 16 for 2);
RETURN ret;
END;'
LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION mac_switch_7b(macaddr)
RETURNS macaddr AS '
DECLARE
  mac_orig ALIAS FOR $1;
  mac_orig_txt text;
  mac_ret macaddr;
  mac_ret_txt text;
  siodmyBit BIT(4);
  buf text;
  bit_orig BIT(4);
  bit_repl BIT(4);
BEGIN
  --- we'll use that to switch the 7th bit from the left to 1
  siodmyBit := ''0010''::BIT(4);
  mac_orig_txt := upper(mac_orig::text);
  --- switching the 7th bit from the left will only affect the second letter of the MAC address
  buf := (SELECT substring(mac_orig_txt from 2 for 1));
  bit_orig := CASE buf
    WHEN 0::text  THEN ''0000''::BIT(4)
    WHEN 1::text  THEN ''0001''::BIT(4)
    WHEN 2::text  THEN ''0010''::BIT(4)
    WHEN 3::text  THEN ''0011''::BIT(4)
    WHEN 4::text  THEN ''0100''::BIT(4)
    WHEN 5::text  THEN ''0101''::BIT(4)
    WHEN 6::text  THEN ''0110''::BIT(4)
    WHEN 7::text  THEN ''0111''::BIT(4)
    WHEN 8::text  THEN ''1000''::BIT(4)
    WHEN 9::text  THEN ''1001''::BIT(4)
    WHEN ''A''    THEN ''1010''::BIT(4)
    WHEN ''B''    THEN ''1011''::BIT(4)
    WHEN ''C''    THEN ''1100''::BIT(4)
    WHEN ''D''    THEN ''1101''::BIT(4)
    WHEN ''E''    THEN ''1110''::BIT(4)
    WHEN ''F''    THEN ''1111''::BIT(4)
  END;
  --- THIS IS WHERE WE SWITCH THE 7th BIT TO 1
  bit_repl := bit_orig | siodmyBit;
  --- END OF SWITCHING
  buf := CASE bit_repl
    WHEN ''0000''::BIT(4) THEN 0::text
    WHEN ''0001''::BIT(4) THEN 1::text
    WHEN ''0010''::BIT(4) THEN 2::text
    WHEN ''0011''::BIT(4) THEN 3::text
    WHEN ''0100''::BIT(4) THEN 4::text
    WHEN ''0101''::BIT(4) THEN 5::text
    WHEN ''0110''::BIT(4) THEN 6::text
    WHEN ''0111''::BIT(4) THEN 7::text
    WHEN ''1000''::BIT(4) THEN 8::text
    WHEN ''1001''::BIT(4) THEN 9::text
    WHEN ''1010''::BIT(4) THEN ''A''
    WHEN ''1011''::BIT(4) THEN ''B''
    WHEN ''1100''::BIT(4) THEN ''C''
    WHEN ''1101''::BIT(4) THEN ''D''
    WHEN ''1110''::BIT(4) THEN ''E''
    WHEN ''1111''::BIT(4) THEN ''F''
  END;
  mac_ret_txt := (SELECT overlay(mac_orig_txt placing buf from 2 for 1));
  mac_ret := mac_ret_txt::macaddr;
RETURN mac_ret;
END;'
LANGUAGE 'plpgsql';

And here’s the output of a test run:

myDB=# SELECT mac_create_suffix('00:0d:b9:1a:ed:b8');
  mac_create_suffix
---------------------
 020d:b9ff:fe1a:edb8
(1 row)

A sidenote: You’d probably want to rewrite the hex-to-bin and bin-to-hex to be in a separate function, probably a hash table, but hey! it’s only a proof of concept

Advertisement