Postgres与Node.js/Sequelize跨层加密解密互通问题求助

Your core issue boils down to mismatched encryption parameters between PostgreSQL and Node.js—even when using AES, differences in mode, padding, IV handling, or encoding will break cross-layer compatibility. Below are two clean solutions that eliminate redundant code and enable bidirectional decryption:

Solution 1: Standardize on AES-CBC Mode (Recommended for Full Interoperability) #

AES-CBC is supported natively by both PostgreSQL's pgcrypto extension and Node.js's crypto/crypto-js libraries. The key is aligning every parameter on both sides.

Node.js Implementation (Crypto/CryptoJS) #

We'll store the random IV (required for CBC) alongside the ciphertext (encoded as base64 for easy text storage in PostgreSQL):

Using Node's Native crypto Module

const crypto = require('crypto');
const AES_KEY = Buffer.from('thirtytwocharsthirtytwocharsplus', 'utf8'); // 32 bytes = AES-256
const AES_IV_LENGTH = 16; // Fixed for CBC mode

// Encrypt function
function encrypt(text) {
  const iv = crypto.randomBytes(AES_IV_LENGTH);
  const cipher = crypto.createCipheriv('aes-256-cbc', AES_KEY, iv);
  let encrypted = cipher.update(text, 'utf8', 'binary');
  encrypted += cipher.final('binary');
  // Concatenate IV + ciphertext, encode to base64
  return Buffer.concat([iv, Buffer.from(encrypted, 'binary')]).toString('base64');
}

// Decrypt function
function decrypt(encryptedText) {
  const buffer = Buffer.from(encryptedText, 'base64');
  const iv = buffer.slice(0, AES_IV_LENGTH);
  const ciphertext = buffer.slice(AES_IV_LENGTH);
  const decipher = crypto.createDecipheriv('aes-256-cbc', AES_KEY, iv);
  let decrypted = decipher.update(ciphertext, 'binary', 'utf8');
  decrypted += decipher.final('utf8');
  return decrypted;
}

Using CryptoJS

const CryptoJS = require('crypto-js');
const AES_KEY = CryptoJS.enc.Utf8.parse('thirtytwocharsthirtytwocharsplus');

// Encrypt function
function encrypt(text) {
  const iv = CryptoJS.lib.WordArray.random(16);
  const encrypted = CryptoJS.AES.encrypt(text, AES_KEY, {
    iv: iv,
    mode: CryptoJS.mode.CBC,
    padding: CryptoJS.pad.Pkcs7 // Matches PostgreSQL's default padding
  });
  // Concatenate IV + ciphertext, encode to base64
  return iv.concat(encrypted.ciphertext).toString(CryptoJS.enc.Base64);
}

// Decrypt function
function decrypt(encryptedText) {
  const buffer = CryptoJS.enc.Base64.parse(encryptedText);
  const iv = buffer.slice(0, 16);
  const ciphertext = buffer.slice(16);
  const decrypted = CryptoJS.AES.decrypt(
    { ciphertext: ciphertext },
    AES_KEY,
    { iv: iv, mode: CryptoJS.mode.CBC, padding: CryptoJS.pad.Pkcs7 }
  );
  return decrypted.toString(CryptoJS.enc.Utf8);
}

PostgreSQL Corresponding Functions #

First, ensure the pgcrypto extension is enabled:

CREATE EXTENSION IF NOT EXISTS pgcrypto;

Decrypt Node.js-Encrypted Data

SELECT 
  convert_from(
    decrypt(
      decode(field, 'base64'), -- Convert base64 string to bytea
      'thirtytwocharsthirtytwocharsplus'::bytea,
      'aes-cbc/pkcs7' -- Match Node.js's mode and padding
    ),
    'utf8'
  ) AS decrypted_field
FROM your_table;

Encrypt Data in PostgreSQL (Node.js-Compatible)

SELECT 
  encode(
    encrypt(
      'test'::bytea, -- Text to encrypt (cast to bytea)
      'thirtytwocharsthirtytwocharsplus'::bytea,
      'aes-cbc/pkcs7',
      gen_random_bytes(16) -- Generate random IV (matches Node.js)
    ),
    'base64'
  ) AS encrypted_field;

Solution 2: Automate PGP_SYM_ENCRYPT/DECRYPT with Sequelize #

If you prefer using PostgreSQL's PGP_SYM_ENCRYPT, you can avoid redundant code with Sequelize scopes and hooks:

Auto-Decrypt with Default Scopes #

Define a default scope to automatically return decrypted values in queries:

const YourModel = sequelize.define('your_table', {
  field: {
    type: DataTypes.TEXT,
    allowNull: false
  }
}, {
  defaultScope: {
    attributes: {
      include: [
        [sequelize.fn('PGP_SYM_DECRYPT', sequelize.col('field'), config.AES_KEY), 'decryptedField']
      ]
    }
  }
});

Now YourModel.findAll() will automatically include the decryptedField without manual query tweaks.

Auto-Encrypt with Hooks #

Add hooks to encrypt fields on create/update:

YourModel.beforeCreate((instance) => {
  instance.field = sequelize.cast(sequelize.fn('PGP_SYM_ENCRYPT', instance.field, config.AES_KEY), 'text');
});

YourModel.beforeUpdate((instance) => {
  if (instance.changed('field')) {
    instance.field = sequelize.cast(sequelize.fn('PGP_SYM_ENCRYPT', instance.field, config.AES_KEY), 'text');
  }
});

Why Your Previous Attempts Failed #

• PGP_SYM_ENCRYPT + Node Crypto: PGP_SYM_ENCRYPT uses OpenPGP-formatted output (with metadata like algorithm IDs and checksums), not raw AES. Node's createDecipher expects plain AES, hence the garbled output.
• CryptoJS + PostgreSQL DECRYPT: PostgreSQL's DECRYPT defaults to AES-ECB mode (no IV), while CryptoJS uses AES-CBC with a random IV. The parameter mismatch caused errors, and online tools worked because they parse CryptoJS's IV-included format automatically.

内容的提问来源于stack exchange，提问作者CJ Thompson