Cannot enable RLS on public.spatial_ref_sys — postgres user is not owner (exploit verified via REST API)

[garykk]

Project ref: vnzamkfyoboshkptyfmp (Free tier). Already filed via dashboard support form as SU-384481; cross-posting here per Supabase's own confirmation message that Free-tier non-outage issues are encouraged on GitHub Discussions.

The exposure is real, not just a lint finding

The Security Advisor flags public.spatial_ref_sys as RLS-disabled (lint 0013_rls_disabled_in_public). I verified this is actually exploitable: with only the public anon key, I can hit the REST API and both PATCH and DELETE return HTTP 204.

PATCH /rest/v1/spatial_ref_sys?srid=eq.999999  →  204
DELETE /rest/v1/spatial_ref_sys?srid=eq.999999 →  204

The PostGIS reference data is publicly modifiable by anyone with the project URL.

Every privilege-escalation path is platform-blocked for project owners

public.spatial_ref_sys is owned by supabase_admin, and every attempt to fix it as postgres is rejected:

Attempt	Error
ALTER TABLE … ENABLE ROW LEVEL SECURITY	42501: must be owner of table spatial_ref_sys
REVOKE INSERT/UPDATE/DELETE/TRUNCATE FROM anon, authenticated	Silently no-ops (only original grantor — supabase_admin — can revoke)
SET ROLE supabase_admin	42501: permission denied to set role "supabase_admin"
SET SESSION AUTHORIZATION supabase_admin	42501: permission denied to set session authorization
REASSIGN OWNED BY supabase_admin TO postgres	42501: Only roles with privileges of role "supabase_admin" may reassign objects owned by it
GRANT supabase_admin TO postgres	42501: "supabase_admin" role memberships are reserved, only superusers can grant them

Same failure mode via:

• Dashboard SQL Editor (runs as postgres)
• Management API /v1/projects/<ref>/database/query (also postgres)

DROP EXTENSION postgis; CREATE EXTENSION postgis; is also not a workaround for any project that uses geometry or geography columns — it CASCADEs and wipes user data. In our case public.pas_records.location is a geography column, so this would destroy data.

What would fix it (server-side, as supabase_admin)

Either of these clears the lint AND closes the actual REST exposure:

Option A — RLS enabled with public SELECT:

alter table public.spatial_ref_sys enable row level security;
drop policy if exists spatial_ref_sys_public_read on public.spatial_ref_sys;
create policy spatial_ref_sys_public_read
  on public.spatial_ref_sys for select to public using (true);

Option B — keep RLS off, revoke write privileges:

revoke insert, update, delete, truncate on public.spatial_ref_sys
  from anon, authenticated, service_role;

PostGIS function calls only need SELECT on this table for SRID lookups, so either path is non-breaking.

Bigger question

Would Supabase consider changing the default postgis extension installation so this isn't a per-project fix? The lint fires on every project that installs PostGIS in public (the default), and the project owner has no way to silence it correctly. A platform-side default of "RLS on + SELECT-to-public policy" on spatial_ref_sys at extension install time would close this for all projects.

Cross-reference: support ticket SU-384481.

Thanks for any pointer to the right fix path.

[GaryAustin1]

You may know this with your investigation, but default for postgis is extensions schema when using Supabase UI. You really need to install it in this schema.

[CharlesKahn]

@GaryAustin1 is right that extensions schema is the target, but the migration from public is the hard part when you have dependent cols.
Give this a try.

note that DROP EXTENSION postgis CASCADE would destroy your pas_records.location column. But you can back up the data, migrate the extension, and restore:

1. sql-- 1. Back up geography data as WKT text (survives extension removal)

CREATE TABLE public._geo_backup AS
SELECT id, ST_AsText(location::geometry) as location_wkt
FROM public.pas_records
WHERE location IS NOT NULL;

2. Drop the dependent column (not the whole table)

ALTER TABLE public.pas_records DROP COLUMN location;

3. Now CASCADE is safe since no user columns depend on postgis

DROP EXTENSION postgis CASCADE;

4. Reinstall in extensions schema

CREATE EXTENSION postgis SCHEMA extensions;

5. Recreate the column

ALTER TABLE public.pas_records
ADD COLUMN location geography(Point, 4326);

6. Restore from backup

UPDATE public.pas_records p
SET location = ST_GeogFromText(b.location_wkt)
FROM public._geo_backup b
WHERE p.id = b.id;

7. Clean up

DROP TABLE public._geo_backup;

This moves postgis out of public, which means spatial_ref_sys is no longer in the public schema, no longer exposed via PostgREST, and the lint should clear.

FYI if you have multiple tables with geography/geometry columns, you'd need to back up and recreate all of them. Test this on a branch database first with supabase db branch (paid plan), or duplicate the project on free tier.

Also For your broader question about platform defaults: agreed that this should be fixed at install time. Every project that followed the older docs is stuck in the same state.

Let me know if that works!