Darren,
thanks for the report. I could indeed reproducing setting first an empty
search path (single quote single quote)
alter role {my_user_name} set search_path='';
Fix in https://github.com/OSGeo/gdal/pull/10980
Even
Le 09/10/2024 à 21:18, Boss, Darren WLRS:EX via gdal-dev a écrit :
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.b...@gov.bc.ca <mailto:darren.b...@gov.bc.ca>
_______________________________________________
gdal-dev mailing list
gdal-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/gdal-dev
--
http://www.spatialys.com
My software is free, but my time generally not.
_______________________________________________
gdal-dev mailing list
gdal-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/gdal-dev
