---
title: "Multi-tenant BYOK encryption in PostgreSQL with pgcrypto"
description: "Implement multi-tenant BYOK column encryption in PostgreSQL with pgcrypto using customer-managed encryption keys."
source: "https://xata.io/blog/multi-tenant-byok-encryption-in-postgresql-with-pgcrypto"
---

# 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.

```
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE organizations (
    id          bigint PRIMARY KEY,
    name        text NOT NULL,
    kms_key_arn text  -- NULL => not BYOK; non-NULL => identifies the KEK in KMS
);

CREATE TABLE orders (
    id      bigint PRIMARY KEY,
    org_id  bigint NOT NULL REFERENCES organizations(id),
    amount  numeric(10,2),
    secret  bytea  -- ciphertext for BYOK orgs, plaintext bytes for non-BYOK orgs
);
```

## Seeding data

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

```
INSERT INTO organizations (id, name, kms_key_arn) VALUES
  (1, 'Acme',    'arn:aws:kms:eu-central-1:111122223333:key/acme-kek'),
  (2, 'Globex',  'arn:aws:kms:eu-central-1:111122223333:key/globex-kek'),
  (3, 'Initech', NULL);  -- not BYOK
```

```
--Simulated unwrapped DEKs (in reality, returned by KMS Decrypt at request time)
\set dek_acme   'acme-dek-unwrapped-from-kms-xxxxx'
\set dek_globex 'globex-dek-unwrapped-from-kms-yyy'

```

```
INSERT INTO orders (id, org_id, amount, secret) VALUES
  (10, 1, 99.00,  pgp_sym_encrypt('Acme card 4111-1111',   :'dek_acme')),
  (20, 2, 50.00,  pgp_sym_encrypt('Globex card 4222-2222', :'dek_globex')),
  (30, 3, 10.00,  convert_to('Initech card 4333-3333', 'UTF8'));
```

You can confirm the data is opaque at rest:

```
SELECT id, org_id, secret FROM orders ORDER BY id;
```

## 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:

```
SELECT o.id, o.amount,
       pgp_sym_decrypt(o.secret, :'dek_acme') AS secret_plain
FROM orders o
WHERE o.org_id = 1;
```

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:

```
WITH keys (org_id, dek) AS (
    VALUES
      (1, 'acme-dek-unwrapped-from-kms-xxxxx'),
      (2, 'globex-dek-unwrapped-from-kms-yyy')
    -- org 3 (Initech) is absent: no DEK needed, not BYOK
)
SELECT o.id,
       org.name,
       CASE
         WHEN org.kms_key_arn IS NULL
           THEN convert_from(o.secret, 'UTF8')   -- non-BYOK: raw bytes
         ELSE pgp_sym_decrypt(o.secret, k.dek)   -- BYOK: decrypt with org's DEK
       END AS secret_plain
FROM orders o
JOIN organizations org ON org.id = o.org_id
LEFT JOIN keys k ON k.org_id = o.org_id
ORDER BY o.id;
```

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:

```
-- Wrong key on an encrypted row -> hard error, not silently corrupt output
SELECT pgp_sym_decrypt(secret, 'wrong-key') FROM orders WHERE id = 10;
ERROR: Wrong key or corrupt data

-- Treating ciphertext as plaintext -> garbage bytes, not the secret
SELECT convert_from(secret, 'UTF8') FROM orders WHERE id = 10;
ERROR:  invalid byte sequence for encoding "UTF8": 0xc3 0x0d
```

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.
- Even if you bind the keys to parameters, note that when `log_min_duration_statement` is set (defaults to disabled), Postgres will log the parameters (and therefore the keys) for slow queries. Use `log_parameter_max_length = 0` to avoid this.
- 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](https://xata.io/docs/platform/extensions). If there's another extension you'd like to use that isn't currently available, let us know.