Levandor

Supabase from GCP — Pooler Mode & RLS-via-Ownership Gotchas

4 min read

Reusable gotchas for connecting a long-lived Rust (sqlx) writer on a GCP VM to Supabase Postgres, found during the slv1 measurement-rig work: (1) which pooler port to use and (2) how the postgres role actually bypasses RLS (both from the spec-grounding pass), plus (3) two sqlx↔Postgres type-decode casts that surfaced building the rig’s report queries (increment 1). Applies to any Supabase-from-GCP service, not just slv1.

For Agents

Reusable infra recipe. A Rust / sqlx service on a stock GCP VM writing to Supabase Postgres should use Supavisor session mode on port 5432 and rely on table ownership (not BYPASSRLS) for the postgres role. Why the obvious alternatives fail is below.

Connection: use Supavisor session mode (:5432)

For a long-lived Rust writer using sqlx on a stock GCP VM, the correct endpoint is the Supavisor session-mode pooler, port 5432. Both alternatives fail:

OptionPort / hostVerdict
Session poolerSupavisor :5432Correct. Persistent server connection per client → prepared statements work
Transaction poolerSupavisor :6543Breaks sqlx — prepared statement "sqlx_s_1" already exists / “transaction mode does not support prepared statements”. Transaction mode multiplexes many clients onto few server conns, so prepared statements collide
Direct DB hostdb.<ref>.supabase.coIPv6-only without a paid add-on; stock GCP VMs are IPv4-only → unreachable

The :6543 trap

sqlx prepares statements by default. On the transaction pooler (:6543) that surfaces as prepared statement "sqlx_s_1" already exists. The clean fix is session mode (:5432) for a persistent writer — do not reach for disabling statement caching as a workaround.

RLS: postgres bypasses via OWNERSHIP, not BYPASSRLS

Common misconception: Supabase’s postgres connection role is a superuser or carries BYPASSRLS. Neither is true.

  • The rig connects as postgres, which bypasses RLS because it OWNS the tables — a table owner is exempt from RLS on its own tables (unless FORCE ROW LEVEL SECURITY is set).
  • postgres is not a superuser and does not carry the BYPASSRLS attribute.
  • BYPASSRLS belongs to service_role — a NOLOGIN role that PostgREST assumes via JWT, not a connection-string login. You cannot connect as service_role from sqlx.

Never FORCE RLS on owner-written tables

FORCE ROW LEVEL SECURITY subjects even the table owner to the policy set. With RLS enabled + no policies (default-deny), that denies ALL writes from the owner — i.e. it breaks the very writer you want. Enable RLS, add no policies (default-deny for anon / authenticated), but never FORCE it on tables the owner must write.

  • Enable RLS on all tables, no policies → PostgREST roles (anon / authenticated) get default-deny.
  • Tables owned by postgres (they are, when migrations run via the Supabase MCP).
  • Defense-in-depth: REVOKE ALL ON <tables> FROM anon, authenticated.
  • Keep the Supabase MCP off the write path — it is a dev/ops tool (migrations, ad-hoc SQL). The service writes via plain DATABASE_URL.

sqlx ↔ Postgres type-decode casts

Two type-mapping mismatches that sqlx surfaces at runtime (not compile time — the rig uses sqlx::query with .bind, not the query! macro), both fixed with an explicit SQL cast:

  • make_interval(hours => $1::int) — sqlx binds a Rust i64 as Postgres int8, but make_interval’s parameters are int4. Passing an i64 hours window without the ::int cast fails on the function-signature type match. Cast the bind: make_interval(hours => $1::int).
  • sum(x)::bigint — sum() over a bigint column returns numeric, which sqlx cannot decode into an i64. Cast the aggregate to the integer type you decode into: sum(x)::bigint.

These are report-query gotchas

Both were hit building the rig’s report subcommand (time-windowed percentile / rollup SQL). The connection-mode gotcha (session vs transaction pooler) is a separate issue — see the Connection section at the top of this note.

Reuse

Other vault projects touching Supabase and/or GCP where this recipe applies if they grow a server-side long-lived pooled writer: levandor-infra (GCP VMs), txArchive 2025 Rewrite and levandor-crm (Supabase backends).

Graph View

Backlinks