Multi-tenant BYOK encryption in PostgreSQL with pgcrypto | xata
12.0k<br>12.0kLog inGet Started
Back to Blog<br>Multi-tenant BYOK encryption in PostgreSQL with pgcrypto<br>Implement multi-tenant BYOK column encryption in PostgreSQL with pgcrypto using customer-managed encryption keys.
By:<br>Tudor Golubenco<br>Published:<br>Aug 11, 2026<br>Reading time:<br>3 min read
Back to Blog<br>Tags<br>PostgreSQL
Featured<br>A thousand Postgres branches for $1Inside Xatastor: ZFS + NVMe-oF for Postgres databasesIntroducing Xata OSS: Postgres platform with branching
Consider the following scenario: a B2B SaaS service uses shared tables to store data for their customers. Some columns contain potentially sensitive data and the customers would like to have that encrypted at rest. However, encryption at rest for the whole database (e.g. disk encryption) is not enough: customers would like to provide their own encryption keys, which they control.<br>This means that if the database as a whole is leaked, the sensitive data is safe as long as the key managed by the customer is not also leaked.<br>In this blog, we will look at how to implement per-organization BYOK column encryption using pgcrypto, including sample schema and example queries.<br>Initial Design Constraints<br>All organizations share the same tables and rows belongs to different organizations.<br>Sensitive columns must be encrypted at rest, per organization, with the org's own key.<br>Keys live in AWS KMS, the application fetches them at query time; the database never holds them.<br>Queries in the application already scoped by organization_id so each query naturally deals with one organization’s key at a time.<br>Non-BYOK organizations should still work, ideally through the same code path.<br>So, our solution needs to allow per-org encryption which means a different key per tenant, applied at the row level.<br>Schema<br>To demonstrate the use-case we will model three organizations with two BYOK tenants and one non BYOK tenant.<br>Each organization may have a KMS key configured. The application is responsible for obtaining the usable key material for the tenant and binding it into the query. Postgres uses that key with pgcrypto to encrypt or decrypt the sensitive column.<br>💡 The approach we used for designing the schema follows envelope encryption :<br>AWS KMS holds a Key Encryption Key (KEK) per organization, this is the customer's key, which they own and can revoke.<br>The application holds (briefly, in memory) a Data Encryption Key (DEK) per organization. The DEK is what actually encrypts the data.<br>The DEK is stored encrypted by the KEK in KMS. To use it, the app calls KMS Decrypt to unwrap the DEK, then passes it directly into the Postgres query.<br>KMS never sees the data. Postgres never sees the raw DEK outside of query execution. The plaintext DEK exists only in application memory, for the lifetime of the request.
For a minimal demo:<br>organizations.kms_key_arn identifies whether the org is using BYOK<br>orders.secret stores ciphertext for BYOK rows<br>non-BYOK rows are stored as plaintext bytes for contrast<br>In production, we’d simplify this further and make the column always ciphertext, using a platform-managed key for non-BYOK tenants.
Copy Code
Seeding data<br>For demonstration, we simulate the DEK as a psql variable (in production, this comes from a KMS Decrypt call in your application):
Copy Code
Copy Code
Copy Code
You can confirm the data is opaque at rest:
Copy Code
Read patterns<br>Pattern 1: single org (the common case)<br>The app unwraps the DEK for the requested org from KMS, then passes it into the query:
Copy Code
This is the fast path. One KMS call per request (or per session, if you cache the DEK in memory), then straight Postgres.<br>Pattern 2: cross-org query with mixed encryption<br>If you ever need to query across organizations — an admin view, an analytics job — you can handle the mixed case in one statement using a CTE to carry the keys:
Copy Code
The LEFT JOIN means Initech rows naturally fall into the kms_key_arn IS NULL branch.<br>Verifying the access gates work<br>The behavior on wrong-key access is worth understanding:
Copy Code
The first case is the important one: pgp_sym_decrypt with a wrong key throws an error rather than returning garbage. This is a property of the PGP format, which includes an integrity check. You won't silently return the wrong data.<br>Production notes<br>The examples above are intentionally small. If you want to ship this pattern in production, a few details matter a lot.<br>Bind keys as parameters, don’t put DEKs in SQL text. If key material ends up in SQL strings, it may leak into logs, traces or session metadata. Keys should be passed as bind parameters from the application.<br>Track encryption status per row, not from organization’s current BYOK setting. A tenant can enable BYOK later, disable it or rotate keys. A better approach is to record encryption state on each row.<br>Give non-BYOK orgs a platform-managed DEK so the sensitive column is always stored as ciphertext.<br>Plan for rotation as key...