Formats disappear after installing postgis_raster

[rhysallister]

Strange. See attached image.

To recreate:

1. Run this container: registry.gitlab.com/rhysallister/elephant:15
2. Connect to database in the container.
3. Execute the following:

CREATE EXTENSION postgis;
CREATE EXTENSION ogr_fdw;
SELECT  cardinality(ogr_fdw_drivers()); -- Returns 79
CREATE TABLE b4 AS SELECT  unnest(ogr_fdw_drivers());
CREATE EXTENSION postgis_raster;
SELECT  cardinality(ogr_fdw_drivers()); -- Returns 64
CREATE TABLE after AS SELECT  unnest(ogr_fdw_drivers());
SELECT * FROM b4 EXCEPT SELECT * FROM after;

-- This last query returns the following:
┌─────────────┐
│   unnest    │
╞═════════════╡
│ MBTiles     │
│ CAD         │
│ PLSCENES    │
│ NGW         │
│ BAG         │
│ OGCAPI      │
│ PDS4        │
│ FITS        │
│ netCDF      │
│ VICAR       │
│ PCIDSK      │
│ HTTP        │
│ JP2OpenJPEG │
│ GPKG        │
│ PDF         │
└─────────────┘
(15 rows)

I don't think my container is that strange, it is based on the Docker Postgres. As shown in the image, dropping the postgis_raster extension does not bring back the formats.

[rhysallister]

Just tried with the official container and the same thing happens.

offical|1|# 🐘SELECT cardinality(ogr_fdw_drivers());
┌─────────────┐
│ cardinality │
╞═════════════╡
│          96 │
└─────────────┘
(1 row)

Time: 1.200 ms
official|1|# 🐘CREATE EXTENSION postgis_raster;
CREATE EXTENSION
Time: 70.973 ms
official|1|# 🐘SELECT cardinality(ogr_fdw_drivers());
┌─────────────┐
│ cardinality │
╞═════════════╡
│          80 │
└─────────────┘

[pramsey]

That is... quite odd. One thing about postgis_raster is that it does try and trim down the available drivers, because for many installations those drivers represent ways to get privilege escalation against the host machine. Now, it seems like there are an awful lot of drivers left after you load raster, so that doesn't quite fit my theory, but it's a reasonable first place to look.

[pramsey]

Good news, this isn't an obscure platform thing, it happens to me locally.

test=# create extension ogr_fdw;
CREATE EXTENSION
test=# SELECT cardinality(ogr_fdw_drivers());
 cardinality 
-------------
          65
(1 row)

test=# create extension postgis;
CREATE EXTENSION
test=# create extension postgis_raster;
CREATE EXTENSION
test=# SELECT cardinality(ogr_fdw_drivers());
 cardinality 
-------------
          52
(1 row)

[pramsey]

So, this is a little hard to explain, but...

• As described above, postgis_raster disables all drivers that are not explicitly allowed in the configuration file. This stops users from hopscotching out of their instance via one of the gdal drivers.
• By default, postgis_raster starts in a mode of DISABLE_ALL. It enforces the disablement of drivers by using the GDAL GDAL_SKIP configuration option to stack all the disallowed drivers into that option at extension load time.
• So, why don't we end up with zero drivers in ogr_fdw_drivers() after enabling postgis_raster?
• It seems that the driver list pulled by postgis_raster, via rt_raster_gdal_drivers() deliberately only pulls drivers that are identified as raster drivers by GDAL.
• Similarly the drivers pulled by ogr_fdw are vector drivers.
• The drivers that "disappear" from ogr_fdw when postgis_raster is installed are drivers that, for some reason, identify as both vector and raster.
• I checked through the disappearing drivers and every one of them showed up on the list of drivers that postgis_raster placed into GDAL_SKIP

So, operationally what does this mean? It means if you want to use any of those raster/vector drivers in ogr_fdw while also having postgis_raster enabled, you'll have to do the configuration step for postgis_raster to add those drivers to the enabled list in the configuration setup.

I do not think there is a more elegant solution or a solution in code to fix this, as the gdal driver purge is very broad brush on purpose to avoid people breaking out of their instances.

[rhysallister]

I reckon it would be good to display this info prominently in the README.