Postgres Row Level Security for Multi-Tenant Apps

Q: Can I Use RLS With a Connection Pool Like PgBouncer?

Yes, as long as tenant context is set with SET LOCAL or set_config inside each transaction rather than a session-level SET, so the value never leaks to the next pooled request.

Row level security is the correct first line of defense for shared-schema multi-tenant apps: it enforces tenant isolation inside the Postgres engine itself, so a forgotten WHERE clause in your application code cannot leak another tenant's data. Making it work requires two things beyond a simple ENABLE: transaction-scoped tenant context on every connection, and FORCE ROW LEVEL SECURITY so the table owner doesn't quietly bypass your policies.

TL;DR:

Ensuring tenant context is set locally within each transaction prevents cross-tenant data leaks caused by connection pooling.

Indexing tenant_id as the leading column in complex indexes is critical to maintain query performance under RLS.

Properly enforcing RLS with FORCE ROW LEVEL SECURITY and correct policies, including matching USING and WITH CHECK expressions, is essential for reliable tenant isolation.

Deployment requires staging the setup of tenant context, backfilling tenant IDs, and verifying index use before enabling policies to avoid zero-row outcomes.

RLS alone does not handle role-based permissions, rate limits, or audit logging; these aspects require complementary controls in the application or additional configurations.

What Row Level Security Does and When to Use It
Essential Commands and Policy Patterns for Tenant Isolation
How Do You Inject Tenant Context Safely?
Indexing and Performance Under Row Level Security
Testing, Deployment Order, and Common Pitfalls
A Copy-and-Paste Pattern for Tenant Isolation
How I Handle RLS Work in a Fixed-Price MVP or Rescue Project
Real-World Scenarios Where Row Level Security Fits
Managing Multiple Roles and Permissions With RLS
Combining Row Level Security With Application-Level Checks
Logging and Auditing Access Under Row Level Security
Where Row Level Security Falls Short
Row Level Security's Impact on Backups and Replication
What I'd Prioritize First If I Were You
Need Help Getting Row Level Security Into Production?
Sources
FAQ

What Row Level Security Does and When to Use It

Row level security (RLS) attaches a predicate to every query Postgres runs against a table, the same way you'd bolt a WHERE tenant_id = current_tenant() onto every SELECT, UPDATE, and DELETE, except the database does it for you, automatically, on every code path. That last part matters more than it sounds. Application-level filtering only works if every engineer, every script, and every future contractor remembers to add the filter. RLS makes forgetting impossible at the query layer.

For the shared-schema, connection-pooled model most SaaS products run on, this is the right fit. It cuts the surface area for a leak down to "did I set the tenant context correctly" instead of "did every query in the codebase remember the filter."

RLS has real limits, though. It won't:

Replace column-level grants for restricting which fields a role can see
Enforce rate limits or usage quotas per tenant
Protect you from a role that has been granted BYPASSRLS

Think of it as one layer, not the whole wall.

Essential Commands and Policy Patterns for Tenant Isolation

Turning on row level security in Postgres takes one command, but getting policies right takes more care. Start here:

