Module 8 · Security

Row-Level Security and Tenant Isolation

Lesson 23 of 30 12 min read Updated

Row-level security, usually shortened to RLS, lets PostgreSQL decide which rows a role may see or change after ordinary table privileges have already allowed access to the table. In a multi-tenant application, that makes tenant isolation a database rule instead of only an application convention. The outcome is concrete: a session acting for tenant alpha can run the same query as a session acting for tenant beta, but PostgreSQL silently filters each result to the rows allowed by policy.

This lesson fits the security section of the PostgreSQL course because RLS combines privileges, roles, session settings, query rewriting, and constraints. It does not replace authentication, schema design, or careful grants. It gives you a second enforcement point at the storage boundary, which is especially valuable when many code paths read the same shared tables.

How PostgreSQL Enforces RLS

RLS is enabled per table with ALTER TABLE ... ENABLE ROW LEVEL SECURITY. After that, PostgreSQL evaluates row security policies for roles that do not bypass RLS. The table owner normally bypasses RLS unless FORCE ROW LEVEL SECURITY is set. Superusers and roles with BYPASSRLS also bypass it, so those roles should not be used by application connections.

A policy has two important expressions. USING controls which existing rows are visible or targetable for SELECT, UPDATE, and DELETE. WITH CHECK controls whether new row versions created by INSERT or UPDATE are allowed. If no applicable policy exists on an RLS-enabled table, access is denied by default. If multiple permissive policies apply, PostgreSQL combines them with OR. Restrictive policies can also be added and are combined more narrowly, but most tenant designs are clearest with one explicit tenant policy per table operation.

Policy expressions are attached to the query by the database. Conceptually, a tenant predicate such as tenant_id = current_setting('app.tenant_id')::uuid is applied whenever the protected table is accessed. The optimizer may still choose indexes and join plans, so tenant columns should be indexed with the access patterns they support. The policy is not a magic post-filter added after all rows are read; it participates in planning as a security condition, subject to PostgreSQL’s normal rules about functions, leakproof operators, and permissions.

Policy Anatomy

A typical tenant-isolated table has a tenant identifier, a runtime way to tell PostgreSQL which tenant is active, ordinary table grants for the application role, and RLS policies that compare the two. The tenant id may come from a login role, a connection pool setting, or a transaction-local custom setting. The custom setting pattern is common with pooled application roles because the database connection role stays stable while each request sets the current tenant.

CREATE SCHEMA IF NOT EXISTS app;

CREATE TABLE IF NOT EXISTS app.projects (
    project_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id uuid NOT NULL,
    name text NOT NULL,
    status text NOT NULL CHECK (status IN ('active', 'archived'))
);

CREATE INDEX IF NOT EXISTS projects_tenant_status_idx
    ON app.projects (tenant_id, status);

CREATE ROLE app_user NOLOGIN;
GRANT USAGE ON SCHEMA app TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON app.projects TO app_user;

ALTER TABLE app.projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE app.projects FORCE ROW LEVEL SECURITY;

CREATE POLICY projects_tenant_select
    ON app.projects
    FOR SELECT
    TO app_user
    USING (tenant_id = current_setting('app.tenant_id', true)::uuid);

CREATE POLICY projects_tenant_insert
    ON app.projects
    FOR INSERT
    TO app_user
    WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::uuid);

CREATE POLICY projects_tenant_update
    ON app.projects
    FOR UPDATE
    TO app_user
    USING (tenant_id = current_setting('app.tenant_id', true)::uuid)
    WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::uuid);

The true argument to current_setting returns null instead of raising an error when the setting is missing. The cast to uuid then makes malformed values fail. Some teams prefer a missing setting to raise immediately by omitting true. Either choice is acceptable if tests cover the behavior and the application sets the tenant before touching protected tables.

Example 1: Tenant-Scoped Reads

Start with two tenants in the same table. The SQL below inserts deterministic rows, sets the session’s active tenant, and runs a plain application query. Notice that the query does not include a tenant predicate. The policy supplies it.

