anon role can UPDATE/DELETE public.spatial_ref_sys (owned by supabase_admin), project owner cannot revoke

[leo90903]

On my project, the anon role can write to public.spatial_ref_sys through the REST API: a PATCH rewriting an existing row with its own value returns 200, and a DELETE returns 200. The grants come from supabase_admin, the table owner (created by the PostGIS extension). As a result, REVOKE ... FROM anon, authenticated run as postgres succeeds silently but changes nothing. Enabling RLS fails with "must be owner", and moving PostGIS to the extensions schema fails with "must be owner of extension".

The anon key ships in a public mobile app, so anyone could alter or delete the SRID reference table. Could a Supabase admin run the following on my project? I have already opened a support ticket (Free plan) and can share its number.

REVOKE INSERT, UPDATE, DELETE, TRUNCATE ON public.spatial_ref_sys FROM anon, authenticated;

Better still, could this be applied by default to PostGIS projects? Related: issue #47206, discussions #26302 and #19143. Thanks.

[GaryAustin1]

Postgis is supposed to be installed in the extensions schema. That is the default dashboard setting.

[BryceEWatson]

Gary's right, and Supabase's PostGIS guide covers this exact case (troubleshooting). It says spatial_ref_sys only holds PostGIS's coordinate system definitions, never your data, and that the fix is moving PostGIS out of public, the schema the Data API exposes. Someone deleting or rewriting rows can still break geography distance queries like ST_DWithin, which look up the SRID there, and any coordinate transform that uses them. So it's worth fixing, but your users' data isn't what's at risk.

The guide gives two ways to move it (steps):

• Ask support to run the relocation SQL in that section. You already have a ticket open, so paste it there with your PostGIS version (select extversion from pg_extension where extname = 'postgis';), since the SQL needs it. It moves the extension in place, so your geometry columns and their data stay where they are. Ask them to run your REVOKE in the same ticket too, since the move keeps the table's current grants. For a live app, that's the one I'd pick.
• Do it yourself, as the guide describes: back up, run DROP EXTENSION postgis CASCADE;, then CREATE EXTENSION postgis SCHEMA extensions;, then restore what was dropped. CASCADE removes everything that depends on PostGIS, including your geometry and geography columns and their data, any indexes, views and policies that use them, functions whose arguments or results use PostGIS types, and add-ons like postgis_topology, so plan for downtime and a careful restore.

Either way, PostGIS ends up in extensions, so anything that reaches it through public breaks: functions or triggers whose search_path lacks extensions, and any SQL that names public.st_..., public.geometry or public.geography. Search your migrations for those (including quoted forms like "public"."geography") and fix them on the live database right after the move.