PostGIS: postgis_raster out-db storage errors

Question

PostGIS raster out-db storage doesn't work on my Windows 10 PostGIS installation. No issue with in-db rasters.

SELECT postgis_full_version();

POSTGIS="3.3.1 3.3.1" [EXTENSION] PGSQL="150" GEOS="3.11.0-CAPI-1.17.0" PROJ="7.2.1" GDAL="GDAL 3.4.3, released 2022/04/22" LIBXML="2.9.9" LIBJSON="0.12" LIBPROTOBUF="1.2.1" WAGYU="0.5.0 (Internal)" RASTER

I used raster2pgsql to load my tiff file with the -R flag (full file path):

raster2pgsql.exe -I -F -R C:\Users\MY_USER\path\to\raster.tiff my_raster_table | psql.exe
 postgresql://...

postgis_raster and GDAL drivers are set:

CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS postgis_raster;

ALTER DATABASE mydb SET postgis.enable_outdb_rasters = true;
ALTER DATABASE mydb SET postgis.gdal_enabled_drivers TO 'ENABLE_ALL';

I can read raster's metadata, but not the raster itself:

SELECT ST_SummaryStats(rast) 
FROM my_raster_table 

ERROR: rt_band_load_offline_data: Cannot open offline raster: C:\Users\MY_USER\path\to\raster.tiff
CONTEXT:  fonction SQL « st_summarystats », instruction 1

SQL state: XX000

Same issue if I try to add a band using the tiff file:

-- https://postgis.net/docs/RT_ST_AddBand.html
SELECT
    *
FROM ST_BandMetadata(
    ST_AddBand(
        ST_MakeEmptyRaster(10, 10, 0, 0, 1, -1, 0, 0, 0),
        'C:\Users\MY_USER\path\to\raster.tiff'::text, NULL::int[]
    ),
    ARRAY[]::integer[]
);

ERROR: RASTER_addBandOutDB: Cannot open out-db file with GDAL
CONTEXT:  fonction SQL « st_addband », instruction 1


SQL state: XX000

I tried to set postgis.gdal_datapath manually:

ALTER DATABASE mydb SET postgis.gdal_datapath = 'C:\Program Files\PostgreSQL\15\gdal-data';

The raster file is in my user directory, so it would be surprising if it wasn't accessible by PostgreSQL.

Answer 1

Moving my raster files to the C:\ root folder solved my problem.

This has to do with the network service account the postgresql service is running on, and it has nothing to do with your user account. On my computer, this network service account does not have read access in my user folder (corporate security policy).

A specific network service account must be created, with the appropriate read permissions.