Unindexed RLS predicates: the index your policy is begging for

Why a policy needs an index

Row Level Security does not run as a separate pass over your data. Postgres takes each applicable policy expression and splices it into the query itself, where it is evaluated against every candidate row the planner considers — exactly like a WHERE clause you had typed by hand. Whatever the query plan can and cannot exploit applies to the policy condition identically.

When the filtered column carries a valid index, the planner can turn the condition into a targeted descent and touch only the rows that match. When it does not, the condition becomes a filter applied after a sequential scan, which means Postgres reads the entire table on every request — selects, inserts with RETURNING, updates, everything that touches the table through the policy.

Two properties make the failure easy to miss. It is invisible at small scale: a five-thousand-row table scans in negligible time, so development feels fine and the damage appears only after real data accumulates. And it produces no error anywhere: queries succeed, latency creeps, and the platform bill climbs faster than traffic because compute consumption tracks the scanning, not the useful work.

How to check it yourself

Two catalog queries give you the full picture. First, the policies and the expressions they apply — pg_policies renders each policy's USING and WITH CHECK clauses as text:

SELECT schemaname, tablename, policyname, cmd, qual, with_check
FROM pg_policies
WHERE schemaname = 'public'
ORDER BY tablename, policyname;

Then ask what backs those columns

Second, the indexes that exist per table. Cross-reference the columns named in the policy expressions above against this list, and any predicate column absent from every index is a finding:

SELECT tablename, indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname;

What fixing it looks like

One statement per uncovered column. Create a plain b-tree index on it, concurrently, so building the index never holds a lock that blocks writes on a live table. CONCURRENTLY cannot run inside a transaction block, so run the statement on its own rather than inside a migration transaction:

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_projects_owner_id
  ON public.projects (owner_id);

One index serves many policies

Resist the urge to create an index per policy. Three policies on the same table filtering the same ownership column need exactly one index between them, because Postgres splices whichever policies apply into the query and the condition lands on the same column either way. This is also why RowShield reports one finding per table and column rather than one per policy — three alerts for one fix is three times the noise for no extra information.

A column counts as covered if it appears anywhere in a valid index key, though a leading position in the key serves the predicate best and lets the same index serve sorting and range filters besides. If a composite index on (owner_id, created_at) already exists for your list screens, the policy predicate is covered by it.

RowShield automates exactly this check on every scan: it deparses every developer policy, extracts the columns referenced in USING and WITH CHECK, and compares them against the valid indexes on the table. Findings arrive with the remediation SQL above generated from your real column names, rated medium — this is a cost and latency problem, not an access-control failure. Scans run on your plan's schedule, alerts fire on transitions rather than repeating, and the finding resolves when the index lands. Run a free audit at rowshield.dev/audit — the public check needs no account — or connect the project for the full catalog view.

RowShield is an independent product, not affiliated with or endorsed by Supabase. It reads pg_catalog metadata only, never your rows.

Frequently asked

Is an unindexed RLS predicate a security problem?

No. Access control behaves precisely as written — the policy admits and denies exactly the rows it should. The damage is latency and compute: every query degrades toward a sequential scan, which is why the rule rates the finding medium rather than critical.

Why CREATE INDEX CONCURRENTLY?

A normal index build holds a lock that blocks writes for the duration, on a live table that can be minutes. The concurrent form builds alongside normal traffic and cannot run inside a transaction, so execute it as a standalone statement.

My policy filters two columns. One index or two?

Start with one composite index ordered equality-first — the column always filtered first, then the occasionally-filtered one. Postgres can serve conditions on the second column only by scanning the first column's matches, so ordering should mirror how often each column appears alone in filters.