Skip to content

Instantly share code, notes, and snippets.

@bwbroersma

bwbroersma/totp.pg.sql

Last active June 22, 2025 20:10
Show Gist options
  • Save bwbroersma/676d0de32263ed554584ab132434ebd9 to your computer and use it in GitHub Desktop.
Save bwbroersma/676d0de32263ed554584ab132434ebd9 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
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;
@danwdart
Copy link

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment