Enforcing EU-Only Data Residency in PostgreSQL

A residency commitment is a promise about physical location that must survive every code path, replica and backup, and this page makes PostgreSQL itself enforce it. It is a focused part of Tenant Data Residency: pinning a tenant to a region, refusing writes that arrive in the wrong one, and producing evidence a data protection officer will accept.

Problem Framing

Residency is usually implemented as routing: resolve the tenant's region, connect to that region's database, done. Routing is necessary and insufficient, because it fails open. A cache miss, a failover to a secondary in another region, a batch job started with a default connection string, an analytics replica created by a well-meaning data team — each writes EU personal data outside the EU, and none of them produces an error.

The stronger design makes the database the enforcement point. Each regional cluster knows its own region, every tenant row carries its allowed region, and a constraint or trigger rejects any row whose tenant is not permitted there. A misrouted write then fails loudly with a constraint violation instead of succeeding quietly in Ohio.

Two supporting facts complete it. Replicas inherit residency, so the topology must be declared and audited rather than assumed — a read replica in another region is as much an export as a write. And backups are copies: a backup bucket with cross-region replication enabled silently defeats the whole arrangement.

Step-by-Step Guide

1. Tell every database which region it is in

A database-level setting is what lets one migration work in every region without templating.

-- Applied per cluster during provisioning.
ALTER DATABASE saas SET app.deployment_region = 'eu-west-1';

-- Readable by any session on this cluster.
SELECT current_setting('app.deployment_region');

2. Pin the tenant and enforce it in the schema

CREATE TABLE tenant (
  id             uuid PRIMARY KEY,
  name           text NOT NULL,
  allowed_region text NOT NULL,
  -- The row physically cannot exist in a cluster whose region differs.
  CONSTRAINT tenant_region_pinned
    CHECK (allowed_region = current_setting('app.deployment_region', true))
);

A CHECK referencing a setting is evaluated on insert and update, which is exactly the moment a misrouted write happens. It is not a substitute for correct routing — it is the assertion that turns a silent routing bug into an exception with a stack trace.

3. Propagate the guard to every tenant-scoped table

Child tables inherit residency through their tenant. A trigger is the cheapest way to assert it without duplicating the region on every row.

CREATE OR REPLACE FUNCTION assert_tenant_region() RETURNS trigger AS $$
DECLARE
  tenant_region text;
BEGIN
  SELECT allowed_region INTO tenant_region FROM tenant WHERE id = NEW.tenant_id;
  IF tenant_region IS NULL THEN
    RAISE EXCEPTION 'unknown tenant % on cluster %', NEW.tenant_id,
      current_setting('app.deployment_region', true);
  END IF;
  IF tenant_region <> current_setting('app.deployment_region', true) THEN
    RAISE EXCEPTION 'residency violation: tenant % is pinned to %, cluster is %',
      NEW.tenant_id, tenant_region, current_setting('app.deployment_region', true)
      USING ERRCODE = 'check_violation';
  END IF;
  RETURN NEW;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER patient_note_residency
  BEFORE INSERT OR UPDATE ON patient_note
  FOR EACH ROW EXECUTE FUNCTION assert_tenant_region();

Attach the trigger from the same registry that drives encryption and deletion, so a new table cannot be created without one — the per-row cost is a single indexed lookup, and it is almost always already in cache.

4. Declare the replica topology and audit it

CREATE TABLE cluster_topology (
  cluster_key text PRIMARY KEY,
  role        text NOT NULL,            -- 'primary' | 'replica' | 'analytics'
  region      text NOT NULL,
  replicates  text REFERENCES cluster_topology(cluster_key)
);

-- Any replica whose region differs from its source is a residency violation.
SELECT r.cluster_key, r.region AS replica_region, p.region AS source_region
FROM   cluster_topology r
JOIN   cluster_topology p ON p.cluster_key = r.replicates
WHERE  r.region <> p.region;
-- expected: no rows

Run the same check against the actual infrastructure, not only the declared table — query the cloud provider for replicas of each instance and compare. A declared topology that has drifted from reality is the failure this is meant to catch.