INSERT INTO app.projects (tenant_id, name, status) VALUES
('00000000-0000-0000-0000-0000000000a1', 'Alpha billing', 'active'),
('00000000-0000-0000-0000-0000000000a1', 'Alpha archive', 'archived'),
('00000000-0000-0000-0000-0000000000b2', 'Beta portal', 'active');

SET ROLE app_user;
SET app.tenant_id = '00000000-0000-0000-0000-0000000000a1';

SELECT name, status
FROM app.projects
ORDER BY name;

The expected rows are Alpha archive and Alpha billing. Beta portal exists, but it is not visible to this role with this tenant setting. If the application also adds WHERE status = 'active', PostgreSQL applies both the application predicate and the RLS predicate.

Example 2: Inserts and Cross-Tenant Writes

USING is not enough for tenant isolation. Without WITH CHECK, a role might be able to create or move a row into a tenant it should not control. The insert policy above rejects rows whose tenant_id does not match the current session setting.

SET ROLE app_user;
SET app.tenant_id = '00000000-0000-0000-0000-0000000000a1';

INSERT INTO app.projects (tenant_id, name, status)
VALUES ('00000000-0000-0000-0000-0000000000b2', 'Wrong tenant write', 'active');

The expected behavior is an error like new row violates row-level security policy. That error is desirable. The session is acting for tenant alpha while trying to create a beta row. A safer application interface usually avoids accepting tenant_id from the client at all; it derives it from authenticated request context and binds it server-side.

Example 3: Updates Cannot Move Rows Across Tenants

Updates need both sides of the policy. USING says which existing rows can be targeted. WITH CHECK says what the updated row is allowed to become. Together they prevent a tenant from modifying someone else’s rows and from changing its own row into another tenant’s row.

SET ROLE app_user;
SET app.tenant_id = '00000000-0000-0000-0000-0000000000a1';

UPDATE app.projects
SET tenant_id = '00000000-0000-0000-0000-0000000000b2'
WHERE name = 'Alpha billing';

The expected result is again an RLS violation. If the update instead changes status from active to archived while keeping the same tenant id, it succeeds for alpha’s row. A useful test suite should assert both results, because one proves allowed work still works and the other proves the isolation boundary rejects tenant movement.

Example 4: Shared Reference Rows

Some tables need tenant-owned rows plus global rows, such as feature flags, catalog entries, or templates. Do not overload tenant isolation accidentally. Model the rule directly and test it.

CREATE TABLE IF NOT EXISTS app.templates (
    template_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id uuid,
    name text NOT NULL,
    body text NOT NULL,
    CHECK (tenant_id IS NOT NULL OR name LIKE 'global:%')
);

GRANT SELECT ON app.templates TO app_user;
ALTER TABLE app.templates ENABLE ROW LEVEL SECURITY;

CREATE POLICY templates_read
    ON app.templates
    FOR SELECT
    TO app_user
    USING (
        tenant_id = current_setting('app.tenant_id', true)::uuid
        OR tenant_id IS NULL
    );

This design says global templates have tenant_id IS NULL and tenant templates have a tenant id. The trade-off is that every reader sees global rows. If some tenants should not see some shared rows, use an explicit membership or entitlement table instead of a null shortcut.

Design Choices and Trade-Offs

The first design choice is role strategy. One database role per tenant makes policies simple because current_user can identify the tenant, but it creates many roles and complicates pooling. One application role plus a transaction-local tenant setting works well with connection pools, but the application must set and clear that setting correctly for every request. Prefer SET LOCAL app.tenant_id = ... inside a transaction when possible so the value disappears at commit or rollback.

The second choice is tenant key placement. Put tenant_id directly on high-value tenant tables even if it can be inferred through joins. RLS policies that must look through several tables are harder to reason about and may cost more. Foreign keys should include tenant-aware relationships where appropriate so that a row cannot point to another tenant’s parent.

The third choice is whether RLS is the primary isolation boundary or a backup boundary. Separate databases or schemas can provide stronger operational separation for large or regulated tenants, but they raise migration and fleet-management cost. Shared tables with RLS are efficient and simpler to evolve, but a policy mistake can affect many tenants. Many systems use tiers: shared RLS for standard tenants and stronger physical isolation for exceptional tenants.

