Invalid search path when listing tables from hosted Postgres databases (like Supabase or Neon)

[arredond]

What is the bug?

Hosted Postgres solutions like Supabase or Neon frequently set the search path to an empty string. When this happens, GDAL ends up composing an invalid search path and producing an error with the message ERROR: relation "geometry_columns" does not exist (despite the geometry_columns view not being the actual issue).

This can be worked around by setting the ACTIVE_SCHEMA open option to any valid schema (like public).

Steps to reproduce the issue

Copy the following snippet in a terminal to list tables from a Supabase database using a read-only user. This should be successful the first time:

ogrinfo -so -al "postgresql://read_only_user.amcliteeqmfizqcmlmpf:read_only_pwd@aws-0-us-east-1.pooler.supabase.com:6543/postgres"

Now run the same command again and verify that you see the following error:

ERROR 1: ERROR:  zero-length delimited identifier at or near """"
LINE 1: SET search_path='',"", "$user", public, extensions
                           ^

ERROR 1: ERROR:  function postgis_version() does not exist
LINE 1: SELECT postgis_version()
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

INFO: Open of `postgresql://read_only_user.amcliteeqmfizqcmlmpf:read_only_pwd@aws-0-us-east-1.pooler.supabase.com:6543/postgres'
      using driver `PostgreSQL' successful.
ERROR 1: ERROR:  relation "geometry_columns" does not exist
LINE 1: ... JOIN pg_namespace n ON c.relnamespace=n.oid JOIN geometry_c...
                                                             ^

ERROR 1: ERROR:  relation "geometry_columns" does not exist
LINE 1: ... JOIN pg_namespace n ON c.relnamespace=n.oid JOIN geometry_c...

To understand what's going on, we can log into the database using psql and show the current search path at every step:

psql -Atx "postgresql://read_only_user.amcliteeqmfizqcmlmpf:read_only_pwd@aws-0-us-east-1.pooler.supabase.com:6543/postgres"

And now, inside the Postgres shell:

-- Before running any ogrinfo commands
SHOW search_path;  --  Returns an empty string: `""`

-- After running the ogrinfo command once (successfully) - GDAL has appended the schema where it found the PostGIS extension
SHOW search_path; -- Returns "", extensions

-- When running the ogrinfo command again, GDAL composes an invalid search path and fails

Versions and provenance

• GDAL version: GDAL 3.10.0, released 2024/11/01
• Operating system: macOS Sonoma 14.5 (23F79)
• Installed via homebrew

In this example, Postgres and PostGIS versions are managed by Supabase and outside the user's control:

• Postgres version: PostgreSQL 15.6 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 13.2.0, 64-bit
• PostGIS version: 3.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1

Additional context

No response