What is row-level security in a relational database, and when would you enforce tenant filtering with database row policies instead of a WHERE clause in application queries?

## The gap it fills The classic privilege model is coarse. `GRANT SELECT ON invoice TO app_user` says the role may read the table — all of it. Column privileges narrow *which columns*, never *which rows*. Yet almost every application has row-scoped authorization: a tenant sees its own invoices, a clinician sees their own patients. Traditionally that lives in the application: every query carries `AND tenant_id = ?`. The rule is then only as strong as the least careful query ever written. Row-level security moves the rule into the table definition. You turn the feature on for a table and attach one or more **policies**. A policy is a named boolean SQL expression over the row's own columns plus session context (the current database role, a session variable holding a tenant id, a lookup into a membership table). Whenever any statement touches the table, the engine adds the applicable policy expressions to it. Rows failing the expression simply are not there: SELECT does not return them, UPDATE and DELETE do not match them. ## Shape of a policy Two steps. First enable RLS on the table. Second create policies. A policy names the commands it applies to (SELECT, INSERT, UPDATE, DELETE or ALL), the roles it applies to, a `USING` predicate (which existing rows are visible/touchable) and optionally a `WITH CHECK` predicate (which row values may be written). A critical default: **enabling RLS with no policy means deny-all**, not allow-all. Non-exempt roles see an empty table. That fail-closed default is deliberate; it is also the number-one "my table vanished" incident after a migration. Multiple policies for the same command are, by default, **permissive** — they are OR-ed together, so each additional policy *widens* access. Engines also offer restrictive policies that AND in, used to layer a mandatory condition (for example "and the row is not soft-deleted") on top of permissive ones. ## Why put it in the database 1. **Defence in depth.** A new endpoint, an ad-hoc report, an ORM query built without the tenant scope, or an SQL injection hole all fail closed instead of returning the whole table. 2. **It covers every access path.** Analysts with psql, a BI connection, an ETL job and a support engineer all get the same rule. Application-layer filtering only protects the application. 3. **It survives refactors.** The rule is one object in the schema, reviewed once, rather than a convention that must hold in thousands of call sites. 4. **It enables direct exposure.** Architectures that put an HTTP layer straight over SQL depend on RLS for authorization entirely. ## What it does not do - It is not a substitute for `GRANT`. The role still needs table privileges; RLS narrows what those privileges reach. - It does not hide column *values* — masking a card number is a different mechanism. - It is bypassed by superusers, by roles flagged to bypass it, and by the table owner unless you force RLS on the owner too. - It cannot protect you from an application that can set the tenant context to any value it likes; the context must be set by trusted code. ## Costs to weigh The predicate is evaluated per row, so it must be index-friendly — a policy on `tenant_id` is worthless for performance unless `tenant_id` leads the useful indexes. Policies that call subqueries or non-inlinable functions can turn into per-row work. Plans also become harder to reason about because the predicate is invisible in the SQL text. And tests prove nothing if they run as an exempt role: your test harness must connect as the same non-exempt role production uses. ## The pragmatic position Keep both layers. The application keeps its explicit tenant predicate — it documents intent and gives the planner the same qual up front — and RLS sits underneath as the enforcement that does not depend on anybody remembering.