1.3.2 How RLS Rewrites Queries (Mechanism)

Once you put row-level security (RLS) on a table, queries that read from that table don't run the way the user wrote them. A single line SELECT * FROM accounts comes out of the rewriter looking more like SELECT * FROM accounts WHERE owner = current_user. The user never wrote a WHERE clause, but it's there. What RLS as a feature does is covered in 7.5. Here we look at what that "silently changes" actually is, inside Postgres.

The qual the rewriter adds

The core of RLS is simpler than it sounds. It takes the expression written in the policy and grafts it onto the target table as a qual. That's the whole mechanism. What PG calls a qual is a shorthand for a boolean expression that filters rows, the kind of thing that lives in a WHERE clause (short for "qualification", and the same concept as what the SQL standard usually calls a predicate). If a query is the whole tree carrying SELECT/FROM/WHERE and so on, a qual is one expression node inside that tree. RLS doesn't run a separate enforcement engine. It plants the policy's expression into the query tree and deforms the tree itself.

This work happens at the same site as view expansion (covered in 1.3.1). The RIR pass, driven by fireRIRrules, finishes everything else first (CTE handling, view expansion, sublink recursion) and then, as the last step, applies RLS policies. It walks the query's range table one more time and, for each RTE that points at a regular relation, attaches the policy's conditions.

The place those conditions go is the securityQuals field on the RTE. The comment in the PG source describes the field this way:

/*
 * securityQuals is a list of security barrier quals (boolean expressions),
 * to be tested in the listed order before returning a row from the
 * relation.  It is always NIL in parser output.  Entries are added by the
 * rewriter to implement security-barrier views and/or row-level security.
 */

Two things stand out. First, securityQuals is filled by the rewriter, not the parser. Conditions appear on the RTE that weren't in the user's query at all. Second, RLS and security-barrier views share the same container. A security-barrier view (defined with CREATE VIEW ... WITH (security_barrier)) is one whose filter conditions the planner is forbidden from reordering against the user's WHERE clauses. Its purpose is essentially the same as RLS, so PG implements both on top of the same machinery. Even though 1.3.1 showed view expansion turning a view reference into a sub-query, the conditions of a security-barrier view and the conditions of an RLS policy end up in the very same securityQuals list. (The leaky view attack scenarios that security-barrier views guard against, and the operational pitfalls, are covered in 7.5.3.)

RLS fills the same container the same way. A "policy" in RLS is a catalog object created by CREATE POLICY. A single policy bundles five things together: a name, which commands it applies to (SELECT/INSERT/UPDATE/DELETE or ALL), which roles it applies to, the USING expression that filters existing rows, and the WITH CHECK expression that validates new rows. It's stored as one row in the pg_policy catalog, and it rides along when the table's relcache is loaded. The rewriter pulls the USING and WITH CHECK expressions out of that catalog row and grafts them onto the query tree.

Suppose the accounts table has this policy:

CREATE POLICY account_owner ON accounts
    FOR SELECT USING (owner = current_user);

The user runs SELECT * FROM accounts WHERE balance > 0. After the analyzer, the Query tree's RTE for accounts has an empty securityQuals. When the rewriter's RLS pass visits that RTE, it pulls the policy's USING expression owner = current_user, copies it, fixes up the Var references so they point at the right relation, and drops it into securityQuals. What the planner ends up seeing, conceptually, is something like:

SELECT * FROM accounts WHERE (owner = current_user) AND (balance > 0)

owner = current_user is a condition the user never wrote. The rewriter pulled it out of the policy and stuck it in.

One thing to flag, though. That SQL above is a conceptual view. PG isn't actually rewriting the user's SQL text into that form. The text the user submitted stays as it was. What gets transformed is the Query tree it was parsed into. The policy condition lands on the accounts RTE's securityQuals field inside that tree. The SQL above is just a human-readable rendering of the tree-level change. What pg_stat_statements records and what shows up in the logs is the original SQL the user typed. The RLS transformation happens at the tree level, not at the text level.

Why the rewriter, not somewhere else

Doing RLS as a query transformation is itself a design choice. Row-level access control can be built in other ways. One alternative is to add a separate filter stage in the execution engine that evaluates the policy condition as each row comes off the relation. PG didn't go that route. It does the work in the rewriter, planting the condition into the query tree.

