pglite icon indicating copy to clipboard operation
pglite copied to clipboard
verified publisher icon electric-sql

pgcrypto PoC

Open tdrz opened this issue 1 month ago • 16 comments

Here is a PoC for pgcrypto!

First of all it needs proper testing (see packages/pglite/contrib/pgcrypto.test.js) since I only wrote a loading + crypt + gen_salt without actually verifying anything.

Second, it is unlikely that we will merge this PR as is. This is because it increases the pglite.wasm size by linking libssl and libcrypto, which are only needed for this extension.

A improved version would link libssl and libcrypto only against this library (pgcrypto.so), if this is even possible. This is a version that we might release.

tdrz avatar Nov 01 '25 17:11 tdrz

  • PGlite: https://github.com/electric-sql/pglite/actions/runs/19000500241/artifacts/4438608149
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/19141085303/artifacts/4488991727
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/19141085303/artifacts/4489006951
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/19141085303/artifacts/4489012519
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/19141085303/artifacts/4489023180
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/19141085303/artifacts/4489167759
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/19142893587/artifacts/4489769001
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/19142893587/artifacts/4489775471
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/19142893587/artifacts/4489776874
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/19142893587/artifacts/4489797617
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/19142893587/artifacts/4489831217
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/19213576019/artifacts/4513270792
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/19213576019/artifacts/4513274956
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/19213576019/artifacts/4513275400
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/19213576019/artifacts/4513275836
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/19213576019/artifacts/4513278033
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/19214158020/artifacts/4513458853
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/19214158020/artifacts/4513461507
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/19214158020/artifacts/4513462992
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/19214158020/artifacts/4513465057
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/19214158020/artifacts/4513469103
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/19214416483/artifacts/4513546484
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/19214416483/artifacts/4513547437
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/19214416483/artifacts/4513550172
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/19214416483/artifacts/4513553949
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/19214416483/artifacts/4513557324
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/19214881970/artifacts/4513669311
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/19214881970/artifacts/4513669713
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/19214881970/artifacts/4513671477
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/19214881970/artifacts/4513673169
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/19214881970/artifacts/4513677063
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/19244984665/artifacts/4524308834
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/19244984665/artifacts/4524314722
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/19244984665/artifacts/4524317032
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/19244984665/artifacts/4524380508
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/19244984665/artifacts/4524392896
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/19245753417/artifacts/4524600672
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/19245753417/artifacts/4524610810
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/19245753417/artifacts/4524611271
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/19245753417/artifacts/4524617072
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/19245753417/artifacts/4524645566
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/20096694601/artifacts/4823307766
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/20096694601/artifacts/4823310679
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/20096694601/artifacts/4823322870
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/20096694601/artifacts/4823328409
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/20096694601/artifacts/4823329023
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/20113097068/artifacts/4830113107
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/20113097068/artifacts/4830122308
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/20113097068/artifacts/4830127924
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/20113097068/artifacts/4830129047
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/20113097068/artifacts/4830135777
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/20113560053/artifacts/4830302647
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/20113560053/artifacts/4830305083
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/20113560053/artifacts/4830317922
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/20113560053/artifacts/4830320765
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/20113560053/artifacts/4830413264
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/20126113430/artifacts/4834747052
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/20126113430/artifacts/4834747328
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/20126113430/artifacts/4834751191
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/20126113430/artifacts/4834754927
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/20126113430/artifacts/4834761351
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/20126743409/artifacts/4834965464
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/20126743409/artifacts/4834966715
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/20126743409/artifacts/4834970781
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/20126743409/artifacts/4834973631
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/20126743409/artifacts/4834973872
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/20127022435/artifacts/4835076364
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/20127022435/artifacts/4835083441
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/20127022435/artifacts/4835084652
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/20127022435/artifacts/4835084768
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/20127022435/artifacts/4835085054
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/20127022435/artifacts/4848351795
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/20127022435/artifacts/4848353057
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/20127022435/artifacts/4848358659
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/20127022435/artifacts/4848359863
  • PGlite: https://github.com/electric-sql/pglite/actions/runs/20127022435/artifacts/4848360022

github-actions[bot] avatar Nov 01 '25 18:11 github-actions[bot]

  • PGlite: https://github.com/electric-sql/pglite/actions/runs/19000500241/artifacts/4438608561

github-actions[bot] avatar Nov 01 '25 18:11 github-actions[bot]

  • PGlite: https://github.com/electric-sql/pglite/actions/runs/19000500241/artifacts/4438610851

