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

[Even Rouault]

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.Boss at gov.bc.ca <mailto:Darren.Boss at gov.bc.ca>
>
>
> _______________________________________________
> gdal-dev mailing list
> gdal-dev at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/gdal-dev

-- 
http://www.spatialys.com
My software is free, but my time generally not.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20241009/88b58775/attachment-0001.htm>