Row-level security (RLS) is a database security mechanism that restricts which rows of a table a user can see or modify by attaching policies that the database evaluates on every query. In database security, it moves authorization from application code into the database engine itself, so the rule applies no matter which client or tool issues the query.
Ordinary privileges answer the question “may this role access this table?”. Row-level security adds a second question: “which rows of this table?”. It is widely used in multi-tenant applications, regulated data and reporting platforms where many users share one table.
How row-level security filters rows
A policy is a predicate, a Boolean expression evaluated against each row in the context of the current session. When a user queries a protected table, the engine silently adds the predicate to the query, so rows that fail it are invisible, as if they did not exist.
Policies usually distinguish between reading and writing. A filter predicate decides which existing rows are visible to SELECT, UPDATE and DELETE, while a check predicate decides whether new or modified rows are allowed, preventing a user from inserting data on behalf of another tenant.
| Component | Purpose | Example |
|---|---|---|
| Policy | Named rule attached to a table | tenant_isolation on orders |
| Filter predicate | Limits visible rows | tenant_id equals the session tenant |
| Check predicate | Validates inserted or updated rows | New rows must carry the session tenant |
| Session context | Identifies the current user or tenant | A session variable set at login |
| Bypass privilege | Exempts trusted roles | Owner or BYPASSRLS role |
Row-level security in major database systems
Implementations follow the same idea with different syntax and defaults. The main differences lie in how policies combine and whether the table owner is exempt.
| System | Mechanism | Notable behavior |
|---|---|---|
| PostgreSQL | CREATE POLICY with ENABLE ROW LEVEL SECURITY | Owners bypass unless FORCE is set |
| SQL Server and Azure SQL | Security policy with inline table-valued predicate functions | Separate filter and block predicates |
| Oracle Database | Virtual Private Database policies | Predicate generated by a PL/SQL function |
| MySQL and MariaDB | No native RLS | Emulated with views or definer routines |
In PostgreSQL, multiple permissive policies are combined with OR, while restrictive policies are combined with AND. Enabling RLS on a table without any policy results in default deny: ordinary users see no rows at all.
How it relates to roles, views and encryption
Row-level security works alongside other controls rather than replacing them. A database role decides which tables and columns a user can reach, RLS narrows the rows, and database encryption protects data at rest or in transit. A database audit records what users actually did.
| Control | Granularity | Enforced by | Main limitation |
|---|---|---|---|
| Database role privileges | Table or column | Database | All or nothing per table |
| Row-level security | Individual rows | Database | Requires reliable session context |
| Filtered views | Rows and columns | Database | Base tables must be hidden separately |
| Application filtering | Any | Application code | Bypassed by direct database access |
| Encryption | Data at rest or fields | Storage or application | Does not decide who sees rows |
The decisive advantage of RLS over application filtering is defense in depth. Reporting tools, ad hoc SQL consoles and new services all connect to the same tables, and a policy in the engine protects them all without each one having to reimplement the same filter correctly.
Designing policies for multi-tenant systems
In a shared-schema multi-tenant design, every tenant table carries a tenant identifier column, and one policy compares it with the tenant set in the session. The application sets that value at the start of each request, for example with SET app.tenant_id in PostgreSQL or sp_set_session_context in SQL Server.
With connection pooling, the session context must be reset for every request, otherwise a reused connection can carry the previous tenant’s identity. Setting the value inside a transaction with a transaction-local scope removes most of this risk.
Performance depends on the predicate. A simple equality on an indexed tenant column adds almost no cost, while predicates that call functions or subqueries on every row can make scans several times slower. Leading composite indexes with the tenant column keeps policy filtering efficient.
Pitfalls and misconceptions
- Superusers and table owners often bypass policies, so tests run as an administrator prove nothing.
- Forgetting a check predicate lets users insert rows they cannot read, a quiet form of data tampering.
- Error messages and timing can still leak information about hidden rows, for example through unique constraint violations.
- Treating RLS as a complete authorization system: it does not replace column privileges or application logic.
- Policies that depend on user-controlled session variables are only as secure as the code that sets them.
A non-technical view
Imagine a large filing cabinet shared by many companies. Row-level security is a clerk who checks each visitor’s badge and hands over only the folders that belong to that visitor’s company.
Visitors never see the other folders, even if they ask for “everything”, because the clerk filters every request. The rule lives with the cabinet, not with each visitor, so it applies whichever door they use.
A concrete example
A software company runs an invoicing service for about 2,000 small businesses in one PostgreSQL database, with a tenant_id column on every table. Earlier, each query in the application added a tenant filter by hand, and a missed filter in one report once exposed another customer’s invoices during testing.
The team enables row-level security with one policy per table that compares tenant_id with a transaction-local setting written at the start of every request. The application role has no bypass privilege, and tests run under that role. When a developer later writes a new report without any tenant filter, it still returns only the current customer’s rows, and the risk of cross-tenant leaks drops sharply.