github-actions[bot] avatar Nov 01 '25 18:11 github-actions[bot]

thanks. hoping this will be released too..

ctzurcanu avatar Nov 01 '25 19:11 ctzurcanu

thanks. hoping this will be released too..

We are looking for help with this one as we're busy with other things at the moment. The bulk of the work has been done, there are a couple of tests failing which are a good starting point if anyone is willing to put effort into it.

tdrz avatar Nov 04 '25 19:11 tdrz

Tests in tests/contrib/pgcrypto.test.js pass in https://github.com/loredanacirstea/pglite/tree/tdrz/fe-try-pgcrypto I created some WASM import shims that pgcrypto tries to import during init.

loredanacirstea avatar Nov 05 '25 21:11 loredanacirstea

Tests in tests/contrib/pgcrypto.test.js pass in https://github.com/loredanacirstea/pglite/tree/tdrz/fe-try-pgcrypto I created some WASM import shims that pgcrypto tries to import during init.

Nice, thank you for this!

that pgcrypto tries to import during init.

How did you find out which functions is pgcrypto is trying to import?

tdrz avatar Nov 06 '25 09:11 tdrz

@tdrz

How did you find out which functions is pgcrypto is trying to import?

mkdir -p /tmp/pgcrypto-inspect
tar -xzf ./packages/pglite/release/pgcrypto.tar.gz -C /tmp/pgcrypto-inspect ./lib/postgresql/pgcrypto.so
wasm2wat --enable-all /tmp/pgcrypto-inspect/lib/postgresql/pgcrypto.so > /tmp/pgcrypto-inspect/pgcrypto.wat
grep -E '^\s*\(import' /tmp/pgcrypto-inspect/pgcrypto.wat

GOT.* imports for OpenSSL I understand are from the dynamic loader. The env.* imports are expected to be provided by host.

loredanacirstea avatar Nov 06 '25 13:11 loredanacirstea

@tdrz if you consider that @loredanacirstea help was not trivial: you will let her know what you need from her version to accept her eventual PR - for some official recognition

to encourage cooperation

ctzurcanu avatar Nov 06 '25 15:11 ctzurcanu

@tdrz if you consider that @loredanacirstea help was not trivial: you will let her know what you need from her version to accept her eventual PR - for some official recognition

to encourage cooperation

I will take full recognition, delete all the messages here and conquer the world!

Of course I will, just putting some things together before I know what's next for this.

tdrz avatar Nov 06 '25 16:11 tdrz

  • Demos: https://github.com/electric-sql/pglite/actions/runs/19141085303/artifacts/4489246623

github-actions[bot] avatar Nov 06 '25 16:11 github-actions[bot]

🚀 Deployed on https://690cd8f1824caca4fac9c603--pglite.netlify.app

github-actions[bot] avatar Nov 06 '25 16:11 github-actions[bot]

@loredanacirstea Your contribution makes pgcrypto work with PGlite! Thank you!

Using your code, I took a different route of:

  1. Disabling threads in all dependencies (see postgres-pglite/pglite-wasm/builder/Dockerfile
  2. Adding the missing includes to postgres-pglite/pglite-wasm/included.pglite.imports

We really appreciate your approach to this and might use its learnings to improve PGlite!

As stated before, it is unlikely we will merge this as-is because it increases the size of the binary by ~30% (mainly because of the libssl/libcrypto linking). Ideally libssl/libcrypto would only be linked to pgcrypto extension since, at least for the time being, it is the only one that needs it.

tdrz avatar Nov 06 '25 16:11 tdrz

  • Demos: https://github.com/electric-sql/pglite/actions/runs/19142893587/artifacts/4489907129

github-actions[bot] avatar Nov 06 '25 17:11 github-actions[bot]

  • Demos: https://github.com/electric-sql/pglite/actions/runs/19142893587/artifacts/4490035204

github-actions[bot] avatar Nov 06 '25 17:11 github-actions[bot]

Update 2025.11.17:

  1. I've linked libssl/libcrypto statically directly to the pgcrypto extension, but some of the tests are failing.
  2. I am doubting that this is the right approach tough. I believe building libssl/libcrypto as shared libs (SHARED_MODULEs to be precise) and using those would be a more sane approach.
  3. I am busy with lots of other things but still would like to see this over the finish line at some moment. If anyone is interested in having a go, I would really appreciate it.

tdrz avatar Nov 17 '25 13:11 tdrz