Enforce Multi-Tenant Data Isolation with Postgres Row-Level Security

What you'll build

A multi-tenant notes table protected by Postgres Row-Level Security (RLS) policies that automatically filter every SELECT, INSERT, UPDATE, and DELETE to the current tenant, enforced at the database layer with no application-side WHERE clauses required.

Prerequisites

• PostgreSQL 14 or later (RLS exists from 9.5, but 14+ is current stable)
• psql or any SQL client connected to your database
• Basic familiarity with SQL DDL statements

1. Create the schema

CREATE TABLE notes (
  id         BIGSERIAL PRIMARY KEY,
  tenant_id  UUID NOT NULL,
  content    TEXT NOT NULL
);

Every table you want to protect needs a tenant_id column. This is the anchor all your policies reference.

2. Create a dedicated app role

Your application should never connect as a superuser or as the table owner. Both bypass RLS silently.

CREATE ROLE app_user LOGIN PASSWORD 'change_this_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON notes TO app_user;
GRANT USAGE ON SEQUENCE notes_id_seq TO app_user;

Since app_user doesn't own the notes table, Postgres applies RLS policies to it automatically.

3. Enable RLS and create the isolation policy

ALTER TABLE notes ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON notes
  USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID)
  WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

Two things worth understanding here. current_setting('app.current_tenant_id', true) reads a session variable your app sets before each query. The second argument (true) is the missing_ok flag: if the variable was never set, the function returns NULL instead of throwing an error. A NULL comparison matches nothing, so unidentified sessions see zero rows. That's the right default.

USING controls row visibility for SELECT, UPDATE, and DELETE. WITH CHECK validates rows being written by INSERT and UPDATE. One policy, all four operations covered.

4. Set the tenant ID per transaction

Before running any application queries, set the session variable inside a transaction:

SET LOCAL app.current_tenant_id = '11111111-1111-1111-1111-111111111111';

SET LOCAL is important with connection pools (PgBouncer, RDS Proxy, Neon, etc.). It resets automatically when the transaction ends, so a tenant ID can't leak across requests on a recycled connection. Plain SET (without LOCAL) persists for the entire session, which is dangerous in a pooled environment.

In Node.js with the pg package, SET and SET LOCAL don't support parameter binding via $1 placeholders. Use set_config() instead, which is a regular SQL function and can be safely parameterized. The third argument (true) makes it transaction-local, equivalent to SET LOCAL:

await client.query('BEGIN');
await client.query(
  "SELECT set_config('app.current_tenant_id', $1, true)",
  ['11111111-1111-1111-1111-111111111111']
);
// run your application queries here
await client.query('COMMIT');

Verify it works

Seed rows as the table owner (not app_user):

INSERT INTO notes (tenant_id, content) VALUES
  ('11111111-1111-1111-1111-111111111111', 'Tenant A note'),
  ('22222222-2222-2222-2222-222222222222', 'Tenant B note');

Connect as app_user and test isolation:

BEGIN;
SET LOCAL app.current_tenant_id = '11111111-1111-1111-1111-111111111111';
SELECT * FROM notes;
COMMIT;

You should see exactly one row: the Tenant A note. Run it again with Tenant B's UUID and you'll get only their row.

Now verify that unauthorized writes are rejected:

BEGIN;
SET LOCAL app.current_tenant_id = '11111111-1111-1111-1111-111111111111';
INSERT INTO notes (tenant_id, content)
  VALUES ('22222222-2222-2222-2222-222222222222', 'Cross-tenant write attempt');
COMMIT;

Expected: ERROR: new row violates row-level security policy for table "notes".

Troubleshooting

Policies are silently ignored. Your app is connecting as the table owner or a superuser. Verify with SELECT current_user;. Fix: use app_user, or run ALTER TABLE notes FORCE ROW LEVEL SECURITY to apply policies to the owner too (useful in dev when you can't change the connection role).

ERROR: invalid input syntax for type uuid. The ::UUID cast is failing. Check that your tenant ID is a properly formatted UUID (8-4-4-4-12 hex characters with no invalid characters like g or z).

All queries return empty outside a transaction. SET LOCAL outside BEGIN...COMMIT has no lasting effect; the variable is set and immediately reset. Wrap your SET LOCAL and queries in an explicit transaction.

Multiple policies conflict. Permissive policies (the default) are OR'd together. If you've added more than one policy and see unexpected access, list all active policies with:

SELECT * FROM pg_policies WHERE tablename = 'notes';

Next steps

• Apply RLS to every table that holds tenant data, not just one. A single unprotected table is enough for a cross-tenant leak.
• Read the Postgres Row Security Policies docs for RESTRICTIVE policies, role-specific policies, and how to safely bypass RLS for admin queries.
• If you're on Supabase, it exposes auth.uid() and JWT claims directly in policy expressions, building on exactly this mechanism. Their RLS guide shows how to wire those together.