[raster] Create out-db file using ST_AddBand

[gdrolet]

Following the examples in the manual, I wanted to create a new out-db file using ST_AddBand:

SELECT ST_AddBand(
	ST_MakeEmptyRaster(10, 10, 0, 0, 50), 1,
	'E:\Temp\mytestraster.tif'::text, ARRAY[]::integer[]);

Which returns with this error:

ERROR:  RASTER_addBandOutDB: Could not open out-db file with GDAL
********** Error **********

After checking that the permissions on the path are ok, I realized that the out-db file must already exist somewhere in the system for the query to work (or am I wrong?).

I think it would be nice if we had the possibility to create a new raster file (registered as an out-db file in the database) using the query above or something similar.

PostGIS version:

POSTGIS="2.3.0dev r14604" GEOS="3.5.0-CAPI-1.9.0 r4090" PROJ="Rel. 4.9.1, 04 March 2015" GDAL="GDAL 2.0.1, released 2015/09/15" LIBXML="2.7.8" LIBJSON="0.12" RASTER

PostgreSQL 9.4 on Windows 7 Enterprise 64-bit (SP1)

[robe]

Do you have out-db enabled. By default it's disabled.

​http://postgis.net/docs/manual-dev/postgis_enable_outdb_rasters.html

To enable across all databases try

ALTER SYSTEM SET postgis.enable_outdb_rasters TO true;

-- followed by the below and then reconnecting
SELECT pg_reload_conf();

[robe]

May also want to consider changing the slash to E:/Temp … I forget if raster is sensitive to that issue, but for example with many FDWs on windows I still need to use Unix like slashes.

[robe]

gdrolet did my suggestions help any?

[gdrolet]

Replying to robe:

gdrolet did my suggestions help any?

Thanks for your suggestions but gdal drivers were already enabled. Changing backslashes for slashes in the path didn't help either.

[dustymugs]

@robe. I think gdrolet is asking that ST_AddBand create the out-db file if one is found at the path indicated. This will never happen as out-db is intentionally read-only to mitigate any security risks.

[robe]

I think you are right. Well in that case I'll close this out.

gdrolet feel free to reopen if that is not what you were asking.

[pracine]

Replying to dustymugs:

What kind of security risk?

[gdrolet]

Replying to dustymugs:

@robe. I think gdrolet is asking that ST_AddBand create the out-db file if one is found at the path indicated. This will never happen as out-db is intentionally read-only to mitigate any security risks.

I was asking that ST_AddBand create the out-db file if one is NOT found at the path indicated (and the user has write permission at the path location). Would that be possible?

[robe]

Like dustymugs said, we'd consider that a security hole so wouldn't build that feature in PostGIS. The permissions on the OS always run under the postgres account, so there is no idea of permissions for the user distinct from permissions from daemon account.

Now that said, if you really wanted to, you might be able to achieve it with the the COPY TO PROGRAM (which is only available to postgres super user accounts) feature in PostgreSQL ​http://www.depesz.com/2013/02/28/waiting-for-9-3-add-support-for-piping-copy-tofrom-an-external-program/

and then once it's created, attach to it as you did above.