7 ms·

RFC6238 TOTP implementation in pure PostgreSQL

68 points by pyramation 6y ago

potatochup 6y ago
Can someone explain where/why this might be used? Or is it just for fun?
- statenjason 6y ago
  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/ https://github.com/hasura/graphql-engine/ [2]: https://www.graphile.org/postgraphile/ https://www.graphile.org/postgraphile/ [3]: https://postgrest.org https://postgrest.org
  - smallnamespace 6y 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 https://www.youtube.com/watch?v=XDOrhTXd4pE
    - pyramation 6y ago
      exactly! this is the ecosystem that I'm a part of that inspired me to build this :) I do agree about making sure you have experience writing PL/pgSQL and would also add that you should make sure you have a good test-driven environment when writing code in the db.
    - e12e 6y ago
      > Like, if you decide to do auth and TOTP in the DB, you'll end up implementing it in PL/pgSQL. Not really? Postgres (as well as other rdbms) support many other languages - I don't see many good reasons to insist on pl/pgSQL for uses like these? Both python, perl and TCL are part of the standard distribution in addition to pl/pgSQL. https://www.postgresql.org/docs/13/xplang.html https://www.postgresql.org/docs/13/xplang.html https://www.postgresql.org/docs/13/external-pl.html https://www.postgresql.org/docs/13/external-pl.html
      - smallnamespace 6y ago
        Right, except if my backend is in JS, there's a cognitive cost to adding another language to the stack. I could use plv8, but there's a more subtle preference here for JS to wrap SQL, rather than the reverse (plus tooling is less convenient, e.g. if you're writing Typescript). My rule of thumb is: SQL great, PL not so much.
        e12e 6y ago
        > except if my backend is in JS, there's a cognitive cost to adding another language to the stack. Agreed. However, I don't see why not use one of the other mature "real" extension languages other than pl/pgSQL. As for typescript, maybe there's (distant) hope: https://github.com/supabase/postgres-deno https://github.com/supabase/postgres-deno
  - pyramation 6y ago
    exactly! https://www.graphile.org/postgraphile/ https://www.graphile.org/postgraphile/ is the system I'm using on and wanted to avoid writing a resolver in JS
- rbut 6y ago
  Incrementing the counter, and generating the next HOTP code in a single UPDATE statement could be useful.
- pyramation 6y 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.
  - e12e 6y ago
    On the other hand, one could also write js in postgres: https://www.postgresql.org/docs/13/external-pl.html https://www.postgresql.org/docs/13/external-pl.html https://github.com/plv8/plv8 https://github.com/plv8/plv8
    - pyramation 6y ago
      yea totally! I love plv8... it's actually how I got started with postgres functions. I ultimately switched to native PG. There were known memory leaks in plv8 and eventually after learning pl/pgsql it became more natural and clean, plus AWS at the time didn't support plv8 which was another incentive not to use language extensions
nayuki 6y ago
The author's main SQL code seems to be in this file: https://github.com/pyramation/totp/blob/master/packages/totp/deploy/schemas/totp/procedures/generate_totp.sql 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-tools https://www.nayuki.io/page/time-based-one-time-password-tool... . I even have a 6-line Python function.
- pyramation 6y ago
  The file you're pointing to is not the full extension, here it is: https://github.com/pyramation/totp/blob/master/packages/totp/sql/launchql-totp--0.0.3.sql https://github.com/pyramation/totp/blob/master/packages/totp...
- vletal 6y ago
  Cool. If I had to choose I'd wrap the simple py implementation in a PL/Python UDF.
  - scrollaway 6y 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.
    - Tostino 6y ago
      I mean, why though? I haven't played around much with other languages within Postgres, and mainly stick to pl/pgsql, so real question. Is it performance related? Not requiring another language to be installed?
      - scrollaway 6y ago
        Yes and yes; all other things equal, performance will be a lot worse when using Python. And you'll need Python installed.
pyramation 6y ago
Author here. Here is the full code if anyone is interested: https://github.com/pyramation/totp/blob/master/packages/totp/sql/launchql-totp--0.0.3.sql https://github.com/pyramation/totp/blob/master/packages/totp...
susam 6y 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/totp/sql/launchql-totp--0.0.3.sql#L111 https://github.com/pyramation/totp/blob/7ec3104/packages/tot... [2]: https://github.com/pyramation/totp/blob/7ec3104/packages/totp/sql/launchql-totp--0.0.3.sql#L121 https://github.com/pyramation/totp/blob/7ec3104/packages/tot...
- namibj 6y 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.
- pyramation 6y 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?
  - susam 6y 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 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.
mattowen_uk 6y ago
I can't be the only [UK] person who sees 'TOTP' and immediately thinks 'Top of the Pops'! XD
darkr 6y ago
nice to see sqitch[1] in use here 1: https://sqitch.org/ https://sqitch.org/
- pyramation 6y 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 6y 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/676d0de32263ed554584ab132434ebd9 https://gist.github.com/bwbroersma/676d0de32263ed554584ab132...
- pyramation 6y ago
  https://github.com/pyramation/totp/blob/master/packages/totp/deploy/schemas/totp/procedures/generate_totp.sql#L10 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/packages/totp/sql/launchql-totp--0.0.1.sql 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/%40launchql/base32/sql/launchql-base32--0.0.3.sql 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

68 points by pyramation 6y ago

potatochup 6y ago

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

statenjason 6y ago
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/ https://github.com/hasura/graphql-engine/ [2]: https://www.graphile.org/postgraphile/ https://www.graphile.org/postgraphile/ [3]: https://postgrest.org https://postgrest.org
- smallnamespace 6y 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 https://www.youtube.com/watch?v=XDOrhTXd4pE
  - pyramation 6y ago
    exactly! this is the ecosystem that I'm a part of that inspired me to build this :) I do agree about making sure you have experience writing PL/pgSQL and would also add that you should make sure you have a good test-driven environment when writing code in the db.
  - e12e 6y ago
    > Like, if you decide to do auth and TOTP in the DB, you'll end up implementing it in PL/pgSQL. Not really? Postgres (as well as other rdbms) support many other languages - I don't see many good reasons to insist on pl/pgSQL for uses like these? Both python, perl and TCL are part of the standard distribution in addition to pl/pgSQL. https://www.postgresql.org/docs/13/xplang.html https://www.postgresql.org/docs/13/xplang.html https://www.postgresql.org/docs/13/external-pl.html https://www.postgresql.org/docs/13/external-pl.html
    - smallnamespace 6y ago
      Right, except if my backend is in JS, there's a cognitive cost to adding another language to the stack. I could use plv8, but there's a more subtle preference here for JS to wrap SQL, rather than the reverse (plus tooling is less convenient, e.g. if you're writing Typescript). My rule of thumb is: SQL great, PL not so much.
      - e12e 6y ago
        > except if my backend is in JS, there's a cognitive cost to adding another language to the stack. Agreed. However, I don't see why not use one of the other mature "real" extension languages other than pl/pgSQL. As for typescript, maybe there's (distant) hope: https://github.com/supabase/postgres-deno https://github.com/supabase/postgres-deno
- pyramation 6y ago
  exactly! https://www.graphile.org/postgraphile/ https://www.graphile.org/postgraphile/ is the system I'm using on and wanted to avoid writing a resolver in JS
rbut 6y ago
Incrementing the counter, and generating the next HOTP code in a single UPDATE statement could be useful.
pyramation 6y 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.
- e12e 6y ago
  On the other hand, one could also write js in postgres: https://www.postgresql.org/docs/13/external-pl.html https://www.postgresql.org/docs/13/external-pl.html https://github.com/plv8/plv8 https://github.com/plv8/plv8
  - pyramation 6y ago
    yea totally! I love plv8... it's actually how I got started with postgres functions. I ultimately switched to native PG. There were known memory leaks in plv8 and eventually after learning pl/pgsql it became more natural and clean, plus AWS at the time didn't support plv8 which was another incentive not to use language extensions

nayuki 6y ago

The author's main SQL code seems to be in this file: https://github.com/pyramation/totp/blob/master/packages/totp/deploy/schemas/totp/procedures/generate_totp.sql 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-tools https://www.nayuki.io/page/time-based-one-time-password-tool... . I even have a 6-line Python function.

pyramation 6y ago
The file you're pointing to is not the full extension, here it is: https://github.com/pyramation/totp/blob/master/packages/totp/sql/launchql-totp--0.0.3.sql https://github.com/pyramation/totp/blob/master/packages/totp...
vletal 6y ago
Cool. If I had to choose I'd wrap the simple py implementation in a PL/Python UDF.
- scrollaway 6y 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.
  - Tostino 6y ago
    I mean, why though? I haven't played around much with other languages within Postgres, and mainly stick to pl/pgsql, so real question. Is it performance related? Not requiring another language to be installed?
    - scrollaway 6y ago
      Yes and yes; all other things equal, performance will be a lot worse when using Python. And you'll need Python installed.

pyramation 6y ago

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

susam 6y 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/totp/sql/launchql-totp--0.0.3.sql#L111 https://github.com/pyramation/totp/blob/7ec3104/packages/tot... [2]: https://github.com/pyramation/totp/blob/7ec3104/packages/totp/sql/launchql-totp--0.0.3.sql#L121 https://github.com/pyramation/totp/blob/7ec3104/packages/tot...

namibj 6y 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.
pyramation 6y 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?
- susam 6y 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 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.

mattowen_uk 6y ago

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

darkr 6y ago

nice to see sqitch[1] in use here 1: https://sqitch.org/ https://sqitch.org/

pyramation 6y 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 6y 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/676d0de32263ed554584ab132434ebd9 https://gist.github.com/bwbroersma/676d0de32263ed554584ab132...

pyramation 6y ago
https://github.com/pyramation/totp/blob/master/packages/totp/deploy/schemas/totp/procedures/generate_totp.sql#L10 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/packages/totp/sql/launchql-totp--0.0.1.sql 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/%40launchql/base32/sql/launchql-base32--0.0.3.sql 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