spatial_ref_sys is anon-writable on hosted Supabase and cannot be fixed from the project role

[TortoiseWolfe]

Split out of #75/#84 as a tracked, currently-unfixable limitation.

The exposure

public.spatial_ref_sys (PostGIS reference data) is the only table in public with RLS disabled, and Supabase's default privileges grant anon full access. An unauthenticated request can modify or delete rows:

DELETE /rest/v1/spatial_ref_sys?srid=eq.2000   -> HTTP 204,  8500 rows -> 8499

Confirmed against the live project. (The row was restored immediately; the table is back to 8500.)

Impact is availability, not confidentiality: SRID definitions are public reference data. But assign_user_metro_area() casts through ST_SetSRID(..., 4326), so losing SRID rows breaks home-location handling and every geography comparison the product depends on until the data is restored.

Why it is not fixable from here

The table is owned by supabase_admin; our migrations run as postgres.

• REVOKE ... ON public.spatial_ref_sys FROM anon — Postgres treats a REVOKE by a non-owner without grant option as a silent no-op. It returns success and the ACL is unchanged. Verified: anon=arwdDxtm before and after.
• ALTER TABLE public.spatial_ref_sys ENABLE ROW LEVEL SECURITY — ERROR: 42501: must be owner of table spatial_ref_sys.

The trap this created

The hardening does work locally and self-hosted, because the dev image runs migrations as the table owner. So tests/rls/authz-boundary.sh passes this check locally for a reason that does not hold in production.

That divergence is now called out in the migration and in tests/rls/README.md. It is worth remembering as a general caution: a green local RLS/grant result is not evidence about the hosted project whenever ownership differs.

Options

• Ask Supabase support to revoke the default anon grants on this table, or to move PostGIS into a dedicated schema not exposed via PostgREST.
• Accept and monitor: add a row-count check (expected 8500) to a scheduled workflow so tampering is detected rather than silently breaking geography queries later.
• Revisit if PostgREST's exposed schemas become configurable to exclude it.

Definition of done

• Either the grant is gone on the hosted project, or a detection check exists and this is documented as accepted risk