Blocking, in one function call per statement

Google Play requires any app hosting user content to ship in-app reporting, in-app blocking, real content removal and published standards. In August we had none of the four.

The fast version is client-side: fetch everything, filter hidden posts and blocked users in the app. It takes an afternoon, and it is advisory. Anyone with the app's public key and a terminal sees everything. It also has to be right in every fetcher. The content tables had six read paths: the feed, a thread, a public profile, the embedded reaction and comment counts, and two poll heartbeats. A filter in each is correct in six places until someone adds a seventh, and this codebase had already been bitten by that shape once, with a handle denylist enforced in the client and not the database.

So the rule went into row-level security. The migration lists what that bought:

Every existing reader inherits both rules with no code change: fetchPaper, fetchLetters, fetchPublicProfile, the embedded reactions/comments counts, and both poll heartbeats. That is the payoff, and also the surprise: a future query against these tables cannot opt out.

This article is about making that predicate cheap enough to ship, and about the lines in one migration that would each have emptied every feed while applying cleanly.

Why the block set needs elevated rights

Two content tables get a hidden_at column. A blocks table records who blocked whom. A row is visible if it is not hidden and its author is not in your block set.

The block set has to cover both directions. If you block someone, you stop seeing them and they stop seeing you. But the blocks table only shows you your own blocks, because nobody should be able to list who has blocked them:

create policy blocks_select_self on public.blocks
  for select to authenticated using (blocker_id = auth.uid());

So an inline not exists (select 1 from blocks …) inside another table's policy runs as the caller, hits that policy, and sees only the half of each pair where the caller is the blocker. Two-way blocking quietly becomes one-way. It runs, returns rows, and is wrong.

The block set therefore comes from a security definer function, which can see both halves:

create or replace function public.blocked_ids()
returns uuid[]
language sql stable security definer set search_path = public
as $$
  select coalesce(
    array_agg(case when b.blocker_id = auth.uid() then b.blocked_id else b.blocker_id end),
    '{}'::uuid[]
  )
  from public.blocks b
  where b.blocker_id = auth.uid() or b.blocked_id = auth.uid();
$$;

Four decisions sit in those lines and the policy that calls them.

The N+1 you cannot see in the query

The natural policy passes the row's author into the function:

-- the rejected version
using (hidden_at is null and not blocked_between(user_id))

The planner does not inline a security definer function. It is a real call, and written like this it runs once per row. The row count is also larger than it looks, because RLS filters the rows behind PostgREST's embedded aggregates too. The feed asks for 50 posts with their comment counts, and the comments policy fires once per comment.

Our design reviews are AI-assisted: an architect role proposes, a critic role attacks, and the team decides. The critic put a number on it:

on a post with 200 letters your policy fires 200 times to render one integer. Fifty such posts is 10,000 function calls to paint one screen, and paperHeartbeatTick re-queries every 20–45s per mounted client. You have moved the cost from code you can profile into a place nobody thinks to look.

That is a worst case, not a measurement: 50 posts with 200 comments each. But the shape is real. Per row includes rows behind aggregates you did not write and cannot see in your query.

The mechanism is easy to state slightly wrong, so precisely: a stable function is not guaranteed to run once per statement. Only immutable functions get constant-folded. What forces a single evaluation is wrapping the call in a scalar subquery that does not reference the outer row. Postgres then runs it as an InitPlan, once, before the scan. Zero arguments is what makes an uncorrelated subquery possible in the first place.

alter policy posts_select_authenticated on public.posts
  using (hidden_at is null and not ((select public.blocked_ids()) @> array[user_id]));

One call per statement, then an in-memory array check per row against a block set that is a few IDs long. The migration says it directly:

A bare call in a qual is evaluated PER ROW, and RLS also filters the rows behind PostgREST's embedded aggregates, so fetchPaper's comments(count) across 50 posts would call it once per COMMENT.

Why @> and not = any

The natural way to test membership is user_id = any (…). It fails here, because of a genuine ambiguity in SQL's grammar. = any has two forms: ANY over an array, and ANY over the rows of a subquery. Written as

user_id = any ((select blocked_ids()))

