Cannot access remote raster files when querying raster

[tuannguyen]

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

[tuannguyen]

For more information, I was running database container with docker image postgis/​postgis:14-3.2. It turns out that the image is not shipped with ca-certificates as the container logs show

ERROR 11: CURL error: server certificate verification failed. CAfile: none CRLfile: none
2022-08-05 11:43:47.299 UTC [243] ERROR:  rt_band_load_offline_data: Cannot open offline raster: /vsigs/<bucket>/pop12.tif
2022-08-05 11:43:47.299 UTC [243] 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

Install that package into the container and the database succeeded to return the result.

[robe]

User fixed error by installing ca-certificates.

issue is with the docker image.

@tuannguyen I've reported this issue to the docker-postgis project -

​https://github.com/postgis/docker-postgis/issues/307

[ImreSamu]

PR: ​https://github.com/postgis/docker-postgis/pull/311

• adding ca-certificates for all images ( debian,alpine,master )

[ImreSamu]

PR accepted / merged : ​https://github.com/postgis/docker-postgis/pull/311

And the new docker image ( with ca-certificates ) available for user testing: docker pull postgis/postgis:14-3.2

@tuannguyen: If you have some time, we'd love to have you test it.