Encrypt columns

Is there a recommended way to ensure sensitive data is encrypted-at-rest when building on Neon ?

11 Replies

genetic-orange•2y ago

Have you seen https://www.postgresql.org/docs/current/pgcrypto.html?

PostgreSQL Documentation

F.28. pgcrypto — cryptographic functions

F.28. pgcrypto — cryptographic functions # F.28.1. General Hashing Functions F.28.2. Password Hashing Functions F.28.3. PGP Encryption Functions F.28.4. Raw Encryption …

mute-gold•2y ago

@Woet , just seeing how you got on trying to implement this. I'll be looking into doing this across my tables in the next coming weeks and looking for any "gotchas"

exotic-emeraldOP•2y ago

@QuinTRON I ended up using the "crypto" package from Node to do this, so doing the encryption process on the server before storing it in the database and decrypting whenever I use it; feel free to use the functions I built for this if the same approach could work for you:

import crypto from "crypto";

export function encrypt(plainText: string) {
  const algorithm = "aes-256-ctr";
  const secretKey = Buffer.from(process.env.CRYPTO_MASTER_KEY, "hex");

  // Generate an initialization vector
  const iv = crypto.randomBytes(16);

  // create Cipher object
  const cipher = crypto.createCipheriv(algorithm, secretKey, iv);

  // encrypt the data
  let encryptedText = cipher.update(plainText, "utf-8", "hex");

  // finalize the encryption
  encryptedText += cipher.final("hex");

  // prepend the IV to the encrypted data
  return `${iv.toString("hex")}:${encryptedText}`;
}

export function decrypt(encryptedText: string) {
  const algorithm = "aes-256-ctr";
  const secretKey = Buffer.from(process.env.CRYPTO_MASTER_KEY, "hex");

  // extract the IV and the encrypted data
  const [ivHex, encryptedData] = encryptedText.split(":");
  const iv = Buffer.from(ivHex, "hex");

  // create Decipher object
  const decipher = crypto.createDecipheriv(algorithm, secretKey, iv);

  // decrypt the data
  let decryptedText = decipher.update(encryptedData, "hex", "utf-8");

  // finalize the decryption
  decryptedText += decipher.final("utf-8");

  return decryptedText;
}

import crypto from "crypto";

export function encrypt(plainText: string) {
  const algorithm = "aes-256-ctr";
  const secretKey = Buffer.from(process.env.CRYPTO_MASTER_KEY, "hex");

  // Generate an initialization vector
  const iv = crypto.randomBytes(16);

  // create Cipher object
  const cipher = crypto.createCipheriv(algorithm, secretKey, iv);

  // encrypt the data
  let encryptedText = cipher.update(plainText, "utf-8", "hex");

  // finalize the encryption
  encryptedText += cipher.final("hex");

  // prepend the IV to the encrypted data
  return `${iv.toString("hex")}:${encryptedText}`;
}

export function decrypt(encryptedText: string) {
  const algorithm = "aes-256-ctr";
  const secretKey = Buffer.from(process.env.CRYPTO_MASTER_KEY, "hex");

  // extract the IV and the encrypted data
  const [ivHex, encryptedData] = encryptedText.split(":");
  const iv = Buffer.from(ivHex, "hex");

  // create Decipher object
  const decipher = crypto.createDecipheriv(algorithm, secretKey, iv);

  // decrypt the data
  let decryptedText = decipher.update(encryptedData, "hex", "utf-8");

  // finalize the decryption
  decryptedText += decipher.final("utf-8");

  return decryptedText;
}

And this helper to generate the random MASTER_KEY:

const crypto = require("crypto");

// create random bytes
const randomBytes = crypto.randomBytes(32);

// convert to hex
const randomString = randomBytes.toString("hex");

console.log(randomString);

const crypto = require("crypto");

// create random bytes
const randomBytes = crypto.randomBytes(32);

// convert to hex
const randomString = randomBytes.toString("hex");

console.log(randomString);

sensitive-blue•2y ago

I would recommend using aes-256-gcm if possible, or preferably nacl secretbox to handle the code for you. It's worth noting that encrypting before sorting means you don't have access to more complex queries, but it might not be necessary. aes-256-ctr is unauthenticated encryption which can open yourself up to chosen ciphertext attacks if you don't include a MAC, which you are not doing here

sensitive-blue•2y ago

https://tonyarcieri.com/all-the-crypto-code-youve-ever-written-is-probably-broken

Tony Arcieri on Svbtle

All the crypto code you’ve ever written is probably broken

tl;dr: use authenticated encryption. use authenticated encryption. use authenticated encryption. use authenticated encryption. use authenticated encryption. use authenticated encryption. use authenticated encryption. use authenticated encryption....

exotic-emeraldOP•2y ago

Thanks for that feedback @Conrad Ludgate , I'm not really a cryptography expert, but still want to do everything I can to make my app secure. My use-case right now is that I want to encrypt access & refresh tokens from an API integration at rest in my database (I already never expose those outside the server).

exotic-emeraldOP•2y ago

Would you know if this library is a good idea to use for implementing the nacl secretbox encryption? https://github.com/dchest/tweetnacl-js?tab=readme-ov-file#secret-key-authenticated-encryption-secretbox

GitHub

GitHub - dchest/tweetnacl-js: Port of TweetNaCl cryptographic libra...

Port of TweetNaCl cryptographic library to JavaScript - dchest/tweetnacl-js

sensitive-blue•2y ago

Yes that would be the one I would use in JS

exotic-emeraldOP•2y ago

ok nice, I'll implement this one Thanks! 🙌

mute-gold•2y ago

Thanks for the quality thread guys, much appreciated. I might start small and only encrypt certain cells before thinking of encrypting entire data sets; mostly because we're still in exploration phase of the project and changes can be expected. I'm hesitant in not being able to debug from a database-viewer 😅 If Neon/Posgres encrypt natively, would I be able to read content as plain text in a DB viewing tool?

extended-salmon•2y ago

Neon encrypts data at rest, as outlined here: https://neon.tech/docs/security/security-overview#data-at-rest-encryption Reading the data back and viewing it in a DB viewing tool will show the unencrypted values, i.e plaintext. If the developer using Neon encrypts the columns themselves in their application before writing to Neon, then the DB viewing tool will show encrypted values, i.e ciphertext

Neon

Security overview - Neon Docs

At Neon, security is our highest priority. We are committed to implementing best practices and earning the trust of our users. A key aspect of earning this trust is by ensuring that every touchpoint i...

Gaming

Programming

Encrypt columns

Did you find this page helpful?