the parser picks the subquery form. It sees one row holding one value, a uuid[], and fails:

42883: operator does not exist: uuid = uuid[]

The obvious repair is to drop the subquery wrapper so the array form is chosen. That parses, and silently puts you back to per-row evaluation.

@> (array contains) has no subquery overload. The scalar subquery stays scalar, the InitPlan survives, and it reads as what it means: the block set does not contain this row's author. Two correct-looking spellings, one an error and one per-row. The migration carries the warning in capitals, because every reviewer's first instinct is to tidy it:

CALLERS USE @> (array contains), NOT = any. DO NOT "SIMPLIFY" THIS BACK.

Four ways to blackout the feed with a clean migration

Each of these commits without a warning and shows up only as behaviour.

The empty set. array_agg over zero rows returns NULL, not an empty array. Any comparison with NULL is NULL, and a policy reads NULL as deny. So for every user who has never blocked anyone, which on day one is every user, the block set would be NULL and the feed empty. The coalesce(…, '{}') is not defensive programming. It is the difference between a working product and none. The logic was right for the case the author had in mind; the bug lived in the most common case in production.

The grant.

revoke all on function public.blocked_ids() from public, anon;
grant execute on function public.blocked_ids() to authenticated;

Every read of posts and comments now calls this function. If authenticated cannot execute it, every read fails with a permission error. The platform's default privileges should already grant it. The explicit grant removes the assumption, and granting twice costs nothing. This caution had a precedent: in our own test harness, a revoke … from public, anon on another function had also stripped authenticated, because off-platform that role inherits through PUBLIC.

The half-applied migration. The first 25 migrations were almost entirely additive, and none wrapped itself in a transaction. That was harmless for create table. This one alters live policies on the two tables the whole product reads, and review caught that the original plan (drop the policy, then create the new one) had a moment where RLS was enabled with no select policy. Fail there without a transaction and production stays dark. The migration now uses alter policy, which cannot half-apply, and wraps itself anyway. Its comment: "Being atomic twice costs nothing; being atomic zero times cost the feed."

The regex. Prohibited terms are checked by a trigger on every insert into posts and comments:

where t ~* ('\m' || regexp_replace(b.term, '([\\^$.|?*+()\[\]{}])', '\\\1', 'g') || '\M')

Without the regexp_replace, a stored term is interpolated straight into a regular expression. One seeded term with an unbalanced parenthesis, and every write in the product throws. An outage delivered by a data migration, no attacker required.

The migration checks its own work before committing

The unusual part of the file is at the end. Before commit, the migration reads the content tables as an authenticated user and aborts if the new policies deny everything:

if public.blocked_ids() is null then
  raise exception 'ABORT 0026: blocked_ids() returned NULL, not ''{}''. The feed would be dark for every user.';
end if;

select count(*) into svc_posts from public.posts where hidden_at is null;
perform set_config('request.jwt.claim.sub', probe::text, true);
set local role authenticated;
select count(*) into auth_posts from public.posts;
reset role;

if svc_posts > 0 and auth_posts = 0 then
  raise exception 'ABORT 0026: an authenticated user can see 0 of % visible posts. The new policy denies everything.', svc_posts;
end if;

There are eight such checks in the block, covering the execute grant, comments, the signup trigger and a benign-text probe against the term guard. DDL is transactional in Postgres, so an exception here rolls the whole migration back. "The feed is empty and nobody knows why" becomes "the migration refused to apply, and said why", checked in the environment that matters against the data that is actually there.

The trigger that would have hidden nothing

Auto-hide is an after insert trigger on reports: when enough distinct people report something, set its hidden_at.

The posts and comments tables have no UPDATE policy at all. They were created with select, insert and delete only. That is what makes hidden_at client-immutable for free: nobody can update those rows, so nobody can un-hide their own post.

It is also what breaks the trigger. The trigger runs inside the reporter's statement, so its update posts set hidden_at = now() runs as the reporter. RLS filters it to zero rows and the statement succeeds. The report insert, which is what the client is watching, works. Nothing is hidden. The fix is security definer on the trigger function, and the migration writes both halves of the fact down together:

