Multi-Tenant Postgres: Row-Level Security Done Right

Shared-database multi-tenancy is the right default for most SaaS products. One database, all tenants in the same tables, isolated by policy. It’s cheaper than spinning up a database per tenant and operationally simpler than sharding by tenant ID at the application layer.

The problem is that most implementations get row-level security wrong in ways that only surface under load or during a security audit.

The naive approach

The most common first attempt looks like this:

-- every query includes a WHERE clause
SELECT * FROM documents WHERE tenant_id = $1 AND id = $2;

This works. It’s also a security model that depends entirely on developers never forgetting to include the WHERE tenant_id clause. In a team of one, that’s manageable. In a team of five with a deadline, it’s an incident waiting to happen.

Row-Level Security as the enforcement layer

Postgres RLS moves the isolation guarantee into the database itself. Even if application code forgets the filter, the policy enforces it.

-- Enable RLS on every table that stores tenant data
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- Create a policy that reads tenant context from the session
CREATE POLICY tenant_isolation ON documents
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

Before executing any query, the application sets the tenant context:

SELECT set_config('app.tenant_id', $1, true); -- true = local to transaction

Now every query against documents is automatically filtered, regardless of whether the application code includes a WHERE clause.

The superuser exception

RLS policies don’t apply to superusers or table owners by default. This is a footgun: if your application connects as the table owner, RLS is silently bypassed.

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

-- The application connects as app_user, not as the superuser

Always connect with a role that is subject to RLS policies. Use the superuser only for migrations and administrative operations.

Performance: indexes and the tenant_id prefix

RLS adds a filter, but it doesn’t change the query plan unless your indexes account for it. A query like:

SELECT * FROM documents WHERE created_at > NOW() - INTERVAL '7 days';

…with an RLS policy applied becomes:

SELECT * FROM documents WHERE tenant_id = $tenant AND created_at > NOW() - INTERVAL '7 days';

Your index on created_at alone won’t help here. You need composite indexes with tenant_id as the leading column:

CREATE INDEX idx_documents_tenant_created
  ON documents (tenant_id, created_at DESC);

This applies to any column you filter or sort by in tenant-scoped queries. Missing composite indexes is the most common cause of multi-tenant performance problems that only appear in production.

Bypassing RLS for internal operations

Some queries need to bypass RLS — analytics aggregations, billing calculations, admin tooling. Use a security definer function or a separate role with BYPASSRLS:

CREATE ROLE internal_reader BYPASSRLS;

-- Or use a security definer function for specific operations
CREATE FUNCTION count_all_documents()
RETURNS bigint
LANGUAGE sql
SECURITY DEFINER  -- runs as the function owner, which has BYPASSRLS
AS $$
  SELECT COUNT(*) FROM documents;
$$;

Never give BYPASSRLS to the application role. Keep it in functions with explicit, auditable interfaces.

Testing your policies

RLS policies should have automated tests. The easiest approach is to set the tenant context in a test transaction and verify that cross-tenant data is not visible:

BEGIN;
SELECT set_config('app.tenant_id', 'tenant-a-uuid', true);
-- This should return 0 rows if RLS is working
SELECT COUNT(*) FROM documents WHERE tenant_id = 'tenant-b-uuid';
ROLLBACK;

If that query returns rows, your policy is wrong.

Summary

Multi-tenant RLS done right requires four things: policies on every table, a restricted application role, composite indexes with tenant_id as the leading column, and automated tests that verify cross-tenant isolation. Get those four right and you have a model that’s both safe and fast.