I wanted to do digital signatures validation, preferably ed25519, inside PostgreSQL triggers. Here is how it went:
Surely pgcrypto must be supporting it, right? Most Postgres cloud hosting providers already support pgcrypto so this would be perfect. Right?
Well, pgcrypto only supports PGP and that too excludes digital signatures. Let's give PGP a try anyway and see how far can we go.
Installed gpg to generate the keys and the experience is less than pleasant. Sometimes it gets stuck at the passphrase prompt. The keys are too big, but still I can make pgcrypto's pgp_pub_encrypt and pgp_pub_decrypt methods work. Just remeber to convert keys in ASCII to binary and vice-versa using armor()/dearmor(). I hate the big key size in RSA, even though GPG defaults to 2048-bit keys and not the more secure 4096-bit ones. Let's look into ed25519 now.
No problem, Postgres supports PL/Python and PL/v8 so it should be easy to code these functions quickly using existing libraries. Turns out, no cloud hosting provider actually supports PL/Python because it doesn't have sandboxing and is therefore an "untrusted" extension. I'd really like to avoid running my own database server so let's give up on PL/Python and check out PL/v8 instead.
Found TweetNaCl.js (https://tweetnacl.js.org/) which implements ed25519. But since PL/v8 is sandboxed, I can't just require an NPM module within a PostgreSQL function. Thankfully, someone on the Web (https://rymc.io/2016/03/22/a-deep-dive-into-plv8/) figured out how to cobble together a module system for PLv8, compatible with NPM. You, sir, are a life-saver!
Coded up the wrapper methods but it fails due to Uint8Array being not supported. What's going on?
Turns out, the version of PLv8 shipping everywhere (Ubuntu, Mac, AWS, Heroku) is an outdated one which doesn't support Uint8Arrays which libraries like TweetNaCl.js need. Google's CloudSQL doesn't even offer PL/v8 in the first place. Even Debian's seems to be an unmaintained one.
Found some custom libv8 PPA for Ubuntu which has an updated version. Oops.. it's not really meant as a replacement for system V8 and actually goes in /opt instead of /usr/include. Would be too much effort to build PLv8 with this.
Tried compiling V8 on my own, but no, Google has started using this fancy build system called GN which doesn't quite work on my machine. After 30 minutes of downloading and compiling this big project, I am left with one error message where googling isn't of much help. Their issue-tracker feels very crowded and no hope that somebody will actually respond. Idea: I will just use the V8 that ships with NodeJS!
No dice, as they have stopped exposing their internal dependency. Gaah.. PL/V8 seems to be a dead-end. Let me go back to PL/Python. Hmm.. I will have to run my own Postgres. Let me spin up an instance on Linode.
Wow, can't use Linode site with uBlock enabled. Giving them a chance by disabling it. But damn.. not able to ping or connect to the machine. No problem, let me check out Hetzner. I hear they have dropped their prices recently!
Oh, they want to see a picture of my ID proof and credit card. Fuck that shit! Let me try Docker then! Some guy on the Internet has a Dockerfile which includes PL/Python. I will just modify the Dockerfile, include pip install ed25519, and publish on DockerHub so that I can deploy it on Hyper.sh.
Hyper.sh offers a Postgres dockerfile but has no documentation on how to actually access it. What's the hostname or credentials? Spend some time in their docs. hyper info doesn't help much. Oh, I'm supposed to attach a floating IP to the container. Screw it.. let me first make the whole thing work in Docker running locally.
Oops, Docker doesn't let me just publish the modified Dockerfile. It wants me to build the image locally and THEN push to Hub. Fine. I'll set up Docker locally. Wow, this thing takes a long time to start up!
Changed the Dockerfile and running docker build. Took me a while to figure out how to reset postgres password via environment variables and exposing the port outside so that I can access via psql. I can create extension and define wrapper methods now. Wait, need to double check how to correctly map return types from Python code to PL/sql. I can generate the keypair, but loading a SigningKey gives AssertionError. Let me compare with Postgres.app.
Oh it works fine in my local Postgres install. What's going on? Wait.. I have Python 2.7 on my machine but the Docker container is using Python 3. Also, I mistakenly used pip instead of pip3. Wait, it looks like ed25519 install requires "Python.h" but then why didn't I see an error? Anyway, let me include python-dev in Dockerfile. Or should it be python3-dev...
FUCK. THIS. SHIT. I'm going to bed.
Oh man, I can relate. That's one reason why I started using a Docker for a lot of my development as of several months ago. Once you get over the initial hump of things like "use environment variables to configure Docker services" and "gotta use pip3 instead of pip for Python 3", you'll find that it becomes much easier. I'm pretty much at the point of using Docker for all of my production work at this point.
So try to stick with Docker (and you'll also want to learn Docker Compose, because it's easier than using --link between two containers) and you should have an easier time with challenges like these.
Feel free to hit me up with any Docker questions, BTW. I'm a pretty big fan of it. :-)