Column-level encryption

Snowflake stores only ciphertext. Your key stays at home.

Postgres 17 as an on-premise encryption proxy between your applications and Snowflake.

Kevin Keller · kevinkeller.org

Why it matters

"Encryption at rest" means the cloud holds the keys

  • Some compliance teams and regulators need more for sensitive columns.
  • Under GDPR, DORA or sector rules, organisations may need a key they control.
  • Names, IDs, addresses and phone numbers should never sit in plaintext in the cloud.

The idea

Make Postgres the crypto boundary

Applications speak plain SQL to Postgres. Postgres encrypts every value before it leaves your perimeter, and decrypts on the way back.

How it flows

Plaintext on-premise, ciphertext in the cloud

  1. The application writes a row to Postgres.
  2. PL/Python and pgcrypto encrypt each value with AES-256-CBC.
  3. PyDAL writes the ciphertext to Snowflake.
  4. On read, Postgres fetches ciphertext and decrypts locally.

The AES key lives only on-premise.

Two encryption modes

Choose per column: searchable or maximum security

Non-deterministicDeterministic
IVRandom each timeDerived from a hash of the value
Same inputDifferent ciphertextSame ciphertext
Search and join in SnowflakeNoYes
Frequency analysisResistantSusceptible

Mix both in the same table.

Search without decrypting

Equal plaintexts make equal ciphertexts

  • Postgres encrypts the search value, such as emp1005.
  • Snowflake matches the ciphertext directly.
  • Postgres decrypts the result and returns plaintext.
  • Aggregations happen in Postgres after decryption.

A second layer

Masking policies decide who sees what in Snowflake

  • A masking policy can decrypt for one role and show masked to everyone else.
  • Tag-based masking applies the policy across all columns with one tag.
  • Direct queries without the right role see nothing useful.

No app changes

Existing ETL and CSV imports encrypt transparently

  • Triggers: encrypt every row on insert and forward to Snowflake
  • No application logic needed for encryption
  • High throughput: encrypt locally, export, bulk load via a stage
  • Streamlit app: sees plaintext while Snowflake sees ciphertext

Before production

A reference architecture, not a finished product

  1. Replace hardcoded keys with a key management system: HSM, Vault or a cloud KMS.
  2. Implement key rotation.
  3. Use parameterised queries to prevent SQL injection in the PL/Python functions.

The takeaway

Even a full breach of Snowflake storage yields nothing useful without your key.

Read the full article

Kevin Keller · Reference architecture, not production-ready as is