# Postgres Row-Level Security · Multi-Tenant Template

> Production-grade tenant-isolation template for a multi-tenant SaaS on Postgres.
> Free · MIT · [dfieldsolutions.com](https://dfieldsolutions.com)

---

## 0 · What RLS actually buys you

Row-level security makes Postgres enforce tenant isolation at the database layer. If your application layer has a bug (forgotten WHERE clause, LLM-generated SQL, cosmic-ray flipped bit), the database still refuses to hand back rows that don't belong to the calling tenant. It's defense-in-depth, not your only defense.

---

## 1 · Schema pattern

Every tenant-aware table gets a `tenant_id` column and RLS enabled:

```sql
CREATE TABLE projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES tenants(id),
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  created_by UUID NOT NULL REFERENCES users(id),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_by UUID NOT NULL REFERENCES users(id)
);

CREATE INDEX idx_projects_tenant ON projects(tenant_id);
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects FORCE ROW LEVEL SECURITY;
```

`FORCE ROW LEVEL SECURITY` is critical — without it, table owners (typically your app role) bypass RLS.

---

## 2 · The policies

```sql
-- SELECT: only rows matching the current tenant context
CREATE POLICY projects_tenant_read ON projects
  FOR SELECT
  USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- INSERT: only into the current tenant
CREATE POLICY projects_tenant_insert ON projects
  FOR INSERT
  WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- UPDATE: only rows matching current tenant, and can't reassign tenant
CREATE POLICY projects_tenant_update ON projects
  FOR UPDATE
  USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid)
  WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- DELETE: only rows matching current tenant
CREATE POLICY projects_tenant_delete ON projects
  FOR DELETE
  USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
```

---

## 3 · Setting the tenant context per request

In your app code, before any query runs:

```typescript
// Inside a transaction-scoped DB call
await db.execute(
  `SET LOCAL app.current_tenant_id = $1`,
  [currentUser.tenantId]
);
```

`SET LOCAL` scopes to the transaction — safer than `SET`, which persists for the connection.

---

## 4 · PgBouncer coexistence

PgBouncer in **transaction-pooling mode** works with RLS but requires care:
- `SET LOCAL` is fine (transaction-scoped).
- Plain `SET` is **not** safe — the next transaction on the same connection will inherit it.
- Prepared statements: use `SET LOCAL` inside each transaction, not at connection init.

**Recommended**: set tenant context in an `AS OF` wrapper on your DB client that runs `SET LOCAL` as the first statement in every transaction.

---

## 5 · Five most common RLS bypass bugs

### 1 · Forgetting `FORCE`

Without `FORCE ROW LEVEL SECURITY`, the owner of the table bypasses the policies. In most apps, the owner is the application role itself — defeating the purpose. Always `FORCE`.

### 2 · Missing `current_setting` on connection

If `app.current_tenant_id` is unset, `current_setting(..., true)` returns empty string. Your policies should FAIL CLOSED:

```sql
USING (
  tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid
)
```

Without `NULLIF`, empty string casts to null, which in a WHERE clause is always false — which is safe — but the cast itself errors. Explicitly NULL out.

### 3 · Superuser bypass

The `postgres` superuser bypasses RLS. Your application should NEVER connect as superuser in production. Create a dedicated role, grant exactly the tables + sequences it needs.

### 4 · `SECURITY DEFINER` functions

Functions declared `SECURITY DEFINER` run with the definer's privileges, not the caller's. They bypass RLS by default. Use `SECURITY INVOKER` for anything tenant-scoped.

### 5 · `RETURNING` leaks

An `INSERT ... RETURNING *` respects RLS for SELECT. But an `INSERT ... RETURNING <specific columns>` on an RLS-enabled table needs the SELECT policy too, otherwise the insert fails silently. Add an explicit SELECT policy for the app role.

---

## 6 · Testing tenant isolation

Write tests that actually cross tenant boundaries:

```sql
-- Test: tenant A can't read tenant B's rows
BEGIN;
SET LOCAL app.current_tenant_id = '<tenant-B-uuid>';
INSERT INTO projects (tenant_id, name, created_by, updated_by)
  VALUES ('<tenant-B-uuid>', 'B-secret', '<user>', '<user>');
ROLLBACK;

BEGIN;
SET LOCAL app.current_tenant_id = '<tenant-A-uuid>';
SELECT COUNT(*) FROM projects;  -- expect 0 for tenant A
ROLLBACK;
```

Run these in CI on every migration.

---

Cite as: DField Solutions, "Postgres RLS Multi-Tenant Template" (April 2026) · MIT licensed.