April 22, 2026·Rivestack Team

PostgreSQL Row Level Security: A Complete Guide

PostgreSQL

security

RLS

multi-tenancy

SaaS

production

database

Your application code knows which tenant owns which row. Your ORM always filters by WHERE tenant_id = $1. Your team has reviewed the queries and they look fine.

Then someone forgets the WHERE clause. Or a bulk operation skips the filter. Or a new developer writes a raw query without knowing the convention. Suddenly one tenant can read another tenant's data, and you find out from a support ticket two weeks later.

Row Level Security (RLS) moves the tenant isolation logic inside PostgreSQL itself. Instead of trusting application code to filter every query correctly, the database enforces the policy automatically, on every access, regardless of how the query was written. You cannot accidentally return the wrong rows because the database will not give them to you.

This guide covers how RLS actually works, how to write policies that hold up in production, and the traps that catch teams who set it up without reading the fine print.

What Row Level Security Does

RLS lets you attach policies to a table that control which rows a given database role can see or modify. The policy is a SQL expression that runs as an invisible WHERE clause on every query against that table.

Enable RLS on a table:

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

That single command changes the default behavior: without any policies defined, no rows are visible to non-superusers. The table still exists and queries succeed, they just return zero rows. This is intentional and important: the safe default is deny, not permit.

Now create a policy:

CREATE POLICY documents_tenant_isolation
  ON documents
  FOR ALL
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

From this point on, any query against documents automatically filters by tenant_id. A SELECT * FROM documents that would previously return every row now returns only the rows belonging to the current tenant. No changes to your queries required.

The Policy Expression

The USING clause is the row filter. It's a boolean expression that PostgreSQL appends to every query as an additional WHERE condition. If it returns true for a row, the row is visible. If it returns false or null, the row is invisible.

For writes, you also have WITH CHECK. This expression controls which rows can be written. If a write would create or modify a row that doesn't satisfy WITH CHECK, PostgreSQL raises an error.

CREATE POLICY documents_tenant_policy
  ON documents
  FOR ALL
  USING (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

For SELECT and DELETE, only USING applies (you can only read/delete what you can see). For INSERT, only WITH CHECK applies (the row doesn't exist yet, so there's nothing to filter). For UPDATE, both apply: USING determines which rows you can update, and WITH CHECK validates the post-update state.

If you only provide USING and omit WITH CHECK on a policy that covers INSERT or UPDATE, PostgreSQL uses the USING expression for both. This is usually what you want, but knowing the distinction saves debugging time when you have separate read and write policies.

Setting the Tenant Context

The policy expression needs some way to know which tenant the current connection belongs to. There are two main approaches: database roles and session variables.

Session Variables

Session variables are the most common approach for web applications using connection poolers. Before executing any queries for a given request, your application sets a session variable:

-- Set at connection setup time, or at the start of each transaction
SET LOCAL app.tenant_id = '550e8400-e29b-41d4-a716-446655440000';

SET LOCAL scopes the variable to the current transaction. SET (without LOCAL) scopes it to the session. For connection poolers in transaction mode, always use SET LOCAL so the variable resets when the transaction ends and the connection returns to the pool. If you use SET, a pooled connection will carry the wrong tenant_id into the next request.

Read the variable in your policy with current_setting():

USING (tenant_id = current_setting('app.tenant_id', true)::uuid)

The second argument to current_setting() is missing_ok. Pass true so the function returns null instead of raising an error when the variable isn't set. This matters during migrations, maintenance scripts, and any context where your full application setup hasn't run.

Database Roles

If each tenant has their own database role, you can use current_user directly:

CREATE POLICY tenant_role_policy
  ON documents
  FOR ALL
  USING (tenant_id::text = current_user);

This is cleaner but doesn't scale to thousands of tenants. It works well for per-customer database setups or when you have a small, fixed number of roles (e.g., a public role, an admin role, an API role).

Separate Policies Per Operation

The FOR ALL shortcut is convenient, but real applications usually need different rules for reads and writes. Break them out:

-- Anyone in the tenant can read
CREATE POLICY documents_select
  ON documents
  FOR SELECT
  USING (tenant_id = current_setting('app.tenant_id', true)::uuid);

-- Only the document owner can update
CREATE POLICY documents_update
  ON documents
  FOR UPDATE
  USING (
    tenant_id = current_setting('app.tenant_id', true)::uuid
    AND owner_id = current_setting('app.user_id', true)::uuid
  )
  WITH CHECK (
    tenant_id = current_setting('app.tenant_id', true)::uuid
    AND owner_id = current_setting('app.user_id', true)::uuid
  );

-- Service role can insert rows for any tenant
CREATE POLICY documents_insert
  ON documents
  FOR INSERT
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::uuid);

