Security Advisor flags spatial_ref_sys and other PostGIS objects in the public schema

When PostGIS is installed in the public schema, the Security Advisor reports findings on spatial_ref_sys and other objects that PostGIS creates there. This page helps you check whether your project is affected and resolve the findings by moving PostGIS to the extensions schema.

Overview

The Security Advisor reports the following findings for PostGIS objects in public:

These objects hold PostGIS reference data, plus any coordinate systems you added to spatial_ref_sys. The main risk is that anyone with your project’s anon key can change the coordinate system definitions in spatial_ref_sys, which breaks PostGIS queries. For details, see What the findings expose.

These findings aren’t false positives. The Security Advisor reports them for as long as PostGIS is installed in public.

Why you can’t fix the objects themselves

Supabase creates extensions as the supabase_admin role, so supabase_admin owns spatial_ref_sys and every other PostGIS object. In the public schema, the default privileges of supabase_admin give anon, authenticated, and service_role all privileges on new tables and execute on new functions. PostGIS objects created in public receive these grants, and they receive them again each time PostGIS is created in public.

Only a table’s owner can enable Row Level Security on it or create policies for it. Enabling Row Level Security or creating a policy on spatial_ref_sys fails with the following error:

1
ERROR:  42501: must be owner of table spatial_ref_sys

A role can revoke only the privileges that it granted, so the postgres role can’t remove the grants that supabase_admin made. A revoke on these objects prints WARNING: no privileges could be revoked and changes nothing. The SQL Editor doesn’t display warnings, and a migration that contains the revoke completes, so the statement looks successful. For more information, see the Postgres documentation on REVOKE.

The following approaches also don’t resolve the findings:

• Asking Support to change the grants. Only Supabase Support can change the grants on PostGIS objects, because supabase_admin made them. Supabase doesn’t recommend it, and can’t guarantee that PostGIS keeps working correctly after such a change.
• A trigger on spatial_ref_sys that blocks writes. The postgres role can create the trigger but can’t drop it, because only the table’s owner can drop a trigger. Dropping it fails with must be owner of relation spatial_ref_sys.

Move PostGIS instead, as described in Solve the problem.

What the findings expose

spatial_ref_sys holds the coordinate system definitions that PostGIS ships with, plus any that you added. Anyone with your project’s anon key can insert, update, and delete its rows through the Data API. If a row that your queries use is changed or deleted, PostGIS functions that use that spatial reference ID (SRID) fail. For example, after the row for SRID 4326 is deleted, geography functions fail:

1
ERROR:  XX000: Cannot find SRID (4326) in spatial_ref_sys

The other findings expose less:

• st_estimatedextent is a security definer function, so it runs with the privileges of supabase_admin. Any role that can call it can read the estimated bounding box of a geometry column in any table, including tables that the role can’t select from.
• The Security Advisor names /rest/v1/rpc/st_estimatedextent as the path. The function’s parameters have no names, so the Data API can’t call it and returns a PGRST202 error. Roles that connect to the database directly, such as roles you create with login, can call it.
• geometry_columns and geography_columns list only the tables that the calling role can select from.

Diagnose the problem

Check which schema PostGIS is in

To see which schema PostGIS is installed in, and whether the anon role can change spatial_ref_sys, run the following SQL using the SQL Editor in the Supabase Dashboard or with psql over a direct connection. To connect with psql, see Direct connection.

1
select
2
  extnamespace::regnamespace as postgis_schema,
3
  has_table_privilege(
4
    'anon',
5
    extnamespace::regnamespace || '.spatial_ref_sys',
6
    'insert, update, delete'
7
  ) as anon_can_write
8
from pg_extension
9
where extname = 'postgis';

Check whether rows in spatial_ref_sys were changed

To count the rows changed in spatial_ref_sys since the database statistics were last reset, run the following SQL using the SQL Editor in the Supabase Dashboard or with psql over a direct connection. To connect with psql, see Direct connection.

1
select n_tup_upd as rows_updated, n_tup_del as rows_deleted
2
from pg_stat_user_tables
3
where relname = 'spatial_ref_sys';

The query doesn’t count inserted rows, because the rows that PostGIS loads when the extension is created also count as inserts. To find inserts, and to confirm what happened, query the logs for Data API requests that tried to insert, update, or delete rows:

1. Open Explorer and select Run SQL.

2. Select Logs as the query source, and choose a time range.

