Multi-tenant BYOK encryption in PostgreSQL with pgcrypto

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.

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.

In this blog, we will look at how to implement per-organization BYOK column encryption using pgcrypto, including sample schema and example queries.

Initial Design Constraints

• All organizations share the same tables and rows belongs to different organizations.
• Sensitive columns must be encrypted at rest, per organization, with the org's own key.
• Keys live in AWS KMS, the application fetches them at query time; the database never holds them.
• Queries in the application already scoped by organization_id so each query naturally deals with one organization’s key at a time.
• Non-BYOK organizations should still work, ideally through the same code path.

So, our solution needs to allow per-org encryption which means a different key per tenant, applied at the row level.

Schema

To demonstrate the use-case we will model three organizations with two BYOK tenants and one non BYOK tenant.

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.

For a minimal demo:

• organizations.kms_key_arn identifies whether the org is using BYOK
• orders.secret stores ciphertext for BYOK rows
• non-BYOK rows are stored as plaintext bytes for contrast

In production, we’d simplify this further and make the column always ciphertext, using a platform-managed key for non-BYOK tenants.

Seeding data

For demonstration, we simulate the DEK as a psql variable (in production, this comes from a KMS Decrypt call in your application):

You can confirm the data is opaque at rest:

Read patterns

Pattern 1: single org (the common case)

The app unwraps the DEK for the requested org from KMS, then passes it into the query:

This is the fast path. One KMS call per request (or per session, if you cache the DEK in memory), then straight Postgres.

Pattern 2: cross-org query with mixed encryption

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:

The LEFT JOIN means Initech rows naturally fall into the kms_key_arn IS NULL branch.

Verifying the access gates work

The behavior on wrong-key access is worth understanding:

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.

Production notes

The examples above are intentionally small. If you want to ship this pattern in production, a few details matter a lot.

• 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.
• 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.
• Give non-BYOK orgs a platform-managed DEK so the sensitive column is always stored as ciphertext.
• Plan for rotation as key rotation is not an edge case. If a tenant changes keys you need a clear story for older rows.
• Check and benchmark for potential performance implications. To minimize impact, only encrypt the columns that contain sensitive data.

Closing thought

PostgreSQL with pgcrypto is enough to build per-tenant encryption for sensitive customer data in a multi-tenant application. If you have a similar use case, we'd love to hear about it.

pgcrypto is available as one of the PostgreSQL extensions in Xata. If there's another extension you'd like to use that isn't currently available, let us know.