Enable it. ALTER TABLE orders ENABLE ROW LEVEL SECURITY; turns on enforcement. With zero policies defined, Postgres defaults to deny: no rows are visible to anyone except the owner, until you add a policy.
Force it. ALTER TABLE orders FORCE ROW LEVEL SECURITY; closes the loophole where the table owner (often the app's connection role in dev, sometimes in production too) skips policy checks entirely.
Write the policy. CREATE POLICY tenant_isolation ON orders USING (tenant_id = current_setting('app.tenant_id')::uuid) WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

USING and WITH CHECK do different jobs. USING filters what a query can see: SELECT, UPDATE, DELETE all check it. WITH CHECK validates what a query is allowed to write: it runs on INSERT and on the new row value of an UPDATE. The official CREATE POLICY documentation is explicit that these are separate checks, and keeping them identical is the single easiest way to close write-side leaks.

Postgres also supports permissive and restrictive policies. Permissive policies (the default) combine with OR, so a row is visible if any one of them matches. Restrictive policies combine with AND, so every restrictive policy must pass. Most tenant-isolation setups need exactly one permissive policy per table and nothing more exotic.

Pro Tip: Write the same expression in USING and WITH CHECK every time, even when it feels redundant. Asymmetric policies are the number one source of "tenant A can somehow write rows tagged tenant B" bugs.

How Do You Inject Tenant Context Safely?

The tenant context problem is really a connection-pooling problem. If you run SET app.tenant_id = 'abc' on a pooled connection, that setting sticks around after your transaction ends, and the next request to grab that connection from the pool inherits it. That's an actual tenant-isolation bug waiting to happen, not a theoretical one.

The fix is to scope the setting to the transaction, not the session:

Use SET LOCAL app.tenant_id = 'abc'; right after BEGIN, or the function form set_config('app.tenant_id', 'abc', true) where the third argument (is_local) does the same job.
Either form resets automatically at COMMIT or ROLLBACK, so the setting never survives a pooled connection handoff to the next request.
Wrap every request in: begin transaction, SET LOCAL the tenant, run your queries, commit or roll back. No exceptions, no shortcuts for "internal" endpoints.

Add two more defenses on top. Set a server_reset_query at the pooler level (PgBouncer supports this) so any accidental session-level state gets cleared between checkouts regardless. And write your tenant-context helper function to fail closed, raising an error or returning NULL rather than defaulting to some "safe" value when the setting is missing. A loud failure beats a silent leak.

Indexing and Performance Under Row Level Security

A policy predicate is just another condition Postgres has to evaluate, and if tenant_id isn't indexed properly, that condition turns into a sequential scan as your tables grow past a few hundred thousand rows.

The fix is straightforward: make tenant_id the leading column of every composite index that supports a tenant-scoped query. An index defined as (tenant_id, created_at) lets the planner satisfy both the policy filter and your sort in one index scan. An index defined as (created_at, tenant_id) won't help the policy at all.

Lead every relevant composite index with tenant_id
Confirm the plan with EXPLAIN ANALYZE; you want to see an index scan on the tenant index, with the policy predicate folded into the index condition, not applied as a separate filter step afterward
Watch for non-immutable functions inside policy expressions. Wrapping current_setting() in something that isn't marked immutable can quietly force a slower plan

Most teams that see a "RLS made everything slow" problem actually have a missing-index problem. AWS's guidance on multi-tenant isolation confirms the pattern: index-led policies typically add only marginal overhead. Once a single tenant's data outgrows what a shared table can serve comfortably, partitioning by tenant_id is worth the added operational complexity; below that, it's premature.

Testing, Deployment Order, and Common Pitfalls

Deployment order determines whether enabling RLS is a non-event or an outage. Get it backward and every query returns zero rows the moment policies go live, because the application was never setting tenant context in the first place.

The safe sequence:

Ship application code that sets tenant context on every request path, with tests confirming it.
Deploy the tenant-context helper function, written to fail closed.
Backfill tenant_id on existing rows, add a NOT NULL constraint, then add the index.
Enable RLS and add policies.
Run verification: isolation assertions (tenant A truly cannot read tenant B), role-switching tests, EXPLAIN ANALYZE checks confirming index usage, and end-to-end tests running through your actual connection pool.

Four mistakes cause most real-world incidents. Owner bypass: the app's database role owns the tables and skips policies entirely until you run FORCE ROW LEVEL SECURITY. Missing WITH CHECK: a tenant inserts a row stamped with someone else's tenant_id, and the bug stays invisible because that tenant's own SELECT policy filters the bad row out of their view immediately. Unindexed tenant_id: fine in staging, a slow-query incident in production. Session-level context in a pool: intermittent, hard-to-reproduce cross-tenant bugs that only show up under real concurrent load.

A Copy-and-Paste Pattern for Tenant Isolation

Here's the minimal version I actually deploy, stripped to the parts that matter.

Schema. Every tenant-scoped table gets a tenant_id uuid NOT NULL column and a composite index leading with it: CREATE INDEX ON orders (tenant_id, created_at);
Helper function, written to fail closed:

CREATE FUNCTION current_tenant() RETURNS uuid AS $$
  SELECT current_setting('app.tenant_id', true)::uuid
$$ LANGUAGE sql STABLE;

Returning NULL when the setting is missing means every policy check fails, since tenant_id = NULL never matches. Nothing leaks by default.

Policy:

CREATE POLICY tenant_isolation ON orders
  FOR ALL
  USING (tenant_id = current_tenant())
  WITH CHECK (tenant_id = current_tenant());

Application transaction:

BEGIN;
SET LOCAL app.tenant_id = 'a1b2c3...';
SELECT * FROM orders WHERE status = 'open';
COMMIT;

Verify with EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'open'; inside that same transaction and confirm the plan uses the tenant index rather than a sequential scan.

Step	Command	Purpose
Enable	`ALTER TABLE orders ENABLE ROW LEVEL SECURITY;`	Turns on policy enforcement
Force	`ALTER TABLE orders FORCE ROW LEVEL SECURITY;`	Applies policies to the table owner too
Isolate	`CREATE POLICY ...`	Defines the tenant predicate for reads and writes
Scope context	`SET LOCAL app.tenant_id = '...';`	Sets tenant per transaction, never per session

How I Handle RLS Work in a Fixed-Price MVP or Rescue Project

I'm Hanad Kubat. Ten years of engineering work, including systems built for Deutsche Bahn, BMW, and BRZ, before I started taking on fixed-price builds for founders and owner-operators directly.

When a prototype audit or rescue rebuild touches multi-tenant data, row level security is usually part of the deliverable, not an afterthought bolted on later. That means deployable policies with matching USING and WITH CHECK clauses, the index changes to keep them fast, isolation tests that prove tenant A can't see tenant B's rows, and a documented admin access pattern for the cases where a support role genuinely needs BYPASSRLS.

Scope gets frozen at kickoff. Delivery runs weeks, not months. You own the code from the first commit, every line written by me, no juniors billing hours on your project.

Real-World Scenarios Where Row Level Security Fits

The clearest use case is the shared-schema SaaS product: one orders table, one documents table, thousands of tenants, all in the same database because running a separate schema or database per customer doesn't scale operationally past a certain size. RLS is what makes that shared table safe.

A second common scenario is the internal admin tool that needs a "view as tenant" mode for support staff. Rather than writing separate query paths for admin versus tenant access, you grant the support role a policy that checks against a broader condition, or a temporary BYPASSRLS grant scoped to an audited session, while regular application traffic runs through the standard tenant policy.

A third: compliance-driven isolation, where a healthcare or fintech product needs to demonstrate, to an auditor or in a due-diligence review, that tenant data cannot cross boundaries even if application code has a bug. RLS gives you something concrete to point to: a policy definition, not just a promise that "the code checks for that."

A fourth scenario worth naming: hierarchical access within a single tenant, where a manager role should see their team's rows and an individual contributor should see only their own. This stacks a second predicate on top of the tenant filter, typically combined with a department_id or manager_id check inside the same USING clause, so both boundaries get enforced by the same mechanism instead of two separate systems that can drift apart.

Managing Multiple Roles and Permissions With RLS

Row level security and role-based grants solve different problems, and conflating them causes confusion. Grants (GRANT SELECT ON orders TO app_user;) control which columns and tables a role can touch at all. Policies control which rows within a table a role can see, once access is already granted.

A typical multi-tenant setup runs at least three roles. An app_user role that every tenant-facing request uses, subject to the standard tenant-isolation policy. A read_replica or reporting role, often granted SELECT only, still subject to the same tenant policy so analytics queries can't cross tenant boundaries either. And a narrowly scoped admin_support role, granted BYPASSRLS only when it's actually needed, ideally behind an application-layer check that logs every time that bypass gets exercised.

Policies can also target specific roles directly: CREATE POLICY tenant_isolation ON orders FOR ALL TO app_user USING (...). This lets you layer a stricter restrictive policy on top for a subset of roles, using AS RESTRICTIVE, without touching the base permissive policy everyone else relies on. That's useful when a "read-only reporting" role needs the same tenant boundary plus an extra constraint, like excluding soft-deleted rows, that the main application role doesn't need.

Keep the role count small. Every additional role with its own policy variant is another thing to test and another thing that can drift out of sync when the schema changes.

Combining Row Level Security With Application-Level Checks

RLS is not a replacement for application security logic, it's the layer underneath it. Microsoft's own guidance on row-level security frames it the same way: treat RLS as the guarantee, and keep application-level checks for everything RLS was never designed to do.

Application code still owns the user experience around access denial. RLS will silently return zero rows to a query that violates a policy. It won't return a helpful "you don't have permission to view this invoice" message. That distinction is worth building into your API layer deliberately: catch the empty result set and translate it into a real error before it reaches the frontend.

Application code also still owns authentication, session management, and the step of resolving which tenant a request belongs to in the first place. RLS trusts whatever value lands in app.tenant_id. If your authentication logic assigns the wrong tenant to a session, no policy will catch that; the query genuinely does belong to that tenant as far as Postgres is concerned. Get identity resolution right first, and let RLS handle everything downstream of it.

A practical split that works well: authentication and authorization decisions (does this user have access to this feature at all) stay in application code. Data isolation (does this row belong to this tenant) stays in the database, as a related SaaS security checklist lays out for teams building this into a broader review process. Each layer catches what the other one misses.

Logging and Auditing Access Under Row Level Security

RLS enforces the boundary; it doesn't log who tried to cross it. If you need an audit trail, pgaudit is the standard extension for logging statement-level activity, and you can configure it to capture the tenant context alongside each query by including app.tenant_id in your log line format.

Workshop tools and blueprints for auditing

For anything approaching compliance work, log three things at minimum: every BYPASSRLS session, tagged with the role and the reason; every failed write that a WITH CHECK clause rejected, since a spike in these often signals either an application bug or an active probing attempt; and the tenant context itself on every query, so an incident review can reconstruct exactly what a given session was scoped to.

Postgres's own statement logging (log_statement, log_line_prefix) can include %d for the database and custom fields for session variables, but it won't include your app.tenant_id setting by default. You have to add it to log_line_prefix explicitly, or rely on pgaudit's more structured output. Either way, treat this as a deliberate configuration step, not something you'll get automatically by turning RLS on.

Where Row Level Security Falls Short

RLS has sharp edges worth knowing before you commit to it as your primary isolation strategy.

Superusers and any role granted BYPASSRLS skip policy enforcement entirely, by design, which means your database backup jobs, migration tooling, and any role your ORM connects as in development need explicit attention, not an assumption that RLS "just handles it."

Policy expressions that call non-immutable functions can silently degrade query plans, turning an index scan into something slower without an obvious error message. EXPLAIN ANALYZE is the only reliable way to catch this, and it needs to become a habit, not a one-time check when policies first go live.

RLS also adds real complexity to schema migrations. Adding a column, changing a constraint, or restructuring a table now means checking whether existing policies still apply the way you expect, because a policy written against one column layout can silently stop matching rows correctly after a schema change if nobody re-verifies it.

Finally, RLS is a Postgres-specific feature. If your application logic ever needs to run the same isolation guarantee against a different datastore, a cache layer, a search index, a queue, none of that inherits the protection automatically. The guarantee stops at the Postgres boundary.

Row Level Security's Impact on Backups and Replication

Backups taken through standard tools like pg_dump run as a role with sufficient privileges to read the full table, which typically means the backup process itself needs to bypass RLS to capture every tenant's data in one dump. That's expected and fine for a backup job, but it means the role running your backup pipeline deserves the same scrutiny as any other BYPASSRLS role: locked down, audited, and never reused for anything else.

Streaming replication and logical replication both operate below the RLS layer entirely. Physical replication copies data at the storage level, so policies never enter into it: your replica has the exact same data as the primary, full stop. Logical replication publishes changes based on table and row identity, not on any tenant-aware filter, so a subscriber gets every row a publication is configured to send regardless of policy definitions.

The practical implication: if you're using logical replication to feed a subset of tenant data somewhere else, deliberately, RLS policies won't do that filtering for you. You need to build that selection into the publication definition itself, or filter downstream after the data lands. Treat "replication respects my tenant boundaries" as an assumption to verify, not a guarantee that comes free with the feature.

What I'd Prioritize First If I Were You

Most articles on this topic treat RLS as a checkbox: turn it on, write a policy, done. That's the part that gets you a demo that works. It's not the part that keeps you out of an incident review six months later.

The advice I'd push back on hardest is the "set it and forget it" framing around tenant context. The actual failure mode I've seen isn't a missing policy, it's a SET instead of a SET LOCAL somewhere in an older code path that nobody remembers writing, sitting quietly in a connection pool until traffic gets heavy enough for that pooled connection to get reused across two different tenants in the same second. That bug is invisible in every test that runs one request at a time.

If you're deploying RLS for the first time, prioritize three things in this order: transaction-scoped context on every single code path, a fail-closed helper function, and an EXPLAIN ANALYZE habit that outlives the initial rollout. Policies themselves are usually the easy part. The context-passing discipline is where real incidents come from, and it's the part conventional advice glosses over fastest.

— Hanad Kubat

Need Help Getting Row Level Security Into Production?

Hanad Kubat is the alternative to a traditional agency for getting multi-tenant isolation right the first time: one senior engineer, a fixed price, and code you own from the first commit, instead of a project-manager layer between you and whoever actually writes the SQL.

If your prototype was built in Lovable, Bolt, v0, Replit, Cursor, Bubble, Softr, or Glide and it's now hitting the parts that have to be right, the login, the payments, the tenant boundaries, the prototype becomes the spec for the rebuild. A prototype audit runs €1,500, takes three to five days, and is credited against the build if you move forward. Full builds start at €12,000; rescue rebuilds where an agency or AI tool handed you code nobody can maintain run €12,000 to €20,000. Both run weeks, not months, with scope frozen at kickoff and no surprise invoices along the way.

What's included: deployable RLS policies with matching USING and WITH CHECK clauses, the index changes to keep them fast, isolation tests, and a documented admin access pattern. What's not included: juniors, an agency layer, or offshore markup, since there's no overhead structure here to markup against in the first place. If you want a second opinion on a schema that's already live, or a plan for one that isn't yet, start with a prototype audit.

Sources

PostgreSQL: Documentation: 18: 5.9. Row Security Policies

FAQ

What Is Postgres Row Level Security?

Row level security is a Postgres feature that lets you attach a filtering condition to a table, so every query automatically excludes rows that don't match, most commonly used to isolate tenant data in a shared-schema database.

Does RLS Replace Application-Level Access Control?

No. RLS guarantees data isolation at the database layer, but authentication, session handling, and user-facing permission checks still belong in application code.

Why Does My RLS Policy Not Apply to the Table Owner?

Postgres exempts table owners and superusers from policies by default; running ALTER TABLE ... FORCE ROW LEVEL SECURITY closes that exemption.

Is Row Level Security Slow?

Not inherently. Performance problems almost always trace back to a missing index on the tenant_id column rather than the policy check itself, according to AWS's guidance on multi-tenant isolation.

Can I Use RLS With a Connection Pool Like PgBouncer?

Yes, as long as tenant context is set with SET LOCAL or set_config inside each transaction rather than a session-level SET, so the value never leaks to the next pooled request.

What Happens if I Enable RLS Without Setting Tenant Context First?

Every query returns zero rows, since the default-deny behavior blocks access when no context value is present, which is why deployment order matters: ship the context-setting code before enabling RLS.