3. Run the following query:

   1
   select
   2
     timestamp,
   3
     log_attributes['request.method'] as method,
   4
     log_attributes['request.path'] as path
   5
   from logs
   6
   where source = 'edge_logs'
   7
     and log_attributes['request.path'] like '%/spatial_ref_sys%'
   8
     and log_attributes['request.method'] in ('POST', 'PATCH', 'PUT', 'DELETE')
   9
   order by timestamp desc
   10
   limit 100;

From a terminal, send the same query to the Management API endpoint GET /v1/projects/{ref}/analytics/endpoints/logs, in the sql query parameter. Set iso_timestamp_start and iso_timestamp_end to a range of up to 24 hours. Repeat the request to cover a longer period.

Logs are available only for the retention period of your plan. For more information about running log queries, see Advanced log filtering.

Read the output

If rows were inserted, updated, or deleted, compare spatial_ref_sys with the same table in a project with the same PostGIS version. To find your version, run select extversion from pg_extension where extname = 'postgis';.

How you repair the table depends on how you move PostGIS:

• If you drop and re-create PostGIS, the rows that PostGIS ships are restored. After the move, delete the rows you didn’t add.
• If Support moves PostGIS for you, the rows move unchanged. After the move, restore the missing rows, replace the updated rows, and delete the rows you didn’t add.

Solve the problem

By default, the Data API doesn’t expose the extensions schema, and the Security Advisor doesn’t report these findings for objects in it. Moving PostGIS there resolves all of these findings. The Dashboard doesn’t let you add extensions to the exposed schemas in your Data API settings. Keep it out of them, because anon and other roles can still read or call PostGIS objects after the move.

First prepare your functions. Then choose how to move PostGIS:

• If your tables have PostGIS columns, ask Supabase Support to move PostGIS.
• If they don’t, you can drop and re-create PostGIS yourself.

Prepare your functions

The postgres role and the Data API include extensions in their search path. Their queries that use PostGIS functions and types without a schema name keep working after the move. Roles you create with login don’t include extensions in their search path. Functions that set their own search_path keep the value they have. If that value doesn’t include extensions, the function fails after the move with an error such as function st_distance(extensions.geography, extensions.geography) does not exist.

1. For each role you created with login, run the following SQL using the SQL Editor in the Supabase Dashboard or with psql over a direct connection. To connect with psql, see Direct connection. Replace app_user with the role name:

   1
   alter role app_user set search_path = "$user", public, extensions;

   The setting applies to new connections.

2. List your functions that use PostGIS and set a search_path without extensions:

   1
   select p.oid::regprocedure as function_name, p.proconfig as settings
   2
   from pg_proc p
   3
   where p.prosrc ~* '\m(st_\w+|geometry|geography)\M'
   4
     and not exists (
   5
       select 1 from pg_depend d
   6
       where d.objid = p.oid and d.deptype = 'e'
   7
     )
   8
     and exists (
   9
       select 1
   10
       from unnest(p.proconfig) as setting
   11
       where setting like 'search_path=%'
   12
         and setting not like '%extensions%'
   13
     );

3. For each function in the result, add extensions to the end of its current search path, and keep the schemas it already lists in the same order. For example, for a function with search_path=public, run:

   1
   alter function public.my_function(integer)
   2
     set search_path = public, extensions;

   If a function uses an empty search path and calls PostGIS with the public. prefix, such as public.st_x(geom), change the prefix to extensions..

4. If your app calls PostGIS functions through the Data API, such as with supabase.rpc('st_distance'), create a function in public that calls the PostGIS function, and set its search path to include extensions. Those calls stop working after the move, because the Data API doesn’t expose extensions. For example, to return the distance in meters between two points:

   1
   create function public.distance_meters(
   2
     lon1 float8, lat1 float8, lon2 float8, lat2 float8
   3
   )
   4
   returns float8
   5
   language sql
   6
   stable
   7
   set search_path = public, extensions
   8
   as $$
   9
     select st_distance(
   10
       st_setsrid(st_makepoint(lon1, lat1), 4326)::geography,
   11
       st_setsrid(st_makepoint(lon2, lat2), 4326)::geography
   12
     );
   13
   $$;

   Then change your app to call your function, such as with supabase.rpc('distance_meters', { lon1, lat1, lon2, lat2 }). The function works before and after the move.

Option 1: Ask Supabase Support to move PostGIS

