Row-Level Security: Database Authorization at the Data Layer
What Row-Level Security Does
Row-level security (RLS) lets the database enforce which rows a user can see or modify based on policies defined at the table level. Instead of filtering data in application code—checking if user_id matches the session, or if a role permits access—the database applies those rules automatically to every query.
This moves authorization down the stack. When RLS is active on a table, queries only return rows the current database user is permitted to access. The application no longer needs to remember to add WHERE tenant_id = ? to every query. The database handles it.
Why Authorization Belongs in the Database
Application-layer filtering is brittle. A developer writes a query, forgets the filter, and suddenly users see data they shouldn’t. The mistake doesn’t surface in code review because the query looks fine—it’s just missing a WHERE clause.
RLS eliminates that class of bug. Once a policy is in place, every query on that table respects it. No missing filter means no unauthorized access. The database becomes the single enforcement point, rather than dozens of query builders scattered across services.
This is especially useful in multi-tenant systems, where one database holds data for many customers. Without RLS, every query must include the tenant ID check. With RLS, the database session identifies which tenant it represents, and the policy restricts access automatically.
How Policies Work
A row-level security policy is a SQL expression that returns true for rows the user can access. In Postgres, you define a policy on a table like this:
CREATE POLICY tenant_isolation ON documents
USING (tenant_id = current_setting('app.tenant_id')::uuid);
When a user queries documents, Postgres implicitly adds the policy condition to the query. The database evaluates current_setting('app.tenant_id') for the session and only returns matching rows.
Policies can be more complex. You can write different policies for SELECT, INSERT, UPDATE, and DELETE. You can apply policies to specific roles. You can check multiple conditions, reference other tables, or call functions. The policy is just SQL, so any predicate the database can evaluate works.
Session Context and Identity
RLS depends on the database knowing who the current user is. The simplest approach uses database roles: each application user connects as a different database user, and policies check the current role. This works well when the number of users is small and stable.
For applications with many end users, connecting as different database roles doesn’t scale. Instead, the application connects with a single database user but sets session variables to identify the tenant or user. The policy reads those variables.
Postgres uses current_setting() to read session variables. The application sets them at the start of each transaction:
SET LOCAL app.tenant_id = '550e8400-e29b-41d4-a716-446655440000';
The LOCAL keyword ensures the variable resets after the transaction, preventing one request from leaking context into another when using connection pools.
Performance Considerations
RLS adds a condition to every query. If the policy references an unindexed column, queries become full table scans. A tenant isolation policy that checks tenant_id requires an index on tenant_id.
Complex policies—those that join other tables or call functions—can slow queries down. The policy executes for every row candidate, so expensive checks multiply cost. Keeping policies simple and indexable matters.
Some databases optimize RLS by pushing the policy predicate down into the query plan early, letting the optimizer use indexes effectively. Others apply the policy as a post-filter, which is slower. Understanding how your database handles RLS affects whether it’s practical in performance-sensitive paths.
When to Use RLS
RLS makes sense when authorization logic is consistent across the application and naturally expressed as data-level predicates. Multi-tenant SaaS applications are the canonical case: tenant isolation is the same rule everywhere, and forgetting it is catastrophic.
It’s less useful when access control is complex and varies by feature. If viewing a document requires checking permissions, group memberships, sharing links, and feature flags, encoding that in SQL becomes awkward. Application-layer authorization is often clearer for conditional logic.
RLS also doesn’t replace authentication. The application still verifies identity and sets session context correctly. RLS only enforces rules after the database knows who’s asking.
The Defense-in-Depth Argument
Even teams that implement authorization in application code add RLS as a backstop. The application does the real work—checking roles, computing permissions, logging access—but the database policy catches mistakes.
This is defense in depth. If a developer writes a direct SQL query for a one-off report and forgets the tenant filter, the policy prevents data leakage. If a caching bug serves stale session data, the policy limits the blast radius. RLS doesn’t replace careful code, but it reduces the cost of errors.
Databases are good at enforcing invariants. Access control is an invariant. Putting it in the data layer makes it harder to bypass.