Multi-tenant applications often store many customers' records in the same tables. Every query must then include the right tenant filter. One forgotten WHERE tenant_id = ... can expose another customer's data—even when authentication and application routing work correctly.

PostgreSQL Row-Level Security (RLS) adds a database-enforced boundary. Once enabled, policies decide which rows a database role may read or change. This tutorial builds a practical shared-table design for PostgreSQL 18, the current major version documented by the PostgreSQL project, while using patterns that also apply to supported earlier releases.

RLS is defense in depth, not a replacement for application authorization. Your application must still decide which user belongs to which tenant and what that user may do inside it.

Start with an Explicit Tenant Key

Every tenant-owned table needs an immutable tenant identifier. The example uses UUIDs and a composite relationship that prevents an invoice from referencing a customer in another tenant.

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE tenants (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    name text NOT NULL
);

CREATE TABLE customers (
    tenant_id uuid NOT NULL REFERENCES tenants(id),
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    name text NOT NULL,
    email text NOT NULL,
    PRIMARY KEY (tenant_id, id)
);

CREATE TABLE invoices (
    tenant_id uuid NOT NULL,
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    customer_id uuid NOT NULL,
    amount_cents bigint NOT NULL CHECK (amount_cents >= 0),
    status text NOT NULL CHECK (status IN ('draft', 'open', 'paid', 'void')),
    created_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (tenant_id, id),
    FOREIGN KEY (tenant_id, customer_id)
        REFERENCES customers (tenant_id, id)
);

The composite foreign key is important. RLS controls visibility, but a database constraint should still make cross-tenant relationships structurally impossible.

Index tenant-first access paths because nearly every query will include tenant_id:

CREATE INDEX invoices_tenant_created_idx
    ON invoices (tenant_id, created_at DESC);

CREATE INDEX invoices_tenant_status_idx
    ON invoices (tenant_id, status);

Do not add indexes mechanically. Confirm real query plans with EXPLAIN (ANALYZE, BUFFERS) after representative data exists.

Separate Migration and Runtime Roles

Table owners normally bypass RLS, and superusers or roles with BYPASSRLS always bypass it. The application must therefore connect as a non-owner, non-superuser role.

CREATE ROLE app_owner NOLOGIN;
CREATE ROLE app_runtime LOGIN
    PASSWORD 'replace-through-your-secret-manager'
    NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT NOBYPASSRLS;

ALTER TABLE tenants OWNER TO app_owner;
ALTER TABLE customers OWNER TO app_owner;
ALTER TABLE invoices OWNER TO app_owner;

GRANT USAGE ON SCHEMA public TO app_runtime;
GRANT SELECT, INSERT, UPDATE, DELETE ON customers, invoices TO app_runtime;

Use a secret manager or deployment secret for the real password; do not commit it. Migrations run as the owner role, while normal requests use app_runtime. This separation makes an accidental RLS bypass much less likely.

Avoid granting app_runtime membership in an owner or privileged role. Also audit managed-database convenience roles: broad data privileges do not always bypass RLS, but any role with BYPASSRLS does.

Pass Tenant Context into Each Transaction

Policies need a trustworthy value representing the current tenant. A common server-side pattern is a custom PostgreSQL setting scoped to the current transaction.

The application starts a transaction and sets app.current_tenant using set_config:

import pg from 'pg';

const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
});

