Encrypt postgres data using nodejs/sequelize

Question

I've created a public key and stored it in my database. Now I'm trying to insert encrypted data that is encrypted using the public key.

This is what I am doing

  const createdTransaction = await this.model.create({
    organizationId,
    customerId,
    type: Sequelize.fn("pgp_pub_encrypt", type, publicKey)
  });

When I run this, I get this error

original: error: Wrong key or corrupt data
      at Connection.parseE ()
      at Connection.parseMessage ()

type has Sequelize type

  type: {
    type: Sequelize.BLOB(),
    required: true
  },

The public key is stored with type

Sequelize.BLOB('tiny')

And it is created like this

const { publicKey, privateKey } = crypto.generateKeyPairSync("rsa", {
  modulusLength: 4096,
  publicKeyEncoding: {
    type: "spki",
    format: "pem"
  },
  privateKeyEncoding: {
    type: "pkcs8",
    format: "pem",
    cipher: "aes-256-cbc",
    passphrase: password
  }
});

which is the library's way of storing something as bytea.

This is the SQL that is running

INSERT INTO "Transactions" ("id","organizationId","customerId","type","createdAt","updatedAt") VALUES ($1,$2,$3,pgp_pub_encrypt('PAYMENT', E'\\\\x2d2d2d2d2d424547494e205055424c4943204b45592d2d2d2d2d0a4d494943496a414e42676b71686b6947397730424151454641414f43416738414d49494343674b434167454130415a7667524d436e67585476303565597559740a715a732f7875693759566675325061546b623448646177464d2b596e3035564e6b647862305771687638306f5774672b592b4b2b4372726c3463624e56544f310a415058786c72724572423361586c594832646559676443693253346b3042567667356b372b2f5247654e78394f794a7949624d6f646e39526462372f4232656d0a7a57496a754941526a677270414f71786d4f753569504143697a796953706c7764327459394257554a4f63676969505764445a576b6d3349342b7279463270350a7158464c542b4f305632635539766e442b6530336c6d4c38567759747139626c58364b654139366f576a53396956354a30527a68356c6d702f4433376c4878570a6f496675365065693841335059474b6277753468694533674434672b734a77354765554f2b4c456a733242397634437a6f4577313435736e424350304b456a320a666a6e4f6872693132303572774a53723430794a4550505157596b51564f714f5867363664775969336130777847397a3665464477784e53726e65307258352b0a6e39786877766e4a2f3576424b58334e47554d68625a6b4d3852373575554e746b37655a453966566f66716673786d352b365357625457703542417445766b440a3655676c4d6d35466a613437776d346672385130307369562b78634a59596570676145326c784d556a3349543975593552314432417059616f504b475172712b0a6a6e42786f4542734573645a6765703058685a315065462b41727166786d30794654737570384c56394c6437533478364c5663667179626c79613466594250360a61616c6859383032695841634254644a4b4930654b7a77414662746358486365553350596c6a514d7552464b6e546449306b764b786d36797870734b727a61420a664a584f5a4c51516f72566a735a726a6c774136615655434177454141513d3d0a2d2d2d2d2d454e44205055424c4943204b45592d2d2d2d2d0a'),$4,$5) RETURNING *;,

I've also tried converting the public key into a binary string and into a utf string.

But the error persists.

It sounds like my data type for the public key is wrong - what should it be?

jjanes jjanes · Accepted Answer · 2019-09-29T15:34:37

If I do:

select encode(decode('2d2d2d2d2d424547494e205055424c4943204b45592d2d2d2d2d0a4d494943496a414e42676b71686b6947397730424151454641414f43416738414d49494343674b434167454130415a7667524d436e67585476303565597559740a715a732f7875693759566675325061546b623448646177464d2b596e3035564e6b647862305771687638306f5774672b592b4b2b4372726c3463624e56544f310a415058786c72724572423361586c594832646559676443693253346b3042567667356b372b2f5247654e78394f794a7949624d6f646e39526462372f4232656d0a7a57496a754941526a677270414f71786d4f753569504143697a796953706c7764327459394257554a4f63676969505764445a576b6d3349342b7279463270350a7158464c542b4f305632635539766e442b6530336c6d4c38567759747139626c58364b654139366f576a53396956354a30527a68356c6d702f4433376c4878570a6f496675365065693841335059474b6277753468694533674434672b734a77354765554f2b4c456a733242397634437a6f4577313435736e424350304b456a320a666a6e4f6872693132303572774a53723430794a4550505157596b51564f714f5867363664775969336130777847397a3665464477784e53726e65307258352b0a6e39786877766e4a2f3576424b58334e47554d68625a6b4d3852373575554e746b37655a453966566f66716673786d352b365357625457703542417445766b440a3655676c4d6d35466a613437776d346672385130307369562b78634a59596570676145326c784d556a3349543975593552314432417059616f504b475172712b0a6a6e42786f4542734573645a6765703058685a315065462b41727166786d30794654737570384c56394c6437533478364c5663667179626c79613466594250360a61616c6859383032695841634254644a4b4930654b7a77414662746358486365553350596c6a514d7552464b6e546449306b764b786d36797870734b727a61420a664a584f5a4c51516f72566a735a726a6c774136615655434177454141513d3d0a2d2d2d2d2d454e44205055424c4943204b45592d2d2d2d2d0a','hex'),'escape')

It looks like your publicKey was already given as ASCII armored text(although it doesn't like exactly valid) before you converted it to bytea. If you want to convert ASCII armored text to binary, you should use the pgp-specific "dearmor" function, not simply cast it to bytea.

Encrypt postgres data using nodejs/sequelize

1 Answers