PBKDF2 for Postgres

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');

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

on which line?

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

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

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.

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