[postgis-tickets] [PostGIS] #5199: Cannot access remote raster files when querying raster

[PostGIS]

#5199: Cannot access remote raster files when querying raster
------------------------+---------------------------
 Reporter:  tuannguyen  |      Owner:  pramsey
     Type:  defect      |     Status:  new
 Priority:  medium      |  Milestone:  PostGIS 3.2.3
Component:  postgis     |    Version:  3.2.x
 Keywords:              |
------------------------+---------------------------
 I'm using raster2pgsql tool to ingest raster data into Postgres database.
 The files are stored in Google Cloud Storage with private access. To
 provide access, I did following things:
 * provide keys by running

 {{{
 export GS_SECRET_ACCESS_KEY=<secret>

 export GS_ACCESS_KEY_ID=<access id>
 }}}

 * Enable related extensions and configurations

 {{{
 CREATE EXTENSION postgis_raster;
 ALTER DATABASE postgres SET postgis.enable_outdb_rasters = true;
 ALTER DATABASE postgres SET postgis.gdal_enabled_drivers TO 'ENABLE_ALL';
 }}}

 * Loading raster data into database

 {{{
 raster2pgsql -s 990000 -t 256x256 -I -R /vsigs/<bucket>/pop12.tif pop12 |
 psql -h localhost -U postgres
 }}}

 * provide access keys to database to read from cloud rasters

 {{{
 alter database postgres
 SET postgis.gdal_vsi_options='GS_SECRET_ACCESS_KEY=ABC
 GS_ACCESS_KEY_ID=ABC'
 }}}

 When doing query,

 {{{
 select
         ST_Intersection(rast,
         ST_GeomFromEWKT('SRID=990000;POLYGON((-2764474 3232111, -2764374
 3232111, -2764374 3232211, -2764474 3232211, -2764474 3232111))'))
 from
         pop12
 limit 1;
 }}}

 I got following error

 {{{
 ERROR:  rt_band_load_offline_data: Cannot open offline raster: /vsigs
 /raster-bucket/pop12.tif
 CONTEXT:  SQL function "st_pixelaspolygons" statement 1
 SQL statement "SELECT public.ST_Buffer(public.ST_Collect(t.geom), 0)
 FROM public.ST_PixelAsPolygons(rast, nband) AS t"
 PL/pgSQL function _st_intersects(geometry,raster,integer) line 21 at SQL
 statement
 PL/pgSQL function st_intersection(geometry,raster,integer) line 11 at
 assignment
 SQL function "st_intersection" statement 1
 }}}
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5199>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.