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
- The application writes a row to Postgres.
- PL/Python and pgcrypto encrypt each value with AES-256-CBC.
- PyDAL writes the ciphertext to Snowflake.
- 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-deterministic | Deterministic | |
|---|---|---|
| IV | Random each time | Derived from a hash of the value |
| Same input | Different ciphertext | Same ciphertext |
| Search and join in Snowflake | No | Yes |
| Frequency analysis | Resistant | Susceptible |
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
maskedto 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
- Replace hardcoded keys with a key management system: HSM, Vault or a cloud KMS.
- Implement key rotation.
- 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.
Kevin Keller · Reference architecture, not production-ready as is