Row-level security in PostgreSQL for true multi-tenant isolation

Multi-tenant SaaS has two primary data isolation strategies: separate databases per tenant (expensive, operationally complex, but truly isolated) and a shared database with application-layer filtering. The shared database approach has a critical failure mode: a missing WHERE org_id = ? clause exposes one tenant's data to another. PostgreSQL's Row-Level Security (RLS) moves this isolation guarantee from the application layer into the database itself — a bug in your query logic can't break tenant isolation when the DB enforces it automatically.

How RLS works

RLS policies are expressions evaluated by PostgreSQL for every query on a table. When RLS is enabled on a table and a user that isn't the table owner runs a query, only rows matching the policy expression are visible or modifiable. The database engine applies the policy as if it were automatically appended to every WHERE clause.

-- Enable RLS on a table (nothing is accessible until policies are defined)
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- Create a permissive policy: users can only see rows in their organization
CREATE POLICY tenant_isolation ON documents
  AS PERMISSIVE
  FOR ALL  -- applies to SELECT, INSERT, UPDATE, DELETE
  USING (org_id = current_setting('app.current_org_id')::uuid)
  WITH CHECK (org_id = current_setting('app.current_org_id')::uuid);

-- USING clause: filter rows for SELECT/UPDATE/DELETE
-- WITH CHECK clause: validate rows for INSERT/UPDATE

Setting the tenant context

The policy references current_setting('app.current_org_id') — a session-level configuration parameter. You must set this at the start of every database connection before executing any queries:

// Middleware: set tenant context on every request
async function setTenantContext(req: AuthRequest, db: Pool): Promise<void> {
  const orgId = req.auth?.orgId;
  if (!orgId) throw new AuthError('No org context');

  // SET LOCAL only affects the current transaction
  await db.query(`SET LOCAL app.current_org_id = $1`, [orgId]);
  // Also set user ID for user-level policies
  await db.query(`SET LOCAL app.current_user_id = $1`, [req.auth.userId]);
}

// In your request handler:
const client = await pool.connect();
try {
  await client.query('BEGIN');
  await setTenantContext(req, client);

  // All queries in this transaction automatically filtered by org_id
  const docs = await client.query('SELECT * FROM documents');

  await client.query('COMMIT');
  return docs.rows;
} catch (e) {
  await client.query('ROLLBACK');
  throw e;
} finally {
  client.release();
}

The auth.uid() pattern (Supabase-style)

A common alternative to configuration parameters is to define a helper function that extracts the current user from a session JWT stored in the connection context:

-- Helper function that reads user ID from session context
CREATE OR REPLACE FUNCTION auth.uid() RETURNS UUID AS $$
  SELECT current_setting('app.current_user_id', true)::uuid;
$$ LANGUAGE sql STABLE;

CREATE OR REPLACE FUNCTION auth.org_id() RETURNS UUID AS $$
  SELECT current_setting('app.current_org_id', true)::uuid;
$$ LANGUAGE sql STABLE;

-- User-level isolation: only see your own data
CREATE POLICY user_isolation ON user_settings
  USING (user_id = auth.uid());

-- Org-level isolation with role check
CREATE POLICY org_member_access ON org_secrets
  USING (
    org_id = auth.org_id()
    AND EXISTS (
      SELECT 1 FROM org_members
      WHERE org_id = auth.org_id()
        AND user_id = auth.uid()
        AND role IN ('admin', 'owner')
    )
  );

Superuser bypass

PostgreSQL superusers bypass RLS entirely by default. Your application should never connect as a superuser. Use a dedicated application role with limited privileges:

-- Create a dedicated application role
CREATE ROLE app_user NOLOGIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

-- Create a login user that uses the app_user role
CREATE ROLE app_api LOGIN PASSWORD '...' IN ROLE app_user;

-- Table owners also bypass RLS by default.
-- If your app role is the table owner, use FORCE ROW LEVEL SECURITY:
ALTER TABLE documents FORCE ROW LEVEL SECURITY;
-- This applies RLS even to the table owner — essential for safety.

Cross-tenant queries: the admin role pattern

Your internal admin tooling needs to query across all tenants. This requires bypassing RLS. Don't use superuser — create an explicit admin role with RLS bypass for specific operations:

-- Admin role: bypasses RLS
CREATE ROLE internal_admin NOLOGIN BYPASSRLS;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO internal_admin;

-- In your admin API handler, connect with SET ROLE:
BEGIN;
SET LOCAL ROLE internal_admin;
-- Now queries bypass RLS
SELECT COUNT(*) FROM documents; -- counts all documents across all tenants
COMMIT;

Performance implications

RLS policies are evaluated per row. For large tables, ensure the policy expression is efficiently indexable:

-- Essential index: the RLS filter column must be indexed
CREATE INDEX idx_documents_org_id ON documents (org_id);

-- For composite filters, use a composite index
CREATE INDEX idx_documents_org_user ON documents (org_id, user_id);

-- Verify the planner uses the index (RLS filter should appear in the plan)
EXPLAIN ANALYZE
SET LOCAL app.current_org_id = 'org_abc';
SELECT * FROM documents WHERE title ILIKE '%search%';
-- Should show: Index Scan using idx_documents_org_id on documents
-- Filter: (org_id = current_setting('app.current_org_id')::uuid)

Testing RLS in CI

RLS is security-critical. Test it explicitly in your CI pipeline by verifying that cross-tenant data leaks are impossible:

describe('Row-Level Security', () => {
  it('prevents cross-tenant data access', async () => {
    const orgA = await createTestOrg();
    const orgB = await createTestOrg();

    const docA = await createDocument(orgA.id, 'Org A Secret');

    // Query as org B — should not see org A's document
    const client = await pool.connect();
    await client.query('BEGIN');
    await client.query('SET LOCAL app.current_org_id = $1', [orgB.id]);
    await client.query('SET LOCAL app.current_user_id = $1', [orgB.adminId]);

    const result = await client.query('SELECT * FROM documents WHERE id = $1', [docA.id]);
    expect(result.rows).toHaveLength(0); // RLS blocked it

    await client.query('ROLLBACK');
    client.release();
  });
});

Add this class of test to every table that has RLS enabled. A regression in your policy definitions is a data breach — catch it before production.