Safer multi-tenancy with Postgres's row-level security

May 16, 2026

There are roughly three ways to isolate tenant data in a multi-tenant app:

Out of these three, the multi-tenant tables approach gives the strongest scaling and least operational overhead, but it carries a higher risk of leaking tenant data because it relies on perfect behavior in application code. Postgres's row-level security (RLS) lets us mitigate some of that risk while keeping complexity low.

An early attempt at enforcing query scoping might look like this, a thin wrapper that takes the tenant id as an argument:

def get_documents(conn, tenant_id):
    return conn.execute(
        "SELECT * FROM documents WHERE tenant_id = $1",
        tenant_id,
    )

This works as long as every database call goes through a scoped function. But code constantly changes and evolves. Later, someone adds a search function and forgets the scope:

def search_documents(conn, tenant_id, query):
    return conn.execute(
        "SELECT * FROM documents WHERE content ILIKE $1",
        f"%{query}%",
    )

A tenant searching for "invoice" now sees everyone's invoices.

Automated testing and code review usually catch this, but not 100% of the time.

How does row-level security help?

Enabling RLS pushes the scope check from application code into the database, enforced on every query.

Here's the SQL:

-- Migration: enable RLS and write the policy
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY documents_owner ON documents
    USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

• USING (...): a boolean expression Postgres evaluates per row. Only rows where it's TRUE are visible to queries.
• current_setting('app.current_tenant_id'): reads a custom configuration parameter that the app sets per request. The name (app.current_tenant_id) is arbitrary, but Postgres requires the dot-form x.y.

Running the buggy search_documents from earlier raises an error:

ERROR: unrecognized configuration parameter "app.current_tenant_id"

Forgetting to set the tenant_id is an explicit failure. Postgres's error message is a bit confusing: it seems to be implying that our policy was created wrong, but it really means our code tried to query documents without setting app.current_tenant_id.

Here's one way to set this up in middleware. We can use set_config() with its third argument true and pass in the tenant_id to set app.current_tenant_id:

from contextlib import asynccontextmanager

# Middleware: set the tenant per request, scoped to the transaction
@asynccontextmanager
async def tenant_scope(tenant_id):
    async with engine.begin() as conn:
        await conn.execute(
            text("SELECT set_config('app.current_tenant_id', :tid, true)"),
            {"tid": str(tenant_id)},
        )
        yield conn

The middleware sets the tenant_id and yields the connection. Anything that runs on it inherits the context.

async def search_endpoint(tenant_id, query):
    async with tenant_scope(tenant_id) as conn:
        return await search_documents(conn, tenant_id, query)

When search_documents runs on conn, Postgres adds the tenant filter to the query. The function returns only the current tenant's rows.

What RLS doesn't do

RLS is a consistency check, not an authentication check. It enforces that every row touched matches whatever tenant_id is set in the session, but it doesn't verify that you set the right one. If your auth middleware pulls the wrong claim from a JWT, or grabs a tenant_id from a URL parameter (don't do this) instead of the session, RLS will return that wrong tenant's rows.

Pitfalls

• Superusers and roles with the BYPASSRLS attribute always bypass RLS.
• Table owners bypass their own policies by default, unless the table is created with ALTER TABLE ... FORCE ROW LEVEL SECURITY.

A setup that avoids these is creating one role for migrations and a least-privileges role that the application uses.

Views execute with the owner's RLS by default, but can be configured to use the caller's RLS with WITH (security_invoker = true).

RLS adds overhead, usually negligible, but it can compound with complex policies or at scale.

Outro

RLS is one of my favorite features because it catches errors early. Fixing a bug locally is orders of magnitude cheaper than handling a production failure. It's a small investment that pays dividends!

If you'd like to talk through your own setup, reach out: andrewlngdn@gmail.com

References

• PostgreSQL: Row Security Policies
• PostgreSQL: CREATE ROLE