Security Barrier Views and Leakproof Functions #

A tenant-scoped search — WHERE lower(email) = lower($1) — uses an expression index and returns in a millisecond when a DBA runs it. Through the application, connected as a role subject to row-level security, the same query scans every row the tenant can see and takes two seconds. The index exists, the statistics are fine, and the plan still refuses to use it.

Normally the planner pushes predicates as close to the scan as possible, as described in filter pushdown mechanics. Security barriers are the deliberate exception: pushing a predicate down can leak data, so the planner declines unless the predicate is provably safe.

The Planner Condition #

Two features create a security boundary between a user’s predicates and the rows they filter:

The risk is simple: if a user’s predicate runs before the security condition, a function in it could observe rows the user must not see — by raising an error containing a value, logging, or timing. So the planner orders evaluation by security level and applies a rule:

A user predicate may be evaluated ahead of a security condition — and used as an index condition — only if every function and operator in it is LEAKPROOF.

Leakproof status lives in pg_proc.proleakproof. Many built-in comparison operators are leakproof: integer and text equality, most numeric and timestamp comparisons. Many common functions are not: lower(), upper(), LIKE and regular-expression matches, casts that can raise errors, and any user-defined function unless a superuser marks it.

In plan terms, a non-leakproof predicate cannot become an Index Cond; it is applied as a Filter after the security conditions, above a scan that could only use the policy’s own indexable conditions.

Leakproof predicates may pass the barrier Two stacks. Left: email equals value, a leakproof operator, is pushed below the security condition and becomes an index condition. Right: lower of email equals value, which uses a non-leakproof function, is held above the security condition and applied as a filter only to rows the policy already allowed. email = $1 (leakproof) security condition: tenant_id = … Index Cond: email = $1 predicate may run first → index used lower(email) = $1 (not leakproof) Filter: lower(email) = $1 security condition: tenant_id = … predicate held above the barrier → no index the lower box runs first; only leakproof predicates may be placed there

Annotated EXPLAIN Evidence #

Setup:

ALTER TABLE contacts ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON contacts
  USING (tenant_id = current_setting('app.tenant_id')::bigint);
CREATE INDEX contacts_lower_email_idx ON contacts (lower(email));

As a superuser (policies bypassed):

Index Scan using contacts_lower_email_idx on contacts  (actual time=0.03..0.04 rows=1 loops=1)
  Index Cond: (lower(email) = '[email protected]'::text)
Execution Time: 0.07 ms

As the application role:

Index Scan using contacts_tenant_id_idx on contacts  (actual time=0.05..1904.2 rows=1 loops=1)
  Index Cond: (tenant_id = (current_setting('app.tenant_id'))::bigint)   -- the policy
  Filter: (lower(email) = '[email protected]'::text)                     -- user predicate held back
  Rows Removed by Filter: 1840211
Execution Time: 1904.6 ms
-- the lower() index is never considered: lower() is not leakproof

Confirm the leakproof status:

SELECT proname, proleakproof FROM pg_proc WHERE proname IN ('lower', 'texteq', 'textlike');
 proname  | proleakproof
----------+--------------
 lower    | f
 texteq   | t
 textlike | f
Spotting a held-back predicate Three plan lines are annotated. The Index Cond is the policy condition on tenant_id. The Filter contains lower of email, a non-leakproof function. Rows Removed by Filter of 1.84 million shows the cost. Index Cond: (tenant_id = …) the policy, not your predicate Filter: (lower(email) = 'ana@…') non-leakproof → kept above Rows Removed by Filter: 1840211 every tenant row examined check proleakproof for each function in the Filter

Step-by-Step Resolution #

  1. Capture the plan as the application role. Use SET ROLE app_user and set any session variables the policy reads. Superusers, BYPASSRLS roles and table owners without FORCE ROW LEVEL SECURITY do not see policies.

  2. Find user predicates below Filter: alongside a policy Index Cond. A large Rows Removed by Filter on a query that should be selective is the signal — the interpretation is in understanding Filter vs Recheck conditions.

  3. Check proleakproof for every function and operator in the held-back predicate.

  4. Rewrite with leakproof operators. Store the normalised value and compare with plain equality:

    ALTER TABLE contacts ADD COLUMN email_normalized text
  GENERATED ALWAYS AS (lower(email)) STORED;
CREATE INDEX CONCURRENTLY contacts_tenant_email_norm_idx
  ON contacts (tenant_id, email_normalized);
-- query: WHERE email_normalized = lower($1)
-- lower($1) is evaluated on the constant, not on table rows, so nothing leaks

  5. Or put the predicate inside the security boundary. For a security_barrier view, a parameterised set-returning function owned by a trusted role can apply both conditions internally. Marking a function LEAKPROOF is a superuser decision that must be justified by reading its code; do not mark functions that can raise data-dependent errors.

  6. Verify that the user predicate now appears in Index Cond:

    Index Scan using contacts_tenant_email_norm_idx on contacts  (actual time=0.04..0.05 rows=1 loops=1)
  Index Cond: ((tenant_id = (current_setting('app.tenant_id'))::bigint) AND (email_normalized = '[email protected]'::text))
Execution Time: 0.09 ms
Rows the scan must examine Two bars. With lower(email) as a filter held above the policy, the scan examines 1.84 million rows of the tenant. With a stored normalized column compared by leakproof equality inside a composite index, the scan examines one row. Filter: lower(email) 1,840,212 rows examined Index Cond: normalized 1 row examined same tenant, same result, same policy

Before and After #

-- BEFORE: app role, lower(email) predicate under RLS
Index Cond: (tenant_id = …)   Filter: (lower(email) = …)   Rows Removed by Filter: 1840211   Execution Time: 1904.6 ms

-- AFTER: stored normalized column, leakproof equality
Index Cond: ((tenant_id = …) AND (email_normalized = …))                                    Execution Time: 0.09 ms

Common Pitfalls #

Diagnosing as a superuser. The plan you see has no policies. Diagnostic signal: fast plans in psql that do not match auto_explain output. Fix: SET ROLE to the application role.

Marking functions LEAKPROOF to make plans faster. A function that raises an error such as “invalid input: ” leaks the value to users who should not see the row. Diagnostic signal: requests to mark casts or parsers leakproof. Fix: rewrite the predicate instead, and reserve LEAKPROOF for functions audited to raise no data-dependent errors.

LIKE searches under RLS. textlike is not leakproof, so a prefix search cannot use a pattern index ahead of the policy. Diagnostic signal: Filter: (name ~~ 'abc%') over all tenant rows. Fix: include the tenant column in the index so the policy’s own index condition narrows the scan, or search a normalized column with range equality.

Views without security_barrier used for isolation. A plain view lets predicates be pushed into it, and a crafted function can see hidden rows. Diagnostic signal: tenant isolation implemented with an ordinary view. Fix: use RLS or WITH (security_barrier); the planning cost above is the price of that protection.

Frequently Asked Questions #

What does LEAKPROOF mean for a function? It reveals nothing about its arguments except through the return value: no data-dependent errors and no side effects. The planner may evaluate it before security conditions. Only superusers can mark a function LEAKPROOF.

Does row-level security disable index usage? Not in general. Policy conditions can use indexes, and user predicates built from leakproof operators can too. Predicates calling non-leakproof functions cannot be index conditions ahead of the policy.

Why does the planner treat a superuser’s query differently? Superusers and BYPASSRLS roles skip policies, and table owners do too unless FORCE ROW LEVEL SECURITY is set, so their plans contain no security conditions.

Up: Filter Pushdown Mechanics