在Postgres和Node之间进行加密/解密 [英] Encrypt/Decrypt between Postgres and Node

问题描述

问题:我们必须对特定表(Postgres)上的特定列进行加密.必须在我们的nodejs/sequelize应用程序层的SQL查询和中将其解密.加密可以在任一层中进行，但必须可以从任一层进行解码.

Problem: We have to encrypt a certain column on a certain table (Postgres). It has to be decryptable in SQL queries and in our nodejs/sequelize application layer. The encryption can happen in either layer, but it must be decodable from either.

我遇到的问题(我确定是用户错误)是，如果我在数据库中加密，我只能在数据库中解密，对于节点也是如此.

The issue I'm running into (and I'm sure it's user error) is that if I encrypt in the db I can only decrypt in the db, and the same for node.

我尝试在postgres中使用PGP_SYM_ENCRYPT和ENCRYPT，在节点中使用crypto和crypto-js/aes.我已经知道它解密时没有错误，但是返回乱码.

I've tried using PGP_SYM_ENCRYPT and ENCRYPT in postgres and crypto and crypto-js/aes in node. I've gotten it to the point where it's decrypting without an error, but returns gibberish.

到目前为止我已经尝试过的一些事情(测试键为thirtytwocharsthirtytwocharsplus):

set() {
  this.setDataValue('field', seq.cast(seq.fn('PGP_SYM_ENCRYPT', val, 
  config.AES_KEY), 'text'))
}

这样可以正确地写入字段，以便PGP_SYM_DECRYPT将其解密，但是(显然吗?)没有办法告诉Sequelize用函数调用包装字段名，所以我觉得很多额外的js是可以避免的

This properly writes the field such that PGP_SYM_DECRYPT will decrypt it, but there's (apparently?) no way to tell Sequelize to wrap the field name with a function call so it's a lot of extra js that I feel is avoidable

const decipher = crypto.createDecipher('aes256', config.AES_KEY)
decipher.setAutoPadding(false);
return decipher.update(new Buffer(this.getDataValue('field', 'binary'), 'binary', 'ascii')) + decipher.final('ascii')

这将解码该字段，但返回乱码(�Mq��8Ya�b)而不是值(test)

This will decode the field but returns gibberish (�Mq��8Ya�b) instead of the value (test)

aes.encrypt('test', config.AES_KEY)
aes.decrypt(field, config.AES_KEY).toString(CryptoJS.enc.Utf8)

这可以很好地加密，可以很好地解密，但是在尝试解密(使用PGP_SYM_DECRYPT或DECRYPT)时出现Postgres错误.将结果字段转换为::TEXT并将其粘贴到在线AES Decrypter中，将返回预期值.

This encrypts fine, decrypts fine, but Postgres errors when trying to decrypt (using either PGP_SYM_DECRYPT or DECRYPT). Casting the resulting field to ::TEXT and pasting it into an online AES Decrypter returns the expected value.

我真的想要避免在节点存储库/查询中添加一堆样板，我真的觉得这应该可行.使用相同的加密算法应产生相同的结果

I really want to avoid having to add a bunch of boilerplate to our node repositories/queries, and I really feel like this should work. Using the same crypto algorithm should yield the same results

任何微动或指针将不胜感激

Any nudge or pointer would be greatly appreciated

推荐答案

Postgres的原始加密功能文档非常不清楚.经过几次尝试和失败之后，我设法在nodejs中复制了大多数逻辑逻辑.

Postgres has rather unclear documentation about the raw encryption functions. After a few tries and failures, I managed to replicate most of logic logic in nodejs.

这是我使用的程序.

const crypto = require('crypto');

const iv = Buffer.alloc(16); // zeroed-out iv

function encrypt(plainText, algorithm, key) {
  const cipher = crypto.createCipheriv(algorithm, key, iv);
  let encrypted = cipher.update(plainText, 'utf8', 'base64');
  encrypted += cipher.final('base64');
  return encrypted;
}

function decrypt(encrypted, algorithm, key) {
  const decrypt = crypto.createDecipheriv(algorithm, key, iv);
  let text = decrypt.update(encrypted, 'base64', 'utf8');
  text += decrypt.final('utf8')
  return text;
}

const originalText = "hello world";
const userKey = 'abcd'
const algorithm = 'aes-128-cbc';

const paddedKey = Buffer.concat([Buffer.from(userKey), Buffer.alloc(12)]); // make it 128 bits key

const hw = encrypt(originalText, algorithm, paddedKey);
console.log("original", originalText);
console.log("encrypted:", hw);
console.log("decoded: ", decrypt(hw, algorithm, paddedKey).toString());

这也是一个列表，未列出有关postgres原始功能的内容:

Also here is a list of things not documented for the raw functions of postgres:

• 密钥将自动填充以匹配以下三种长度之一:128位，192位，256位
当密钥长度超过限制时，
• 算法将自动升级.例如如果密钥超过128位，则将使用aes-192-cbc进行加密
• 如果密钥超过256位，它将被截断为256位.

• the key will be auto padded to match one of the 3 lengths: 128 bits, 192 bits, 256 bits
• algorithm is automatically upgraded, when key length exceeds the limit. e.g. if key exceeds 128bits, aes-192-cbc will be used to encrypt
• if key exceeds 256 bits, it will be truncated to 256 bits.

如果Postgres拥有关于这些功能的适当文档，则使用应用程序语言(JavaScript或Java)复制它会更加容易.

It would be easier to replicate it in application language (either Javascript or Java), if Postgres has proper documentation of these functions.

这篇关于在Postgres和Node之间进行加密/解密的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！