The reason lies one step downstream, in the planner. Once the rewriter has folded the policy condition in as a normal qual, the planner doesn't see it as anything special. It looks like just another condition in the query. So if there's an index on the column, the planner can pick an index scan. If the condition can be pushed below a join, the planner can push it. It estimates selectivity from statistics like any other qual. The RLS condition benefits from the planner's full optimization machinery.

A separate runtime filter wouldn't get any of this. The filter tends to bolt onto the tail of an already-built plan, which means the table gets fully scanned before the policy condition is applied. There's no path to index the policy condition. Putting RLS in the rewriter is the choice that keeps access control inside the part of the query the planner can optimize.

There's also a reason fireRIRrules saves RLS for last. A policy's condition can contain a sub-query (for example, USING (owner IN (SELECT ...))), and adding such a condition means the infinite-recursion check needs to run again on the new condition. If RLS were folded into the view-expansion loop above, the tree walk for view expansion would end up visiting the sub-queries inside RLS conditions twice. Pulling RLS out into a separate pass after view expansion avoids that.

The qual that filters and the qual that checks

A policy can carry two kinds of condition, and the two land in different parts of the query tree and get enforced in different ways. This distinction is the most important point in the whole RLS mechanism.

The USING condition decides which of the existing rows in the table are visible. The owner = current_user from before is a USING. It goes into securityQuals and behaves like a WHERE clause. Rows that don't match are simply absent from the result. They disappear silently.

The WITH CHECK condition decides which new rows are allowed in. Rows added by INSERT or UPDATE have to satisfy it. This condition doesn't go into securityQuals. It goes into a separate list on the Query node called withCheckOptions, and it gets enforced differently. When a row violates the condition, the row isn't quietly dropped. PG raises an error.

Why is one silent and the other loud? The scenarios make it obvious. Suppose the user runs SELECT * FROM accounts and there happen to be 100 accounts they don't own. It's natural for the USING condition to remove those from the result. The user asked for "rows I can see," and the unseen rows being missing is the expected behavior. It's not an error.

Now suppose the user runs INSERT INTO accounts VALUES (...) trying to insert an account owned by someone else. If that row were silently dropped, the user would believe the INSERT succeeded while the data isn't actually there. The command reported success, but there's no result behind it. That's the worst kind of bug to leave for the application. So PG raises an error on WITH CHECK violations. The comment in rowsecurity.c spells out this intent: the check option exists specifically to make sure a policy violation is signaled as an error, because otherwise rows you were trying to add could silently disappear.

So USING is a visibility filter and WITH CHECK is a write gate. One is silent, the other is loud. The same RLS policy gets unpacked into different containers and different enforcement on the read path versus the write path.

There's one convenience. If a policy doesn't specify an explicit WITH CHECK, PG reuses the USING condition as the WITH CHECK. The reasoning is that "only rows you can read can be written" is a reasonable default in most cases. Specifying a separate WITH CHECK lets you write policies where the read condition and the write condition differ.

How policies get combined

A single table can carry multiple policies. By what rule does the rewriter combine them into one condition?

Policies come in two flavors: permissive and restrictive. A permissive policy says "allow if this condition is met," and multiple permissive policies are combined with OR. Any one of them passing is enough to make the row visible. A restrictive policy says "must satisfy this condition," and they're combined with AND. Violating any single restrictive policy hides the row. The rewriter filters the policies by command type and role, then ORs the permissive ones into a single chunk and ANDs the restrictive ones onto it.

Imagine the accounts table has three policies. One is a permissive policy that lets users see accounts they own, with the condition owner = current_user. Another is a permissive policy that also lets users see accounts flagged as public, with is_public = true. The third is a restrictive policy that blocks deleted accounts no matter what, with deleted_at IS NULL. When the user runs a SELECT, the rewriter combines the three into the following condition on accounts's securityQuals:

(owner = current_user OR is_public = true) AND deleted_at IS NULL

The two permissive conditions OR together to form an allow-channel ("either I own it, or it's public"), and the single restrictive condition ANDs on top to enforce "and also, not deleted." Adding more permissive policies widens the allow-channel, so more rows become visible. Adding more restrictive policies adds more gates that have to pass, so fewer rows become visible. The general shape is (permissive1 OR permissive2 OR ...) AND restrictive1 AND restrictive2 AND ....

There's one rule worth highlighting. If there are no permissive policies at all, no row is visible. That holds even if there are plenty of restrictive policies on the table. The reason is that permissive policies are the grounds for visibility, and restrictive policies are constraints that trim what's already allowed. With no grounds for visibility, there's nothing to trim. The rewriter handles this by putting a single false constant into securityQuals. The effective condition becomes WHERE false, and no row passes.

