PostgreSQL Row-Level Security: A Practical Multi-Tenant Guide

A multi-tenant application stores data for many customers in one system. That model is efficient, but it creates a hard security requirement: every query must return only rows belonging to the current tenant. A forgotten tenant filter can become a cross-tenant leak. PostgreSQL row-level security, or RLS, moves the isolation rule into the database, where a policy is evaluated whenever protected rows are read or changed. RLS does not replace authentication, application authorization, least-privilege roles, secure secrets, or testing. It adds a strong defense-in-depth boundary when those controls work together.

What PostgreSQL row-level security does

Traditional grants answer broad questions: can a role select from this table, insert rows, update columns, or delete records? RLS answers a narrower question for every row: may this role operate on this particular record? A policy uses USING to control which existing rows are visible to SELECT, UPDATE, and DELETE. WITH CHECK controls which rows may be inserted and what values an UPDATE may produce. When RLS is enabled but no applicable policy exists, PostgreSQL defaults to deny. Normal table privileges remain necessary; policies filter access but do not grant SELECT or UPDATE themselves.

Table owners normally bypass RLS. Superusers and roles carrying BYPASSRLS also bypass it. ALTER TABLE ... FORCE ROW LEVEL SECURITY makes the owner subject to policies during ordinary access. This is crucial in testing: a test connected as the owner or postgres can appear successful while exercising no policy. Use a dedicated migration owner and a separate, non-owner runtime role. Never give the application superuser or BYPASSRLS merely to simplify deployment.

Design tenant-owned tables explicitly

In a shared-schema SaaS design, every tenant-owned table should carry a stable tenant_id, commonly a UUID, declared NOT NULL. Include tenant identity on projects, tasks, attachments, comments, and other customer data. Foreign keys should prevent a child row from referencing a parent belonging to another tenant. A basic projects table can include id uuid primary key, tenant_id uuid not null, and name text not null. Index tenant_id because policy-filtered queries commonly use it. Composite indexes such as tenant_id with status or created_at may better support real access patterns; verify them with EXPLAIN and representative data.

Pass trusted tenant context safely

The database needs trusted request context. One practical pattern stores the authenticated tenant UUID in a transaction-local PostgreSQL setting and references that setting from policies. The application must derive the value from a verified session or token, never directly from a request body, URL parameter, or client-supplied header. A transaction can run BEGIN; SELECT set_config('app.current_tenant','8c02d8b8-...',true); then execute tenant-scoped queries and COMMIT. The true argument makes the value local to that transaction.

Transaction-local scope matters with connection pools. A session-level SET can survive after a connection returns to the pool and contaminate a later request. Assign context and execute every dependent statement inside the same explicit transaction. Test timeouts, exceptions, rollbacks, retries, and pool reuse. If a SECURITY DEFINER function sets context, validate its arguments, fix its search_path, restrict schema creation, control ownership, and limit EXECUTE privileges. Privileged helper functions are part of the security boundary.

Create a tenant isolation policy

Enable protection with ALTER TABLE projects ENABLE ROW LEVEL SECURITY and, where appropriate, ALTER TABLE projects FORCE ROW LEVEL SECURITY. A combined policy can use CREATE POLICY tenant_projects ON projects FOR ALL TO app_user USING (tenant_id = current_setting('app.current_tenant', true)::uuid) WITH CHECK (tenant_id = current_setting('app.current_tenant', true)::uuid). USING blocks viewing, changing, or deleting another tenant's rows. WITH CHECK blocks inserting or updating a row into another tenant. With missing_ok set to true, absent context returns NULL, and the comparison does not pass.

Separate policies by command when permissions differ. Users may read every row in their tenant but update only records assigned to them. PostgreSQL combines permissive policies with OR and restrictive policies with AND. Keep policy names explicit, document the intended truth table, and avoid overlapping rules no reviewer can explain. Background jobs need an equally deliberate design: tenant-specific workers can use normal context, while rare cross-tenant billing or maintenance tasks should use separate, audited credentials.

Common RLS failures

