Encrypt/Decrypt between Postgres and Node

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.

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";

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

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.

👍

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