RFC6238 TOTP implementation in pure PostgreSQL

by pyramation • 5 years ago

68 38 5 years ago

potatochup• 5 years ago

Can someone explain where/why this might be used? Or is it just for fun?

First thing that came to mind was in one of the "Postgres-backend" projects like Hasura[1], Postgraphile[2], or PostgREST[3].

[1] https://github.com/hasura/graphql-engine/

[2] https://www.graphile.org/postgraphile/

[3] https://postgrest.org

smallnamespace2• 5 years ago

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

pyramation14• 5 years ago

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

rbut• 5 years ago

Incrementing the counter, and generating the next HOTP code in a single UPDATE statement could be useful.

pyramation14• 5 years ago

Hi! It was a bit of both fun and work ;)

I use graphile and didn't want to have to implement a resolver in JavaScript, keeping logic and integrity of auth systems in the database.

e12e3• 5 years ago

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

nayuki• 5 years ago

The author's main SQL code seems to be in this file: https://github.com/pyramation/totp/blob/master/packages/totp...

For comparison, these are my relatively short TOTP implementations in {TypeScript, Python, Java, Rust, C++}: https://www.nayuki.io/page/time-based-one-time-password-tool... . I even have a 6-line Python function.

pyramation14• 5 years ago

The file you're pointing to is not the full extension, here it is:

https://github.com/pyramation/totp/blob/master/packages/totp...

vletal• 5 years ago

Cool. If I had to choose I'd wrap the simple py implementation in a PL/Python UDF.

scrollaway3• 5 years ago

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.

susam4• 5 years ago

I even have a 6-line Python function.

In case, anyone is curious what that might look like, I have a 30-line Python code here (TOTP generation only, no verification): https://github.com/susam/mintotp. Indeed the core function (the HOTP function) contains only 6 lines, thanks to Python's extensive standard library.

pyramation14• 5 years ago

Author here. Here is the full code if anyone is interested: https://github.com/pyramation/totp/blob/master/packages/totp...

susam4• 5 years ago

This is very interesting if it was done for fun. However, this is very likely unsuitable for real world usage. A couple of issues I could see with a quick glance:

- Using '=' for comparing TOTPs in the totp.verify function[1] is not safe from timing attacks.

- The function random() used in the totp.random_base32 function[2] is not a cryptographically secure random number generator.

[1] https://github.com/pyramation/totp/blob/7ec3104/packages/tot...

[2] https://github.com/pyramation/totp/blob/7ec3104/packages/tot...

namibj• 5 years ago

Yeah, timing attacks on string comparison are surprisingly easy, at least if the token/MAC has password-like entropy, not passphrase-like.

To clarify:

The boundary is somewhere around 70 bits where a significant financial incentive or considerable discretionary spending will be required to mount a successful attack.

pyramation14• 5 years ago

Thanks of the tips!

the random() seems easily addressable with pgcrypto, but do you have any information or practical examples of how a timing attack would be mitigated here? It seems that speakeasy (a JS lib) or any TOTP that uses '=' to compare would have this issue... what else are you supposed to do?

susam4• 5 years ago

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.

pyramation14• 5 years ago

I'm not an expert in timing attacks, but isn't this if we were doing equality on something like the secret? Not the TOTP value (which changes every interval)?

susam4• 5 years ago

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.

mattowen_uk• 5 years ago

I can't be the only [UK] person who sees 'TOTP' and immediately thinks 'Top of the Pops'! XD

darkr• 5 years ago

nice to see sqitch[1] in use here

1: https://sqitch.org/

pyramation14• 5 years ago

yea! I LOVE sqitch. As a person who likes to write pure sql with no ORM, sqitch is the absolute best choice

steve-chavez• 5 years ago

Cool! I remember seeing a pg TOTP implementation in this gist[1] before. Seems this extension was based off that?

[1] https://gist.github.com/bwbroersma/676d0de32263ed554584ab132...

pyramation14• 5 years ago

https://github.com/pyramation/totp/blob/master/packages/totp... yes in the notes of the source here

The first TOTP implementation I wrote was here was much less efficient, literally the algorithm in steps: https://github.com/pyramation/learn-totp/blob/master/package...

That gist is essentially the pure TOTP algo, but it was missing what we use in industry practice that the RFC was missing... base32 encode/decode.

So I implemented a base32 encode/decode so that the TOTP algo actually works with google authenticator and authy https://github.com/pyramation/totp/blob/master/extensions/%4...

When I found the gist, while the original code worked, the gist was much smaller (used more efficient bitwise operations) and the gist author I collaborated briefly and decided to combine the gist and the code for OSS

RFC6238 TOTP implementation in pure PostgreSQL

Related Stories