Row-Level Security in Postgres is a shockingly underused feature. For multi-tenant apps it’s the difference between “we hope nobody forgets a WHERE clause” and “the database enforces isolation.” This post is the working playbook.

The problem

A multi-tenant app:

SELECT * FROM orders WHERE tenant_id = $1;

Works. Until a developer forgets the WHERE, or a query path is added without it. Then:

SELECT * FROM orders;          -- ⛔ returns ALL tenants

Cross-tenant leak. CVE.

RLS as a safety net

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.tenant_id')::int);

Now every query against orders is implicitly scoped to the current tenant. The forgotten-WHERE bug returns 0 rows instead of cross-tenant data.

Setting the tenant context

In the connection check-out path:

async def get_db_with_tenant(tenant_id: int):
    conn = await pool.acquire()
    await conn.execute("SET app.tenant_id = $1", str(tenant_id))
    try:
        yield conn
    finally:
        await conn.execute("RESET app.tenant_id")
        await pool.release(conn)

Every query through this connection sees only this tenant’s rows.

For the broader patterns see Multi-Tenancy SaaS Patterns .

Policy types

-- SELECT only
CREATE POLICY orders_read ON orders FOR SELECT
  USING (tenant_id = current_setting('app.tenant_id')::int);

-- INSERT
CREATE POLICY orders_insert ON orders FOR INSERT
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::int);

-- ALL operations
CREATE POLICY orders_all ON orders FOR ALL
  USING (tenant_id = current_setting('app.tenant_id')::int)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::int);

USING filters reads; WITH CHECK validates writes. Use ALL for the common case.

Per-role policies

CREATE POLICY admin_full_access ON orders FOR ALL
  TO admin_role
  USING (true)
  WITH CHECK (true);

CREATE POLICY tenant_user ON orders FOR ALL
  TO tenant_role
  USING (tenant_id = current_setting('app.tenant_id')::int);

Multiple policies are OR’d. Admins bypass; users are scoped.

Performance

RLS adds the policy as an implicit WHERE. Indexes must include the tenant column for plans to be efficient:

CREATE INDEX orders_tenant_status ON orders (tenant_id, status);

Without tenant_id as the leading index column, RLS forces filtering across the whole table.

For deeper indexing patterns see PostgreSQL Indexing and EXPLAIN .

Bypassing RLS for migrations

Schema migrations often need to run without RLS (a backfill that touches all tenants):

-- Run as a role that bypasses RLS
CREATE ROLE migration_admin BYPASSRLS;

Or per-session:

SET row_security = off;     -- requires superuser or BYPASSRLS

Common mistakes

1. RLS without setting app.tenant_id

If you forget to set the GUC, queries return 0 rows (the policy compares to NULL). Catch this by failing fast in your DB middleware.

2. Trusting RLS alone

App code should still filter by tenant_id. Two layers; both must agree.

3. Missing indexes on tenant_id

Without the index, RLS makes every query a seq scan filtered by tenant. Slow.

4. RLS not enabled on all tables

A new table created without ENABLE ROW LEVEL SECURITY is unprotected. Audit.

5. Policies that allow too much

Test policies. Try queries as different tenants. Confirm isolation.

Audit your RLS

SELECT schemaname, tablename, rowsecurity, policies
FROM pg_tables t
LEFT JOIN (
  SELECT schemaname, tablename, count(*) AS policies
  FROM pg_policies GROUP BY 1, 2
) p USING (schemaname, tablename)
WHERE schemaname = 'public';

Tables with rowsecurity = false and no policies are unprotected. Prioritize fixing.

Read this next

If you want my RLS audit query + middleware template, it’s at rajpoot.dev .

Building something AI-, backend-, or data-heavy and want a second pair of eyes? I do consulting and freelance work — see my projects and ways to reach me at rajpoot.dev .