PBKDF2 function in PostgreSQL

Question

How can the PBKDF2 function be done in PostgreSQL? There does not appear to be a native implementation.

Dudgeon

I think this may belong in codereview.stackexchange.com – Befoul

As a check, you can use the PBKDF2 test vectors at my Giuthub repository to test your implementation. You also may be able to achieve significant speedup through either using a straight OpenSSL/PolarSSL call, or through loop unrolling and HMAC unrolling, since HMAC does some operations each time that only need to be done once. See the MS SQL Server implementation in the above link for details. – Dumps

Nice. Usage for Django: select concat(substring(password from 1 for 33),encode(PBKDF2(substring(password from 21 for 12)::bytea,'%password',36000,32,'sha256'),'base64'))=password from auth_user where username = '%user; – Scarletscarlett

Requires the PGCrypto extension. I've tested this against two other implementations and it seems to work well :) – Intaglio

Answer 1

Moved Answer out of Question to adhere to Stack Overflow guidelines. See original revision of post.

Original post (Revision link)

Not able to find it natively, and based on PHP code found on the 'net, I came up with this PBKDF2 function for PostgreSQL. Enjoy.

create or replace function PBKDF2 
  (salt bytea, pw text, count integer, desired_length integer, algorithm text)
  returns bytea
  immutable
  language plpgsql
as $$
declare 
  hash_length integer;
  block_count integer;
  output bytea;
  the_last bytea;
  xorsum bytea;
  i_as_int32 bytea;
  i integer;
  j integer;
  k integer;
begin
  algorithm := lower(algorithm);
  case algorithm
  when 'md5' then
    hash_length := 16;
  when 'sha1' then
    hash_length = 20;
  when 'sha256' then
    hash_length = 32;
  when 'sha512' then
    hash_length = 64;
  else
    raise exception 'Unknown algorithm "%"', algorithm;
  end case;
  
  block_count := ceil(desired_length::real / hash_length::real);
  
  for i in 1 .. block_count loop    
    i_as_int32 := E'\\000\\000\\000'::bytea || chr(i)::bytea;
    i_as_int32 := substring(i_as_int32, length(i_as_int32) - 3);
    
    the_last := salt::bytea || i_as_int32;
    
    xorsum := HMAC(the_last, pw::bytea, algorithm);
    the_last := xorsum;
    
    for j in 2 .. count loop
      the_last := HMAC(the_last, pw::bytea, algorithm);
      
      --
      -- xor the two
      --
      for k in 1 .. length(xorsum) loop
        xorsum := set_byte(xorsum, k - 1, get_byte(xorsum, k - 1) # get_byte(the_last, k - 1));
      end loop;
    end loop;
    
    if output is null then
      output := xorsum;
    else
      output := output || xorsum;
    end if;
  end loop;
  
  return substring(output from 1 for desired_length);
end $$;

I've tested against other implementations without deviation, but be sure to test it yourself.

Scream