[Pdns-users] PostgreSQL schema for DNSSEC signing

[ktm at rice.edu]

On Thu, Jan 09, 2014 at 10:26:07AM -0600, ktm at rice.edu wrote:
> Hi,
> 
> I am working on porting your auto-signing schema proof-of-concept for Oracle:
> 
> http://wiki.powerdns.com/trac/browser/trunk/pdns/modules/oraclebackend/schema.sql
> 
> to PostgreSQL. I have found something that looks like a bug in the following
> function dnsname_to_raw():
> 
> ------------------------------------------------
> CREATE OR REPLACE FUNCTION dnsname_to_raw (in_dnsname IN VARCHAR2) RETURN RAW AS
>   dnsname VARCHAR2(512) := LOWER(in_dnsname);
>   rawname RAW(512);
> 
>   lpos BINARY_INTEGER := 1;
>   rpos BINARY_INTEGER;
>   label VARCHAR2(63);
> 
>   TYPE convarray IS VARRAY(64) OF RAW(1);
>   byteval convarray := convarray(
>     '00', '01', '02', '03', '04', '05', '06', '07',
>     '08', '09', '0A', '0B', '0C', '0D', '0E', '0F',
>     '10', '11', '12', '13', '14', '15', '16', '17',
>     '18', '19', '1A', '1B', '1C', '1D', '1E', '1F',
>     '20', '21', '22', '23', '24', '25', '26', '27',
>     '28', '29', '2A', '2B', '2C', '2D', '2E', '2F',
>     '30', '31', '32', '33', '34', '35', '36', '37',
>     '38', '39', '3A', '3B', '3C', '3D', '3E', '3F'
>   );
> BEGIN
>   IF dnsname IS NULL THEN
>     RETURN('00');
>   END IF;
> 
>   WHILE lpos <= LENGTH(dnsname) LOOP
>     rpos := INSTR(dnsname, '.', lpos);
>     IF rpos = 0 THEN
>       rpos := LENGTH(dnsname) + 1;
>     END IF;
>     label := SUBSTR(dnsname, lpos, rpos - lpos);
>     rawname := UTL_RAW.CONCAT(
>       rawname,
>       byteval(LENGTH(label) + 1),
>       UTL_I18N.STRING_TO_RAW(label, 'US7ASCII')
>     );
>     lpos := rpos + 1;
>   END LOOP;
> 
>   IF rpos = LENGTH(dnsname) THEN
>     rawname := UTL_RAW.CONCAT(rawname, '00');
>   END IF;
> 
>   RETURN(rawname);
> END;
> ------------------------------------------------
> 
> At the end of the function, it has a test to see if "rpos = LENGTH(dnsname)"
> and if so, appends a 00 byte to the end, but the only place that sets rpos
> earlier:
> 
>     IF rpos = 0 THEN
>       rpos := LENGTH(dnsname) + 1;
>     END IF;
> 
> specifically sets rpos to the length of the string + 1, which would mean
> that the trailing nul would never be added. This looks like a bug, but I
> am working from the "code-as-documentation" instead of a spec for what
> the dnsname_to_raw output definition. Once the PostgreSQL schema has been
> tested, I will post/submit our final version for inclusion in the dist.
> Any assistance would be appreciated.
> 
> Regards,
> Ken

Okay, using the assumption that the code has a bug, here is the PostgreSQL
version:

------------------------------------------------
CREATE OR REPLACE FUNCTION dnsname_to_raw (
  in_dnsname VARCHAR
) RETURNS BYTEA AS $$

DECLARE
  dnsname VARCHAR := LOWER(in_dnsname);
  rawname BYTEA;

  i INTEGER;
  label VARCHAR[];

  byteval VARCHAR[64] := ARRAY[
    '00', '01', '02', '03', '04', '05', '06', '07',
    '08', '09', '0A', '0B', '0C', '0D', '0E', '0F',
    '10', '11', '12', '13', '14', '15', '16', '17',
    '18', '19', '1A', '1B', '1C', '1D', '1E', '1F',
    '20', '21', '22', '23', '24', '25', '26', '27',
    '28', '29', '2A', '2B', '2C', '2D', '2E', '2F',
    '30', '31', '32', '33', '34', '35', '36', '37',
    '38', '39', '3A', '3B', '3C', '3D', '3E', '3F'
  ];

BEGIN
  IF dnsname IS NULL THEN
    RETURN('00');
  END IF;

  label := string_to_array(dnsname, '.');

  FOR i IN 1 .. ARRAY_UPPER(label, 1)
  LOOP

    IF rawname IS NULL THEN
      rawname := decode(byteval[LENGTH(label[i]) + 1], 'hex')
                       || convert_to(label[i], 'SQL_ASCII');
    ELSE
      rawname := rawname || decode(byteval[LENGTH(label[i]) + 1], 'hex')
                       || convert_to(label[i], 'SQL_ASCII');
    END IF;

  END LOOP;

  rawname := rawname || decode('00', 'hex');
  RETURN rawname;

END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;
------------------------------------------------

Here is a sample of the results for a test case:

test=> select dnsname_to_raw('moe.rice.edu');
         dnsname_to_raw         
--------------------------------
 \x036d6f6504726963650365647500
(1 row)

This seems like it should be the correct result, but I would appreciate
it if someone who knew would corroborate this. Thank you.

Regards,
Ken