My Postgres RLS policy ran its permission check 28,765 times per query

I'm building WebKeeper, a shared record of the client websites a freelancer or small agency looks after. It tracks who controls each domain and hosting account, who's responsible for renewals, and when a registrar, nameserver or certificate quietly changes. It also runs the usual website checks: response times every hour (down to every minute on the bigger plans) and a daily screenshot. I wrote about why I built it after a decade of running an agency.

Every customer's portfolio lives in one PostgreSQL database, and row-level security keeps them apart. That detail matters in a minute.

This week I shipped a dashboard refresh. Every Site card got a small screenshot and a 24-hour response-time sparkline, all served by one batch endpoint. Locally it felt instant. In production the endpoint took eight seconds.

Eight seconds for a sparkline.

My first suspicion was the data. Some Sites are checked every minute, so 24 hours is 1,440 samples each. But the query already did the sensible thing: date_bin into hourly buckets inside PostgreSQL, so only 25 points per Site ever left the database. The query plan used the right index. Nothing about the SQL itself looked slow.

So I built a throwaway PostgreSQL 17 container with the real migrations, seeded 20 Sites at 1-minute checks and timed the reads as the actual API role:

glance (all sites): 16630 / 16674 / 16855 ms
history 30d (1-minute site): 52954 / 50394 / 50221 ms

Worse than production, because production had fewer busy Sites. Same shape, though.

The policy that looked harmless

In WebKeeper a tenant is a workspace: one agency, its team and its Sites. Every tenant table has a forced row-level security policy, and every policy looked like this:

CREATE POLICY http_measurement_authority ON wk_private.http_measurement
  USING (wk_boundary.has_current_workspace_authority(workspace_id, workspace_generation))
  WITH CHECK (wk_boundary.has_current_workspace_authority(workspace_id, workspace_generation));

The function checks that the session's authority is real: it exists, isn't revoked or expired, belongs to the workspace the transaction is bound to, and the workspace's trial still allows reading. That's several joins and a couple of nested function calls. It's declared STABLE SECURITY DEFINER with its own search_path, which is exactly what you want from a security boundary.

It's also exactly what stops PostgreSQL from doing anything clever with it.

A policy's USING expression is added to every query as a filter on each row. This one passes the row's own columns into the function, so the result can differ from row to row, and PostgreSQL has to call it for every row it looks at. STABLE doesn't help: it only promises the same answer for the same arguments within one statement, PostgreSQL doesn't cache function results, and here the arguments change with every row anyway. And a SECURITY DEFINER function with a SET clause is never inlined, so the planner can't flatten the check into the main query either. Every call runs the whole function body.

I turned on track_functions = 'all' and counted:

authority calls, glance: 28765

Twenty Sites times 1,440 samples is 28,800. The permission check ran once per measurement row, each time asking the same question with the same answer.

I'd already fixed this once

The funny part is that I'd met this problem two weeks earlier and didn't recognize it. Back then the Sites list took five seconds, and profiling showed the time going into planning the nested queries inside the permission function, 3 ms of planning for 0.1 ms of work, repeated per row. I pinned join_collapse_limit = 1 on the function, planning got cheap, the list dropped to 600 ms, and I moved on.

That fix made each call cheaper. It didn't make there be fewer calls. A table of 50 rows hid the difference. A table of 30 days of 1-minute measurements didn't.

One check per statement

The way out is to notice what the function really needs from the row. Nothing, it turns out. The check already required its arguments to equal the workspace the transaction was bound to:

WHERE authority.workspace_id = p_workspace_id
  AND authority.workspace_id = current_setting('webkeeper.workspace_id', true)::uuid
  -- and the same for the generation

So "this row passes" really meant "this row belongs to the bound workspace, and the bound workspace passes the check". The second half doesn't depend on the row at all.

I split it accordingly. A new function takes no arguments, runs the same full check against the session's own settings and returns the one workspace it may see, or no row:

CREATE FUNCTION wk_boundary.authorized_workspace()
RETURNS TABLE (workspace_id uuid, workspace_generation bigint)
LANGUAGE sql STABLE SECURITY DEFINER SET search_path TO pg_catalog AS $$
  SELECT bound.workspace_id, bound.workspace_generation
  FROM (SELECT nullif(current_setting('webkeeper.workspace_id', true), '')::uuid AS workspace_id,
               nullif(current_setting('webkeeper.workspace_generation', true), '')::bigint
                 AS workspace_generation) AS bound
  WHERE wk_boundary.has_current_workspace_authority(bound.workspace_id, bound.workspace_generation);
$$;

And the policy compares the row with it through a sub-select:

CREATE POLICY http_measurement_authority ON wk_private.http_measurement
  USING ((workspace_id, workspace_generation) =
         (SELECT a.workspace_id, a.workspace_generation FROM wk_boundary.authorized_workspace() AS a))
  WITH CHECK (/* the same expression */);

The sub-select mentions no column of the outer row, so PostgreSQL plans it as an InitPlan: run once when the statement first needs it, then reused. The per-row work is now a plain equality on two columns, which the planner can even push into an index condition:

Aggregate (actual time=7.854..7.854 rows=1 loops=1)
  InitPlan 1
    ->  Function Scan on authorized_workspace a (actual time=1.026..1.026 rows=1 loops=1)
  ->  Index Only Scan using http_measurement_target_history on http_measurement
        Index Cond: ((workspace_id = (InitPlan 1).col1) AND (workspace_generation = (InitPlan 1).col2))

If the session has no valid authority, the function returns no row, the sub-select is NULL, and NULL = anything lets nothing through. Forged settings still fail, because the function checks them against the authority table exactly as before.

Same container, same data:

About 700 times faster, and not one line of application code changed.

Rewriting 38 policies without holding your breath

Every tenant table had the same policy, 38 of them, so the migration rewrote them in a loop. Since this is the only thing standing between two customers' data, I made it refuse to guess:

IF policy.policyname <> policy.tablename || '_authority' OR policy.permissive <> 'PERMISSIVE'
  OR policy.roles <> ARRAY['public']::name[] OR policy.cmd <> 'ALL'
  OR policy.qual IS DISTINCT FROM previous OR policy.with_check IS DISTINCT FROM previous THEN
  RAISE EXCEPTION 'unexpected tenant policy shape on %.%', policy.schemaname, policy.tablename;
END IF;

It stops if it rewrites anything other than exactly 38, and again if any policy anywhere still calls the per-row function. A test now pins the exact policy text, so a future table can't quietly come back with the slow shape.

A few things are worth knowing before you copy this:

• The migration takes locks. DROP POLICY and CREATE POLICY take an ACCESS EXCLUSIVE lock on each table in turn. Mine ran in 26 seconds without hitting its lock timeout, but run it at a quiet hour and expect a retry.
• The answer is fixed for the whole statement. A statement that changes its own session settings halfway through keeps seeing its original, already authorized tenant until it ends. The per-row version would have started refusing rows mid-statement. It can't reach another tenant either way, but it isn't byte-for-byte the same behavior.
• EXECUTE is checked even when the policy folds away. A role without permission on the new function now gets an error on SELECT ... WHERE false where it used to get zero rows. Grant it to the same roles as the old one.

If you've used Supabase, this is their (select auth.uid()) advice in different clothes. auth.uid() = user_id calls the function per row, while (select auth.uid()) = user_id calls it once. I'd read that tip before and filed it under "Supabase trivia". It's just PostgreSQL.

Takeaway

A policy expression runs for every row, and so does any function you hand a row column to. Give the function nothing from the row, ask it once, and compare.

• Postgresql
• Debugging