Cannot revoke anon/authenticated write access on public.spatial_ref_sys — REVOKE as postgres silently does nothing

[lianasekro-ship-it]

Summary: anon and authenticated hold INSERT/UPDATE/DELETE on
public.spatial_ref_sys and I cannot revoke them as postgres.

Impact: the table is writable through the Data API with the public anon key.
We use PostGIS geography for delivery distances, so an anonymous write to the
SRID table would break delivery pricing. Ongoing.

Expected behaviour: the API roles should not hold write privileges on a
PostGIS system table.

Actual behaviour: relacl shows
anon=arwdDxtm/supabase_admin, authenticated=arwdDxtm/supabase_admin,
owner supabase_admin.

Steps to reproduce:

1. REVOKE ALL ON TABLE public.spatial_ref_sys FROM anon, authenticated;
2. SELECT has_table_privilege('anon','public.spatial_ref_sys','DELETE');
   -> still true

Troubleshooting completed: REVOKE as postgres completes without error and
changes nothing (postgres is neither the grantor nor a superuser).
ALTER TABLE ... ENABLE ROW LEVEL SECURITY fails on ownership. All
application tables are already RLS-protected and verified against the Data
API. Dropping PostGIS is not an option — orders.delivery_coords,
active_deliveries.last_known_coords, delivery_zones.boundary and
fulfillment_nodes.location are geography columns.

Environment: project qeuwnzzvzcvfxtwqemri, region ap-northeast-2,
Postgres 17.6.1.084, PostGIS in the public schema.

Request: revoke INSERT, UPDATE, DELETE on public.spatial_ref_sys from anon
and authenticated. Same for EXECUTE on the three
public.st_estimatedextent overloads, unless they must stay public.

[GaryAustin1]

You need to reinstall postgis in the proper extensions schema (default setting).
It should not be in the public schema.

[patchandship]

Supabase documents this case on its PostGIS page, under Troubleshooting, "Security advisor flags public.spatial_ref_sys": https://supabase.com/docs/guides/database/extensions/postgis

The cause is PostGIS being installed in public, which the Data API exposes. Your postgres role doesn't own spatial_ref_sys, so enabling RLS isn't possible, and the docs say RLS isn't the fix anyway. The fix is to move PostGIS out of public into the extensions schema.

The page gives two ways:

1. Do it yourself. Back up first (the CLI or pg_dumpall), then DROP EXTENSION postgis CASCADE; and create it again in the new schema. CASCADE also drops objects that depend on PostGIS, which includes your geography columns, so plan to restore them and their data from the backup.
2. Contact Supabase Support. The docs suggest this if you want to avoid the rebuild. Your has_table_privilege results and the st_estimatedextent grants are useful to include in the ticket, since the docs don't cover those functions.

For what it's worth, Supabase's page calls this "expected, not a data exposure risk", because the table holds coordinate system definitions, not your data.

[solvionholdings]

The fix above (move PostGIS out of public) is the right one. On why the REVOKE "silently does nothing":

The relacl shows anon=arwdDxtm/supabase_admin, so the grantor is supabase_admin. Per the REVOKE docs (https://www.postgresql.org/docs/17/sql-revoke.html), a non-owner's REVOKE only removes privileges it holds grant options for, and otherwise issues a warning, not an error. The SQL Editor doesn't show those warnings (Supabase mentions this in https://supabase.com/kb/troubleshooting/revoking-access-to-pg_net-objects-has-no-effect). In psql you'd see the WARNING.

To check any revoke actually worked, look at grantors, not just has_table_privilege:

select a.grantee::regrole, a.grantor::regrole, a.privilege_type
from pg_class c, aclexplode(c.relacl) a
where c.oid = 'public.spatial_ref_sys'::regclass
order by 1, 3;

Any row whose grantor isn't you is one you can't revoke. That needs the owner, support, or moving the object out of the exposed schema.

[cekuu35]

You diagnosed it correctly, and the diagnosis is the answer: the grants you need removed were made by supabase_admin, and postgres cannot revoke a grant it did not make and does not hold grant option on. The relacl string is the whole story — arwdDxtm/supabase_admin says the access exists because supabase_admin granted it, and the REVOKE ... FROM anon you ran is a no-op for the same reason a GRANT by an unrelated role would be.

Why every path you tried is closed

• REVOKE without being the grantor or a superuser completes silently — you reproduce this, and it matches the docs: revoking requires you to be the grantor, or hold grant option, or be superuser.
• ENABLE ROW LEVEL SECURITY requires the table owner (supabase_admin), so RLS on that table is not available to you.
• Differences from the extension-owned-table behavior you may have read about: the Security Advisor lint was changed in July to exclude extension-owned tables (fix(advisor): exclude extension-owned tables from RLS disabled lint #48234) — that fixed the advisor noise, not the grants. The grants on the PostGIS system tables are ours to clean up, not yours.

The supported fix is the same request you already made, with the revoke as a migration rather than a paragraph. The statement support should run, and the evidence that makes it a two-minute ticket:

revoke insert, update, delete on table public.spatial_ref_sys from anon, authenticated;
revoke all on function public.st_estimatedextent from anon, authenticated;

Include the relacl line and has_table_privilege('anon','public.spatial_ref_sys','DELETE') returning true after your own revoke attempt — that pair proves the request is not a duplicate of a change that already landed. On the st_estimatedextent question: it is a plain measurement function, safe to make non-public, but it also does not need to be public; the same revoke covers all three overloads.

What you can do right now, without the ticket, in order of usefulness

1. Alert on the write path. You cannot trigger or policy on the table, but Postgres logs statements, and Supabase's log drain / Logflare exposes them. Add a filter on relation_name = 'spatial_ref_sys' with command tags INSERT/UPDATE/DELETE and page yourself on a hit. Given a geography column and delivery pricing downstream, the thing you actually need is minutes-level detection, and this gives it today.
2. Shrink what the anon key reaches. The Data API exposes every table in the exposed schema. If your application's PostGIS usage is all server-side, the supported move is to move the application tables into a dedicated schema and point the exposed schema there... but note this is a bigger lever than this problem warrants, and PostGIS in public is the default layout the platform documents. Worth knowing about; I would not do it for one system table.
3. Verify from the outside, continuously. A one-line cron (Supabase scheduled Edge Function) attempting insert into public.spatial_ref_sys (srid, auth_name, auth_srid, srtext, proj4text) values (...) with the anon key, asserting a 401/403, gives you the same signal as an attacker's retry and turns the support ticket from "trust me" into "here is the repro." A select attempt works today; a write attempt also works today — that is what the ticket needs to say.

What the platform change from 30 October means for this table, since it is adjacent

The change removed automatic grants on newly created public tables; spatial_ref_sys predates it and keeps its existing ACL. So this is not a regression from that change and it is not something your migrations can fix — which is also the answer to the natural next question ("can I add a grant-control migration?"): no, the ACL entry was made by a role your session cannot sign for.

One expectation-setting note

The platform owner of that table runs the same revoke as a managed migration; there is no client-side sequencing that reproduces it. Meanwhile the alert in step 1 plus the repro in step 3 are what I would hold myself to until the ticket closes.