Stop Trusting Every WHERE Clause: Tenant Isolation in PostgreSQL

Multi-tenant apps usually start with a simple rule:

Every query needs to be scoped to the current tenant.

That works fine until it doesn't.

Someone writes a new endpoint. Someone adds a reporting query. Someone ships a quick admin tool. One missing WHERE tenant_id = ... later, one account can see data that belongs to another account.

That's the kind of bug that can end a product.

I don't like leaving tenant isolation entirely up to application code. The app should still scope queries correctly, but the database should also enforce the boundary. If the database is the last place where the data lives, it shouldn't blindly trust every query that reaches it.

PostgreSQL Row-Level Security gives you a way to push that boundary down into the database.

The basic idea

Row-Level Security, or RLS, lets PostgreSQL decide which rows are visible for a given query.

Instead of relying only on code like this:

SELECT * FROM customer_records WHERE tenant_id = $1;

you can make the table itself enforce the tenant boundary.

That means a careless query like this:

SELECT * FROM customer_records;

doesn't automatically return everything.

The database checks the active tenant context first.

Start with a tenant anchor

Every tenant-owned table needs an anchor column. In most systems that is something like tenant_id, account_id, or organization_id.

Here's a small example:

CREATE TABLE tenants (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL
);

CREATE TABLE customer_records (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES tenants(id),
  full_name TEXT NOT NULL,
  email TEXT NOT NULL
);

The important part isn't the table name. The important part is that each row belongs to a tenant.

Now enable RLS:

ALTER TABLE customer_records ENABLE ROW LEVEL SECURITY;

Once RLS is enabled, PostgreSQL will apply policies before returning rows. Without a policy, the table is effectively locked down for normal access.

That default-deny behavior is the point.

Pass tenant context into the transaction

The database needs to know which tenant is making the request.

I usually prefer setting that inside the transaction, then letting the policy read it.

Example using Node.js and pg:

async function getCustomerRecordsForTenant(client, tenantId) {
  try {
    await client.query("BEGIN");

    await client.query(
      "SELECT set_config('app.current_tenant_id', $1, true)",
      [tenantId]
    );

    const result = await client.query("SELECT * FROM customer_records");

    await client.query("COMMIT");

    return result.rows;
  } catch (error) {
    await client.query("ROLLBACK");
    throw error;
  }
}

Notice the query:

SELECT * FROM customer_records

There's no WHERE tenant_id = ... in that query.

The filtering is not gone. It moved into the database policy.

Write the RLS policy

Now connect the row’s tenant_id to the tenant context set on the session.

CREATE POLICY tenant_isolation_policy
ON customer_records
FOR ALL
USING (
  tenant_id = current_setting('app.current_tenant_id', true)::UUID
)
WITH CHECK (
  tenant_id = current_setting('app.current_tenant_id', true)::UUID
);

The USING clause controls which existing rows are visible.

The WITH CHECK clause controls which rows can be inserted or updated.

So if the active tenant is Tenant A, the database should only expose rows for Tenant A. It should also reject writes that try to create or move records into another tenant.

Read isolation and write isolation are separate problems. You need both.

Why this is safer than app-only filtering

Application-layer filtering depends on every developer remembering to write the right condition every time.

Database-enforced isolation gives you a second boundary.

If someone writes:

SELECT * FROM customer_records;

the policy still applies.

If someone writes:

DELETE FROM customer_records;

the policy still applies.

If the tenant context is missing, the comparison does not match tenant-owned rows, so the query should fail closed instead of exposing everything.

That does not mean RLS is magic. You still need to be careful with privileged roles, service-role access, migrations, background jobs, and admin tooling. A bad bypass can still cause damage.

But RLS changes the default failure mode. A missing application filter no longer has to mean cross-tenant exposure.

A few rules I would not skip

Use a tenant context that's set inside the transaction. Don't rely on a loose global variable in application memory.

Keep service-role or admin access away from normal request paths. If you have a role that bypasses RLS, treat it like a dangerous tool.

Test the policies directly. Do not only test the API layer.

For example:

SELECT set_config('app.current_tenant_id', 'TENANT_A_UUID', true);
SELECT * FROM customer_records;

Then repeat with a different tenant and confirm the rows change.

Also test writes:

INSERT INTO customer_records (tenant_id, full_name, email)
VALUES ('TENANT_B_UUID', 'Test User', '[e-mail]');

If the session is scoped to Tenant A, that insert should not be allowed for Tenant B.

Those tests are boring. They are also the kind of boring that prevents ugly production incidents.

Use the database as a backstop

I still write scoped application queries. I still validate permissions in the API. I still treat authorization as an application concern.

But I don't want the database to be passive.

In a multi-tenant system, the database should know enough to say no.

RLS is not a replacement for good application design. It is a backstop for the day someone writes the wrong query, ships the wrong report, or builds an admin endpoint too quickly.

That day comes eventually.

When it does, I would rather have PostgreSQL enforce the tenant boundary than hope every WHERE clause was perfect.