[gdal-dev] Invalid search_path created in ogrpgdatasource.cpp

[Boss, Darren WLRS:EX]

I have a Python back-end that uses gdal.Warp to clip a GeoTiff using a geometry from a Postgres 14 (with PostGIS) table. I set up the gdal.warpOptions and pass them into gdal.warp as follows:

DB_READ_STRING = "postgresql://{read_user}:{postgres_password}@{postgres_read_host}:{postgres_port}/{postgres_database}"
warp_options = gdal.WarpOptions(format="GTiff", cutlineDSName=DB_READ_STRING, cutlineSQL=f"SELECT geom FROM my_table WHERE id=1", cropToCutline=True)
res = gdal.Warp(output_path, my_raster, options=warp_options)

This has been working great with gdal 3.4 in a K8s cluster running in a private cloud using an Ubuntu 20 base image. I’m in the process of updating to an Ubuntu 24 LTS image which will be using gdal 3.8.4 and running into problems where the table name (my_table) in the cutlineSQL can’t be found. The problem seems to be related to the search_path and the changes made in this PR: https://github.com/OSGeo/gdal/pull/8642

I turned ON the CPL_DEBUG flag and see the following when trying to perform the warp:

PG: Client encoding: 'UTF8'
PG: PostGIS schema: 'public'
PG: Modifying search_path from "" to '',"",'public'
PG: Command "SET search_path='',"",'public'" failed. Trying without 'public'.

I managed to narrow down the source of the issue and workaround the problem but I think there is room for improvement in gdal.

I narrowed the problem down to ogrpgdatasource.cpp which executes a query (SHOW search_path) to retrieve the search_path. In my case the search_path being returned was an empty string (or maybe it was a string containing a pair of double quotes like ‘””’ or NULL, I’m not sure). The code in ogrpgdatasource.cpp would then create a new search_path with three comma separated values: ‘’, “”, public. Gdal then tries to set this as the search_path and postgres complains about the empty string with double quotes.

SET search_path=‘’,“”,public
ERROR:  zero-length delimited identifier at or near """"

It looks like the issue stemmed from the fact that the user specified in the connection string does not have an associated schema. The workaround in my case was to create a matching schema and explicitly set the search path for my user to “$user”,public. Simply setting the search_path to $user/public for my role or user was insufficient, I needed the schema and also had to grant usage on the new schema to the appropriate role. Setting the search path on the database was also insufficient to resolve the issue. Here’s the debug output now:

PG: Client encoding: 'UTF8'
PG: PostGIS schema: 'public'
PG: Modifying search_path from "$user", public to '',"$user", public

So yes, there was likely a configuration issue in my crunchyDB cluster, but my question is, would gdal’s creation of an invalid search_path with the inclusion of an empty, double quoted string be considered a bug from your point-of-view? The empty, single quoted string that gdal adds to the search_path also seems odd but doesn’t appear to cause any issues.

I’m happy to create a new issue in GitHub if you feel this is an issue.

Thanks,

Darren Boss
Lead Full Stack Developer, Development & Digital Services
Water, Land & Resource Stewardship | NRIDS
Darren.Boss at gov.bc.ca<mailto:Darren.Boss at gov.bc.ca>


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20241009/d6ee3a57/attachment.htm>