export async function withTenant(tenantId, work) {
  const client = await pool.connect();

  try {
    await client.query('BEGIN');
    await client.query(
      'SELECT set_config($1, $2, true)',
      ['app.current_tenant', tenantId],
    );

    const result = await work(client);
    await client.query('COMMIT');
    return result;
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
}

The third set_config argument is true, so the value is transaction-local. That matters with connection pools: the tenant context disappears at commit or rollback instead of leaking into the next request that reuses the connection.

Only the trusted application server should have direct database credentials. If untrusted users can issue arbitrary SQL as app_runtime, they can change a custom setting themselves; in that threat model, custom settings alone are not an identity boundary.

Create a Fail-Closed Tenant Function

A small stable function keeps policy expressions readable. When no tenant context exists, it returns NULL, causing equality checks to reject every row.

CREATE SCHEMA app_private AUTHORIZATION app_owner;

CREATE FUNCTION app_private.current_tenant_id()
RETURNS uuid
LANGUAGE sql
STABLE
AS $$
    SELECT NULLIF(current_setting('app.current_tenant', true), '')::uuid
$$;

REVOKE ALL ON SCHEMA app_private FROM PUBLIC;
GRANT USAGE ON SCHEMA app_private TO app_runtime;
GRANT EXECUTE ON FUNCTION app_private.current_tenant_id() TO app_runtime;

The missing_ok argument in current_setting(..., true) prevents an error when the setting is absent. The result is NULL, not a default tenant. Never fall back to a real tenant identifier.

Enable RLS and Add Policies

Enable RLS on each tenant-owned table, then create policies for the runtime role:

ALTER TABLE customers ENABLE ROW LEVEL SECURITY;
ALTER TABLE customers FORCE ROW LEVEL SECURITY;

CREATE POLICY customers_tenant_isolation
ON customers
FOR ALL
TO app_runtime
USING (tenant_id = app_private.current_tenant_id())
WITH CHECK (tenant_id = app_private.current_tenant_id());

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;

CREATE POLICY invoices_tenant_isolation
ON invoices
FOR ALL
TO app_runtime
USING (tenant_id = app_private.current_tenant_id())
WITH CHECK (tenant_id = app_private.current_tenant_id());

USING controls which existing rows are visible for reads, updates, and deletes. WITH CHECK controls which new row values may be inserted or produced by an update. Using both prevents a tenant from writing a row under another tenant ID.

FORCE ROW LEVEL SECURITY also subjects the table owner to policies during ordinary access, although superusers and BYPASSRLS roles still bypass them. Keeping the runtime role separate remains essential.

If RLS is enabled and no applicable policy exists, PostgreSQL uses default-deny behavior. That makes a missing policy safer than an accidental allow-all rule, but it can look like an empty table during debugging.

Query Through the Tenant Wrapper

All request queries must use the same checked-out connection and transaction:

const invoices = await withTenant(request.auth.tenantId, async (db) => {
  const { rows } = await db.query(
    `SELECT id, customer_id, amount_cents, status, created_at
       FROM invoices
      WHERE status = $1
      ORDER BY created_at DESC
      LIMIT 50`,
    ['open'],
  );

  return rows;
});

Notice that the query does not need a tenant predicate. You may still include one for clarity or plan shaping, but security does not depend on every developer remembering it.

Never set tenant context on one pooled connection and execute the query on another. ORM helpers that hide connection checkout can cause this mistake. Use the ORM's interactive transaction API and run both set_config and application queries inside the same callback.

Test Isolation as the Runtime Role

Tests must connect as app_runtime; testing as the schema owner gives false confidence. Seed two tenants with distinct records, then verify both positive and negative cases.

BEGIN;
SELECT set_config('app.current_tenant', '11111111-1111-1111-1111-111111111111', true);

SELECT count(*) FROM invoices;

INSERT INTO invoices (tenant_id, customer_id, amount_cents, status)
VALUES (
  '22222222-2222-2222-2222-222222222222',
  '22222222-2222-2222-2222-222222222223',
  5000,
  'open'
);

ROLLBACK;

The count must include only tenant one. The cross-tenant insert must fail the policy check. Add tests for SELECT, INSERT, UPDATE, and DELETE, plus a request with no tenant setting that sees zero rows.

Inspect policy metadata during reviews:

SELECT schemaname, tablename, policyname, roles, cmd, qual, with_check
FROM pg_policies
WHERE schemaname = 'public'
ORDER BY tablename, policyname;

Troubleshooting

Queries unexpectedly return zero rows

Check the active role and tenant value inside the same transaction:

SELECT current_user,
       current_setting('app.current_tenant', true),
       row_security_active('public.invoices'::regclass);

An absent setting should fail closed. If the value is present, confirm its UUID matches seeded data and that the policy targets the active role.

Tests can see every tenant

Run SELECT current_user; and inspect pg_roles.rolsuper and pg_roles.rolbypassrls. The connection is probably using a superuser, a BYPASSRLS role, or the table owner without forced RLS.

Inserts fail despite the correct tenant

Verify both tenant_id and related composite keys. WITH CHECK evaluates the proposed row, while foreign keys independently validate its relationships.

Tenant context appears to leak

Ensure set_config uses true and is called after BEGIN. Never use a session-level SET on pooled connections unless you reliably reset it. Log only a non-sensitive request correlation ID and tenant ID during diagnosis—never database passwords or customer data.

Background jobs have no tenant

Queue workers need the same explicit context. Put the tenant identifier in trusted job metadata, validate it when the job starts, and run the work through the same transaction wrapper. For truly cross-tenant maintenance, use a separately controlled role and make the bypass visible in code review.

Production Checklist

  • Every tenant-owned table has a non-null tenant key.
  • Cross-table relationships include the tenant key where appropriate.
  • The runtime role is not a superuser, owner, or BYPASSRLS role.
  • RLS is enabled and policies include both USING and WITH CHECK.
  • Missing tenant context returns no rows instead of selecting a default tenant.
  • Tenant context is set transaction-locally on the same pooled connection as the query.
  • Isolation tests run as the real runtime role across all write operations.
  • Indexes begin with tenant_id where actual query plans benefit.
  • Migration, runtime, reporting, and maintenance roles have separate credentials.
  • Logs and metrics can identify policy failures without recording sensitive data.

Official Sources

RLS turns tenant filtering from a coding convention into a database rule. When paired with least-privilege roles, transaction-local context, structural constraints, and tests executed as the real application role, it provides a strong second boundary against cross-tenant data exposure.