auth.uid() returning null in the wrong context

The mechanics of null

auth.uid() extracts the sub claim from the request JWT. Null arises when the claim is absent, when the token belongs to an anonymous sign-in without a linked user, or when the call path never presented a token at all. In SQL, NULL = anything is not false — it is unknown, and unknown fails policy tests identically to denial.

That is why the symptom is silence: empty lists, zero-count aggregates, upserts that silently insert duplicates keyed by a null owner.

Three-valued logic does the rest. NULL compared to anything yields unknown, unknown fails the policy test exactly as false would, and the planner does not distinguish the two in its output. There is no error, no warning, no partial result — the predicate simply eliminates every row. Dashboards keep rendering and counters keep returning zero while the underlying cause sits in a missing claim on a token nobody inspected.

Reproducing the context

Emulate each caller explicitly on staging and watch what auth.uid() yields inside the transaction:

Vary the claims deliberately: an empty object, a token with a sub but an unexpected role, and a well-formed user token cover the three contexts production serves. The differences in visible rows tell you which branch of your design each caller takes, before production decides for you.

SET ROLE authenticated;
SET request.jwt.claims = '{}';  -- no sub claim
SELECT auth.uid();               -- expect NULL
SELECT count(*) FROM public.projects; -- expect 0 rows visible
RESET ROLE;

Choosing the fix deliberately

Three defensible designs exist, and mixing them carelessly is how regressions start. Server-side operations that genuinely act for a user should forward that user's token rather than rely on ambient context. True background work should connect under a role whose policies grant it explicitly, named rather than implied. And policies guarding human-only tables should reject null early with a clear predicate, so denial is legible in EXPLAIN rather than inferred:

USING (
  auth.role() = 'service_role'
  OR (SELECT auth.uid()) = user_id
)

Related automated checks

While the null-context problem itself is manual territory, two adjacent defects are detected on every scan: bare auth.uid() calls that re-evaluate per row (a cost finding with the InitPlan wrap as remedy), and predicates filtering columns that carry no index. Fixing both while you are in the policy file costs nothing extra.

Common false leads

Token expiry gets blamed first, and occasionally correctly — but expired tokens usually produce authentication errors upstream rather than null subjects at the database. Indexes come next and cannot help: the failure is logical, not physical. The most damaging lead is disabling RLS to prove the query works; it always proves the query works, and the sweep to re-enable is where new incidents begin. Reproduce the context instead, with the SET ROLE block above.

Environment comparisons mislead too. The same policy behaves differently across dev and prod because the call path differs — a local script reusing one long-lived session versus server routes forwarding per-request tokens. Compare contexts, not policy text.

Verifying the fix from outside

Whichever design you choose, verify it per caller. Replay the background job or webhook and confirm rows land once, attributed correctly; present a real user token and confirm visibility matches ownership; present a token without a sub claim and confirm the outcome is the denial or explicit branch you designed. A temporary debug function logging auth.uid() during staging makes the invisible context legible without touching production policies.

Frequently asked

Why does the same policy work in dev and fail in prod?

Usually the difference is how tokens reach the database: local development often reuses one long-lived session with stable claims, while production paths vary by route, and background routes may present no token at all. Reproduce each context with the SET ROLE block rather than toggling policies until behaviour coincidentally matches.

Is COALESCE(auth.uid(), ... ) safe?

Only with eyes open: substituting a sentinel identity effectively creates a shared principal that every null-context caller becomes. Named service roles with explicit policies are auditable and revocable; sentinel UUIDs embedded in functions are folklore that outlives the people who chose them.

Does RowShield flag bare auth.uid() for security?

No — that finding is performance: per-row evaluation cost, medium severity, with the InitPlan wrap as the remedy. Whether a null subject should deny or take a designed branch is a correctness decision about your application, and the scanner leaves it to you.