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

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.