TOTP implementation for https://github.com/PostgREST/postgrest/issues/1250#issuecomment-551847083

totp.pg.sql

CREATE EXTENSION pgcrypto;
CREATE OR REPLACE FUNCTION totp(key BYTEA, clock_offset INT DEFAULT 0) RETURNS INT AS $$
DECLARE
    c BYTEA := '\x000000000' || TO_HEX(FLOOR(EXTRACT(EPOCH FROM NOW()) / 30)::INT + clock_offset);
    mac BYTEA := HMAC(c, key, 'sha1');
    trunc_offset INT := GET_BYTE(mac, 19) % 16;
    result INT := SUBSTRING(SET_BIT(SUBSTRING(mac FROM 1 + trunc_offset FOR 4), 7, 0)::TEXT, 2)::BIT(32)::INT % 1000000;
BEGIN
    RETURN result;
END;
$$ LANGUAGE plpgsql;

I modified this to expose a few parameters, and also the base32 encode https://gist.github.com/pyramation/15e2b531ea973fccd0011ce334030adc

CREATE FUNCTION totp.generate ( secret text, period int DEFAULT 30, digits int DEFAULT 6, time_from timestamptz DEFAULT now(), hash text DEFAULT 'sha1', encoding text DEFAULT 'base32', clock_offset int DEFAULT 0 ) RETURNS text AS $EOFCODE$
DECLARE
    c INT := FLOOR(EXTRACT(EPOCH FROM time_from) / period)::INT + clock_offset;
    key bytea;
BEGIN

  IF (encoding = 'base32') THEN 
    key = ( '\x' || totp.base32_to_hex(secret) )::bytea;
  ELSE 
    key = secret::bytea;
  END IF;

  RETURN totp.hotp(key, c, digits, hash);
END;
$EOFCODE$ LANGUAGE plpgsql STABLE;

@bwbroersma as you can see, my base32 is probably not as efficient as it could be, which is why I was wondering if there is a better way to implement base32. Currently the base32 does work if you'd like to use it! But it's more functional string parsing instead of bit shifting which would be better.