That's RLS's default-deny behavior. If you run ALTER TABLE ... ENABLE ROW LEVEL SECURITY on a table without creating a single policy, no one (except the table owner or a user with BYPASSRLS) sees a single row. It might feel counterintuitive that not writing a WHERE clause results in zero rows, but RLS doesn't sit on the same layer as the user's WHERE. It's an access-control gate sitting above that layer. The gate's default being deny is the same fail-closed principle behind firewalls and filesystem ACLs. Zero policies means zero rules granting passage, so zero rows pass through, which is internally consistent. Turning RLS on is in itself a declaration that "anything not explicitly allowed is forbidden," and that's the safe default that prevents one forgotten policy from leaking data.

Security barrier: quals have an order

That securityQuals is not just a list but an ordered list is the last piece of the RLS mechanism. The field comment said "tested in the listed order," and that order matters.

Why does it matter? After the rewriter plants the policy conditions, the planner picks each element of securityQuals out (in process_security_barrier_quals) and moves it into the rel's qual list. As it does so, it assigns each element a distinct security_level. Earlier elements get a lower level, later elements a higher level. Conditions from the user's own WHERE clause get an even higher level than any of those.

The level enforces evaluation order. Lower-level conditions have to be evaluated before higher-level ones. The concrete thing it prevents is a function the user wrote in WHERE getting evaluated before the RLS condition.

Picture this query:

SELECT * FROM accounts WHERE leaky_function(account_number);

Suppose leaky_function is a function that leaks its argument value somewhere. The leak can take several forms. A PL/pgSQL function might write the argument to the server log with RAISE NOTICE '%', $1. Or you can write a function that deliberately raises an error for certain argument values, like 1 / (CASE WHEN account_number = '...' THEN 0 ELSE 1 END) triggering a division-by-zero. When PG raises a runtime error like that, the error context includes the call site and the argument expression, so the argument value gets carried out into the user-visible error message or the server log. Any path where information about the argument escapes the function through something other than its return value makes the function leaky. (The opposite is leakproof, a function guaranteed to have no such paths.) PG groups all such functions under the term leaky functions.

The RLS policy is supposed to only show rows where owner = current_user. But if the planner, for optimization reasons, evaluates leaky_function before the RLS condition, the function receives the account_number of rows the user has no right to see as its argument. Even if the policy then removes those rows from the result, the function already saw the values and leaked them, into the server log or the error message. RLS has been pierced.

The security_level prevents this. The RLS condition is at a lower level, and the user's WHERE conditions are at a higher level. The planner therefore cannot push a user condition containing a non-leakproof function below the RLS condition. The RLS condition filters first, and only the rows that pass make it to the user's function. This is the substance of the name "security barrier": qual order keeps optimization from crossing the security boundary.

There's one more thing about the order. The rewriter attaches the RLS conditions to the front of rte->securityQuals. If a table has both an RLS policy and conditions coming in from a security-barrier view, the RLS condition on the table itself ends up at the lower level, the position that gets evaluated first. The decision is that policies bound directly to a table take precedence over conditions coming in through a view.

What this means in practice

First, the performance of queries on an RLS-protected table comes down to whether the policy condition can use an index. The RLS condition isn't a separate filter, it's a regular qual merged into the query, so the planner may or may not be able to use an index for it. A simple USING (owner = current_user) will use an index scan if there's one on owner. A USING (owner IN (SELECT ... FROM ...)) with a sub-query, on the other hand, attaches that sub-query to every query against the table. If queries on an RLS table feel slow, the first step is to use EXPLAIN to see how the policy condition is being executed. Policy conditions are part of index design too.

Second, if an INSERT under RLS completes without error, the row actually went in. WITH CHECK violations are loud, not silent. Rows blocked by the policy don't vanish behind a success response, so you can trust that on the read side. What's a separate concern is whether the application code is catching that error and handling it properly. Make sure WITH CHECK violation errors are routed through the same handling path as ordinary constraint violations.

Third, turning RLS on without creating a policy locks the table down entirely. ENABLE ROW LEVEL SECURITY is itself a default-deny declaration. If a migration enables RLS in one statement and creates policies in another, and the second statement is missing, any code deployed in between gets empty results. To make matters worse, table owners bypass RLS, so the problem won't surface in a development environment where you connect as the owner; it only shows up in production. The lesson is to put the RLS-enable statement and the policy creation in a single transaction, and to test at least once with a role that isn't the owner.