Two factor authentication using Totp

pbel78 commented 5 years ago

Was somebody already thinking how to implement TOTP two factor authentication into the postgreyt workflow? I understand that a OTP could be passed during login. But then in postgres I would need to evaluate if it is valid. Would it make sense to use a java library in pl java that offers such a functionallity ? If yes: is there any recommended library to do so? Regards Petr

bwbroersma commented 4 years ago

There is an extension for postgres that can do a TOTP verify: https://github.com/Apsalar/ap_pgutils

Another option is implementing TOTP yourself with pgcrypto according to the spec RFC4226 / RFC6238:

HOTP(K,C) = Truncate(HMAC-SHA-1(K,C))

Where K is the shared key, C is the counter (unix time / x with a default of 30 seconds) Truncate = use the last nibble (4 bits) in the output as offset for a 4 byte number, clear the first bit (for signed int issues), convert to unsigned (31 bit) number, modulo 1000000

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;

oathtool is a handy tool as a reference (of course you could also use this in a COPY ... FROM PROGRAM oathtool setting, but that would be unadvised. See:

oathtool --totp 3132333435363738393031323334353637383930
# or oathtool --totp  -b GEZDGNBVGY3TQOJQGEZDGNBVGY3TQOJQ
psql -c "SELECT totp('\x3132333435363738393031323334353637383930');"

Where 3132333435363738393031323334353637383930 is the example hex string from the RFC meaning 12345678901234567890 (or GEZDGNBVGY3TQOJQGEZDGNBVGY3TQOJQ in base32).

bwbroersma commented 4 years ago

Update which implements the full RFC4226/6238 spec: added HOTP and options for sha256 and sha512 hash algorithms + non default number of digits (and fix for prefix zeros): https://gist.github.com/bwbroersma/676d0de32263ed554584ab132434ebd9#gistcomment-3080008

PostgREST / postgrest

Two factor authentication using Totp #1250