When multiple policies apply to the same operation, they combine with OR by default. This is the permissive policy model: if any policy grants access, the row is accessible. Keep this in mind when stacking policies. If you intend to restrict access rather than expand it, use AS RESTRICTIVE:

CREATE POLICY require_active_tenant
  ON documents
  AS RESTRICTIVE
  FOR ALL
  USING (
    EXISTS (
      SELECT 1 FROM tenants
      WHERE id = current_setting('app.tenant_id', true)::uuid
        AND status = 'active'
    )
  );

A restrictive policy combines with AND against all other policies. The row is only visible if it passes the restrictive policy AND at least one permissive policy. Use restrictive policies for blanket rules that should always apply regardless of other grants.

Bypassing RLS

Two categories of users bypass RLS entirely:

Superusers. Any role with SUPERUSER bypasses RLS. This is why your application should never connect as a superuser. Create a dedicated application role with only the permissions it needs.

Roles with BYPASSRLS. You can grant this attribute explicitly:

ALTER ROLE admin_role BYPASSRLS;

This is useful for admin tooling, analytics queries, and migration scripts that need to operate across all tenants. Treat it with the same care as superuser access.

To see which roles bypass RLS on your cluster:

SELECT rolname, rolbypassrls FROM pg_roles WHERE rolbypassrls = true;

Table owners also bypass RLS by default unless you force it:

ALTER TABLE documents FORCE ROW LEVEL SECURITY;

Without FORCE ROW LEVEL SECURITY, the table owner's queries skip all policies. If your application connects as the same role that owns the tables (a common setup with ORMs), your RLS policies do nothing. This is the most common RLS gotcha. Always either:

Use FORCE ROW LEVEL SECURITY, or
Have your application connect as a non-owner role

Performance

The obvious question: does adding a WHERE condition to every query slow things down?

The honest answer: sometimes, and it depends entirely on your policy expression and whether PostgreSQL can use an index for it.

A policy on tenant_id works well when you have an index on that column:

CREATE INDEX idx_documents_tenant_id ON documents (tenant_id);

With this index, the RLS filter is just an index lookup. The performance overhead is negligible. Without the index, every query does a full table scan filtered by tenant, which gets expensive as the table grows.

Check that the planner is using the index:

SET LOCAL app.tenant_id = '550e8400-e29b-41d4-a716-446655440000';

EXPLAIN ANALYZE SELECT * FROM documents WHERE title ILIKE '%invoice%';

You should see the tenant_id condition in the Filter and the index in the plan:

Index Scan using idx_documents_tenant_id on documents
  (cost=0.42..8.45 rows=1 width=256)
  (actual time=0.031..0.058 rows=3 loops=1)
  Index Cond: (tenant_id = '550e8400...'::uuid)
  Filter: (title ~~* '%invoice%')

If you see a Seq Scan, you're missing the index. A partial index on tenant_id for active tenants can also help if inactive tenant data is large and rarely queried.

For policies that join other tables (like the restrictive require_active_tenant example above), watch the query plan carefully. Subqueries in RLS policies run for every row being evaluated, which can kill performance on large tables. Materialize frequently checked values in session variables instead:

-- Check tenant status in application code, then set a verified flag
SET LOCAL app.tenant_verified = 'true';

-- Use the flag in the policy instead of a subquery
USING (
  tenant_id = current_setting('app.tenant_id', true)::uuid
  AND current_setting('app.tenant_verified', true) = 'true'
)

Testing RLS Policies

The cleanest way to test is to run queries as the role that will actually be using the policies:

SET ROLE app_user;
SET LOCAL app.tenant_id = '550e8400-e29b-41d4-a716-446655440000';

-- Should only return rows for this tenant
SELECT count(*) FROM documents;

-- Should fail if trying to update another tenant's row
UPDATE documents SET title = 'hacked' WHERE tenant_id != current_setting('app.tenant_id', true)::uuid;

RESET ROLE;

Test the boundary conditions:

What happens when app.tenant_id is not set? (It should return zero rows or error cleanly.)
Can tenant A read tenant B's rows? (It should not.)
Does the table owner bypass the policy? (It will unless you use FORCE ROW LEVEL SECURITY.)

Write these as automated tests in your test suite, not just manual spot checks. RLS policies are easy to accidentally break with schema changes.

RLS and Connection Pooling