Failure Modes and Troubleshooting

Symptom: application queries return zero rows after deployment. Likely cause: the app role has table privileges but no tenant setting, or the setting is malformed. Diagnose: run SELECT current_user, current_setting('app.tenant_id', true); in the same connection path, then test a known tenant id. Correct: set the tenant at transaction start and reject requests before database access when tenant context is missing.

Symptom: a migration or maintenance job ignores RLS and sees every row. Likely cause: the job connects as table owner, superuser, or a role with BYPASSRLS. Diagnose: inspect pg_roles.rolbypassrls, table ownership, and whether FORCE ROW LEVEL SECURITY is enabled. Correct: use a dedicated maintenance role with reviewed privileges, or deliberately disable RLS only inside a controlled migration that records why it is safe.

Symptom: queries become slow after RLS is enabled. Likely cause: tenant predicates are not supported by indexes, or policies call expensive functions per row. Diagnose: compare EXPLAIN (ANALYZE, BUFFERS) for representative tenant queries and check whether scans filter many rows. Correct: add composite indexes starting with tenant_id for common access paths and keep policy expressions stable and simple.

Symptom: one request sees another tenant’s rows in a pooled application. Likely cause: session-level SET leaked across reused connections. Diagnose: log the connection pool checkout path and verify whether SET LOCAL is inside a transaction. Correct: wrap each request in a transaction, use SET LOCAL, and reset connections on pool return if the framework cannot guarantee cleanup.

Security and Performance Implications

RLS reduces the blast radius of application bugs, report queries, and missed predicates, but it is only as strong as the roles and policies around it. Never let normal application code connect as a superuser, table owner, or migration owner. Keep policy functions free of side effects and avoid granting users access to helper functions that reveal tenant membership indirectly.

For performance, index the policy columns and measure with realistic tenant sizes. A small tenant and a large tenant can have very different plans. RLS also affects debugging: a developer may think data is missing when it is merely hidden. Make tenant context visible in structured logs without logging secrets or personal data.

Hands-On Lab

Prerequisites: a disposable PostgreSQL database where you can create schemas, tables, roles, and policies. Do not run this lab in a shared production database.

  1. Create the app.projects table, index, app_user role, grants, and policies from the policy anatomy section.
  2. Insert the three sample project rows as a setup or owner role.
  3. Run SET ROLE app_user and set app.tenant_id to the alpha tenant id.
  4. Run the read example and verify that only the two alpha rows are returned.
  5. Run the cross-tenant insert and cross-tenant update examples. Verify that both fail with an RLS policy error.
  6. Change app.tenant_id to the beta tenant id and verify that only Beta portal is visible.
  7. Run EXPLAIN on SELECT * FROM app.projects WHERE status = 'active' and confirm that the tenant/status index is available to the planner.

Cleanup: in the disposable database, run RESET ROLE, then drop the schema and role with DROP SCHEMA app CASCADE; and DROP ROLE app_user;. If the role owns objects in your environment, reassign or drop those objects first.

Assessment Exercises

  1. A table has RLS enabled and grants SELECT to app_user, but no policies. What should app_user be able to read, and why?
  2. Write a policy pair for a tenant-owned invoices table that allows reads and inserts only for the current tenant. Explain where USING and WITH CHECK differ.
  3. Your connection pool uses session-level SET app.tenant_id. Describe a test that would prove whether tenant context leaks between requests.
  4. A query on an RLS table scans millions of rows and filters most of them. Which policy and indexing details would you inspect first?
  5. When would you choose separate databases instead of shared tables with RLS for tenant isolation?

Summary

PostgreSQL row-level security makes tenant isolation an enforceable database behavior. Enable it on tenant tables, grant only needed table privileges, avoid bypass roles for application traffic, and write policies that cover both visible rows and new row versions. The strongest designs keep tenant context explicit, index the tenant predicates, test denied paths as carefully as allowed paths, and document any role that is permitted to bypass the boundary.

Progress is saved in this browser.