totp

TOTP implementation in pure PostgreSQL plpgsql

This extension provides the HMAC Time-Based One-Time Password Algorithm (TOTP) as specified in RFC 6238/4226 as pure plpgsql functions. Runs on PostgreSQL 18+ (and likely earlier versions back to PG 9.6).

Installation

Requires Docker.

make up          # Build image and start Postgres 18
make install-ext # Install extensions into the running container
make test        # Run pg_regress tests

Or manually:

docker compose up -d --build
docker compose exec db make -C /ext install
docker compose exec db make -C /ext installcheck PGUSER=postgres

Usage

totp.generate

SELECT totp.generate('mysecret');

-- you can also specify period, digits, time, hash, encoding
SELECT totp.generate('mysecret', 30, 6);

Produces a TOTP code of length 6:

013438

totp.verify

SELECT totp.verify('mysecret', '765430');

-- you can also specify period and digits
SELECT totp.verify('mysecret', '765430', 30, 6);

Returns TRUE or FALSE.

totp.url

SELECT totp.url(
    'customer@email.com',
    'mysecret',
    30,
    'Acme Inc'
);

Produces a URL-encoded otpauth string:

otpauth://totp/customer@email.com?secret=mysecret&period=30&issuer=Acme%20Inc

Caveats

• Currently only supports sha1
• Currently only supports 20 byte secrets

Pull requests welcome!

Credits

• https://tools.ietf.org/html/rfc6238
• https://www.youtube.com/watch?v=VOYxF12K1vE
• https://pgxn.org/dist/otp/