Two errors, one error code, and an hour in the wrong editor : What i learnt building WP-Supabase Sync

Claudio Ibe — Fri, 07 Aug 2026 18:38:46 +0000

If you write to Supabase from anything, sooner or later Postgres hands you
SQLSTATE 42501. It means one of two things:

permission denied for table wp_content
new row violates row-level security policy for table "wp_content"

The first says your GRANT is missing. Postgres refused before row level
security was ever consulted, so your policies are irrelevant. You can rewrite
them all afternoon and nothing will change. The second says the grant is fine and
a policy's WITH CHECK rejected the row.

Same code, opposite fixes. And the advice you find first is almost always the
policy one, because that's the interesting failure and it's what people blog
about. So you go read your policies, and they look correct, because they are.

I built a WordPress plugin recently that pushes content into Supabase, and this
specific confusion is the thing it's organised around.

What it does

WP Supabase Sync mirrors
published WordPress posts into a Postgres table on your Supabase project.
WordPress stays the editor and the source of truth. Supabase becomes the read
layer your Next.js app or mobile client queries directly, with the anon key and
RLS on top, instead of going through the WP REST API.

That part is not hard. Post saves, hook fires, row gets upserted. Any competent
afternoon produces a working version.

What takes the time is everything after "working."

The diagnostics are the product

There's a wp supabase doctor command, and a matching admin screen, that runs
twelve checks in dependency order. When something fails it names the layer that
refused you, quotes what Postgres actually returned, and prints the SQL that
fixes it.

For the two 42501 cases above, that's the difference between "check your RLS
policies" and:

grant select, insert, update, delete on public.wp_content to service_role;

The distinction is pinned by a test that drops the grant and asserts the wording.
Which sounds like overkill for an error message, except a wrong one sends you
somewhere else for an hour.

Two smaller decisions in the same spirit:

Failures don't cascade. If the project is unreachable, the eleven downstream
checks report as skipped, not failed. A privilege failure upstream makes "can
you write?" unanswerable, so the honest answer is to not answer it. One accurate
red line beats eight.

Every translated error is recorded from a real response.
tests/fixtures/errors/ holds actual PostgREST replies, captured by deliberately
provoking each one against a live stack: PGRST205, PGRST204, PGRST301,
both 42501 variants, 23505, 23502, 22P02, 42703. None of the strings
the translator matches on is one I imagined. That's the only reason I'm willing
to claim the 42501 disambiguation works.

I couldn't provoke 42P01 relation does not exist at all. PostgREST checks
its own schema cache first and returns PGRST205 before Postgres sees the query,
so it never surfaces over the Data API. The translator handles it anyway, since
it can arrive via an RPC call into a function referencing a dropped table, but
there's no fixture and the docs say so rather than quietly implying coverage.

Things testing taught me that I'd have got wrong

I wrote a spec first, then built against a real local stack, and kept a file of
every place reality contradicted the plan. A few worth passing on:

identity and serial are not interchangeable, and nothing tells you so until
a locked-down role can't insert. I wanted to confirm that service_role could
insert without an explicit sequence grant. First measurement said yes. First
measurement was garbage: Postgres's default privileges had already granted service_role
UPDATE on the sequence, and nextval() accepts USAGE or UPDATE, so the test
proved nothing.

Revoking everything and comparing both column styles directly:

IDENTITY column insert with NO sequence privileges: SUCCEEDED
SERIAL   column insert with NO sequence privileges: FAILED
  -> permission denied for sequence wpsb_serial_probe_id_seq

An identity column's sequence is owned by the table and its privileges aren't
checked separately. A serial column's sequence is its own object and needs its
own grant. It is a one-word schema choice that silently decides whether your
inserts work under a locked-down role.

No API key doesn't mean no access, locally. My spec had "is the project
reachable" and "is the key valid" as separate checks against GET /rest/v1/.
They aren't separable there: with no key at all, a local stack returns 200 and
the full OpenAPI document, and GET /rest/v1/posts?limit=1 returns rows, because
no key behaves as anon. Hosted Supabase returns 401. So a check written against
local behaviour would mean something different in production.

The fix was better than the original plan anyway. The reachability check sends
no credentials and passes on any HTTP response including a 401, since all it
asks is whether there's a Supabase there. The auth check sends the key and fails
on 401/403. Now "wrong URL" and "wrong key" are cleanly separated and behave
identically local and hosted.

Not every key is a JWT any more. The spec said decode the key and read the
role claim. That only works for legacy keys. A current supabase start emits
both generations, and sb_secret_… and sb_publishable_… are opaque. A JWT-only
check would report "malformed key" for a perfectly good secret key on any modern
project.

human_time_diff() is unsigned. My cron check rendered an event that was due
three hours ago as "next run due in 3 hours." A stopped scheduler, described as a
healthy one, by the plugin whose entire pitch is not doing that. I found it by
looking at a screenshot, not from a test. It now checks the direction and says
the schedule was due N ago and hasn't run. Four regression assertions hold it
there.

The unglamorous parts

Writes go through a queue table rather than straight out over HTTP. A unique key
on (object_type, object_id) coalesces events, so editing 100 posts fires
several hundred hooks and produces exactly 100 rows. Batches of 50, one request
per action. Backoff at 2^attempts minutes capped at an hour, retrying only 5xx,
429 and timeouts — a 401 or a missing table dead-letters immediately instead of
burning eight attempts pretending it might resolve itself.

Claiming a batch uses a token unique to the call, not a claimed_at timestamp.
Two overlapping cron runs can stamp the same second and each would then read the
other's rows.

Three other choices:

The plugin talks to the Data API over HTTPS rather than opening a Postgres
connection. PHP's request-per-process model exhausts a pool fast, pdo_pgsql is
often missing on managed WordPress hosts, and port 443 survives restrictive
egress rules.

The plugin never runs DDL. wp supabase schema --print emits a migration for you
to read and apply. Handing a WordPress plugin authority to alter your schema with
a key that bypasses RLS is more power than it needs to do its job.

Syncing is off by default and stays off until you've run the diagnostics.

If you want to try it

GPL-2.0, plain PHP, no build step, WordPress 6.4+ and PHP 8.1+.

https://github.com/0xclaudi0/wp-supabase-sync
Not affiliated with Supabase.

test!

Claudio Ibe — Thu, 06 Aug 2026 23:59:50 +0000

Test post for my blog