Restrict direct access to spatial_ref_sys without breaking PostGIS

[RHCMorgan]

[GaryAustin1]

If you install PostGIS in the default extensions schema and not public it is protected from API access.

[aymansalkhatib]

Adding some detail to @GaryAustin1's answer, since Supabase now documents this exact case in the PostGIS guide (Troubleshooting → Security advisor flags public.spatial_ref_sys):

Why you can't just enable RLS on it
When PostGIS is installed in public, the table public.spatial_ref_sys is created by the extension and owned by supabase_admin, not by your postgres role, so ALTER TABLE ... ENABLE ROW LEVEL SECURITY isn't allowed. Supabase's docs say this is expected and that enabling RLS "isn't the fix": the table only contains PostGIS's built-in list of coordinate system definitions (EPSG codes), never your data. The real issue is that PostGIS lives in public, which the Data API exposes.

The proper fix: move PostGIS to the extensions schema
PostGIS (≥ 2.3) is not relocatable, so ALTER EXTENSION postgis SET SCHEMA won't work directly. You have two options:

1. Do it yourself (requires a rebuild):

   -- 1. Back up your database first! (geometry columns depend on PostGIS)
   -- 2. Drop PostGIS and everything that depends on it
   DROP EXTENSION postgis CASCADE;
   -- 3. Re-create it in the extensions schema
   CREATE EXTENSION postgis SCHEMA extensions;
   -- 4. Restore your tables/columns/data from the backup

   ⚠️ CASCADE drops every column, index, view and function that uses PostGIS types, so only do this after a full backup and preferably on a branch/staging project first.

2. Avoid the rebuild: ask Supabase Support to move it for you. They can temporarily mark the extension relocatable, run ALTER EXTENSION postgis SET SCHEMA extensions, and update it, without dropping your data (the SQL is shown in the same docs section).

After the move, make sure extensions is in your search_path (it is by default on Supabase), so queries using geometry, ST_* functions etc. keep working without schema prefixes.

Optional quick check of current access

select grantee, privilege_type
from information_schema.role_table_grants
where table_schema = 'public' and table_name = 'spatial_ref_sys'
  and grantee in ('anon', 'authenticated');

You can try revoke insert, update, delete, truncate on public.spatial_ref_sys from anon, authenticated;, but if Postgres replies with "no privileges could be revoked", your role isn't the grantor; in that case moving the extension is the only clean fix.