The first failure is testing as a superuser, which bypasses policies. The second is protecting SELECT while forgetting INSERT, UPDATE, upsert, bulk operations, stored procedures, and ORM helpers. The third is trusting a client-selected tenant identifier: if an attacker controls app.current_tenant, the policy faithfully enforces the attacker's choice. Resolve membership server-side after authentication. Validate token signature, issuer, audience, expiry, subject, and tenant relationship before setting context. The fourth failure is leaking session context through pooling. The fifth is assuming RLS protects backups, exports, caches, logs, search indexes, analytics stores, object storage, or third-party systems. It does not.

Test isolation as a security property

Begin with positive tests: Tenant A can read and modify its own data. Emphasize negative tests: Tenant A cannot select, update, delete, or attach children to Tenant B's records. Cover missing context, malformed context, expired authentication, guessed object IDs, pool reuse, rollback, background jobs, support tools, views, functions, and migrations. Run tests using the same class of role deployed in production. Alternate tenants over a deliberately small connection pool to reveal context leakage quickly.

Automate a policy inventory. Query pg_tables and pg_policies to confirm that every tenant table has RLS enabled and expected policies exist. For example, SELECT schemaname, tablename, rowsecurity FROM pg_tables WHERE schemaname='public' ORDER BY tablename. Add this verification to CI or deployment checks because a migration can add an unprotected table while application tests still pass. Test query plans too: policy predicates participate in planning, tenant sizes may be uneven, and security must remain correct even when performance degrades.

Deployment checklist

Before release, confirm every tenant-owned table has a NOT NULL tenant_id, suitable foreign keys, RLS enabled, and policies for every required command. Confirm the runtime role is not owner, superuser, or BYPASSRLS. Use transaction-local tenant context and verify its trusted source. Test pooled connections and administrative paths. Encrypt backups, limit access, keep sensitive row values out of logs, preserve tenant boundaries in analytics, record privileged support access, and alert on unexpected use of bypass roles. Document how emergency access is approved, used, and revoked.

How RLS fits application authorization

Keep changing business rules in the application where they remain understandable: who may invite users, approve invoices, or edit settings. Use RLS for stable database invariants such as keeping rows inside the authorized tenant and, when useful, simple ownership rules. Duplicating every product rule in SQL can make change difficult; relying only on application query filters creates a fragile single layer. A strong design combines authenticated identity, server-side membership validation, least-privilege database roles, RLS, secure credentials, auditable privileged operations, and automated negative tests.

Frequently asked questions

Does RLS replace WHERE tenant_id filters?

RLS supplies the database-enforced boundary. Explicit filters can still make application intent clear and help query design. Treat the policy as the invariant that must hold even when application code forgets its filter.

Should every tenant have a separate database?

Separate databases can offer stronger isolation and simpler per-tenant backup or residency controls, but they add operational overhead. Shared-schema RLS suits many-tenant systems whose teams can rigorously manage roles, context, policies, and tests. Choose based on risk, scale, compliance, recovery needs, and operational capacity.

Can table owners bypass RLS?

Yes. Owners normally bypass policies. FORCE ROW LEVEL SECURITY subjects an owner to policies during ordinary access, while superusers and BYPASSRLS roles still bypass. Keep the production application role separate from the owner.

What happens when no policy applies?

When RLS is enabled and no applicable policy exists, PostgreSQL defaults to deny. The role also needs normal table privileges; RLS never grants table access on its own.

Is RLS enough to prevent every cross-tenant leak?

No. It protects rows accessed through covered PostgreSQL tables and roles. Caches, files, backups, logs, search systems, analytics copies, privileged functions, and application responses need separate controls and tests.

Build tenant isolation you can prove

RLS is most valuable when treated as a testable system property rather than a one-time SQL feature. Define the trust boundary, keep tenant context transaction-local, restrict runtime roles, cover reads and writes, and run cross-tenant negative tests continuously. NeoAI Tech helps teams turn cloud, data, DevOps, and cybersecurity concepts into practical implementations. Use this guide as a starting checklist for your next multi-tenant PostgreSQL review.

Related: GitHub Actions Secrets Management

Related: SBOM for DevOps

Authoritative references

PostgreSQL Row Security

PostgreSQL CREATE POLICY

PostgreSQL SET

PostgreSQL Administration Functions

OWASP Multi-Tenant Security

OWASP Authorization