LOAD-BEARING IN BOTH DIRECTIONS: posts and comments have NO UPDATE policy, which is what makes these columns client-immutable for free — and what a future migration adding an UPDATE policy would silently remove.

The reader that inherits nothing

One endpoint serves a public web profile to anyone with a link. It runs under the service role, which bypasses RLS. None of the new policies touch it. So the takedown rule is re-implemented by hand there:

.from("posts")
.select("kind, headline, body, created_at, reactions(count), comments(count)")
.eq("user_id", uid)
// This function runs under the SERVICE ROLE and therefore inherits NOTHING
// from the RLS policies migration 0026 added to `posts`. Without this
// predicate, content taken down in the app keeps serving to the open web
// with no symptom visible from inside the app.
.is("hidden_at", null)

The ADR named this reader in advance, which is the only reason it is filtered. It is still the sharp edge of the approach. Every reader gets the rule for free except the ones above RLS, which are the most exposed. And the failure is invisible from inside the product: hide a post, watch it vanish from the app, and it is still on the web. Every RLS design has this hole, and it is exactly as wide as your service-role surface.

We checked this one by doing it, not by reading the code: hid a post, confirmed it was gone from the public profile's data, then restored it.

Two bugs found by using it

Neither was caught by a test. Both are in the 0.6.0 release notes.

Blocked posts stayed on screen. Both poll heartbeats detect change by watching the newest created_at. They exist to notice a row appearing. A block makes rows disappear, which does not move that timestamp. Unless the blocked user had made the most recent post, nothing refreshed, and their content stayed until a pull-to-refresh or a tab focus change. The change-detection strategy was right for the only kind of change that existed when it was written. The fix forces a refetch on block and unblock, which the ADR had in fact required.

Report failures blamed the network. The handler assumed a foreign-key violation meant an invalid reason code. The reports table has two foreign keys. A failure on the other one told users to update the app, and the sheet above it replaced even that with a generic connection error.

A third arrived later: blocking hid a user's posts but not their profile. That was fixed in September.

Weaker on purpose

Three guards are deliberately weaker than the strongest option, and each says why next to the code.

• The term check is callable by clients. contains_banned_term is granted to authenticated, so the app can pre-check and show real copy instead of a raw error. The alternative was giving the function that handles model output a service-role client, which is worse.
• The term check is a floor, not a filter. It is one layer of moderation, alongside reporting, blocking and takedown, and the code documents it that way rather than implying more.
• The roast screen fails open. Model output is screened against the term list before it is saved or shown. If that lookup errors, it lets the text through, because the posts trigger still blocks publication regardless. Fail-open is defensible exactly when you can name the boundary that is still holding.

What it cost

• A function call on every read of the two busiest tables. One per statement, but new, and on the hot path by choice.
• Queries cannot opt out. Anyone writing against these tables next year gets blocking semantics whether they know it or not.
• A false positive surfaces as a SQLSTATE. The term guard raises YM001; the client must map it to real copy or the user sees a database error.
• Auto-hide is a backstop, not the main path. It needs multiple distinct reporters, for every reason. An earlier draft let a single child-safety report hide content; that was rejected as the "report = delete anyone" vector. Reports are reviewed through a triage queue, and the migration describes the automatic path as a backstop rather than implying it is the moderation system.
• Blocking is destructive. It deletes follow edges in both directions and unblocking does not restore them. That matches platform norms; the confirmation copy has to say so.

What we would do differently

• Write the abort block first. For any migration that changes a rule every read depends on, the in-transaction self-check is the specification. Write it before the policies, then write policies until it passes.
• Design the human path in the same change as the automatic one. The auto-hide threshold got a full debate. The triage process behind it deserved the same attention in the same review, not a follow-up commit.
• Test disappearance, not just appearance. Every poller we had was built to notice new rows. A feature that removes rows should ship with a test that the screen changes when rows leave.

The argument list is a performance API

In RLS, whether a predicate costs one call or thousands depends on whether Postgres can hoist it, and that depends on whether it touches the row. A zero-argument function in a scalar subquery is an InitPlan. Anything else is per row, including rows you never wrote a query for.

And the empty set is the common case. The state most users are in on launch day is the one least likely to be in your head while you write the interesting branch.