PBKDF2 for Postgres

Databases
1

I fear someone is going to tell me that this was already built-in, but since I couldn’t find it, here is PBKDF2 for Postgres:

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 $$;

select encode(PBKDF2('123456', 'password', 1000, 16, 'sha256'), 'hex');
2

I can’t edit it now, but add the keyword “immutable”.

3

on which line?

4

Right after “returns” in the header. It tell PSQL that, given the same inputs, the output will always be the same.

5

Thanks. That is what I thought but wasn’t 100% sure. I am still learning Stored Procedures/Functions, especially when it comes to PostgreSQL.

6

Immutable is a big one. Consider this query:

SELECT *, my_function(inputs) FROM my_table

Without Immutable, that function would be recalculated for every record. With it, it’s calculated once and reused resulting in a huge performance boost.

7

I’ve updated Kem’s original to include “immutable”.