pyramation

thanks for sharing! author of pgpm here, we are super excited about pushing e2e and integration testing in the postgres community, a big part of the modular postgres workflows in addition to the modules/packages.

We'll be continuing to improve and work on the developer experience for postgres and building applications, thanks again!

thank you! let us know if you need help setting it up!

appreciate it! let us know if you and your team have any feedback, cheers!!!

thank you! much appreciated ;) hope it helps you guys ship secure supabase projects!

thank you! Appreciate that.

Always want to make the glide path and ensure folks can run the software replicably on their machines!

happy to help if you run into any issues :)

and fwiw, my opinion comes from attempting to upgrade packages that I've depended on that all of a sudden add the module: true, which essentially requires that ALL packages must upgrade — which is 99% of the time not possible because all the other deps are usually CJS.

this is also similar to the non-incremental way TS rolled out in the early days, also causing knee-jerk reactions to TypeScript. However over time, devs found ways to incrementally type JS to TS, and so we've all mostly evolved by now.

If ESM could do similar, allowing for incremental evolution, then it would solve all of the problems. I don't see why cjs and esm cannot co-exist

ES modules are cancer. I'm fine w the syntax — in fact, I love the syntax.

But if you use "module: true" in your package.json, you are splitting the npm ecosystem into two groups, and it's not moving us forward.

My journey to upgrade pgsql-parser to TypeScript led to an exciting discovery in the pganalyze/libpg_query repository: protobuf files defining PostgreSQL's AST. This discovery inspired me to create a proto-to-TypeScript transpiler, streamlining the process of typing the pgsql-parser and unlocking other cool new features :)

The transpiler not only allowed me to add typing for pgsql-parser but also spawned a suite of TypeScript utilities that are invaluable for PostgreSQL development.

Explore the tools:

pg-proto-parser: https://github.com/launchql/pg-proto-parser pgsql-parser: https://github.com/launchql/pgsql-parser

Additionally, we now have new utilities generated by the proto parser, make sure to checkout the @pgsql/utils! That has some cool features in it:

@pgsql/enums: https://github.com/launchql/pgsql-parser/tree/main/packages/... @pgsql/types: https://github.com/launchql/pgsql-parser/tree/main/packages/... @pgsql/utils: https://github.com/launchql/pgsql-parser/tree/main/packages/...

Hope you enjoy! Please let me know anything you'd like to see!

An article about ASTs in postgres, featuring a postgres AST deparser written in pure pgsql

HI all! I wanted to share a useful tool that I built during the pandemic. One of my deepest projects I’ve created in postgres, and I’m excited to share it with the community and get ideas and feedback.

I do a lot of functional programming and needed dynamic SQL. My personal belief is that ORMs are the wrong interface to creating migrations, and I prefer a pg native, functional approach. This library was actually one of the base extensions of a larger set, that I used this to create higher level structures for creating migrations. So while this is “low-level” you can definitely create stored procedures that do make higher-level if you wish.

I hope some of you find this useful with your projects!

postgres-ast-deparser A pure plpgsql AST toolkit and deparser for PostgreSQL, which can be used to create ASTs and deparse them back into strings in native Postgres. https://github.com/pyramation/postgres-ast-deparser

wow, session replays looks pretty slick!

this library is amazing! I couldn't live without it.

for sure there are all sorts of postgres regex tools... You can likely do everything. The main limitations I found in plpgsql were in base encodings (that were missing), which is why I had to implement base32 from scratch. Besides that, it's pretty much a super powerful language

ok thank you! I just saw this. Adding a reference to the issue. Thanks ;)

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

Thanks for the in depth explanation. So it seems that, during the interval (which defaults to 30s) they could possibly determine the TOTP value.

It seems possible to brute force, however as soon as the interval changes, by default every 30 seconds, the TOTP is now new because time has passed.

But assuming they could do some crazy thing like attempt 10,000 times within that interval, I suppose it's arguable that it should still be secure...

So upon research looks like it's quite easily fixed by comparing all digits individually, and then aggregating if all all true, but continuing the iteration and checks even if a false value has been found. I suppose that would cover this case.

Thanks for pointing this out! Will be adding an issue ;)

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

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

agree, this is exactly why the code was written. I had originally tried to use plv8 or others, but as my postgres experience matured and I built testing harnesses, I decided to throw away any non-standard code (plv8, etc).

I think often times folks who come to postgres often don't know PL/pgsql (as I also didn't) and prefer to user their language of choice. I truly think if our postgres dev environments were better, we'd find more people writing code this way, so I can empathize with why people would prefer python or JS in the db, but at this stage I can't get myself to use those extensions.

FWIW, at one point, AWS didn't support a lot of these (sometimes unsafe) language extensions. So going pure PL/pgsql was the only true path forward. I think now for AWS users, they do support plv8 now, but as you pointed out somewhere in this thread, they are much slower... also I remember plv8 specifically had memory leaks.

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)?

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?

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

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

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

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

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.