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
– Robert Harvey, Commented Feb 13, 2020 at 17:49
PostgreSQL doesn't offer a function names "encrypt" taking two arguments. Where did you find it? — jjanes
– jjanes, Commented Feb 13, 2020 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
– atkayla, Commented Feb 13, 2020 at 20:55

jjanes · Accepted Answer · 2020-02-13 23:53:50Z

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, 2020 at 23:53

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

lovelywib Over a year ago

true. pgp_sym_encrypt() is preferred. PostgreSQL's raw encryption functions lack of clear documentation. I left the details in my comments in stackoverflow.com/a/63095117/709065, hopefully it helps save time for other folks.

Collectives™ on Stack Overflow

PostgreSQL encryption to Node.js decryption

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related