If you're using PgBouncer in transaction mode (which most production setups should), RLS with session variables works well, but there is one critical rule: always use SET LOCAL to set tenant context, never SET.

SET LOCAL rolls back automatically when the transaction ends. SET persists for the lifetime of the session. In transaction mode, a session is shared across many clients. If you SET app.tenant_id and don't reset it, the next client that gets that connection from the pool will inherit your tenant context.

The safe pattern:

BEGIN;
SET LOCAL app.tenant_id = '550e8400-e29b-41d4-a716-446655440000';
SET LOCAL app.user_id = 'a1b2c3d4-...';
-- your application queries here
COMMIT;

See our guide to PostgreSQL connection pooling with PgBouncer for more detail on pool modes and what can go wrong in session mode.

RLS in Multi-Tenant SaaS Architecture

Row Level Security is one piece of a multi-tenant data isolation strategy. Here's how it fits with the other pieces:

Schema isolation (one schema per tenant) gives complete isolation but doesn't scale to thousands of tenants and makes migrations painful. Good for small numbers of large enterprise customers.

Database isolation (one database per tenant) is the strongest model but expensive to operate and hard to query across tenants. Rare in practice.

RLS isolation (shared schema, policies enforce tenant boundaries) scales to millions of tenants with minimal overhead if your indexes are right. This is what most modern SaaS applications use.

For the shared schema approach with RLS, the typical setup looks like this:

-- All tenants share the same tables
CREATE TABLE tenants (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'active'
);

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES tenants(id),
  email TEXT NOT NULL,
  role TEXT NOT NULL DEFAULT 'member'
);

CREATE TABLE documents (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES tenants(id),
  owner_id UUID NOT NULL REFERENCES users(id),
  title TEXT NOT NULL,
  content TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Enable RLS
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
ALTER TABLE users FORCE ROW LEVEL SECURITY;
ALTER TABLE documents FORCE ROW LEVEL SECURITY;

-- Create application role (non-owner, non-superuser)
CREATE ROLE app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON users, documents TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;

-- Policies
CREATE POLICY users_tenant_isolation ON users
  FOR ALL
  USING (tenant_id = current_setting('app.tenant_id', true)::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::uuid);

CREATE POLICY documents_tenant_isolation ON documents
  FOR ALL
  USING (tenant_id = current_setting('app.tenant_id', true)::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::uuid);

-- Indexes
CREATE INDEX idx_users_tenant_id ON users (tenant_id);
CREATE INDEX idx_documents_tenant_id ON documents (tenant_id);

Your application connects as app_user, sets app.tenant_id at the start of each transaction, and every query is automatically scoped. You get the simplicity of a single shared schema with the security of enforced isolation.

Inspecting Existing Policies

You can see all policies on a table in psql with \d table_name or query the catalog directly:

SELECT
  schemaname,
  tablename,
  policyname,
  permissive,
  roles,
  cmd,
  qual,
  with_check
FROM pg_policies
WHERE tablename = 'documents';

To see which tables have RLS enabled:

SELECT
  relname AS table_name,
  relrowsecurity AS rls_enabled,
  relforcerowsecurity AS rls_forced
FROM pg_class
WHERE relkind = 'r'
  AND relnamespace = 'public'::regnamespace
ORDER BY relname;

Common Mistakes

Not enabling FORCE ROW LEVEL SECURITY. If your app connects as the table owner, policies are silently bypassed. Always set this or use a non-owner role.

Using SET instead of SET LOCAL with connection poolers. Tenant context leaks between clients. Always use SET LOCAL inside a transaction.

Missing the index on tenant_id. Your policy runs as a filter on every query. Without an index, this means a full table scan on every request.

Treating superuser connections as safe. Superusers bypass RLS completely. If your migration tool, analytics query, or admin panel connects as a superuser, it's operating outside your security model. That might be intentional, but make it explicit.

Not testing cross-tenant access. Write a test that explicitly tries to access another tenant's data and verifies it fails. Don't assume the policy is correct because it looks right.

RLS on Managed PostgreSQL

If you're running PostgreSQL yourself, you control the roles, the connection setup, and the policy lifecycle. But managing that infrastructure also means managing the security of the infrastructure itself: who has superuser access, how credentials rotate, how you audit policy changes.

On Rivestack, RLS policies are part of your schema and deploy with your migrations. The platform handles the underlying access controls, connection pooling configuration, and credential management, so you can focus on writing the policies that matter for your application rather than the operational scaffolding around them. The same isolation guarantees apply whether you're on the free tier with a handful of tenants or scaling to millions of rows.