Blog · July 25, 2026

A citext comparison that type-checks, compiles, and passes review — and silently becomes case-sensitive anyway.

group_upsert is a small find-or-create: given a slug, return the existing group's id or make one. It had never been called anywhere in the pgTAP suite — register_actor/onboarding tests never pass a group — so this week's coverage sweep picked it up as one more independent gap to close. Writing the obvious first assertion (create engineering, resolve engineering again, expect the same id) passed immediately. The second assertion — resolve ENGINEERING and expect the same row, since the slug column is citext and citext comparisons are supposed to fold case — failed. Not with a wrong answer. With an unhandled 23505 duplicate key error, because the function had fallen through to the insert branch instead of finding the row that was plainly already there.

Every function in this codebase runs with an empty search path, on purpose

Ledgenter's RPCs are security definer with set search_path = ''. That's a deliberate hardening choice, not an accident: an empty search path means every unqualified table or function reference has to be resolved explicitly (public.groups, not groups), which closes off an entire class of search-path-injection attack where a caller could shadow a system function with one of their own in a schema earlier in their path. It's correct, and it's why an unqualified = between two citext values in group_upsert's where clause looked completely unremarkable:

select id into v_id from public.groups
where tenant_id = v_tenant and slug = p_slug::extensions.citext
  and deleted_at is null;

Here's the part that isn't obvious from reading it: citext's own = operator is registered in the extensions schema (create extension citext with schema extensions), not pg_catalog. Under an empty search path, Postgres can't see it — extensions is never implicitly searched. But the statement doesn't fail. Postgres still has a text = text operator visible in pg_catalog, and citext has an assignment-level cast to text (looser than the implicit casts used for normal operator resolution, but legal here), so the planner quietly resolves the whole expression to a byte-for-byte, case-sensitive text comparison instead. The query type-checks, runs, returns zero rows for ENGINEERING when engineering already exists — and the function falls through to its insert branch and collides with the unique index that does fold case correctly, throwing a raw 23505 the caller was never meant to see.

The first fix was also wrong

The obvious patch — cast the operand explicitly, slug = p_slug::extensions.citext — changes nothing. The problem was never the operand's type; it's which = operator gets resolved, and casting one side doesn't touch that. This got caught before merge, by the same pgTAP assertion that found the original bug, run against the "fixed" version. The actual fix has to schema-qualify the operator, not the value: slug OPERATOR(extensions.=) p_slug::extensions.citext. That syntax forces Postgres to resolve = specifically to the one registered in extensions, regardless of search path — verified directly against the unique index's own citext_ops fold to confirm they agree on every case variant, not just the two tested here.

The same shape is probably elsewhere, untouched for now

The migration's own comment flags it: col = param::extensions.citext under search_path = '' — modeled on this function's own broken first draft — appears in at least three other places: skill_upsert, a couple of project-key lookups, and the team-invites email match. Any of them could have the identical latent bug, just never exercised by a test that tries a different case. That's filed as a follow-up audit, not fixed in this change — the point of keeping this migration to the one RPC its own test actually covers is that a fix nobody wrote a failing test for first is a guess, not a fix.

Start at ledgenter.com.