PostgreSQL encryption to Node.js decryption

Question

In Postgres, I am using:

encrypt('text', 'mykey', 'aes')

https://www.postgresql.org/docs/8.3/pgcrypto.html - F.20.4. Raw encryption functions

I assume this is done using the defaults of AES-128 algorithm, CBC mode.

It appears to be stored as BINARY (16 bytes) in the database and comes back as a base64? encoded string when I request the column on the server with my ORM.

In Node.js, assuming I have 'mykey', how do I convert this value back to its plaintext using crypto or crypto-js libraries?

By using an AES-128, CBC algorithm in Node (give or take some base64 encoding/decoding)? — Robert Harvey, Feb 13 '20 at 17:49
PostgreSQL doesn't offer a function names "encrypt" taking two arguments. Where did you find it? — jjanes, Feb 13 '20 at 19:43
@RobertHarvey Yes, but I was unable to get it to work with the libraries. I could use a basic, working example showing the decryption. — atkayla, Feb 13 '20 at 20:55

score 0 · Answer 1 · answered Feb 13 '20 at 23:53

0

Could you use PostgreSQL's Pgp_sym_encrypt() instead, and then use it with https://www.npmjs.com/package/openpgp to decode? PostgreSQL's raw encryption functions are not really suitable for compatible use with external systems (and really, not suitable for use at all)

answered Feb 13 '20 at 23:53

jjanes

37,812
5
27
34

true. pgp_sym_encrypt() is preferred. PostgreSQL's raw encryption functions lack of clear documentation. I left the details in my comments in https://stackoverflow.com/a/63095117/709065, hopefully it helps save time for other folks. – lovelywib Jul 26 '20 at 01:11

PostgreSQL encryption to Node.js decryption

1 Answers1