RFC6238 TOTP implementation in pure PostgreSQL

Yes, using '=' for comparing secrets is a common mistake in many implementations. The right thing to do would be to implement a string comparison function that always takes the same amount of time to complete regardless of whether the two input strings match or do not match or where they mismatch.

See https://security.stackexchange.com/a/83671 for some code examples that accomplish this by using the bitwise XOR operator to compare two corresponding bytes from both inputs and bitwise OR operator to accumulate the comparison results. As per my professional experience, this is a common pattern used in security-related code.

The TOTP too is a secret between the client (usually a human user) and the server. After all, it is used to gain access to another system, so if an attacker can guess the TOTP then the security of the system is compromised.

Consider the following example. The TOTP value to log into a system at a given time is 27182. Only the client and the server know this because they share the same secret key from which the TOTP values are generated. Now assume an attacker that can send arbitrary TOTPs to the system and has access to reliable timing information about how long it takes the server to compare the received TOTP value with the expected TOTP value. Now in practice, it is hard to get access to reliable timing information, especially when the communication is over a network where random network latency can make any timing measurements reliable. Regardless, good security requires that an attacker should not be able to deduce any information about the secret or the TOTP value despite the possibility of having reliable timing information.

With the assumption of reliable timing information being available to the attacker, here is how an attacker would carry out the attack. Brute force the first digit of the TOTP by sending TOTP values 100000, 200000, etc. The observation that it takes slightly longer for the validation to fail for 200000 helps the attacker deduce that the first digit is 2. The attacker can perform this recursively for other digits and deduce all the digits within a maximum of 60 attempts. This is a great reduction in the security of the scheme which required 1000000 attempts originally.

Of course, one can mitigate an attack like this by locking out the user's account after 5 failed attempts or so but that would be an additional layer of security. Each layer of security should be sound on its own. Therefore even if there is an account lockout facility, the TOTP validation algorithm should be secure on its own and should not be vulnerable to timing attacks.

Why? Implementation-wise, it's far better to have a pure plpgsql function than a Python UDF if you have a choice between the two. A Python UDF would be useful if you need to do more Python stuff in the db in general.

Benjie, one of the PostGraphile creators, has a good talk [1] advocating for database-driven development.

OTOH, having dug into PostGraphile a bit, I personally wouldn't advocate for pushing so much backend logic into the DB.

Like, if you decide to do auth and TOTP in the DB, you'll end up implementing it in PL/pgSQL. Writing core security logic in a less-familiar language feels like it adds risk.

Also, for smaller projects your backend is often just a single server, so moving auth in into the DB doesn't save you from managing distributed state.

[1] https://www.youtube.com/watch?v=XDOrhTXd4pE

exactly! https://www.graphile.org/postgraphile/ is the system I'm using on and wanted to avoid writing a resolver in JS

On the other hand, one could also write js in postgres:

https://www.postgresql.org/docs/13/external-pl.html

https://github.com/plv8/plv8