Reputation: 2869

Encrypt/Decrypt between Postgres and Node

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.

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.

A few things I've tried so far (test key is thirtytwocharsthirtytwocharsplus):

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

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')

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)

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

Upvotes: 5

Answers (5)

sane5ek

Reputation: 21

Ended up with this solution.

Sequelize getter and setter calling decrypt and encrypt in JavaScript:

set(val) {
  const lowercased = val.toString().toLowerCase();
  this.setDataValue('email', encrypt(lowercased));
},
get() {
  const rawValue = this.getDataValue('email');
  return rawValue ? decrypt(rawValue) : '';
}

Encrypt function:

const crypto = require('crypto');

module.exports = (value) => {
  try {
    const iv = crypto.randomBytes(16);
    const key = crypto.createHash('sha256').update('YOUR_SECRET_KEY').digest('base64').substr(0, 32);
    const cipher = crypto.createCipheriv('aes-256-cbc', key, iv);

    let encrypted = cipher.update(value);
    encrypted = Buffer.concat([encrypted, cipher.final()]);
    return iv.toString('hex') + ':' + encrypted.toString('hex');
  } catch (e) {
    return value;
  }
};

Decrypt function:

const crypto = require('crypto');

module.exports = (value) => {
  try {
    const textParts = value.split(':');
    const iv = Buffer.from(textParts.shift(), 'hex');

    const encryptedData = Buffer.from(textParts.join(':'), 'hex');
    const key = crypto.createHash('sha256').update('YOUR_SECRET_KEY').digest('base64').substr(0, 32);
    const decipher = crypto.createDecipheriv('aes-256-cbc', key, iv);

    const decrypted = decipher.update(encryptedData);
    const decryptedText = Buffer.concat([decrypted, decipher.final()]);
    return decryptedText.toString();
  } catch (e) {
    return value;
  }
};

Then, in decided column will be encrypted value. To decrypt it with PostgreSQL, first create extension and SQL function:

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE OR REPLACE FUNCTION decrypt_aes256(encrypted_value TEXT, secret TEXT)
RETURNS TEXT AS $$
DECLARE
    iv BYTEA;
    encrypted BYTEA;
    decrypted BYTEA;
    key BYTEA;
BEGIN
    iv := decode(split_part(encrypted_value, ':', 1), 'hex');
    encrypted := decode(split_part(encrypted_value, ':', 2), 'hex');
    key := substring(encode(digest(secret, 'sha256'), 'base64')::text FROM 1 FOR 32);
    decrypted := decrypt_iv(encrypted, key, iv, 'aes-cbc/pad:pkcs');
    RETURN convert_from(decrypted, 'utf-8');
END;
$$ LANGUAGE plpgsql;

And then, in your queries:

SELECT decrypt_aes256("yourEncryptedColumnName", 'YOUR_SECRET_KEY')
FROM "YourTable";

Upvotes: 2

Govinda Kocharekar

Reputation: 11

For me, It worked with a similar approach suggested by KLPainter above https://stackoverflow.com/a/75861980/13757228

but a little bit of correction it goes as follows:


const key = "<your-encryption-key>";
const decrypt = async (hexFromPostgres) => {
  try {
      // issue was here it removes 3/4 chars instead of just 2 chars so I manually removed the '\x' and defined it as trimmedHex
      // const trimmedHex = hexFromPostgres.slice(hexPrefix.length);
      // remove `\x' from beginning

    // hexFromPostgres = "\x92eb5bf752d8dthisiissample";
    const trimmedHex = "92eb5bf752d8dthisiissample";

    const binaryToDecrypt = Uint8Array.from(Buffer.from(trimmedHex, "hex"));
    const encryptedMsg = await openpgp.readMessage({
      binaryMessage: binaryToDecrypt,
    });

    const decrypted = await openpgp.decrypt({
      message: encryptedMsg,
      passwords: [key], // here added key in array
      format: "binary", // defined the format as binary
    });

    const string = new TextDecoder().decode(decrypted.data);
    console.log(string);
    return string;
  } catch (e) {
    console.log("---error while decryption: ", e);
  }
  return `test`;
};

Hope this is helpful

Upvotes: -1

KLPainter

Reputation: 11

I spent so much time getting this to work, I figured I'd post my full typescript solution here and hopefully save others some time.

These functions encrypt and then decrypt strings that can be stored in postgres text or varchar fields.

import * as openpgp from 'openpgp';

const key = '<your-encryption-key>';
const hexPrefix = `${String.fromCharCode(92)}x`; // postgres needs a \x prefix

export async function encrypt(plainText: string): Promise<string> {
  const message = await openpgp.createMessage({ text: plainText });
  const encryptedBinary = await openpgp.encrypt({
    message,
    passwords: key,
    format: 'binary',
    config: {
      preferredSymmetricAlgorithm: openpgp.enums.symmetric.aes256,
      preferredCompressionAlgorithm: openpgp.enums.compression.zip,
    },
  });
  const encryptedHex = Buffer.from(encryptedBinary).toString('hex');
  return `${hexPrefix}${encryptedHex}`;
}

export async function decrypt(hexFromPostgres: string): Promise<string> {
  const trimmedHex = hexFromPostgres.slice(hexPrefix.length); // remove `\x' from beginning
  const binaryToDecrypt = Uint8Array.from(Buffer.from(trimmedHex, 'hex'));
  const decrypted = await openpgp.decrypt({
    message: await openpgp.readMessage({ binaryMessage: binaryToDecrypt }),
    passwords: key,
  });
  return decrypted.data;
}

The strings can be encrypted and decrypted in postgres with

pgp_sym_encrypt('message to encrypt', '<your-encryption-key', 'compress-algo=1, cipher-algo=aes256')

pgp_sym_decrypt(<column-name>::bytea, '<your-encryption-key')

Upvotes: 1

lovelywib

Reputation: 579

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.

Here is the program I used.

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());

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

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.

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

Upvotes: 3

CJ Thompson

Reputation: 2869

Ok, I got it working, hopefully properly

What I did was:

Encrypting with crypto.createCipheriv('aes-256-cbc', new Buffer(config.AES_KEY), iv) in node, encrypt_iv in pgsql and storing as hex in the db, and decrypting with crypto.createDecipheriv/decrypt_iv into text/utf8

I don't know what part I was missing, but between specifying aes256, using the iv methods, and flipping juggling hex/text it seems to be working.

👍

Upvotes: 0

Encrypt/Decrypt between Postgres and Node

Answers (5)

Related Questions