RE: Advice on loading OpenTopography data into a PostGIS database apreciated

Is there a reason you don't want to set it at the database or system level.

ALTER DATABASE treintaytres SET postgis.gdal_enabled_drivers = 'ENABLE_ALL';

Then I think the setting would apply to any user.

> -----Original Message-----
> From: [email protected] <[email protected]>
> Sent: Saturday, November 2, 2024 8:39 AM
> To: [email protected]
> Subject: Re: Advice on loading OpenTopography data into a PostGIS database
> apreciated
> 
> 
> [email protected] escribió:
> > treintaytres=> set postgis.gdal_enabled_drivers = 'ENABLE_ALL'; SET
> > treintaytres=> WITH foo AS (
> >     SELECT
> >
> ST_AsPNG(ST_AddBand(ST_AddBand(ST_AddBand(ST_MakeEmptyRaster(2,
> 2, 0,
> > 0, 0.1, -0.1, 0, 0, 4326), 1, '8BUI', 1, 0), 2, '8BUI', 2, 0), 3,
> > '8BUI', 3, 0)) AS png ), bar AS (
> >     SELECT 1 AS rid, ST_FromGDALRaster(png) AS rast FROM foo
> >     UNION ALL
> >     SELECT 2 AS rid, ST_FromGDALRaster(png, 3310) AS rast FROM foo
> > )
> > SELECT
> >     rid,
> >     ST_Metadata(rast) AS metadata,
> >     ST_SummaryStats(rast, 1) AS stats1,
> >     ST_SummaryStats(rast, 2) AS stats2,
> >     ST_SummaryStats(rast, 3) AS stats3 FROM bar ORDER BY rid;
> > WARNING:  permission denied to set parameter
> "postgis.gdal_enabled_drivers"
> > ERROR:  rt_raster_to_gdal: Could not load the output GDAL driver
> > CONTEXT:  PL/pgSQL function st_aspng(raster,text[]) line 31 at RETURN
> 
> I figured out that if the executing user has the superuser privilege, it works;
> however, I am not comfortable with this. I wonder whether this is a must.
> 
> 
> test_db=# alter role test_user SUPERUSER;
> 
> --
> 
> test_db=> set postgis.gdal_enabled_drivers = 'ENABLE_ALL'; SET test_db=>
> WITH foo AS (
>      SELECT
> ST_AsPNG(ST_AddBand(ST_AddBand(ST_AddBand(ST_MakeEmptyRaster(2,
> 2, 0, 0, 0.1, -0.1, 0, 0, 4326), 1, '8BUI', 1, 0), 2, '8BUI', 2, 0), 3, '8BUI', 3, 0))
> AS png ), bar AS (
>      SELECT 1 AS rid, ST_FromGDALRaster(png) AS rast FROM foo
>      UNION ALL
>      SELECT 2 AS rid, ST_FromGDALRaster(png, 3310) AS rast FROM foo
> )
> SELECT
>      rid,
>      ST_Metadata(rast) AS metadata,
>      ST_SummaryStats(rast, 1) AS stats1,
>      ST_SummaryStats(rast, 2) AS stats2,
>      ST_SummaryStats(rast, 3) AS stats3
> FROM bar
> ORDER BY rid;
>   rid |         metadata          |    stats1     |    stats2     |     stats3
> -----+---------------------------+---------------+---------------+------
> -----+---------------------------+---------------+---------------+------
> -----+---------------------------+---------------+---------------+----
>     1 | (0,0,2,2,1,-1,0,0,0,3)    | (4,4,1,0,1,1) | (4,8,2,0,2,2) |
> (4,12,3,0,3,3)
>     2 | (0,0,2,2,1,-1,0,0,3310,3) | (4,4,1,0,1,1) | (4,8,2,0,2,2) |
> (4,12,3,0,3,3)
> (2 rows)