Support moves the extension without dropping it, so your tables keep their PostGIS columns, data, and indexes, and views that use PostGIS keep working. The move runs in a single transaction, so it either completes or changes nothing. It doesn’t restart your database.

1. Prepare your functions.
2. Contact Supabase Support and ask them to move PostGIS to the extensions schema.

Option 2: Drop and re-create PostGIS yourself

PostGIS doesn’t support moving to another schema, so alter extension postgis set schema extensions fails with extension "postgis" does not support SET SCHEMA. To move it yourself, drop it and create it again in extensions.

1. Back up your roles, schema, and data. For the commands, see Back up database using the CLI.

2. Prepare your functions.

3. List the extensions that depend on PostGIS, such as postgis_topology and postgis_raster. The drop removes them too, so record the result. Run the following SQL using the SQL Editor in the Supabase Dashboard or with psql over a direct connection. To connect with psql, see Direct connection.

   1
   select e.extname, e.extnamespace::regnamespace as schema
   2
   from pg_depend d
   3
   join pg_extension e on e.oid = d.objid
   4
   where d.classid = 'pg_extension'::regclass
   5
     and d.refobjid = (
   6
       select oid from pg_extension where extname = 'postgis'
   7
     );

4. Stop your app’s traffic to the database. From the next step until the last one, the PostGIS columns, views, and functions that the drop removes don’t exist.

5. Drop PostGIS and create it in the extensions schema in one transaction. The transaction copies spatial_ref_sys first and adds back the rows that PostGIS doesn’t ship, such as coordinate systems you added. If any statement fails, the transaction rolls back and PostGIS stays where it was:

   1
   begin;
   2
   create temp table spatial_ref_sys_backup on commit drop
   3
     as table public.spatial_ref_sys;
   4
   drop extension postgis cascade;
   5
   create extension postgis with schema extensions;
   6
   insert into extensions.spatial_ref_sys
   7
   select * from spatial_ref_sys_backup
   8
   on conflict (srid) do nothing;
   9
   commit;

   The insert also adds back rows that someone else inserted through the Data API. To find and delete them, see Check whether rows in spatial_ref_sys were changed.

6. Re-create each extension that you listed. postgis_raster must be in the same schema as PostGIS, and postgis_topology creates its own topology schema. For example:

   1
   create extension postgis_raster with schema extensions;
   2
   create extension postgis_topology;

7. Re-create the PostGIS columns, indexes, views, and functions that the drop removed, and restore their data from your backup. Then resume your app’s traffic.

Reconnect your app and fix Database Webhooks

Database connections that used PostGIS before the move still look for spatial_ref_sys in public. On those connections, PostGIS functions fail until the connection is closed:

1
ERROR:  42P01: relation "public.spatial_ref_sys" does not exist

If your project has Database Webhooks on tables with PostGIS columns, inserts and updates on those tables fail with permission denied for schema extensions. The webhook trigger runs as part of the insert or update, so the write itself fails. Rows whose PostGIS column is null still succeed.

After either option:

1. Restart your app or its connection pool so that it opens new connections. To close every connection at once, including those of the Data API, restart the project from General settings, or send POST /v1/projects/{ref}/restart to the Management API.

2. If you use Database Webhooks on tables with PostGIS columns, give the webhook role access to the PostGIS types. Run the following SQL using the SQL Editor in the Supabase Dashboard or with psql over a direct connection. To connect with psql, see Direct connection.

   1
   grant usage on schema extensions to supabase_functions_admin;

Confirm the problem is solved

1. Run the query from Check which schema PostGIS is in. postgis_schema is extensions.

   If Support moved PostGIS, anon_can_write can still be true, because the PostGIS objects keep their grants. The Data API can’t reach them while extensions isn’t an exposed schema.

2. Open the Security Advisor and click Rerun linter, or send GET /v1/projects/{ref}/advisors/security to the Management API. The findings for PostGIS objects no longer appear.

3. Run the function-listing query from Prepare your functions again. It returns no rows.

4. If you use Database Webhooks on tables with PostGIS columns, insert or update a row in one of those tables. Check that the write succeeds and that the webhook request is sent.

Prevent the problem

To keep PostGIS out of public on new projects, branches, and local databases, create it with a schema in your migrations:

1
create extension if not exists postgis with schema extensions;

if not exists doesn’t move PostGIS that’s already installed in public. If an earlier migration creates PostGIS without a schema, change that migration. After you apply your migrations to a new database, check the schema with the query in Check which schema PostGIS is in.