5. Keep backups and object storage in region

# terraform — backups stay put, and nothing quietly replicates them
resource "aws_db_instance" "eu_primary" {
  identifier                = "saas-eu-west-1"
  availability_zone         = "eu-west-1a"
  backup_retention_period   = 35
  copy_tags_to_snapshot     = true
  # No automated cross-region snapshot copy is configured, deliberately.
}

resource "aws_s3_bucket" "eu_tenant_files" {
  bucket = "saas-tenant-files-eu-west-1"
}

resource "aws_s3_bucket_public_access_block" "eu_tenant_files" {
  bucket                  = aws_s3_bucket.eu_tenant_files.id
  block_public_acls       = true
  block_public_policy     = true
  ignore_public_acls      = true
  restrict_public_buckets = true
}
# Note: no aws_s3_bucket_replication_configuration for this bucket, by design.

Verification

-- 1. Every tenant on this cluster is pinned to this cluster's region.
SELECT id, allowed_region FROM tenant
WHERE  allowed_region <> current_setting('app.deployment_region');
-- expected: no rows

-- 2. Every tenant-scoped table carries the residency trigger.
SELECT c.relname AS table_missing_trigger
FROM   pg_class c
JOIN   pg_namespace n ON n.oid = c.relnamespace AND n.nspname = 'public'
WHERE  c.relkind = 'r'
  AND  EXISTS (SELECT 1 FROM pg_attribute a
               WHERE a.attrelid = c.oid AND a.attname = 'tenant_id' AND NOT a.attisdropped)
  AND  NOT EXISTS (SELECT 1 FROM pg_trigger t
                   WHERE t.tgrelid = c.oid AND t.tgname LIKE '%_residency');
-- expected: no rows

-- 3. The guard actually rejects a foreign tenant.
INSERT INTO patient_note (tenant_id, body_ct) VALUES ('<a us tenant>', '\x00');
-- expected: ERROR: residency violation: tenant … is pinned to us-east-1, cluster is eu-west-1

The second query is the one worth wiring into CI against a migrated test database: it catches the new table that forgot its trigger at review time rather than at audit time.

Failure Modes & Gotchas

FAQ

Does data residency mean the same thing as data sovereignty? No, and conflating them creates promises you cannot keep. Residency is about physical location and is what this page enforces. Sovereignty is about which government can compel access, which depends on the operator's corporate jurisdiction rather than the data centre's location — a US-headquartered provider storing data in Frankfurt satisfies residency but not the sovereignty concern some customers actually have. Be precise about which one you are contracting for.

Can a single application deployment serve tenants in several regions? The stateless tier can, but it should not: a shared application tier means one process holds connections to several regional clusters, and one routing bug becomes a cross-region write. Deploy the application per region alongside its database, and route at the edge on the tenant's hostname. The residency trigger then exists as a backstop rather than as the primary control.

What legitimately crosses the boundary? Very little, and it should be enumerated in a transfer register: the tenant directory (identifiers, region, plan — no personal data), aggregate billing totals, and operational telemetry with personal data stripped. Everything else — rows, files, logs, backups, search indexes — stays in region. Writing that list down is itself part of the evidence, because "nothing leaves" is never quite true and an unqualified claim is the one that fails review.

How should a tenant's region be changed if the customer asks? Treat it as a migration rather than a setting. The data has to move, which means the same copy-replicate-cutover sequence used for a tier promotion, followed by verified deletion in the source region — and the deletion is the part that matters, because a residency change that leaves the old copy in place has changed nothing. Record the request, the migration and the source-side deletion evidence together, since a customer asking to move regions is usually doing so for a reason they will need to evidence to somebody else.

Does residency apply to metadata as well as content? It applies to personal data wherever it sits, and the tenant directory is the usual grey area. Tenant names, plan tiers and region assignments are ordinarily business information rather than personal data, so a globally replicated control plane holding those is defensible. The moment that directory holds administrator names, email addresses or login timestamps it is holding personal data, and it needs the same treatment as everything else — which is why the transfer register should describe the control plane's contents field by field rather than dismissing it as metadata.