Migration fails if postgis is already installed on a non-public schema

First of all, thanks for your work on this project! I'm looking forward to using it.

I'm trying to run migrations on an existing PostGIS database in which the postgis extension is installed on a schema called postgis. The database's current SEARCH_PATH includes this schema, but it's being overwritten by the following code:

https://github.com/stac-utils/pgstac/blob/main/src/pgstac/sql/000_idempotent_pre.sql#L101-L103
https://github.com/stac-utils/pgstac/blob/main/src/pgstac/sql/000_idempotent_pre.sql#L117
https://github.com/stac-utils/pgstac/blob/main/src/pgstac/sql/005_tileutils.sql#L1
https://github.com/stac-utils/pgstac/blob/main/src/pgstac/sql/006_tilesearch.sql#L1

The overwritten SEARCH_PATH causes the migration to fail because it can no longer find the geometry type in the postgis schema. It also removes other existing schemas from my current SEARCH_PATH, which breaks existing queries that use those schemas.

Rather than completely overwriting the existing SEARCH_PATH, would it be possible to just append the pgstac schema to the current SEARCH_PATH if it isn't included already? E.g.:

DO $$
  DECLARE
    search_path varchar := (SELECT current_setting('search_path'));
  BEGIN
    IF search_path NOT LIKE '%pgstac%' THEN
      SELECT set_config('search_path', 'pgstac,'||current_setting('search_path'), false);
    END IF;
  END
$$;

If the postgis extension is already installed on a non-public schema, it would also be necessary to ensure that pgstac has permission to use that schema, e.g.:

DO $$
  DECLARE
    postgis_schema varchar := (
      SELECT nspname FROM pg_namespace
      JOIN pg_extension ON pg_namespace.oid = extnamespace
      WHERE extname='postgis'
    );
  BEGIN
    EXECUTE format('GRANT USAGE ON SCHEMA %s TO pgstac_admin', postgis_schema);
  END
$$;

I'm not a SQL expert, so there may be a more elegant way to do this using a CTE.

In any case, it would be nice to provide some way to migrate an existing PostGIS database regardless of where the postgis extension was installed, and to not remove any schemas from the current SEARCH_PATH. Failing that, it would be helpful to update the documentation to indicate that the postgis extension needs to be installed on the public schema for the migrations to work, and to warn users that migrating will change the current SEARCH_PATH of their database.

Start by reading the SEARCH_PATH handling in src/pgstac/sql/000_idempotent_pre.sql, then inspect the schema assumptions in 005_tileutils.sql and 006_tilesearch.sql. Verify the migration against a database with PostGIS installed in a non-public schema. Done means migrations succeed, preserve existing SEARCH_PATH entries, and retain access to the PostGIS types.