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;

this is pretty cool! since authy and google authenticator use base32, do you by chance know a simple way to do the base32 decode in 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.

Super cool! Let's use it in prod immediately!